Securing Oracle DB Accounts With Default Passwords

Posted on September 9, 2014 by Neil Johnson

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)

11.2.0.3 Client, Password Expiry and ORA-1017

Posted on September 19, 2012 by Neil Johnson

I have had an intermittent issue at work recently where I am occasionally unable to change my password when it has expired.

From my weapon of choice, DOS prompt/”sqlplus” on Windows, I enter my current password and am prompted to change it due to its expiry. I then enter a new password, one I am confident is consistent with password policy and get a message stating my username/password is incorrect. You can see example output below (and yes I am absolutely certain I entered the same password twice 🙂 ).

u:\sqls>sqlplus neiljohnson@db10g1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 19 09:36:05 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for neiljohnson
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied


Password unchanged
Enter user-name:

I was unable to find a hit in M.O.S and settled on a work-a-round of changing my password on the Unix database server, silently hoping I wasn’t going mad. After a few more occurrences I resorted to Google and via the OTN forums found an M.O.S note detailing the problem.

ORA-1017 Trying To Change Expired Password Through Programmatic Interface From 11.2.0.3 To Lower Database Version [ID 1426233.1]

Below is the suggested solution from this note and I find it out of step with Oracle’s usual stance on these things:

Solution
There are no workarounds available to programatically changing the password, apart from;

a) Use an older client version, or
b) Change the user’s password via an ALTER USER statement from a different session.
c) Upgrade the database version to 11.2.0.3

I’ve always been quite proud to tell my Sysadmin brethren that Oracle clients are backward compatible (and within reason forward compatible too). What we have here is the latest and greatest of the Oracle clients, 11.2.0.3, and a statement that I read as “this is not backward compatible – tough luck!”

I tried to diagnose this further with SQL trace on the Oracle server process but I only capture the insert into sys.aud$. I also tried SQL Net client tracing and in the trace file I can see an error recorded in between the ORA-28001 and ORA-01017. I’ve highlighted it in the extracts from the trace file below.

2012-09-19 10:44:33.304271 : nsbasic_brc:00 00 24 4F 52 41 2D 32  |..$ORA-2|
2012-09-19 10:44:33.304290 : nsbasic_brc:38 30 30 31 3A 20 74 68  |8001:.th|
2012-09-19 10:44:33.304309 : nsbasic_brc:65 20 70 61 73 73 77 6F  |e.passwo|
2012-09-19 10:44:33.304327 : nsbasic_brc:72 64 20 68 61 73 20 65  |rd.has.e|
2012-09-19 10:44:33.304346 : nsbasic_brc:78 70 69 72 65 64 0A     |xpired. |
2012-09-19 10:44:33.304364 : nsbasic_brc:exit: oln=0, dln=69, tot=79, rc=0
2012-09-19 10:44:33.304383 : nioqrc:exit
...
2012-09-19 10:45:59.358748 : nsbasic_brc:exit: oln=11, dln=1, tot=22, rc=0
2012-09-19 10:45:59.359278 : nioqrc: found a break marker...
2012-09-19 10:45:59.359797 : nioqrc: Recieve: returning error: 3111
2012-09-19 10:45:59.360333 : nioqrc:exit
2012-09-19 10:45:59.360834 : nioqrs:entry
...
2012-09-19 10:45:59.406650 : nsbasic_brc:00 00 33 4F 52 41 2D 30  |..3ORA-0|
2012-09-19 10:45:59.407173 : nsbasic_brc:31 30 31 37 3A 20 69 6E  |1017:.in|
2012-09-19 10:45:59.407702 : nsbasic_brc:76 61 6C 69 64 20 75 73  |valid.us|
2012-09-19 10:45:59.408227 : nsbasic_brc:65 72 6E 61 6D 65 2F 70  |ername/p|
2012-09-19 10:45:59.408753 : nsbasic_brc:61 73 73 77 6F 72 64 3B  |assword;|
2012-09-19 10:45:59.409280 : nsbasic_brc:20 6C 6F 67 6F 6E 20 64  |.logon.d|
2012-09-19 10:45:59.409820 : nsbasic_brc:65 6E 69 65 64 0A        |enied.  |

It seems from the M.O.S information below that ORA-03111 is not something I’m going to get very far with, in fact it’s probably a side effect of some other piece of code failing. Either way – it’s back to the work-a-round for me.

Error:  ORA 3111 
Text:   break received on communication channel 
-------------------------------------------------------------------------------
Cause:  A break was processed on the communications channel, but was not 
        properly handled by SQL*Net software.
        This is an internal error message not usually issued.
Action: Contact customer support.

Hopefully this post will save someone else some time if faced with the same issue, sadly I am not currently able to provide a solution.

Changing SYS Password in RAC Databases

Posted on May 22, 2010 by Martin Nash

The post title should really be “Changing SYS Password in RAC Databases when a password reuse limit is in place for SYS and how this has changed from 10.2 to 11.1”, but that would be a bit of a mouthful.

This is the post I was originally working on when I got side tracked by the change in behaviour of profiles assigned to SYS as detailed in “SYS and Password Resource Limits“. At the time I started this I wasn’t aware of the change and was just planning to use SYS passwords as an example of why you should listen to Tom Kyte when he says “… sys is magical, sys is special, sys is governed by a set of rules different from the rest of the world.” He’s right (of course) and the magical powers of SYS are subject to change!

The post is split into pre-11g and 11g or later. We can only assume that the behaviour won’t change back any time soon.

I’ll start with a disclaimer stating that I try to use the “password” command in SQL*Plus wherever possible to avoid displaying passwords in clear text on my screen and I would also need to have a very good reason to put passwords on the command line (OS or SQL*Plus), so the approach used in the examples is purely to make them clear.

Pre-11g

The example shows:

Change password on node 1
Connect to node 1 using new password successfully
Validate instance name
Connect to node 2 using new password unsuccessfully
Connect to node 2 using original password successfully
Attempt to change password on node 2 to new password fails as password reuse is restricted in profile assigned to SYS
Copy and rename password file from node 1 to node 2 (command not shown, but I use scp)
Connect to both nodes using same password successfully

SYS@orcl1> alter user sys identified by temp_pass1;

User altered.

SYS@orcl1> connect sys@orcl1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@orcl2/temp_pass1 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@> connect sys@orcl2/<original password> as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2> alter user sys identified by temp_pass1;
alter user sys identified by temp_pass1
*
ERROR at line 1:
ORA-28007: the password cannot be reused


SYS@orcl2>

Need to copy password file from node 1 and rename at this point (or change profile/resource limit)…

SYS@orcl2> connect sys@kn10st1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@kn10st2/temp_pass1 as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2>

11g or later

The example shows:

Change password on node 1
Connect to node 1 using new password successfully
Validate instance name
Connect to node 2 using new password unsuccessfully
Connect to node 2 using original password successfully
Change password on node 2 to new password successful even though password reuse is restricted in profile assigned to SYS
Connect to both nodes using same password successfully

SYS@orcl1> alter user sys identified by temp_pass1;

User altered.

SYS@orcl1> connect sys@orcl1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@orcl2/temp_pass1 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@> connect sys@orcl2/<original password> as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2> alter user sys identified by temp_pass1;

User altered.

SYS@orcl2> connect sys@orcl1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@orcl2/temp_pass1 as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2>

The key point is that the password for SYS is instance specific. I don’t really understand why Oracle have not implemented something that updates the SYS password file on all nodes of a clustered database, but maybe some reason why this would not be desirable behaviour. If there is then I can’t see it. A friend has suggested that one solution to this would be to use symbolic links for each instance to point to a common password file (on shared storage).

SYS and Password Resource Limits

Posted on May 20, 2010 by Martin Nash

In the process of writing another post I have stumbled into something that has really got me interested. I’ll crack on with the original post soon, but right now I have an uncontrollable urge to share this.

Unless I’m missing something then there has been a fundamental change to the enforcement of “password resource limits” for SYS during the move from 10.2 to 11.1… It might be that this is documented somewhere. I have looked and didn’t find any mention of it. Although, I have to admit that I haven’t read the 11.1 documentation from start to finish, or even the “New Features Guide” in its entirety.

It was quite a while ago that I became aware of the fact that SYS is not subject to password expiry and that this is expected behaviour, as detailed in MOS article ID: 289898.1. This caused me minor concern as it robbed me of a tool to force the hand of the DBA team towards regularly changing the SYS password. But, what I discovered yesterday has potentially serious implications for those of us that care about database security. Please see my findings below…

For the purposes of the example we have 2 users of interest SYS and MARTIN, both have the DEFAULT profile provided by Oracle, with the edition of a password verification function. Watch what happens as you move Oracle version 10.2 to 11.1…

10.2.0.4

[oracle@ora-play ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 20 06:28:57 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select username, profile from dba_users where username in ('SYS','MARTIN');

USERNAME		               PROFILE
------------------------------ ------------------------------
MARTIN			               DEFAULT
SYS                            DEFAULT

SQL> select limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION' and profile = 'DEFAULT';

LIMIT
----------------------------------------
NULL

SQL> @password_function.pls

Function created.

SQL> alter profile default limit password_verify_function password_function;

Profile altered.

SQL> alter user martin identified by simple;
alter user martin identified by simple
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 8


SQL> alter user sys identified by simple;
alter user sys identified by simple
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 8


SQL>

Nothing too radical there. Now moving to 11.1…

11.1.0.7

[oracle@ora-play ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 20 07:00:14 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SYS> select username, profile from dba_users where username in ('SYS','MARTIN');

USERNAME                       PROFILE
------------------------------ ------------------------------
SYS                            DEFAULT
MARTIN                         DEFAULT

SYS> select limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION' and profile = 'DEFAULT';

LIMIT
----------------------------------------
VERIFY_FUNCTION_11G

SYS> @password_function.pls

Function created.

SYS> alter profile default limit password_verify_function password_function;

Profile altered.

SYS> alter user martin identified by simple;
alter user martin identified by simple
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 8


SYS> alter user sys identified by simple;

User altered.

SYS>

SQL> alter profile default limit password_verify_function  password_function;

Profile altered.

SQL> alter user martin identified by simple;
alter user martin identified by simple
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 8


SQL> alter user sys identified by simple;

User altered.

SQL>

^*Note that I replaced the 11g password verification function with my own to be consistent in the testing.

Did you spot that? SYS is not affected by the constraints of the password verification function in 11.1, but is in 10.2. I have confirmed that the 11.1 behaviour is still present at 11.2, in fact I first experienced it there. I’ve tested in both 11.1.0.6 and 11.1.0.7 to confirm the same behaviour in both.

“So what?” I hear some of you say… But, how can I now stand in front of an auditor and say, “All passwords used in our databases comply with the corporate password policy, as enforced by the password verification function.”? OK, there are always going to be ways that someone who wants to set a very simple password can (temporarily changing profile, for example), so there aren’t really any guarantees, but given that setting simple passwords is a “lazy” approach¹, by making it harder to set a simple password than a complex one the “lazy” guy will accept the situation and use a complex password… Right?

I can’t work out why Oracle would choose to do this (although the next post might provide a hit of the reason).

¹ Sorry if that offends anyone.