Password same as Username

If You want to check username have passwords same as login name. e.g. username 'abc123' has password= 'abc123'

Solution:

create or replace function samepwd(username in varchar2, password in varchar2)

return char

authid current_user

is

--

raw_key raw(128):= hextoraw('0123456789ABCDEF');

--

raw_ip raw(128);

pwd_hash varchar2(16);

--

cursor c_user (cp_name in varchar2) is

select password

from sys.user$

where password is not null

and name=cp_name;

--

procedure unicode_str(userpwd in varchar2, unistr out raw)

is

enc_str varchar2(124):='';

tot_len number;

curr_char char(1);

padd_len number;

ch char(1);

mod_len number;

debugp varchar2(256);

begin

tot_len:=length(userpwd);

for i in 1..tot_len loop

curr_char:=substr(userpwd,i,1);

enc_str:=enc_str||chr(0)||curr_char;

end loop;

mod_len:= mod((tot_len*2),8);

if (mod_len = 0) then

padd_len:= 0;

else

padd_len:=8 - mod_len;

end if;

for i in 1..padd_len loop

enc_str:=enc_str||chr(0);

end loop;

unistr:=utl_raw.cast_to_raw(enc_str);

end;

--

function crack (userpwd in raw) return varchar2 

is

enc_raw raw(2048);

--

raw_key2 raw(128);

pwd_hash raw(2048);

--

hexstr varchar2(2048);

len number;

password_hash varchar2(16);

begin

dbms_obfuscation_toolkit.DESEncrypt(input => userpwd, 

       key => raw_key, encrypted_data => enc_raw );

hexstr:=rawtohex(enc_raw);

len:=length(hexstr);

raw_key2:=hextoraw(substr(hexstr,(len-16+1),16));

dbms_obfuscation_toolkit.DESEncrypt(input => userpwd, 

       key => raw_key2, encrypted_data => pwd_hash );

hexstr:=hextoraw(pwd_hash);

len:=length(hexstr);

password_hash:=substr(hexstr,(len-16+1),16);

return(password_hash);

end;

begin

open c_user(upper(username));

fetch c_user into pwd_hash;

close c_user;

unicode_str(upper(username)||upper(password),raw_ip);

if( pwd_hash = crack(raw_ip)) then

return ('Y');

else

return ('N');

end if;

end;

/

set lines 1000

set pages 1000

COLUMN username format A25

COLUMN password format A25

COLUMN account_status format A30

select username, username password,account_status from   dba_users where  samepwd(username, username) = 'Y';

Find Client IP from listener log

Some time we need to know who has made a connection attempt (successful/ unsuccessful ) because listener log is capturing all the connection attempts.

cd $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/<listener name>/trace

ls -ltr 

-rw-rw----. 1 oracle oracle 3733 May 29 10:22 listener.log

cat listener.log | grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' | sort | uniq -c | sort -n

  

if you want to save the screen out to a file:

grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' listener.log | sort | uniq -c | sort -n > listener_unique_ip.txt

Unique Attempt Counts	IP
1	10.1.**.39
4	10.1.**.77
33	10.1.**.76

Based on above result you can implement ACL in sqlnet.ora by tcp.invited_nodes.

AVDF Sowing failed upgrade after installing pre-upgrade package

We installed AVDF 12.2.0.12 pre-upgrade package and rebooted the machine. After the server  reboot it was showing failed upgrade on console and ssh session.

Error:

SYSTEM_STATE=RECOVERY 

INSTALL_STATE=NOT_APPLICABLE

BOOT_STATE=RUNTIME

MIGRATION_SET=POSTRDBMS

MIGRATION_SET_HASH=1000F81C5C6727EF98748CA8CEF2BDA1FFF2B5E855EA86D4AFEFF19AC7EFC206

MIGRATION_SET_STATE=SUCCEEDED

MIGRATION=87

MIGRATION_STATE=SUCCEEDED

MIGRATION_DETAILS=6C61737420286173206E6F626F647929

UPGRADE_STATE=PRE_UPGRADING 

Solution:

The issue is with the pre-upgrade package in place the RPM is going to trigger a recovery state message.

Remove the pre-upgrade package and update /etc/sysconfig/avdf

SYSTEM_STATE="RUNTIME"

UPGRADE_STATE="NOT_APPLICABLE"

If the warning messages is seen again that its in recovery state

/usr/bin/install -m 0644 -o root -g root /dev/null /etc/motd -- will clear the MOTD and avoid false warnings