0591-0593

Previous Table of Contents Next

Page 591

this might be a moot issue. At many sites, the concept of security is taken very seriously. Other sites, especially ones that are not traditional IS organizations, question the necessity. There are three basic reasons for limiting database access: control, protection, and integrity.

Control

Control is a heavy-handed word that evokes images of mainframe shops that required users to file paperwork several times every hour . Although the DBA should not interfere with day-to-day operations and impose undue restrictions, neither should he throw up his hands and cry out, "Why bother?" Unfortunately, either by accident or malicious intent, a database can be easily corrupted by someone who has too much power in the database.

Consider the following scenario: A tablespace needs additional space, and an ambitious user adds a database file to the tablespace instead of calling the DBA. This action goes unnoticed for several weeks. Then the disk drive that contains the database files for this tablespace crashes. The DBA never made changes to the backup script, so there is no way to recover the data.

This is, of course, a doomsday scenario, but it could still happen. In some organizations, every developer is considered a mini-DBA and possesses all the rights and privileges of a DBA. This introduces two key concepts: accountability and responsibility. Accountability means that a user is permitted the authority that comes with being a privileged user. Think of accountability as the list of everyone who has a specified level of access to the database. Responsibility, on the other hand, means that a user has the ultimate duty to keep the database functioning. He is the one who gets telephone calls at 3:00 in the morning.

Control is a tricky issue. It can also be highly political. In a situation with multiple database instances, the manager of each department that has information in the database feels that he needs to have DBA access. This level of access should be limited to people who actually perform DBA functions.

TIP

Determining the level of access and which users are considered privileged happens on a site-by-site basis. It is generally best to give too little access at first and to add privileges as needed.

Protection

Aside from the control issues, limiting database access is one of the most effective methods for protecting the data within a database and the database itself. Although most users would not maliciously harm a database, an accident can be just as devastating as sabotage .

At the operating system level, all database files are owned by the oracle account. Oracle advises against using this account except for performing startups and shutdowns of the database,

Page 592

software installations, and the like. If a delete is issued from the operating system, any of the physical files ”database files, control files, redo logs, and archive logs ”can be removed. This can cause a crash of the database instance. From the database level, users who have access to certain system privileges can modify the database and its objects irrespective of their operating system account ID or who owns the database object. It is far too easy to drop a production table and cripple an entire production system.

Privileges to make modifications to a production database or database objects within a production database should be limited. In most cases, the responsibility for maintaining the database falls on the DBA. There are gray areas, however, such as sites at which maintenance of the database objects is the domain of a project manager or applications manager. In these cases, it is important that the DBA and the other responsible parties work together to control changes to database objects and to ensure that up-to-date build scripts exist. Some shops allow changes to be made to the production database on-the-fly , whereas others choose to implement a change management system that closely regulates changes that are promoted into production.

Integrity

The final consideration for protecting passwords is the integrity of the data within the database objects. Many privileges within the database enable users to manipulate database data indiscriminately. These privileges include system-level privileges such as UPDATE ANY TABLE and DELETE ANY TABLE. With these privileges, a user can override object-level security and modify the data in any database table.

WARNING

Be very careful when granting the ANY class of privileges because they extend to internal database tables and user-owned objects. It is easy to cause database corruption. Avoid using ANY privileges, particularly in a database environment with multiple schemas to which users might not be allowed access to all available schemas.

Making changes to data within database objects is one of the most difficult issues to control. After all, the users must be able to access database data. Oracle provides a means for simplifying this, such as roles. As with controlling access and protecting the physical database itself, protecting the data within the database objects should be of paramount concern when you design and implement a security plan.

Privileged Accounts

Privileged accounts for the Oracle RDBMS take a number of forms. Some are traditional, password-authenticated accounts, whereas others are derived from operating system groups that grant special database privileges. Each site can have its own custom-defined DBA or privileged-access accounts. This discusses users found on all Oracle installations.

Page 593

Operating System Account: oracle

In most installations of Oracle on an operating system such as UNIX, an account is set up called oracle. This is the designated userid that owns the Oracle software. In other words, the installation is run as this user, and the Oracle software files are stored under this user's account. This name is the most common, although some sites (particularly those running multiple versions of Oracle) might create account names called oracle7 or oracle8. Nevertheless, this account has privileges to manipulate Oracle Server files and is able to connect internally to an Oracle database without a password to perform functions such as STARTUP and SHUTDOWN.

Files belonging to the oracle account should be protected through operating system security. On a UNIX operating system, permissions should be set so that only certain groups can access Oracle server files. Server utility executables such as SQL*Plus and Server*Manager should be kept in the ORACLE_HOME directory, ensuring that only approved groups have execute permissions. Only users in the dba, oracle, and oper groups should have access to server utilities such as Server Manager.

Operating System Account: root

This account is the superuser, with the authority to perform any commands or manipulate any files on the operating system. This account is used to create the oracle account, and likewise has the capability to switch user to the oracle account. Switch user is a term that is used on a UNIX operating system to log in as another user while currently logged into the system. This account is also used to adjust operating system parameters to supplement those required by the database.

SYS

The database user SYS is the owner of all base tables, user views, and data dictionary views. At the time of database creation, these tables and views are created through internal mechanisms and scripts that reside in $ORACLE_HOME/rdbms/admin, such as catalog.sql, catproc.sql, and catexp.sql.

Because this owner is the table/view owner and because this account has full system privileges within the database, it should be restricted. Improper use of this account can lead to corruption of the database.

At the time of database creation, the default password for this account is change_on_install. It should be changed immediately. For example:

 % sqlplus sys Password: ................ SQL> alter user sys identified by more_secure_password; 

In general, this account should be used only by the DBA when he changes the core database views and only at the instructions of Oracle. Moreover, no tables other than those created by the Oracle RDBMS should be owned by SYS. Other accounts that can perform the same functions as SYS without the risk to the data dictionary are available.

Previous Table of Contents Next