To make your environment more secure, read more on Oracle Security.
- Oracle Security: Upgrade Implications with User Security
- Password Versions
- Case Sensitive Password files
- Default Users Passwords
- Database Links
Oracle Security has made huge strides in making sure that the data inside your database is secure. One of the biggest changes in this area is making the passwords case sensitive. Although you can disable this and employ the same old case insensitive passwords but the if you opt for the default security settings, passwords will be case sensitive.
In this article we will look at different implications of this new change in database.
Oracle Security: Upgrade Implications with User Security
One of the biggest concerns that most DBAs have is to figure out what will happen if they upgrade a database from a previous version where which had case insensitive passwords. To answer that if you upgrade from a version upgradable to 11g then passwords for all users will stay intact. In other words, they will be accepted as being case insensitive even if you have enabled case sensitivity. However when the password is changed, they will have their passwords as case sensitive.
Same goes for the new strong password feature. Users will have to change their password in order to set a new password that complies with new strong password settings.
Users having the SYSOPER and SYSDBA roles will have their passwords imported to password files as well. Passwords will remain unchanged and password file will be located at $ORACLE_HOME/dbs folder.
The following query will give you the username and password version number of each user of database.
SQL> select username,password_versions
2 from dba_users
3 order by username;
If password version column has a value other than 11g then it means that your password has been imported from a previous version of database. Also it means that your password does not comply with strong password credentials and is case sensitive as well.
To make any user’s password version 11g all you have to do is to change its password from within 11g database using the normal ALTER USER command.
Case Sensitive Password files
If you want to make users in your password file case sensitive then you will have to recreate the password file using the orapwd utility. The utility has an option, ignore case, which you can use to make passwords stored inside password file also case sensitive. You will have to set its value to ‘n’ while creating password file. The default value if ‘y’ which means that by default password file created is case insensitive.
You can use the following command to make case sensitive password file.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=Oracle123 entries=25 ignorecase=n
Default User Password
When creating a database, Oracle creates a lot of default users. These users are created for numerous reasons ranging from administration to samples schema’s. For years, Oracle has a defined set of the same username password combinations for these schema’s. For example the Scott user has always password tiger. This can be an open attack surface for hackers. So it is very important that passwords for these users be changed from the default values. In 11g there is a also a new data dictionary view which lists all users who are still have the default passwords in place.
SQL> select username
order by username;
The usernames which are returned from the above query need your attention. You need to change these user passwords immediately. This view will provide information on both active and inactive users. A more useful query would to be join this view with DBA_USERS view and see which users have default passwords and are active as well.
SQL> select username
where username in
Even more important will be the result of this query. The schema names returned are the one’s having the default passwords and are active as well.
It is worth mentioning that dba_users_with_defpwd view only has information about Oracle supplied schema’s and will not hold information about other users having weak passwords.
You will require the normal ALTER USER command to change the password of supplied users and they will be out of dba_users_with_defpwd view.
Another problem that may be caused by password case sensitivity is when you try to connect from a previous version to 11g. The thing about database links is that all passwords are stored in uppercase. So if you are connecting from 9i, 10g or any previous version then you need to set the user’s password being used in database link to uppercase letters.
However if you are trying to connect from 11g to previous version then you don’t need to worry about that. Because 11gR1 is the first version having case sensitive passwords therefore link from 11g to any previous version will work OK.