Friday, November 21, 2008

10g Migration Ramification Part 2: Making the database a safer place

In Part 2, I will be discussing a couple of the security changes Oracle has made in 10g Release 2 that have affected my life as a DBA. (I can't speak to 10g Release 1 as I pretty much skipped this release altogether). As a whole, these changes point to Oracle's continuing focus on database security, although it could be argued they have a long way to go.

Changes to the DEFAULT profile:
Oracle has made a change to the out-of-the-box setting of FAILED_LOGIN_ATTEMPTS of the DEFAULT profile. In 9i the default setting was UNLIMITED, however, in 10gR2, the profile has been updated to have a value of 10. What the means is that if a user has 10 invalid attempts without a valid loginlogin, the account will be set to the LOCKED(TIMED) status. Since the DEFAULT profile has PASSWORD_LOCK_TIME set to UNLIMITED, the account will never unlock without manual intervention. This makes an out-of-the box 10gR2 database more secure, in theory, but there is one side-effect. If you do not change the setting or create custom profiles with appropriate settings for application accounts, it is quite simple to engage in Denial of Service attacks against the database.

I've seen this change in security cause production issues mainly with third-party applications that don't have the ability to change a user's profile or with internal processes that are used to reset passwords for users. This has caused increased help desk tickets due to locked accounts even after a password reset was completed.

Thankfully, this is pretty easy to solve depending on your business rules.
  1. Alter the default profile to match your company standards. I'm not the biggest fan of this. I"m not sure if Oracle keeps any changes you make to the DEFAULT profile between upgrades. I'll have to test that sometime when moving to 11g.
  2. Create new profiles with your own security settings. To get around certain third-party applications that create their own users, you may need a system trigger to change the profile after a user is created. You may also need to include automatic 'unlock' functionality in the case of a user that has their password changed and is in a LOCKED(TIMED) status. This could alleviate help desk calls.
  3. To deal with the unlock issue, you could also change business processes that reset passwords to also include an unlock of the account. There are a lot of options when it comes to Oracle Security and profiles. Explore all of them.
Changes to default file permissions to the Oracle binaries:
The Oracle 10g software installation on most Unix/Linux platforms has become more secure out of the box. In fact, Oracle has taken it to the opposite extreme. Prior to 9.2.0.8, the Oracle software installation was pretty wide open from a file permission standpoint. Starting somewhere around 9.2.0.8, the ORACLE_HOME has been locked down to only allow access to the owner and group. From a security standpoint this looks to be a good thing, however this can cause slight headaches for applications and developers that use the Oracle client on these platforms. Thankfully, in Oracle 10gR2 there is a script to loosen security on the ORACLE_HOME. changePerm.ksh is located in $ORACLE_HOME/install on most Unix/Linux platforms. You can run it as-is and it will more or less put the permissions back to the way they were in previous versions. There is also the ability to customize what permissions are loosened.

Database Link passwords no longer stored plain text:
This is a wonderful change from previous versions. For far too long, Oracle has been storing database link passwords in the SYS.LINK$ (and exposed via USER_DB_LINKS) plain text. While this has always been a nicety for DBAs to use, it definitely exposes a security hole in the database. In Oracle 10g, the passwords are now stored encrypted (or maybe it is hashed, I forget) so that DBAs and developers alike can not gain access to schema accounts. Even the USER_DB_LINKS view shows no password when logged in and looking at private database links.

What this means to the DBA is a bit of a change in how they move database links. An old trick was to run the following:

SELECT name,password,host FROM sys.link$;

You will also need the user_id to join to user$. The DBA could easily get passwords and recreate database links. Luckily, in 10g, the DBA doesn't need to know passwords. The old trick of using the password hash works just fine for database links as well.

'SELECT name,xpassword,host FROM sys.link$

If you take the output and format a database link create statement like the following, you will have a working database link:

CREATE DATABASE LINK IDENTIFIED BY VALUES '' USING '';

This assumes you are logged on as the owner of the database link.

There are other methods in recreating the database links in 10g. Refer to the Oracle-L thread that occurred recently for more information

I know there are a lot more security changes in Oracle 10g. These are the big ones that I've encountered in migrating from 9i to 10g. Hopefully this will help any small hiccups in your upgrades.