- 16

- Database Security

- Authentication

- Password Authentication
- Operating System Authentication

- Why Protect Passwords?

- Control
- Protection
- Integrity

- Privileged Accounts

- SYS
- SYSTEM
- Operating System Group: dba

- Object Versus System Security

- Object Security

- Access Rights
- The WITH GRANT OPTION Option
- Resolving Object Synonyms

- System Security

- Defined System Privileges

- The WITH ADMIN OPTION Option
- ANY Privileges
- Grants to PUBLIC

- Object Security Model

- Protected Object Ownership Schema
- Capacity Planning Requirements
- Avoiding Tablespace Fragmentation Issues

- Defining Database Roles

- Creating Roles
- Modifying Roles
- Deleting Roles
- Setting and Changing the Default Role
- Password Protecting Roles
- Defining Roles at Operating System Level
- System Privilege Roles

- Database Auditing

- Statement Level Auditing
- Object Level Auditing
- Audit Trail Location

- Database
- Operating System

- Summary

#
16

## Database Security

"Good morning, Jim. Your mission, should you choose to accept it...." So began the television series Mission: Impossible. This chapter begins with that sound bite in a sense of homage. Like the title character in that show, database security is a mission that ultimately the DBA must accept. There are some sites where database security is simply not done"virtually everyone has access to virtually everything. The assumption is that everyone knows what he is doing, so nothing should be restricted. Other sites put a premium on security and regularly use exotic designations, such as B1 and C2 "it takes what seems like an act of Congress for anyone to accomplish anything. Most Oracle sites, though, fall comfortably in the middle.

As the industry "Oracle in particular"moves toward open systems such as UNIX, there is a greater need to configure and monitor an adequate database security plan. Many users do not care a great deal about how the security for the database is configured. Their burning desire is to be able to do their jobs. The ÒpowerÓ users, however, want to push the envelope to see exactly what they can do. They might be motivated by their technical knowledge or even by sheer ego " they think that they should have more power than other users.

Most of the conflict comes from software developers, analysts, and management. In an age when MIS departments are constantly trying to justify their existence against the threat of out-sourcing, it is far simpler for managers to authorize everyone in the organization to have privileged access than to invest the time in a proper setup. Developers and analysts vocally proclaim that they are being handcuffed and cannot do their jobs unless they are given access. In many cases, they simply need information that is contained in a table or a view owned by SYS or SYSTEM " the DBA-level views, for example "that makes them ÒneedÓ DBA access.

Implementing a security plan, therefore, involves theory more than many other topics do, including the backup and recovery plan or user management. If a DBA undertakes a security plan, he accepts responsibility for enforcing the plan and determining the level of security that best suits the needs of his organization. Some organizations have groups of security administrators dedicated to this task. In practice, this becomes yet another task appended to the great DBA Job Description in the Sky.

This chapter focuses on the core ideas associated with implementing a security plan. It offers suggestions for a common security methodology.

### Authentication

As discussed in Chapter 13, "Managing Users," there are two distinct methods of database authentication: password authentication and operating system authentication. With password authentication, the user must enter a unique word or character sequence to gain access. With operating system authentication, the Oracle database permits access to the database based on whether a corresponding user account exists for the current operating system account. When implementing either method, the DBA needs to be aware of various security concerns and constraints.

#### Password Authentication

Password authentication is the more traditional method of user authentication. Although passwords are relatively secure, they have shortcomings that can make them more vulnerable to breach or attack.

In most cases, each user creates his own password. People usually like to keep things simple, which can make password authentication vulnerable to attack. Users who have access to multiple databases often use the same password repeatedly. This creates a massive security breach if a user's password is compromised, enabling the offending party to access all the databases that share the password. Oracle has no mechanism that forces a password change. Oracle8, though, supposedly will offer this feature.

Passwords should conform to the following general standards:

- They should be at least six characters in length.
- They should contain a combination of characters and numbers or special characters that represent the actually spelling of words (such as S1ckle for sickle). Oracle database passwords, however, cannot contain spaces.
- They should not contain obvious passwords.
- They should be changed frequently.

There are only two ways to enforce this level of database password security: user education and third-party products. Neither is an easy solution. A few products on the market, such as SQL<>Secure by BrainTree Technology, give the DBA the latitude to set rules for passwords. Most sites, though, must simply try to educate users about how to set passwords properly. Unless a custom interface is used, it is impossible to prevent users from selecting simple passwords.

A number of public domain programs available on the Internet attempt to break into passwords that use common types of passwords. Using the password standards helps minimize the potential that these programs will break in "although it cannot eliminate the possibility. Some of the more common passwords that programs of this nature scan for are

- Name of user "first, middle, last, or account name
- Name of spouse, children, or pets (more often tried by co-workers or hackers than security programs)
- Colors
- Simple words that many password cracking programs contain in their data dictionaries

This list is by no means exhaustive, but it should provide a basis for judging the effectiveness of a given password. The bottom line on password authentication, however, is training users to use good passwords. Some DBAs use a program that attempts to crack a user's passwords and sends an e-mail to the user indicating that his password has been cracked. This should either induce a sense of paranoia among users or cause them to think seriously about password security.

#### Operating System Authentication

Operating system authentication methods require an underlying password at the operating system level, so it is important to keep in mind the security ramifications of the use of accounts that are authenticated at the operating system level. This means that the Oracle database confirms whether an account "usually prefixed by OPS$" exists within the database that corresponds to an operating system account. In this way, the operating system vouches for the user to the database. If the match is successful, the user is allowed access.

If used properly, operating system authentication is reasonably secure. No passwords need to be changed under this method. Connecting to the database is as simple as issuing a null user name and password. For example,

    sqlplus /

There is a price, however. Anyone with access to the operating system account also has access to the database account. This can take a variety of forms. Aside from someone guessing or learning a user's password, there are other considerations. The system administrator, who has the root password (on a UNIX system), can switch between user accounts without the password. This is generally not a security issue, because the system administrator is considered privileged. Even so, the DBA should be aware of this capability and that the system administrator can switch to other non-root privileged accounts, such as oracle.

Under UNIX, it is possible that a user might access another user's account by means of rlogin. Essentially, a file called .rhosts is optionally located in the user's home directory. This file contains the host machine and the user name, thereby enabling a specified user on a specified machine to perform rlogin without needing the password. For example,

    % hostname

    sandbox

    % whoami

    natashia

    % cd /users/boris

    % ls .rhosts

    .rhosts

    % cat .rhosts

    sandbox natashia

    % rlogin sandbox -l boris

    % whoami

    boris

An account that is authenticated by the operating system is only as secure as the password that protects the operating system account. If proper precautions are taken, though, this method is highly reliable and often more secure than password authentication.

### Why Protect Passwords?

With all the discussion about authentication methods and protecting accounts, it is common to wonder why it is necessary. Depending on the environment in which the database operates, 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 posses 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.

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, 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.

#### 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.

Be very careful when granting the ANY class of privileges, for they extend to internal database tables and user-owned objects. It is easy to cause database corruption.

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.

#### 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.

#### SYSTEM

Like SYS, the SYSTEM account is created when the database is created. Whereas the SYS user owns tables and views that reference internal database information, the SYSTEM account owns tables that are owned by Oracle tools, such as SQL*Menu. This schema is used to install software products for most third-party tools.

SYSTEM has the initial password manager. Many textbooks (such as this one), in fact, show examples that use SYSTEM/MANAGER as the user name and password. As with SYS, this password should be changed immediately after installation.

Although it is generally acceptable to use the SYSTEM account for day-to-day DBA operations, many DBAs do not. The SYSTEM account owns a number of important tables that drive some of the Oracle tools, applications, and so on.

#### Operating System Group: dba

Oracle provides a third method of authentication. It is very specific in nature. It provides certain privileges and must be carefully monitored.

If a user is a member of the dba group, specified in the UNIX /etc/group file, he can do connect internal to the database. This is a privilege level used by the DBA that enables him to connect to the database as SYS with special privileges. The dba group is typically the default group of the user oracle. It is necessary to do connect internal to perform startup and shutdown operations on the database.

WARNING: The umask of the UNIX /etc/group file should be at most 644 "read/write owner, read group, read world"and it should be owned by the root user. If anyone other than root can edit this file, he can set himself up as a privileged database user by placing his UNIX account in the dba group and issuing a connect internal.

A connect internal can be done only from within Oracle Server*Manager or the soon-to-be-obsolete SQL*DBA. This feature is not available from within other tools, such as SQL*Plus or SQL*Forms. As a member of this group, the DBA always has access to the database even if a co-DBA makes changes to the SYS or SYSTEM passwords. Although it is not so designed, the dba group ensures that the DBA can never be locked out of the database. For example,

    % cat /etc/groups

    sys::3:root,uucp

    adm::4:root,adm

    daemon::5:root,daemon

    mail::6:root

    lp::7:root,lp

    tty::10:

    nuucp::11:nuucp

    users::20:

    nogroup:*:-2:

    dba::100:oracle

    % whoami

    oracle

    % svrmgrl

    SVRMGR> connect internal

    Connected.

It is also possible to password protect the internal connections. The specifics on this are covered later in this chapter.

### Object Versus System Security

At the heart of database security lies the concept that someone in a position to grant authority "the DBA"gives a user the specific ability to perform certain operations within the database. These privileges can be granted explicitly by issuing a grant command or implicitly by granting privileges to a role. According to Oracle, privileges fall into two distinct categories: system privileges and object privileges.

#### Object Security

Object security is responsible for defining the specific rights that a user has on a specific database object. The Oracle database itself implements a method of default security. A database user has full privileges on the database object that he owns. This user, in turn, can grant any and all privileges on these objects to another database user. Any user who attempts to access a database object to which he has not been granted access receives an error message. For example,

    % sqlplus susan

    Password: ......

    SQL> select * from cat;

    TABLE_NAME                       TABLE_TYPE

    ------------------------------   -----------

    PAYROLL                          TABLE

    POLICY                           TABLE

    SQL> select count(*) from policy;

      COUNT(*)

    ----------

           100

    SQL> grant select on policy to mike;

    Grant succeeded.

    SQL> connect mike

    Enter password: ......

    Connected.

    SQL> select * from cat;

    TABLE_NAME                       TABLE_TYPE

    ------------------------------   -----------

    POLICY                           TABLE

    SQL> select count(*) from mike.policy;

      COUNT(*)

    ----------

           500

    SQL> select count(*) from susan.policy;

      COUNT(*)

    ----------

           100

    SQL> select count(*) from policy;

      COUNT(*)

    ----------

           500

    SQL> delete from susan.policy;

    ERROR at line 1:

    ORA-01031: insufficient privileges

    SQL> delete from policy;

    500 rows deleted.

It is important to prefix the name of the table with the name of the database user who owns the table "the schema. If this is not done, the database assumes that the table is owned by the current user's account.

##### Access Rights

One of the key aspects of understanding object privileges is to understand what privileges are available to the database user. There are nine Oracle object privileges.

ALTER enables the user to alter a database table, including adjusting storage constraints, adding columns, and performing similar operations.

DELETE enables the user to remove rows from database objects by using the DELETE command from SQL.

EXECUTE enables the user to execute a stored procedure or package within the database.

INDEX enables the user to create a new index or to modify an existing index on a database table.

INSERT enables the user to create new rows in a database table.

REFERENCES enables the user to create a table that references characteristic information, by means of a foreign key, within another table.

SELECT enables the user to view rows of information within a database object.

UPDATE enables the user to modify existing rows within a database object.

ALL gives the user all the previous privileges on a database object.

It is possible to combine multiple database privileges within a single grant option. For example,

    % sqlplus ashley

    Password: ........

    SQL> grant select, insert, update on treat_table to frisko;

    Grant succeeded.

These privileges are valid only for the lifetime of a single database object. Even if a database object is recreated with the same object name, the privileges are not recovered. For example,

    % sqlplus rhett

    Password: ........

    SQL> create table bonnie

      2>    (

      3>    pony_column varchar2(15)

      4>    )

      5> tablespace users;

    Table created.

    SQL> grant select, insert, update on bonnie to scarlett;

    Grant succeeded.

    SQL> connect scarlett

    Enter password: ......

    SQL> select count(*) from rhett.bonnie;

      COUNT(*)

    ----------

             0

    SQL> connect rhett

    Enter password: ........

    Connected.

    SQL> drop table bonnie;

    Table dropped.

    SQL> create table bonnie

      2>    (

      3>    pony_column varchar2(15)

      4>    )

      5> tablespace users;

    Table created.

    SQL> connect scarlett

    Enter password: ......

    Connected.

    SQL> select count(*) from rhett.bonnie;

    Error at line 1:

    ORA-00942: table or view does not exist

RHETT owns the table BONNIE, and he has granted SCARLETT access to the table. If BONNIE is dropped by RHETT and recreated with the same object name, SCARLETT no longer has access to the table. This is true until RHETT makes the grant again. If there were multiple grants, each grant must be made for each user on each database object. This can be quite cumbersome.

##### The WITH GRANT OPTION Option

In many environments, it is beneficial to have users other than the DBA perform grants. For example, the DBA might want to allow a project leader to grant rights on database objects to people working on his project. Thus, the DBA has to do the grants only once; then it falls to the project leader to make further grants as necessary.

Oracle provides a mechanism for doing this: GRANT OPTION of the grant SQL command. With it, a user can issue grant commands just as though he were the actual owner of the database object. For example,

    % sqlplus aimee

    Password: ........

    Connected.

    SQL> grant select on order to jason with grant option;

    Grant succeeded.

    SQL> connect jason

    Enter password: ........

    Connected.

    SQL> grant select on aimee.order to lucinda;

    Grant succeeded.

A database user who has received a grant with the ALL privilege does not receive GRANT OPTION automatically; it must be explicitly granted. As specified with object privileges above, GRANT OPTION remains in effect only until an object is dropped.

SCARLETT owns a view of another database view called GEORGIA. This view is based on a table owned by another user, RHETT, and is called CHARLESTON. SCARLETT must have WITH GRANT OPTION on GEORGIA. If this is not done, grants of this view to other users will not work properly.

##### Resolving Object Synonyms

A synonym is simply a designation for a database object that enables it to be referenced by another name. There are two types of these synonyms: private and public. A private synonym is a synonym created by the user that only he uses; no one other than the user who created the synonym can use it. A public synonym is accessible to all users in the database.

Suppose, for example, that a user, TAL, has a table named HOCKEY for which two synonyms exist"ICE, a private synonym, and SPORT, a public synonym. This gives five distinct methods for accessing this particular table:

- As TAL, using the actual table name, HOCKEY.
- As TAL, using the private synonym name, ICE.
- As TAL, using the public synonym name, SPORT.
- As another user, using the database object owner and object name, TAL.HOCKEY.
- As another user, using the public synonym name, SPORT.

This example assumes that the appropriate grants have been made on the HOCKEY table to permit access. In dealing with synonyms, it is important to understand the order in which the database resolves naming. This is important when you test programs for which there is a global table and a local table. Consider the following SQL statement:

    select * from emp;

When it attempts to resolve this statement, the database first checks whether a database object "such as a table, view, or database link"exists and is owned by the current database user. If it finds a match, it stops. If a match does not exist, it checks for a private synonym that will direct it to a specific database object. If no private synonym exists, it checks for a public synonym that will point it toward an existing database object. If no resolution is found, if the database objects referenced by the synonyms do not exist, or if the user has no privileges on the object in question, an error condition occurs.

#### System Security

Whereas object privilege deals with what a user can do to database objects, system privilege deals with what actions a user can perform against the database itself. The actions include connecting to the database, creating database tables, and dropping an entire tablespace (with all the database objects in it). The functionality of Oracle7 makes the system privileges far more scaleable than in Oracle6.

Under Oracle6, the Oracle RDBMS resembled UNIX in its overall security scheme. UNIX maintains that an account is either the root user or a regular user. Admittedly, UNIX has evolved to enable a greater deal of scalability by using things such as access control lists (ACLs) and root set userid (suid) programs. Oracle6 is set up so that all users are either the DBA or not the DBA. With the release of Oracle7, Oracle moves away from this methodology. It is possible now to grant specific privileges to non-DBA users, thereby enabling them to perform certain applications without giving them full DBA access.

##### Defined System Privileges

In Oracle6, three system privileges are available. Over 80 system privileges are available in Oracle7. The following is a partial list of the database system privileges. The information comes from the Oracle7 Server Administrator's Guide, an excellent reference that describes the capabilities of each privilege.

ALTER DATABASE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLLBACK SEGMENT

ALTER SESSION

ALTER SYSTEM

ALTER TABLESPACE

ALTER USER

AUDIT SYSTEM

BECOME USER

CREATE CLUSTER

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE PROFILE

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SESSION

CREATE SEQUENCE

CREATE SNAPSHOT

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE USER

CREATE VIEW

DROP PROFILE

DROP PUBLIC DATABASE LINK

DROP PUBLIC SYNONYM

DROP ROLLBACK SEGMENT

DROP TABLESPACE

DROP USER

FORCE TRANSACTION

MANAGE TABLESPACE

READUP

RESTRICTED SESSION

UNLIMITED TABLESPACE

Like object privileges, system privileges are given to users through the grant SQL command. The following code segment demonstrates how a system privilege grant is done:

    % sqlplus system

    Password: ........

    Connected.

    SQL> grant create session, alter session to anna;

    Grant succeeded.

The revoke SQL command takes away system or object privileges that were given through the grant command. It is important to note that revoking a privilege does not destroy a database object. In the following example, the table remains even though the privilege to create new tables has been revoked:

    % sqlplus fred

    Password: ......

    Connected.

    SQL> select * from cat;

    TABLE_NAME                       TABLE_TYPE

    ------------------------------   -----------

    SPORTS                           TABLE

    1 rows selected.

    SQL> connect system

    Enter password: ........

    Connected.

    SQL> revoke create table from fred;

    Revoke succeeded.

    SQL> connect fred

    Enter password: ........

    Connected.

    SQL> select * from cat;

    TABLE_NAME                       TABLE_TYPE

    ------------------------------   -----------

    SPORTS                           TABLE

    1 rows selected.

#### The WITH ADMIN OPTION Option

WITH ADMIN OPTION is to database system privileges what WITH GRANT OPTION is to database object privileges. By making a grant WITH ADMIN OPTION, the DBA enables a user to grant the system privilege to another user. For example,

    % sqlplus system

    Password: ........

    SQL> grant create user to helpdesk with admin option;

    Grant succeeded.

#### ANY Privileges

The ANY privileges are a special class of privileges within the database system privileges. They are enhanced system privileges that grant the user the ability to perform specified actions without restrictions. If a user has these system privileges, he can override normal default security. Therefore, he has access to other database objects, regardless of whether an object-level grant is made. The following is a list of the ANY privileges. They are described in detail in the Oracle7 Server Administrator's Guide.

ALTER ANY CLUSTER

ALTER ANY INDEX

ALTER ANY PROCEDURE

ALTER ANY ROLE

ALTER ANY SEQUENCE

ALTER ANY SNAPSHOT

ALTER ANY TABLE

ALTER ANY TRIGGER

BACKUP ANY TABLE

COMMENT ANY TABLE

CREATE ANY CLUSTER

CREATE ANY INDEX

CREATE ANY PROCEDURE

CREATE ANY SEQUENCE

CREATE ANY SNAPSHOT

CREATE ANY SYNONYM

CREATE ANY TABLE

CREATE ANY TRIGGER

CREATE ANY VIEW

DELETE ANY TABLE

DROP ANY CLUSTER

DROP ANY INDEX

DROP ANY PROCEDURE

DROP ANY ROLE

DROP ANY SEQUENCE

DROP ANY SNAPSHOT

DROP ANY SYNONYM

DROP ANY TABLE

DROP ANY TRIGGER

DROP ANY VIEW

EXECUTE ANY PROCEDURE

FORCE ANY TRANSACTION

GRANT ANY PRIVILEGE

GRANT ANY ROLE

INSERT ANY TABLE

LOCK ANY TABLE

SELECT ANY SEQUENCE

SELECT ANY TABLE

UPDATE ANY TABLE

The DBA should be careful when granting system privileges, especially the ANY class of privileges. Some of them are not meant for public use. They put too much power in the hands of users if they are not adequately managed. Although the privileges are more scaleable than under previous Oracle versions, the DBA should treat them as mini-DBA privileges when determining who should receive them.

A final issue regarding protecting system privileges is what effect these privileges have within a secure database. In most databases, some tables contain information that should not be distributed to the general public, such as payroll information. A user with the some of the ANY privileges, such as SELECT ANY or UPDATE ANY, has access to the tables even without an explicit grant.

#### Grants to PUBLIC

It is possible to make grants on both system and object privileges to PUBLIC. This is a special Oracle account to which all other accounts have access. Any grant made to PUBLIC is accessible by any database user. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> grant select on hr.emp_name to public;

    Grant succeeded.

The DBA can use the PUBLIC account to set up a common set of grants "such as the CREATE SESSION privilege, which permits connection to the database" and grant them to PUBLIC. In doing this, the DBA removes the need to make explicit grants to every user when a new account is added.

The DBA can use PUBLIC to lock groups of users out of the database without having to restart the instance in RESTRICT mode. For example, he can grant CREATE SESSION to PUBLIC and make explicit CREATE SESSION grants to key users, such as IS personnel. In the event that activity needs to be done on the database, the following command can be executed from SQL*Plus or Oracle Server*Manager:
revoke create session from public;
This command effectively locks out everyone except the IS users after all the users have logged off the database. The grants can be reinstated as follows:
grant create session to public;

### Object Security Model

The DBA must consider other factors when setting up a security plan. Not only should the setup of the database users be considered, but also the ownership of the database objects. Although there is no right or wrong way to go about this, the following sections outline some of the concerns faced by the DBA when setting up object ownership models.

#### Protected Object Ownership Schema

One security model implemented by many sites is the protected schema "sometimes called the pure schema. Under this model, the DBA sets up an account that is not associated with any specific database user. This account is used as an ownership account for all the database objects"tables, views, and so on. Public synonyms are set up for each database object, and grants are made to each user for each database object. Therefore, a single user owns the objects, but the account can be restricted by not issuing passwords to any users except those who perform database object maintenance.

There is nothing incredibly mystical about this setup. The object owner exists as just another account within the database. Depending on the environment, the DBA can configure the database to have only connection or resource privileges during maintenance windows and then revoke those privileges when completed. Thus, access to the object owner account can be given to other users "who might, for example, want to look at the contents of the CAT or USER_TABLES table" without enabling them to make changes to the database objects themselves.

One important note here is that the DBA or person responsible for database object maintenance should maintain a build script for the object. Although this information can be obtained from the Oracle Data Dictionary, it is important to have this information accessible in emergency situations.

#### Capacity Planning Requirements

In dealing with database objects, one of the key elements for which the DBA is responsible is the capacity planning requirements of the database. Many sites hold to the philosophy that the creation and maintenance of the database objects responsibilities separate from overall database maintenance; most of these sites still agree that capacity planning is a responsibility of the DBA. Everything in the database is stored physically in database files.

Volumes have been written concerning the best ways to optimize the capacity planning of database objects within tablespaces. The main concern of capacity planning in this chapter is on security. Because users other than the DBA might be involved in creating database objects, he should stay abreast of modifications as they occur. For example, it takes only a typographical error in the STORAGE clause to inadvertently fill up a tablespace "1,000K and 10,000K are different by just one zero. When no further space is available to expand the tablespace, this can bring production databases to a screeching halt. By the same token, if the next extent sizes are set wrong forgetting the K in 512K makes it 512 bytes " a database object can quickly reach its MAXEXTENTS. When this happens, the only option is to rebuild the object with proper extent sizes. Depending on how much data is stored, this might be no small feat.

The bottom line on capacity planning requirements from a database security standpoint is to be certain that accountability exists. Object creations should generally be limited to developers or analysts who have the technical knowledge to understand what object creation entails. It should not be necessary to hold anyone's hand. Likewise, they should not be given a blank check.

#### Avoiding Tablespace Fragmentation Issues

Given the prevalence of tools like Defrag by ARIS and TSReorg by Platinum Technology, tablespace fragmentation is an obvious problem for most DBAs. Tablespace fragmentation, illustrated in Figure 16.1, occurs when free space is available in a tablespace, but when the blocks of free space are not group in contiguous blocks. That is, they are not together. Although the amount of fragmentation in Oracle7 is much better than in Oracle6, it remains a persistent problem.

Figure 16.1. Tablespace fragmentation.

Many DBAs might wonder what tablespace fragmentation has to do with database security. The answer is simple: Steps that can be built into the security plan of the database that help minimize some of the main causes of database fragmentation.

Suppose, for example, that a developer calls and complains that he cannot create a new table in tablespace XYZ. Whenever he tries to issue the CREATE TABLE command from SQL, he receives this error message:

    cannot allocate extent of size 99 in tablespace XYZ

A quick check of the view DBA_FREE_SPACE shows the amount of free space available in the tablespace, so it is possible to calculate the total amount of free space capable of holding the table. The first question that the DBA should pose to the developer is, ÒHow often are you dropping tables and indexes?Ó This is the most common cause of tablespace fragmentation, especially for tablespaces to which developers have access. As a rule, developers perform CREATE TABLE/INDEX and DROP TABLE/INDEX operations on a regular basis, which inevitably leads to problems.

It is a good idea to limit or eliminate access to the tablespaces on which production objects reside. You can do this by using tablespace quotas and by not giving anyone other than the protected schema access to the tablespaces. Generally, a special work tablespace called WORK or MISC is created. Developers can perform adds and drops on it. If this tablespace fragments, it can be defragmented at the DBA's discretion. If many people have access to the schema ownership account, it is often a good idea to revoke quotas on the tablespace from the schema until such a time as needed. This depends largely on the user community that is using the account.

By limiting the amount of access to production tablespaces, the DBA can help reduce "although not eliminate" tablespace fragmentation. This helps eliminate the amount of crisis mode management that a DBA must undertake.

### Defining Database Roles

In versions of Oracle prior to Oracle7, explicit grants from the system and the object privilege level are the rule. At first, this was not much of a hindrance because of the size of most databases. Large databases were the exception; small databases were the rule. As databases grew in size, many DBAs began to see how cumbersome and difficult that method of access grants was. For example, in a database system with 40 database tables and views and 100 database users, over 4,000 separate grants must be processed. This is a relatively small database, so it is easy to see the cumulative effects of adding users and objects on larger databases systems. Likewise, if a change in privilege needs to be made or a table is recreated, all the privileges must be made again.

Most DBAs found avenues around this problem by creating SQL scripts or by writing programs that handle the cumbersome grant process. Oracle responded to this problem by providing its user community with database roles.

At the simplest level, roles are simply groups of system or object privileges that can be assigned to database users. Grants on the desired privileges, such as CREATE SESSION or SELECT ON SCOTT.EMP, are made to a role. This role is then granted to a user. The database user has all the privileges that have been assigned to the role. There is no limit to the number of people who can have the role assigned to them, and changes are replicated to all the database users by making a change in the grants to the role.

#### Creating Roles

Aside from defining the privileges necessary for each role and selecting appropriately descriptive names, the process of creating a role is simple. The syntax is similar to creating a database user. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> create role global_mis;

    Role created.

#### Modifying Roles

Making changes to database roles consists of changing the database privileges "object or system" to which the role has access. This is done through standard SQL grant and revoke statements. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> alter role global_mis identified by universe;

    Role altered.

It is not necessary to replicate the change for each database user. Once the grant or revoke is successfully performed on the role, it immediately takes effect for all the database users who have the role assigned to them. There are no special steps or cumbersome processes. In the example database, only 100 grants "the total number of users" must be performed, unless multiple roles are assigned to a single user. For example, if the DBA needs to change access on a table from UPDATE to SELECT, he would issue the appropriate revoke command on the role. The change takes effect on all the database users with that role.

Although it is possible to grant roles to other roles "which is called nesting" there is a limit to the number of active roles that can be in effect for a single user at any one time. This is determined by the value of the INIT.ORA parameter MAX_ENABLED_ROLES. The database does not permit more than the limit set by this value to be enabled.

#### Deleting Roles

The DBA can delete a role from the database. This operation removes information pertaining to the role from all the users and roles currently in existence. As with revoking a system privilege, deleting a role does not affect existing database objects. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> drop role admin;

    Role dropped.

#### Setting and Changing the Default Role

Whenever a role is granted to a user, the privileges within the role do not take effect until the role has been set as the default role. The default role tells the database that it is the role whose privileges are currently being used. The default role determines which database role the user uses when he first connects to the database.. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> alter user amy default role admin;

    Statement processed.

The user or the DBA can also change the current default role. At the discretion of the DBA, every role assigned to a user can be set as the default role. In this way, a user does not have to switch between roles. Instead, he can benefit from the system and object privileges of all of the active roles at a given time without being forced to change default roles each time. For example,

    % sqlplus system

    Password: .......

    Connected.

    SQL> alter user logan default role all;

    Statement processed.

The DBA should carefully evaluate the ramifications of setting all the roles as default roles before he institutes this option.

#### Password Protecting Roles

At some sites, users are grouped into different privilege levels that require a decision before they perform a certain task. Take, for example, a user in an OLTP system who has three roles "ORD_ENTRY, MANAGER, and SUPERUSER. Each level might have different levels of privileges. MANAGER might have SELECT, INSERT, UPDATE, and DELETE capabilities on key tables that are not accessible to ORD_ENTR. MANAGER might lack certain system privileges that belong to SUPERUSER. Whatever the case, the DBA might require a password for each level.

This password helps protect the privilege levels by keeping others out of the role, and it also forces the user to know what role he is currently using. In theory, he would remember which password he most recently entered. Likewise, the password enables the DBA to keep users out of certain privilege groups by changing the password associated with the role.

Switching between roles is the same as switching between roles that have no passwords. The sole difference is that a correct password must be given before Oracle will accept the role change. For example,

    % sqlplus jordan

    Password: ........

    Connected.

    SQL> set role lawyer;

    Enter password: ........

    Statement processed.

#### Defining Roles at Operating System Level

It is possible to grant roles dynamically at the operating system level. For this to take place, a few additional steps must be performed when the roles are created. The benefit is that roles are always identified at the operating system level "in /etc/groups in UNIX, for example. They can easily be changed by someone who has privilege to make modifications" usually the system administrator.

Using operating system authenticated roles should be considered only at sites where the DBA can make changes to the appropriate file. If the DBA lacks this authority, the entire process entails too much overhead to be useful. Grants should be performed at the database level instead.

One of the first steps in creating an operating system authenticated role is to make certain that the parameter OS_ROLES in the INIT.ORA parameter file is set to TRUE. This parameter enables the DBA to enable or disable the use of operating system authenticated roles.

To prevent possible breaches of security, a default role cannot be authenticated at the operating system level when Oracle's Multi-Thread Server is running. If breaches of security are not a concern, you can enable them for multi-thread sites by setting the value of REMOTE_OS_ROLES to TRUE. Consider this carefully.

Operating system authenticated roles, like operating system authenticated accounts, must be created at the database level. As with user accounts, this is done by identifying them EXTERNALLY. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> create role manager identified externally;

    Role created.

Each role to be used by the Oracle database instance must be defined as being identified externally in the database. It must also be defined at the operating system. The role always has the prefix ora_, following by the Oracle SID of the instance and the name of the role. It can also have the suffix d (if it is a default role) or a (if the user has ADMIN OPTION on the role).

In the following example, a role named manager is set up in the norm instance:

    ora_norm_manager_ad:*:512:larry,daryl,o_daryl

The users larry, daryl, and o_daryl have manager as their default role, with ADMIN OPTION on it. Assuming that the role has been created "that is, identified externally" within the database and that the database has been restarted with OS_ROLES set to TRUE, this is all that is required to authenticate an account at the operating system level. Modifying this role is a matter of simply adding a user to the group at the UNIX level. Note that operating system authentication is not available on all platforms.

#### System Privilege Roles

Oracle6 has only three system privileges. The privileges are

- connect, which enables the user to connect to the database
- resource, which enables the user to create objects in database tablespaces
- dba, which gives the user full system rights on the database

Oracle7 provides for backward compatibility by giving combination privileges that simulate the same functionality as their Oracle6 counterparts. It uses roles of system privileges to accomplish this task.

The Oracle7 CONNECT privileges that are assigned to a user by means of system privilege roles are

ALTER SESSION

CREATE CLUSTER

CREATE DATABASE LINK

CREATE SEQUENCE

CREATE SESSION

CREATE SYNONYM

CREATE TABLE

CREATE VIEW

The Oracle7 RESOURCE privileges that are assigned to a user by means of system privilege roles are

CREATE CLUSTER

CREATE PROCEDURE

CREATE SEQUENCE

CREATE TABLE

CREATE TRIGGER

UNLIMITED TABLESPACE

UNLIMITED TABLESPACE is normally not available as part of a role. Oracle enables it specifically to deal with backward compatibility.

The Oracle7 EXP_FULL_DATABASE privileges that are assigned to a user by means of system privilege roles are

SELECT ANY TABLE

BACKUP ANY TABLE

INSERT, UPDATE, and DELETE on SYS.INCEXP

INSERT, UPDATE, and DELETE on SYS.INCVID

INSERT, UPDATE, and DELETE on SYS.INCFIL

The Oracle7 IMP_FULL_DATABASE privileges that are assigned to a user by means of system privilege roles are

BECOME USER

The Oracle7 DBA privileges that are assigned to a user by means of system privilege roles are

ALL PRIVILEGES WITH ADMIN OPTION

EXP_FULL_DATABASE

IMP_FULL_DATABASE

There have been rumors that these system roles have been provided only for backward compatibility with previous versions of Oracle and that future releases will not support them. Although this seems unlikely given the amount of software that that relies on these privileges, you should be aware of it. After all, Oracle undertook drastic changes when it moved from Oracle6 to Oracle7.

### Database Auditing

This section on database auditing is the one that really causes the Mission: Impossible soundtrack to play louder. Auditing gives the DBA the ability to track information within the database. It provides information on who performed a certain operation and when it was performed. This is a powerful security feature of the Oracle RDBMS, but it comes with a price.

Auditing is a reactive function. It gives the DBA information about an activity only after it has already occurred. This reactive information provides a snapshot of what occurred, depending on the level of detail being audited. It gives the DBA a basis for tracking changes within the database.

Because auditing causes additional rows to be added to the database for each operation, it is important to balance the auditing being done against constraints such as performance overhead and physical storage requirements. Unless site-specific reasons require otherwise, the DBA should limit the amount of information being audited. It is not uncommon for DBAs to run continuous high-level audit trails that track which users are connecting to the database, for example. It is much more uncommon for the database to track all SQL statements being issued by all users at all times. As a rule of thumb, the DBA should introduce only lower levels of auditing when he suspects inappropriate activity, and he should be specific about whom the audit is directed against.

To activate auditing for a database instance, the DBA must make certain that the AUDIT_TRAIL parameter of the INIT.ORA parameter file is set to DB or OS to indicate where the audit trail should be written. The default value for this parameter is NONE.

#### Statement Level Auditing

Auditing that occurs at the statement level "sometimes called the privilege level" has a wide scope. With this level of auditing, an audit record is written for each specific SQL statement that is issued. It is possible to limit it to a specific user "such as all CREATE TABLE commands issued by DAVE" or to all users "such as any ALTER TABLE command issued by any user. Depending on how specific the DBA makes this level of auditing, the audit information generated can be substantial.

In the following example, two audit options are set. One option logs CREATE TABLE activity within the database. The other option logs all CREATE SESSION activity done by BETO.

    % sqlplus system

    Password: ........

    Connected.

    SQL> audit create table by access whenever successful;

    Statement processed.

    SQL> audit create session by beto by access whenever successful;

    Statement processed.

Two important parameters appear in every SQL audit command:

- BY SESSION/BY ACCESS
- WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

BY SESSION/BY ACCESS determines how often audit records should be written. In a BY SESSION audit, the database writes a single audit record that sums all the times that an action took place during a given session. In a BY ACCESS audit, the database writes a single audit record for each SQL statement that was issued.

WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL determines the conditions under which the audit records should be written. Audits that are WHENEVER SUCCESSFUL have information written only if they succeed. WHENEVER NOT SUCCESSFUL audits are written only if they do not succeed.

System-level roles can be used to implement auditing, so that only a single SQL statement is required to audit several different operations. Consult the Oracle7 Server Administrator's Guide for more information.

The CLUSTER statement audits:

CREATE CLUSTER

ALTER CLUSTER

DROP CLUSTER

TRUNCATE CLUSTER

The DATABASE LINK statement audits:

CREATE DATABASE LINK

DROP DATABASE LINK

The INDEX statement audits:

CREATE INDEX

ALTER INDEX

DROP INDEX

The EXISTS statement indicates a failure because a value currently exists in the database. This is a feature of Trusted Oracle7 only.

The NOT EXISTS statement indicates a failure because database objects do not exist.

The PROCEDURE statement audits:

CREATE FUNCTION

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

DROP FUNCTION

DROP PACKAGE

DROP PROCEDURE

The PROFILE statement audits:

CREATE PROFILE

ALTER PROFILE

DROP PROFILE

The PUBLIC DATABASE LINK statement audits:

CREATE PUBLIC DATABASE LINK

DROP PUBLIC DATABASE LINK

The PUBLIC SYNONYM statement audits:

CREATE PUBLIC SYNONYM

DROP PUBLIC SYNONYM

The ROLE statement audits:

CREATE ROLE

ALTER ROLE

DROP ROLE

SET ROLE

The ROLLBACK SEGMENT statement audits:

CREATE ROLLBACK SEGMENT

ALTER ROLLBACK SEGMENT

DROP ROLLBACK SEGMENT

The SEQUENCE statement audits:

CREATE SEQUENCE

DROP SEQUENCE

The SESSION statement audits database connections and disconnections.

The SYNONYM statement audits:

CREATE SYNONYM

DROP SYNONYM

The SYSTEM AUDIT statement audits:

AUDIT

NOAUDIT

The SYSTEM GRANT statement audits:

GRANT on system privileges and roles

REVOKE on system privileges and roles

The TABLE statement audits:

CREATE TABLE

DROP TABLE

TRUNCATE TABLE

The TABLESPACE statement audits:

CREATE TABLESPACE

ALTER TABLESPACE

DROP TABLESPACE

The TRIGGER statement audits:

CREATE TRIGGER

ALTER TRIGGER ENABLE/DISABLE

DROP TRIGGER

ALTER TABLE with the ENABLE/DISABLE option

The USER statement audits:

CREATE USER

ALTER USER

DROP USER

The VIEW statement audits:

CREATE VIEW

DROP VIEW

#### Object Level Auditing

It is possible to audit database information at the database object level, which enables you to trap operations done on a specific database object. The syntax is essentially the same as that for a statement level audit:

    % sqlplus system

    Password: ........

    Connected.

    SQL> audit delete on hr.payroll;

    Statement processed.

The statement audit specifies a class of statements and, optionally, which user to audit for these statements. The object audit, on the other hand, points to a type of object operation and the name of an object.

The types of object level operations that can be performed are

ALTER

AUDIT

COMMENT

DELETE

EXECUTE

GRANT

INDEX

INSERT

LOCK

RENAME

SELECT

UPDATE

These object level operations can be performed on any of the following types of database objects:

- Tables
- Views
- Sequences
- Stored procedures, functions, or packages
- Snapshots

Consult the Oracle7 Server Administrator's Guide for more information.

To deactivate object or privilege level auditing, bounce the database and set AUDIT_TRAIL to NONE, or specify the current audit options with the NOAUDIT command. For example,

    % sqlplus system

    Password: ........

    Connected.

    SQL> noaudit all;

    Statement processed.

#### Audit Trail Location

The audit trails from Oracle's AUDIT option can be stored in either the database or the operating system. The location is determined at database startup, based on the value of the INIT.ORA parameter AUDIT_TRAIL.

##### Database

All audit information stored within the database is stored in the table SYS.AUD$, which by default is stored in the SYSTEM tablespace. You should move this table into a separate tablespace to prevent undue fragmentation of SYSTEM.

There are two methods for performing this operation:

- Before you turn on auditing, extract the SQL for this table creation from sql.bsq "do a describe of the table from SQL*Plus to get the exact sizes of the columns. Drop the table and recreate it in the new tablespace. Make sure that all the relevant grants on the table are made.
- Export the table. Generate a copy of the creation SQL by means of the imp utility. Create the table, and import it with the IGNORE=Y flag set.

In an audited database, it is important to make sure that audit trail information is not erased. To prevent that from happening, the DBA should limit the users who can actually write information to this table to SYS. This is difficult to do, however, if users have been granted the DELETE ANY TABLE system privilege. The following code segment illustrates a simple way to trap users who attempt to modify the SYS.AUD$ table:

    % sqlplus system

    Password: ......

    Connected.

    SQL> audit insert, update, delete

     2> on sys.aud$

     3> by access;

    Statement processed.

Although this technique cannot prevent a more experienced user from circumventing auditing, it should keep cursory pilferings at bay, especially if the users lack the ability to modify the audit status.

The database stops processing, however, if the SYS.AUD$ table reaches its maximum capacity. This problem is easily resolved. As a DBA user, do connect internal from Oracle Server*Manager. From Oracle Server*Manager, do truncate sys.aud$. This returns the database to its full operational capacity.

##### Operating System

By directing the database to archive its information at the operating system level, the DBA enables Oracle to store its audit trail information in the same location as the audit information generated by the operating system. This provides a consolidated source of information for DBAs who fill the dual roles DBA and system administrator.

There are a few drawbacks to consider, though. Because the data is no longer in a table, non-database utilities are needed to access it. Likewise, depending on the amount of information being audited, the database can produce double, triple, or even further increase the amount of information that the operating system currently produces.

### Summary

Setting up a security plan for a site can be a challenge for any Oracle DBA. Aside from the development issues involved in defining requirements and developing an overall plan, he is faced with political issues that are often insurmountable in creating the security plan.

It is important to understand that there are two types of privileges: system privileges and object privileges. They are the basis of anything that a user can do while he is connected to an Oracle database. These also form the building blocks of database roles, which provide a means for the DBA to group together sets of similar privileges and to grant them to a single user or a group of users.

The idea of a security policy is not an easy thing to implement, especially in environments in which one has never existed. Even so, it is a crucial piece of the database setup that should not be overlooked.