Securing Oracle DB Accounts With Default Passwords

One view I didn’t know about until recently is DBA_USERS_WITH_DEFPWD. This view appeared in 11g but it obviously passed me by. The reason it cropped up recently was a requirement to ensure that the default accounts in an Oracle database were not left with default passwords, regardless of their account status. In order to achieve this I knocked up a quick snippet of PL/SQL which could be added to automation scripts and therefore tick another box on the audit checklist. The code specifically doesn’t output the passwords to avoid leaving them in log files. I thought it was worth sharing here.

set serveroutput on
begin
  for i in (	select 'alter user '||u.username||' identified by '
                     ||dbms_random.string('a', 10)||'_'||trunc(dbms_random.value(1,99)) cmd
                     , username
                from sys.dba_users_with_defpwd u
                where username <> 'XS$NULL')
  loop
    dbms_output.put_line('Securing '||i.username||'...');
    execute immediate i.cmd;
  end loop;
end;
/

And the output

SQL> set serveroutput on
SQL> begin
  2    for i in (    select 'alter user '||u.username||' identified by '
  3                       ||dbms_random.string('a', 10)||'_'||trunc(dbms_random.value(1,99)) cmd
  4                       , username
  5                  from sys.dba_users_with_defpwd u
  6                  where username <> 'XS$NULL')
  7    loop
  8      dbms_output.put_line('Securing '||i.username||'...');
  9      execute immediate i.cmd;
 10    end loop;
 11  end;
 12  /
Securing GSMUSER...
Securing MDSYS...
Securing OLAPSYS...
Securing LBACSYS...
Securing ORDDATA...
Securing ORDSYS...
Securing DVF...
Securing SYSDG...
Securing APPQOSSYS...
Securing WMSYS...
Securing GSMCATUSER...
Securing OJVMSYS...
Securing SYSTEM...
Securing XDB...
Securing SI_INFORMTN_SCHEMA...
Securing CTXSYS...
Securing ORACLE_OCM...
Securing MDDATA...
Securing ORDPLUGINS...
Securing SPATIAL_CSW_ADMIN_USR...
Securing SPATIAL_WFS_ADMIN_USR...
Securing DVSYS...
Securing DBSNMP...
Securing SYS...
Securing SYSKM...
Securing DIP...
Securing ANONYMOUS...
Securing AUDSYS...
Securing GSMADMIN_INTERNAL...
Securing SYSBACKUP...
Securing OUTLN...

PL/SQL procedure successfully completed.

And a second time, there is nothing to do

SQL> /

PL/SQL procedure successfully completed.

The snippet could be changed to add “ACCOUNT LOCK” if required. Though beware locking SYS on 11.2.0.4 and above:

ORA-28000: The Account Is Locked When Log In As SYS User Remotely While SYS User Was Locked (Doc ID 1601360.1)

Advertisements

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