//
you're reading...
Administration, Security

Securing Oracle Database 11g

In this post we will see the new feature of Oracle Database 11g related to Security Management.

Starting with Oracle Database 11g, you can monitor user accounts with the default passwords. Oracle 11g offers a new view DBA_USERS_WITH_DEFPWD. With the help of this view you get a listing of all user accounts that have default password.

[oracle@ora ~]$ sqlplus dbadmin@O11R1
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Aug 23 03:22:21 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

dbadmin@O11R1>SELECT * FROM v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE  11.1.0.6.0  Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 – Production

dbadmin@O11R1>SELECT * FROM dba_users_with_defpwd;
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
HR
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
SI_INFORMTN_SCHEMA
WMSYS

14 rows selected.

Now if we change the password for user HR to sales:

dbadmin@O11R1>ALTER USER hr IDENTIFIED BY sales;

User altered.

And now query the view

dbadmin@O11R1>SELECT * FROM dba_users_with_defpwd;
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
SI_INFORMTN_SCHEMA
WMSYS

13 rows selected.

We won’t see HR anymore. This makes the task very easy for a DBA to ensure that any user account don’t have default password.

Let’s attention to one more thing that is new to Oracle Database 11g:

Starting with Oracle 11g, password is now case-sensitive.

dbadmin@O11R1>CREATE USER test_user IDENTIFIED BY test_pwd;
User created.

dbadmin@O11R1>GRANT CONNECT TO test_user;
Grant succeeded.

dbadmin@O11R1>CONN TEST_USER/test_pwd
Connected.

dbadmin@O11R1>CONN test_user/TEST_PWD

ERROR:
ORA-01017: invalid username/password; logon denied

But if you want to be with case-insensitive passwords you can use ALTER SYSTEM statement to change SEC_CASE_SENSITIVE_LOGON initialization parameter value to FALSE. The default value is TRUE. This parameter checks whether passwords are case sensitive or not.

Another point to remember is DBA_USERS view. When we query the DBA_USERS view prior to 11g, it shows username column as well as password column containing the HASH values, like this –

dbadmin@PROD01>SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE     10.2.0.1.0             Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

dbadmin@PROD01>SELECT username, password FROM dba_users;
USERNAME                       PASSWORD
------------------------------ ------------------------------
DBADMIN                        8D85436DDC11F738
MGMT_VIEW                      52C55D051831D722
SYS                            8A8F025737A9097A
SYSTEM                         2D594E86F93B17A1
DBSNMP                         FFF45BB2C0C327EC
SYSMAN                         2CA614501F09FCCC
TEST                           7A0F2B316C212D67
OUTLN                          4A3BA55E08595C81
MDSYS                          72979A94BAD2AF80
ORDSYS                         7EFA02EC7EA6B86F
CTXSYS                         71E687F036AD56E5
ANONYMOUS                      anonymous
EXFSYS                         66F4EF5650C20355
DMSYS                          BFBA5A553FD9E28A
WMSYS                          7C9BA362F8314299
.....

22 rows selected.

But in Oracle 11g you will find password column empty.

dbadmin@O11R1>SELECT username, password FROM dba_users;
USERNAME                       PASSWORD
------------------------------ ------------------------------
DBADMIN
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
HR
MDSYS
WK_TEST
CTXSYS
ANONYMOUS
XDB
SCOTT
ORACLE_OCM
TSMSYS
XS$NULL
BI
PM
MDDATA
......

38 rows selected.

Starting with Oracle 11g, the hash value for the password column can be find in the USER$ table.

dbadmin@O11R1>SELECT name, password FROM sys.user$;
NAME                             PASSWORD
------------------------------   ------------------------------
DBSNMP                           00C5EDB40AAC1FA3
APEX_PUBLIC_USER                 084062DA5B2E2B75
SPATIAL_CSW_ADMIN                093913703800E437
WFS_USR_ROLE                     094C14AA84362687
FLOWS_FILES                      0CE415AC5D50F7A1
HR                               7F513C40A9A95C56
PUBLIC
SYSTEM                           106854EB72D09AC4
OWB$CLIENT                       13D492A4459DFE0D
SPATIAL_CSW_ADMIN_USR            1B290858DD14107E
MGMT_VIEW                        1B9878F674F0F59D
RESOURCE
SYS                              26C9FABFD5AA8FCD
WK_TEST                          29802572EB547DBF
....

91 rows selected.

I hope you find this interesting.

You can visit the following links for more information:

11g – The Top Features for DBAs and Developers

ORACLE DATABASE 11g – New Password Protections

Pete Finnigan’s excellent list of Oracle’s Default Password

You can also look at UTLPWDMG.SQL script which is located at ORACLE_HOME/RDBMS/admin for password verification function and complexity.

Advertisements

Discussion

No comments yet.

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

%d bloggers like this: