Thinking Out Loud

September 14, 2017

Assign Pseudo Random Password for Oracle DB Users

Filed under: 12c,oracle — mdinh @ 2:35 am

Due to password verification function, I had to hard code some of  the requirements since I did not want to spend too much time on the matter.

ORA-20024: Password must contain at least 2 lowercase character(s)
ORA-20026: Password must contain at least 1 special character(s)
ORA-20025: Password must contain at least 2 digit(s)

DBMS_RANDOM.STRING opt IN CHAR,len IN NUMBER) RETURN VARCHAR2;

opt:
'u', 'U' - returning string in uppercase alpha characters
'l', 'L' - returning string in lowercase alpha characters
'a', 'A' - returning string in mixed case alpha characters
'x', 'X' - returning string in uppercase alpha-numeric characters
'p', 'P' - returning string in any printable characters.
Otherwise the returning string is in uppercase alpha characters.

len:
Length of the returning string

Modify DB user password:


declare
 pwd varchar2(10);
 l_sql varchar (10000);
begin
for x in (
select username from dba_users where username not in ('XS$NULL') order by 1
)
loop
 pwd := dbms_random.string('X',10);
 l_sql := 'alter user '||x.username||' identified by "ab!'||pwd||'yz90"';
 dbms_output.put_line(l_sql);
 execute immediate l_sql;
end loop;
end;
/

 

Check password modification date.


select name, ctime, ptime from sys.user$
where name in (select username from dba_users)
order by 1; 

 

Advertisements

1 Comment »

  1. […] Pseudo Random Password for Oracle DB […]

    Pingback by Log Buffer #522: A Carnival of the Vanities for DBAs – Cloud Data Architect — September 19, 2017 @ 9:16 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: