Are You Secure? Get the most out of Oracle's security features

In the rapidly shifting world of database technology, one fact has always been, and will always remain, true: a great database is no good if it can easily be broken into. A faulty security plan is not just vulnerable to hackers; it opens your company to data theft, corruption, or even legal action.

Oracle has made the claim of being Unbreakable, and it truly can be with the proper know-how and experience. The built in security of Oracle, both 9i and 10g, is without a doubt some of the best in the industry. Through proper usage, auditing, and documentation of security practices using Oracle's built in tools, you too can possess a truly unbreakable system.

The first step - securing your user base

The fastest database in existence would have one control file, two redo log groups with one member each, and no users.

If that sentence didn't catch your eye the first time, read it again. A database must have users. Without them, there is no profit. But having users connecting to your database presents a whole range of potential problems. You, the DBA, must give privileges to access other objects on the database. An an open database is a hackable one.

There are three levels of users in the average database system:

• Administrators
• Developers
• End users/ Vendors/ Customers/ Viewers

The article you are reading now is stored in a database, and is being delivered by a web application to your computer screen. At this moment, you are an end user. When you go to the bank and use an ATM, you are a user of a database. The act of putting your ATM card into a system and enteriing your PIN is your login to the application. From there, you are a customer and viewer, able to select (view balance), insert (make a transaction), and update (deposit/withdraw).

The main thing that you, the DBA must apply to your users is the principle of least privilege. Here are some ideas for making this principle work:

• Do not give your users more abilities than they need to get the job done
• Revoke unnecessary privileges from the PUBLIC pseudo-user
• Expire and lock unnecessary users

Do not give your users more abilities than they need

This is the foundation of the principle of least privilege. Give your users only the rights they need to do their job, and no more. Do not give DBA to your users just to make life easier on you. Even simple roles such as CONNECT and RESOURCE can pose a security threat to your system. If you are not carefull, a user can easily crash your database using just the CONNECT role.

To comply with auditing standards and to protect your database, you should create separate roles for other DBAs, developers, and other database users. Create your roles according to job roles. If your company has sales clerks and sales managers, create the SALES_CLERK and SALES_MANAGER roles. If you have a junior DBA, create a JRDBA role. This makes it easy to maintain security through changing business rules and employees. It also will help you if you are audited. View the example below:

create role cust_serv_clerk;
grant select on customers to cust_serv_clerk;
grant select, insert, update on issue_track to cust_serv_clerk;

In this example, we create a role called CUST_SERV_CLERK. To this role we grant the ability to select from the CUSTOMERS table, and select, insert, and update the ISSUE_TRACK table. The role now has the privileges of a customer service clerk.

grant cust_serv_clerk to bob;

The above command gave a user named 'bob' the role. Bob now has all of the privileges granted to the role.

create role cust_serv_mgr;
grant cust_serv_clerk to cust_serv_mgr;
grant insert, update, delete on customers to cust_serv_mgr;
grant delete on issue_track to cust_serv_mgr;
grant cust_serv_mgr to mary;

Now we have created a role called CUST_SERV_MGR. To this role we have granted the CUST_SERV_CLERK role. Along with it, we grant the privileges it leaves out: insert, update, and delete on CUSTOMERS, and delete on ISSUE_TRACK. Lastly, we grant this newly created role to the user named 'mary.'

Use roles and privileges to enable your users to do the work they need. However, if you grant too much to your users, it will come back to bite you. Any user has the ability to see the privileges that are granted to them. All it takes is one clever user to figure out how to exploit those privileges - and in the DBA world, a mistake like that could cost you your credibility, if not your job.

Revoke unnecessary privileges from the PUBLIC pseudo-User

In Oracle, there is a pseudo-user named PUBLIC who is everyone and no one. A privilege or role granted to PUBLIC will be given to every user - past, present, and future. Never, under any circumstances, grant privileges to PUBLIC that you do not wish every user to have. Even if you want to do this, make sure there is a pressing business reason for doing so. It is not good enough to argue that it makes life easier for you.

The PUBLIC user comes with several execute privileges on key Oracle packages. Some of these packages include:

• DBMS_RANDOM - Used to generate random numbers, often used as encryption seeds
• DBMS_OBFUSCATION_TOOLKIT - Oracle's encryption toolkit
• UTL_FILE - Utilities to read and write to the file system
• UTL_SMTP - Used to send mail from PL/SQL
• UTL_TCP - Network functionality from PL/SQL

Look again at the descriptions of the above packages. The thought of every database user having these privileges should terrify you. You can very easily revoke privileges from public, and then grant them only to the users that need them - like this:

revoke execute on dbms_obfuscation_toolkit from public;
grant execute on dbms_obfuscation_toolkit to security_user;

To find out what is granted to your PUBLIC user, you can use the following query:

select privilege from dba_sys_privs where grantee = 'PUBLIC';

Be warned that you should NOT revoke every privilege granted to PUBLIC. Look for key packages and use your common sense to find critical ones that should be revoked.

Change passwords, expire, and lock unnecessary users

There are many users on a full installation of Oracle, most of which you probably won't need. To lock a user, you can use the following command:

alter user username account lock;

To unlock the user, simply replace 'lock' with 'unlock.'

Oracle comes with a few default accounts that should never be locked or dropped. These include: SYS, SYSTEM, SYSMAN (Oracle 10g), OUTLN. However, you do want always to change the password for these users. The default password for SYS is change_on_install. It is important that you follow these directions. To change the password for a user:

alter user username identified by new_password;

For non-critical users, you can always lock and expire the account. We can change the password for, lock, and expire the account CTXSYS all at once:

alter user ctxsys identified by 0bscur3 account lock password expire;

This will ensure that CTXSYS password has been changed from the self evident default of CTXSYS; on a successful login the password will have to be changed (because it is expired); and because it is locked, he account can't be logged into under any circumstances.

To aid you in this task, here is a list of users that come with a full database installation that are safe to expire and lock if you are not using their functionality:

• AURORA$ORB$UNAUTHENTICATED - Jserver user
• BI - demo user
• CTXSYS - Oracle Text/interMedia administrator
• DBSNMP - Oracle Intelligent Agent
• DSSYS - Dynamic Services and Syndication Server
• HR - demo user
• MDSYS - Spatial administrator
• ORDSYS/ORDPLUGIN - Object Relational Data user
• OE - demo user
• PERFSTAT - Statspack administrative user
• SCOTT - demo user
• SH - demo user
• TRACESVR - Oracle trace server
• WKSYS - Ultrasearch administrator

The best rule of thumb is to install only the features you need when you install Oracle. If you are not going to use Spatial, interMedia, or UltraSearch, don't install them. Then you will not have to worry about those three users.

Conclusion

While there is more to user management than this article details, these three principles form the foundation of user security. Without them, your database is easily accessible to data theft, corruption, and disruption. Never make the mistake of thinking that hackers would not be interested in breaking into your database. All it takes is one lapse, and you could lose everything you work for.

Tune in next month to read The Second Step - Securing the Oracle Network!