Securing the Database

The remaining chapters of this book discuss how to effectively apply technology features and capabilities to the task of building secure database applications. For this to happen successfully, youll first have to apply the lessons taught in this chapter. Youll need to take certain actions and practice certain behaviors to ensure a good security foundation.

A new Oracle database typically comes installed with over 20 default database schemas (the actual number will vary because some of these schemas are optionally installed during the database creation).

As a Google search on "Default Oracle Users" illustrates, the names, passwords, and privileges of these accounts are anything but secret. These accounts are often used to store metadata and procedures for specific database options, such as the Text Option and the Spatial Option. Consequently, many of these accounts have very significant privileges. They may also have well-known passwords listed both in the Oracle product documentation and on the Internet. This combination creates a risk that an unauthorized person will connect to one of these privileged accounts and access, or manipulate, your sensitive data.

During database creation, you can use the Database Configuration Assistant (DBCA) to choose which default accounts (directly associated with database options) to install. It's important to be selective in your decision about the options you need for your database. Installing options that you'll not be using creates an unnecessary risk.

Keep in mind that commercial applications, as well as Oracle applications, will also have associated and well-known schemas. They all represent targets of opportunity for a hacker. These accounts should also be closely guarded.

While the number of accounts and the associated privileges vary from release to release, it's important to ensure these accounts are secured to limit the risk stated above. This section offers suggestions on how to ensure these accounts are secure. Whether you are securing an Oracle created account, or one that you have created, the process for securing these accounts is the same.

The following suggestions offer ways of controlling access to database accounts. The actions range from restricting logins to the account to removing the account entirely. Combining several of these suggestions together is good practice as it supports a defense in depth approach.

1. Change the default passwords and create a strong password. The DBCA provides a shortcut for creating the initial passwords during the database creation. It allows you to use the same password for all the accounts. Do not choose this option. Create a strong and different password for each schema!
   
2. Create an impossible password. After installation, this little trick, which is covered in the upcoming "Oracle Passwords" section, maintains the account objects and privileges but prevents anyone from directly logging in because the password can't be supplied.
   
3. Create a database log-on trigger to check for specific users that you don't want to log in, and fail the trigger if one tries. A failed log-on trigger prevents a user from logging in. This technique doesn't work for certain privileged users, such as SYS (SYSDBA) and users with the ADMINISTER DATABASE TRIGGER system privilege. Nevertheless, it may be advantageous to only allow these privileged users into the database. As such, this is an excellent little trick for locking out all other users.
   
4. Revoke CREATE SESSION and/or the CONNECT role. Removing the privilege to log in to the database is an obvious way to prevent someone from logging in to an account. Note this will prevent both hackers and legitimate users and applications from logging in to the account. It doesnt matter if they know the password; the privilege to log on has been removed.
   
5. Lock the account. This is a preferred option because it keeps all the data objects and associated procedures while preventing people from logging in. The effect to this is similar to revoking the privilege to log on to the database but no privileges have to be revoked. This capability was introduced with the Oracle9i Database.
   
6. Revoke all privileges and roles. Revoking the schema's privileges allows you to maintain all the existing data while helping to ensure that if the account is compromised, the hacker will not be able to use privileges to access or manipulate data in other schemas. This suggestion is applicable to the schemas that you believe are no longer being used but are reluctant to remove completely. It's particularly useful for the very privileged default schemas installed with the database. Note that revoking privileges could break procedures that are defined within the schema. It's a good idea to capture all the privileges and role grants before revoking them in case you later need to undo this action.
   
7. Drop the schema. You can drop the schemas that you dont need. However, there is significant risk to doing this. Dropping schemas is very destructive. Not only can the user no longer connect, but all of the tables, data, and procedures are gone, too.

For certain database options, such as the Oracle Label Security, theres an officially supported process for removing the option and schema. The Oracle Universal Installer is the best tool for removing already installed database options. Before dropping any Oracle installed schemas, consult the Oracle product documentation to ensure that your removal of the schema is done correctly. Although dropping schemas is the most certain measure you can take to guarantee the account will not be compromised (because it no longer exists), it should be used with caution.

This example illustrates how you might accomplish the task of securing a default account. The following code snippet shows this process as done for the MDSYS schema. MDSYS is the schema that supports the Oracle Spatial technology and as such has been granted access (by way of role privileges) to many powerful procedures and data.

In the default installation, the MDSYS account is locked and the password is expired. This means that the account is made accessible by unlocking the schema and providing the initial password that just happens to be "mdsys." A user with the ALTER USER system privilege only has to unlock the MDSYS account to gain access. Since you may never actually need to log in to this account, there are a couple things you can do to further secure it.

First, revoke the CREATE SESSION privilege and the CONNECT role from MDSYS. You do this because the CONNECT role has been granted the CREATE SESSION privilege, too, so the MDSYS has the privilege twiceonce as a direct grant, and once as an indirect grant received via the CONNECT role. Revoking only the role or only the privilege will not prevent someone from logging in as this user.

After you revoke the privileges, modify the password. The default password is mdsys, which could be easily guessed. In the following example, the privileges are revoked from MDSYS and the password is altered.

sec_mgr@KNOX10g> REVOKE CONNECT, CREATE SESSION FROM MDSYS;
Revoke succeeded.
sec_mgr@KNOX10g> ALTER USER MDSYS IDENTIFIED BY ti1hp2r4m;
User altered.

This process helps to secure the account while still making it usable. That is, the spatial data features can still be used.