1.Default Passwords

Oracle Database 11g now offers a way to quickly identify users with default passwords, implemented in the rather ludicrously simple way of checking a single data dictionary view: DBA_USERS_WITH_DEFPWD. (Note that DBA_ is a standard prefix; it does not contain only DBA users with default passwords.) You can identify these users by issuing:

select * from dba_users_with_defpwd

USERNAME

------------------------------

DIP

MDSYS

WK_TEST

CTXSYS

OLAPSYS

OUTLN

EXFSYS

SCOTT

MDDATA

ORDPLUGINS

ORDSYS

XDB

LBACSYS

SI_INFORMTN_SCHEMA

WMSYS

You can see SCOTT listed above, because his password is TIGER, the default one. Change it with:

SQL> alter user scott identified by tiger1;

User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;

You won't see SCOTT on the list anymore. It's that simple!

2.Case-Sensitive Passwords

In Oracle Database prior to release 11g, user passwords were case insensitive. For example:

SQL> conn scott/tiger

Connected.

SQL> conn scott/TIGER

Connected.

This arrangement presents a problem for standards such as the Payment Card Industry (PCI) Data Security Standard, which require passwords to be case sensitive.

Problem solved; in Oracle Database 11g passwords can be case sensitive as well. While creating the database via DBCA, you will be prompted whether you want to upgrade to the "new security standards," one of which is the case-sensitive password. If you accept, passwords will be recorded in the same case as they were created. Here is the resulting behavior, assuming you have accepted the new standard:

SQL> conn scott/tiger

Connected.

SQL> conn scott/TIGER

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Note how "tiger" and "TIGER" are treated differently.

Now, some of your apps may not be passing the password in proper case right now. A typical example is a user input form: Many forms accept passwords with no case conversion being performed. However, with Oracle Database 11g, that login may fail unless the user enters the password in case-sensitive format or the developer changes the app to convert to upper or lower case (which may not be possible quickly).

If you wish, however, it is still possible to revert to case insensitivity by altering a system parameter, SEC_CASE_SENSITIVE_LOGON, as shown in the example below.

SQL> conn / as sysdba

Connected.

SQL> alter system set sec_case_sensitive_logon = false;

System altered.

SQL> conn scott/TIGER

Connected.

When you upgrade an existing Oracle 10g database to 11g, you can migrate your passwords to the new standard. You can check the status of the password by querying the DBA_USERS view, especially the new column PASSWORD_VERSIONS.

select username, password, password_versions

from dba_users;

USERNAME PASSWORD PASSWORD

------------------------- ------------------------------ --------

SYSTEM 10G 11G

SYS 10G 11G

MGMT_VIEW 10G 11G

The first thing you notice is that the password column is NULL, not populated with the hashed value as it is in Oracle Database 10g and prior versions. So what happened to the password? It's still stored in the database (in the table USER$) but it is not visible in the DBA_USERS view. When the user is created as either global or externally authenticated, the status is indicated?GLOBAL or EXTERNAL?but the hash value of the password is not displayed.

Next, note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or created in 11g directly.

You can enforce, if you wish, the sensitivity of the SYSDBA password as well by entering a new parameter, ignorecase, while creating the password file as shown below:

$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n

In the above example the SYSDBA password will be abc123, not ABC123 or any other variation in case.

The possibility of enforcing a case-sensitive password not only makes it more difficult to crack passwords by brute force, but also enables you to meet many more compliance requirements. Even more important, you can enforce the password requirement dynamically without needing a database shutdown, which comes in handy during upgrades and debugging login issues when upgrading legacy apps.

3.Profiles and Password Verify Function

Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords?for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn't.

In Oracle Database 11g, the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g. At the end, the script has the following lines:

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 1

PASSWORD_VERIFY_FUNCTION verify_function_11G;

The script attaches the function to the profile DEFAULT, which is the default profile for all users, unless something else is explicitly assigned. This makes the authentication compliant with many regulations. All you have to do is run this script to create the 11g version of the password checking function, and the script will enable the password verification feature by attaching itself to the default profile.

4.Improved Out-of-Box Auditing

Auditing is another common pain point. Oracle Database includes powerful auditing features that can be used for tracking user activities. Most people, fearing an I/O contention issue, do not take advantage of them. But the truth is that some auditing can be safely turned on with little risk.

Examples include CREATE SESSION, which writes a record when a session starts and then updates the record when it ends. This audit has minimal impact on I/O but provides powerful benefits.

In Oracle Database 11g, two simple changes have been made to provide an even more powerful auditing solution. First, the database parameter audit_trail is now set to DB by default, not NONE, as it was in previous versions. This allows you to turn on auditing on any object, statement, or privilege without recycling the database.

The second change is more statements have been placed under audit by default. Here is the list:

ALTER SYSTEM

SYSTEM AUDIT

CREATE SESSION

CREATE USER

ALTER USER

DROP USER

ROLE

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

CREATE PUBLIC DATABASE LINK

GRANT ANY ROLE

ALTER DATABASE

CREATE ANY PROCEDURE

ALTER ANY PROCEDURE

DROP ANY PROCEDURE

ALTER PROFILE

DROP PROFILE

GRANT ANY PRIVILEGE

?CREATE ANY LIBRARY

?EXEMPT ACCESS POLICY

?GRANT ANY OBJECT PRIVILEGE

?CREATE ANY JOB

?CREATE EXTERNAL JOB

As you can see, auditing these activities would not cause significant I/O issues, making it possible to maintain some acceptable level of auditing with minimal performance impact.

These two changes create some powerful auditing capabilities out of the box. Of course, they are just database parameters and audit settings; if you want, you can turn them off easily. But if you look at the list of statements, you may actually find them worth auditing, even in development databases. You may want to fine-tune them, however. (For example, in data warehouses, users create and drop a lot of temporary tables so auditing CREATE/DROP TABLE might flood the audit trail.)

For more details on Oracle Security you can view on