Oracle Security Features – A Primer

August 5, 2011 at 3:52 pm | Posted in Oracle, Technical Tips | Leave a comment
Tags: , , , , , ,

As I sit here next to my distinguished colleagues who are working on Microsoft and Cisco practice exams for Transcender, I feel a little bit like an outsider, since I’m the only Oracle expert on the team. Many of their blogs lately have concerned security in the Microsoft and Cisco environments, so not wanting to feel left out, I thought it was appropriate to let you know that Oracle is no slouch when it comes to security.

A small but important subset of the security features that Oracle provides is the security surrounding user access to the database and subsequent access to different objects within the database. Since space and time are limited, I’ve chosen to discuss the top five security features as decided by yours truly. Ladies and Gentlemen (drum roll please), here are:

My 5 favorite security features in Oracle 11g

Number 5: DBA superpowers. In the past few years Oracle has given the DBA a number of additional capabilities for securing database accounts. Accounts can now be locked, passwords can be set to expire (password aging), minimum password length and password complexity can be established, and passwords containing mixed case characters can be enforced. A maximum number of failed login attempts can also be established, and if a user is unable to login after that number of successive attempts, the account will be locked. Password history and the reuse of the same password can also be controlled. You can choose whether passwords should be case sensitive.

You can even query a view called DBA_USERS_WITH_DEFPWD to find if you have any of those default accounts in your database still set with the default password. Do you really want a hacker breaking into your database because she knows that the password for scott is tiger (doesn’t everybody know that?). And, although not a security topic per se, the DBA can insure that users don’t consume an inappropriate amount of CPU resources, both at the individual command level and the session level.

Number 4: The Oracle password file. When properly protected, the password file is a much better way to authenticate remote DBAs than setting the old REMOTE_OS_AUTHENT init.ora parameter equal to TRUE. Relying on a remote client to be responsible for authenticating DBAs, in my mind, is like giving away the keys to the kingdom. The password file is a far superior method because it provides a single repository for authenticating DBAs. This file exists where it should be located, on the database server, and thus prohibits a hacker from impersonating a legitimate DBA and establishing a DBA connection from a client machine.

Side note: There is some confusion regarding the concept of the password file. It exists solely to store the credentials of a person with “super” DBA privileges (called SYSDBA or SYSOPER) who may need to access the database for the purpose of starting it up if it’s down, or shutting it down if it’s up. This is an alternative method for DBAs who may not have been given the traditional O/S authentication privilege to authenticate themselves. It has nothing to do with authenticating ordinary users. Users have only one way to authenticate themselves: by providing a username and password that matches those stored in the data dictionary of the database. And if the database is down – well, they are just out of luck until the DBA brings the database back up.

Number 3: Auditing improvements. Auditing is a feature of Oracle than has been around for quite a while. However, you may be unaware of some upgrades since the days when AUDIT_TRAIL was set to either TRUE or FALSE. Many shops these days, in addition to the traditional use of auditing to check for suspicious activity, are using Oracle’s auditing feature to insure that privacy requirements are being met (HIPAA, for example), that compliance requirements are being met (Sarbanes-Oxley, for example), as well as to gather statistics which are used as the basis for a proposal on a new business opportunity. New options for the initialization parameter AUDIT_TRAIL allow you to do a variety of tasks such as determining where the audit trail is written as well as the level of detail you would like to capture when auditing. And if you want to take the auditing concept to the next level of sophistication, I suggest you brush up on Oracle’s fine-grained auditing (not to be confused with fine-grained access control).

Number 2: Database-side security. When building applications, Oracle 11g provides a lot of flexibility in terms of how you are going to implement security. One option is to handle all of the security in the code of the application, and pretty much ignore the security set up in the database. When that application needs to finally access the database, it makes a connection as a highly privileged user who can do almost anything in the database. But since any user would have to go through the application to get to the database, the application restricts that user to just those database activities that the application deemed appropriate for that type of user.

On the other hand, you could take advantage of all the features of the database to rely on securing the data. This, IMHO, is the better way to go because you aren’t recreating all of the logic that the database already has built in through privileges, roles, virtual private database, and so on. Also, if you don’t rely on the database to perform your security checking, every new application is going to have to be built with all of that logic repeated each time. It’s also very helpful to the developer to use application roles. These can either be default roles that the user acquires at logon, or roles that the user (or the code the user is running) must explicitly request.

For an extra layer of security, you can attach a password with those roles. The user can be prompted for that password as part of the running application. You also have the ability to turn the role on at one point in your code, and then turn it off at another point in your code. The net effect is that the user will only have the privileges contained within that role for the short period of time in the code while the role is turned on. This provides the developer with a whole slew of options in terms of developing highly secure applications.

Number 1: Password customization. My favorite security feature in Oracle 11g is the ability to create your own logic to determine what is and what is not considered to be an acceptable password. If the DBA knows a little PL/SQL, she or he can create some pretty creative rules for users who are attempting to change their password. This feature is implemented by a function you create and name and then assign to a profile. The return value for your function will be a Boolean. If FunctionA is the password function associated with the profile DEFAULT, then any time a user with the DEFAULT profile tries to create or modify a password (for themselves or for others), then FunctionA will automatically run. If that function returns a TRUE, the new password is considered acceptable by Oracle (assuming it’s not violating some other requirement). If that function returns a FALSE, the new password is considered unacceptable by Oracle and the password is NOT changed.

There are three input arguments to the function and their values are automatically populated by the Oracle software. They are the username, new password, and old password of the user whose password is being assigned with the creation of a new account, or whose password is being changed with an existing account. What the function does with those three values, and whether the function returns a TRUE or FALSE, is strictly determined by the logic implemented by the author of the function (you!). For example, since you have access to the values of both new and the old passwords in your code, you could very easily compare them, and based upon whether they were identical, return either a true or false. You could also very easily look at the number of characters in the new password, and if it’s not long enough in your opinion, you could prevent the user from changing it. You can access SYSDATE and prohibit password changes on certain dates or during certain time frames. All of the examples I’ve given could be done with less than a half dozen lines of PL/SQL code. If you want to go crazy, your rule regarding acceptable passwords is only limited by your creativity as a programmer!

Until next time, be secure!

Bob (orcltestguy)

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: