SYS and Password Resource Limits

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” approach1, 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).

1 Sorry if that offends anyone.

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