Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 31

If you’ve already begun creating end user applications in your PHP and Oracle Database XE envi- ronment, you’ve probably revisited the age-old question: do you enforce security at the database or
in your PHP application? In Chapter 14, you explored many authentication methods, one of which includes storing your authentication information in a database table. However, you must also protect the table that has the authentication information. Using Oracle Database XE’s native security features has the key advantage of preventing unauthorized access regardless of the application you use to retrieve and modify data in the database, whether it be a PHP application, a Microsoft Access appli- cation using ODBC, or a dial-in user with SQL*Plus.
In this chapter, we take your security awareness a step further by explaining how to secure your database’s information assets by using the authentication and authorization methods available natively within Oracle Database XE. In addition to covering both authentication and authorization in great detail, we show you how to track user activity once the user has logged into the database by leveraging the many different levels of auditing available in Oracle Database XE.

Security Terminology Overview

To protect one of the most vital assets of a company—its data—DBAs must be keenly aware of how Oracle can protect corporate data from unauthorized access and how to use the different tools they have at their disposal. The Oracle-provided tools and mechanisms fall into three broad categories: authentication, authorization, and auditing.
Authentication includes methods used to identify who is accessing the database, ensuring that
you are who you say you are, regardless of the resources you are requesting from the database. Even if you are merely trying to access the schedule of upcoming campus events, it is important that you iden- tify yourself correctly to the database. If, for example, the PHP-based database application presents customized content based on the user account, you want to make sure you get the campus event menu for your branch office in Troy, Michigan, and not the one for the home office in Schaumburg, Illinois.
Authorization provides access to various objects in the database once you are authenticated by the database. Some users may be authorized to run a report against the daily sales table; some users may be developers and therefore need to create tables and reports, whereas others may only be allowed to see the daily campus event menu. Some users may never log in at all, but their schema may own a number of tables for a particular application, such as payroll or accounts receivable. Additional authorization methods are provided for database administrators, due to the extreme power that a database administrator has. Because a DBA can shut down and start up a database, Oracle provides an additional level of authorization.

535

Authorization goes well beyond simple access to a table or a report; it also includes the rights to use system resources in the database as well as privileges to perform certain actions in the database. A given database user might only be allowed to use 15 seconds of CPU time per session, or can only be idle for 5 minutes before he or she is disconnected from the database. You may grant another database user the privilege to create or drop tables in any other user’s schema, but not to create synonyms or view data dictionary tables. Fine-grained access control (FGAC) gives the DBA more control over how your users access database objects. For example, standard object privileges will either give a user access to an entire row of a table or not at all; using FGAC, a DBA can create a policy implemented by a stored procedure that restricts access based on time of day, where the request originates, which column of the table is being accessed, or all three.
Auditing in an Oracle database encompasses a number of different levels of monitoring in the
database. At a high level, auditing can record both successful and unsuccessful attempts to log in, access an object, or perform an action. As of Oracle9i, not only can fine-grained auditing (FGA) record what objects are accessed, but also what columns of a table are accessed when an insert, an update, or a delete is being performed on the data in the column. FGA is to auditing what FGAC is to standard authorization: more precise control and information about the objects being accessed or actions being performed.
DBAs must use auditing judiciously so as not to be overwhelmed by audit records or create too much overhead by implementing continuous auditing. On the flip side, auditing can help to protect company assets by monitoring who is using what resource at what time and how often, as well as whether the access is successful or not. Therefore, auditing is another tool that the DBA should be using on a continuous basis to monitor the security health of the database.

Security First Steps

All the methodologies presented later in this chapter are useless if access to the operating system is not secure or the physical hardware is not in a secure location. The following list contains a few of the elements outside of the database itself that need to be secure before the database can be consid- ered secure:

Operating system security: Even if the Oracle database is running on its own dedicated hard- ware with only the root and oracle user accounts enabled, operating system security must be reviewed and implemented. Ensure that the software is installed with the root account; the Oracle Database XE installation application will create the oracle user and the dba group (the default group for the oracle user) and change the permissions accordingly. In an advanced security scenario, you may consider using another account instead of oracle, as the owner of the software and the database files, to eliminate an easy target for a hacker. Ensure that the software and the database files are readable only by the oracle account and the dba group. The default installation of Oracle Database XE ensures that this is the case, but it would be a good security task to check on a regular basis that this is still the case.

Turn off the SUID (also known as set UID, or running with root privileges) bit on files that don’t require it. Don’t send passwords (operating system or Oracle) to users via e-mail in plain text. Finally, remove any system services that are not required on the server to support the database, such as telnet and FTP. If your Oracle Database XE installation is on a developer’s workstation, consider dedicating another workstation in your production environment.

This is by no means an exhaustive list. Thoroughly review the security documents for your host operating system to ensure that only authorized users can access your server and Oracle Data- base XE installation.

Securing backup media: Ensure that the database backup media—whether it is tape, disk, or CD/DVD-ROM—is accessible by a limited number of people. A secure operating system and robust, encrypted passwords on the database are of little value if a hacker can obtain backup copies of the database and load them onto another server. The same applies to any server that contains data replicated from your database. You can also encrypt the backups themselves
so that backups that fall into the wrong hands cannot be used. Oracle Database XE supports Transparent Data Encryption to ensure that direct access to the database operating system files, as well as backups of the database operating system files, cannot reveal the contents of encrypted columns without the encryption keys.

Background security checks: Screening of employees that deal with sensitive database data—
whether it is a DBA, an auditor, or an operating system administrator—is a must.

Security education: Ensure that all database users understand the security and usage policies of the IT infrastructure. Requiring that users understand and follow the security policies empha- sizes the critical nature and the value of the data to the company. A well-educated user will be more likely to resist attempts by a hacker using social-engineering skills to access data without proper authorization.

Controlled access to hardware: All computer hardware that houses the database should be located in a secure environment that is accessible only with badges or security access codes.

Understanding Database Authentication

Before the database can allow a person or an application access to objects or privileges, it must authenticate the person or application; in other words, the identity of who is attempting access to the database needs to be validated.
In this section, we first give an overview of the most basic method used to allow access to the database: database authentication. Next, we show you how database administrators authenticate with the database when, for example, the database itself is down and not available to authenticate the administrator.
Other authentication methods, such as network authentication, three-tier authentication,
client-side authentication, and Oracle Identity Management are beyond the scope of this book. See the Oracle online documentation for more information on these advanced authentication methods.

Database Authentication Overview

In an environment where the network is protected with firewalls, and the network traffic between the client and the database server uses some method of encryption, authentication by the database is the most common and easiest method to verify the identify of the user with the database. All infor- mation needed to authenticate the user is stored in a table within the SYSTEM tablespace.
Very special database operations, such as starting up or shutting down the database, require a different and more secure form of authentication, either by using operating system authentication or by using password files.
In the next section, “User Accounts,” we go through all the options available to the DBA for
setting up accounts in the database for authentication.

Database Administrator Authentication

The database is not always available to authenticate a database administrator, such as when the database is down because of an unplanned outage, or for an offline database backup. To address this

situation, Oracle uses a password file to maintain a list of database users who are allowed to perform functions such as starting up and shutting down the database, initiating backups, and so forth.
In this section, we show you how to connect to the database using the password file, as well as create or re-create a password file if the password file is damaged or deleted, or you forgot all of the
administrator passwords!

■Note When using a password file for authentication, ensure that the password file itself is in a directory location
that is only accessible by the operating system administrators and the user or group that owns the Oracle software installation.

Authenticating Using the Password File

We discuss system privileges in more detail later in this chapter in the section “Using System Privi- leges.” For now, though, you need to know that there are two particular system privileges that give administrators special authentication in the database: SYSDBA and SYSOPER. An administrator with the SYSOPER privilege can start up and shut down the database, perform online or offline backups, archive the current redo log files, and connect to the database when it is in RESTRICTED SESSION mode. The SYSDBA privilege contains all the rights of SYSOPER, with the addition of being able to create a database and grant the SYSDBA or SYSOPER privilege to other database users.
To connect to the database from a SQL*Plus or a SQL command-line session, you append AS SYSDBA or AS SYSOPER to your CONNECT command. Here’s an example:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 17 16:11:49 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect rjb/rjb as sysdba
Connected. SQL>
Users who connect as SYSDBA or SYSOPER have additional privileges and the default schema is for these users when they connect to the database. Users who connect with the SYSDBA privilege connect as the SYS user; the SYSOPER privilege sets the user to PUBLIC:

SQL> show user
USER is "SYS"

As with any database connection request, you have the option to specify the username and password on the same line as the sqlplus command, along with the SYSDBA or SYSOPER keyword:

C:\ > sqlplus rjb/rjb as sysdba

Creating or Re-creating the Password File

Although a default installation of Oracle Database XE will automatically create a password file, there are occasions when you may need to re-create one if it is accidentally deleted or damaged. The orapwd command will create a password file with a single entry for the SYS user and other options, as noted, when you run the orapwd command without any options:

C:\> orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

where
file - name of password file (mand), password - password for SYS (mand),
entries - maximum number of distinct DBA and
force - whether to overwrite existing file (opt), OPERs (opt),
There are no spaces around the equal-to (=) character.

C:\>

Once you re-create the password file, you will have to grant the SYSDBA and SYSOPER privileges to

those database users who previously had those privileges. In addition, if the password you provide in the orapwd command is not the same password that the SYS account has in the database, you will have to change the SYS account’s password the next time you are connected to the database so that the password in the database and the password in the password file stay in sync.
The system initialization parameter REMOTE_LOGIN_PASSWORDFILE controls how the password file is used for the database instance. It has three possible values: NONE, SHARED, and EXCLUSIVE.
If the value is NONE, Oracle ignores any password file that exists. Any privileged users must be authenticated by other means, such as by operating system authentication, which is discussed in the next section.
With a value of SHARED, multiple databases can share the same password file, but only the SYS user is authenticated with the password file, and the password for SYS cannot be changed. As a result, this method is not the most secure, but it does allow a DBA to maintain more than one database with
a single SYS account.

■Tip If you must use a shared password file, ensure that the password for SYS is at least eight characters long
and includes a combination of alphabetic, numeric, and special characters to defend against a brute-force attack.

A value of EXCLUSIVE binds the password file to only one database; other database user accounts can exist in the password file. As soon as you create the password file, use this value to maximize the security of SYSDBA or SYSOPER connections.
The dynamic performance view V$PWFILE_USERS lists all the database users who have either
SYSDBA or SYSOPER privileges, as shown here:

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- ----- SYS TRUE TRUE RJB TRUE FALSE SYSTEM TRUE FALSE

SQL>

User Accounts

In order to gain access to the database, a user must provide a username to access the resources asso- ciated with that account. Each username must have a password and is associated with one and only one schema in the database. Some accounts may have no objects in the schema, but instead would have the privileges granted to that account to access objects in other schemas.
In this section, we explain the syntax and give examples for creating, altering, and dropping users. In addition, we show you how to become another user without explicitly knowing the pass- word for the user.

Creating Users

The CREATE USER command is fairly straightforward. It has a number of parameters, which we present in Table 31-1 along with a brief description of each one. The Oracle Database XE GUI (shown in Figure 31-1) provides most of the functionality provided with the CREATE USER command; you only need to use CREATE USER when you need a parameter not available in the GUI, such as QUOTA or PROFILE.

Table 31-1. The Options for the CREATE USER Command

Parameter Definition

username The name of the schema, and therefore the user, to be created. The username can be up to 30 characters long and cannot be a reserved word unless it is quoted (which is not recommended).

IDENTIFIED { BY password |EXTERNALLY | GLOBALLY AS 'extname' }

How the user will be authenticated: by the database with a password, by the operating system (local or remote), or by a service (such as Oracle Internet Directory).

DEFAULT TABLESPACE tablespace The tablespace where permanent objects are created, unless a tablespace is explicitly specified during creation.

TEMPORARY TABLESPACE tablespace The tablespace where temporary segments are created during sort operations, index creation, and so forth.

QUOTA { size | UNLIMITED } ON tablespace

The amount of space allowed for objects created on the specified tablespace. Size is in kilobytes or megabytes.

PROFILE profile The profile assigned to this user. Profiles are discussed later in this chapter. If a profile is not specified, the DEFAULT profile is used.

PASSWORD EXPIRE The prompt for the user to change his password at first logon.

ACCOUNT {LOCK | UNLOCK} The state of the account: locked or unlocked. By default, the account is unlocked.

In the following example, we create a user (KELLYC) to correspond with the user Kelly Chung, employee number 188 in the HR.EMPLOYEES table from the sample schemas installed with Oracle Database XE:
SQL> create user kellyc identified by kellyc311
2 account unlock
3 default tablespace users
4 temporary tablespace temp;

User created.

SQL>

The user KELLYC is authenticated by the database with an initial password of KELLYC311. The

second line is not required; all accounts are unlocked by default. Both the default permanent tablespace and the default temporary tablespace are defined at the database level, so the last two lines of the command aren’t required unless you want a different default permanent tablespace or a different temporary tablespace for the user.
Even though the user KELLYC has been either explicitly or implicitly assigned a default permanent
tablespace, she cannot create any objects in the database until we provide both a quota and the rights to create objects in their own schema.
A quota is simply a space limit, by tablespace, for a given user. Unless a quota is explicitly assigned or the user is granted the UNLIMITED TABLESPACE privilege (privileges are discussed later in this chapter in the section “Using Object Privileges”), the user cannot create objects in her own schema. In the following example, we give the KELLYC account a quota of 250MB in the USERS tablespace:

SQL> alter user kellyc quota 250M on users; User altered.
Note that we could have granted this quota at the time the account was created, along with almost every other option in the CREATE USER command. A default role, however, can only be assigned after the account is created. (We discuss role management later in this chapter in the section “Creating, Assigning, and Maintaining Roles.”)
Unless we grant some basic privileges to a new account, the account cannot even log in; there- fore, we need to grant at least the CREATE SESSION privilege or the CONNECT role (roles are discussed in detail in the “Creating, Assigning, and Maintaining Roles” section). The CONNECT role contains the CREATE SESSION privilege, along with other basic privileges, such as CREATE TABLE and ALTER SESSION. In the following example, we grant KELLYC the CONNECT privilege:
SQL> grant connect to kellyc; Grant succeeded.
Now the user KELLYC has a quota on the USERS tablespace as well as the privileges to create objects in that tablespace.
All these options for CREATE USER are available in the Oracle Database XE Web-based interface, as you can see in Figure 31-1.

Figure 31-1. Creating users with the Oracle Database XE GUI

Altering Users

Changing the characteristics of a user is accomplished by using the ALTER USER command. The syntax for ALTER USER is nearly identical to that of CREATE USER, except that ALTER USER allows you to assign roles as well as grant rights to a middle-tier application to perform functions on behalf of the user.
In this example, we change user KELLYC to use a different default permanent tablespace:

SQL> alter user kellyc
2 default tablespace php_apps quota 500m on php_apps;

User altered.

SQL>

Note that the user KELLYC still can create objects in the USERS tablespace, but she must explicitly

specify USERS in any CREATE TABLE and CREATE INDEX commands.

Dropping Users

Dropping users is very straightforward and is accomplished with the DROP USER command. The only parameters are the username to be dropped and the CASCADE option; any objects owned by the user must be explicitly dropped or moved to another schema if the CASCADE option is not used. In the following example, the user QUEENB is dropped, and if there are any objects owned by QUEENB, they are automatically dropped as well:

SQL> drop user queenb cascade; User dropped.
If any other schema objects, such as views or packages, rely on objects dropped when the user is dropped, the other schema objects are marked INVALID and must be recoded to use other objects and then recompiled. In addition, any object privileges that were granted by the first user to a second user via the WITH GRANT OPTION clause are automatically revoked from the second user if the first user is dropped.

Becoming Another User

To debug an application, a DBA sometimes needs to connect as another user to simulate the problem. Without knowing the actual plain-text password of the user, the DBA can retrieve the encrypted password from the database, change the password for the user, connect with the changed password, and then change back the password using an undocumented clause of the ALTER USER command. It is assumed that the DBA has access to the DBA_USERS table, along with the ALTER USER privilege. If the DBA has the DBA role, these two conditions are satisfied.
The first step is to retrieve the encrypted password for the user, which is stored in the table
DBA_USERS:

SQL> select password from dba_users where username = 'KELLYC';

PASSWORD
------------------------------ E18FBF6B825235F2

SQL>

Save this password using cut and paste in a GUI environment, or save it in a text file to retrieve

later. The next step is to temporarily change the user’s password and then log in using the temporary password:
SQL> alter user kellyc identified by temppass; User altered.
SQL> connect kellyc/temppass; Connected.

At this point, you can debug the application from KELLYC’s point of view. Once you are done debugging, change the password back using the undocumented by values clause of ALTER USER:

SQL> alter user kellyc identified by values 'E18FBF6B825235F2'; User altered.

User-Related Data Dictionary Views

A number of data dictionary views contain information related to users and characteristics of users. Table 31-2 lists the most common views and tables.

Table 31-2. User-Related Data Dictionary Views and Tables

Data Dictionary View Description

DBA_USERS Usernames, encrypted passwords, account status, and default tablespaces.

DBA_TS_QUOTAS Disk space usage and limits by user and tablespace, for users who have quotas that are not UNLIMITED.

DBA_PROFILES Profiles that can be assigned to users with resource limits assigned to the profiles.

USER_HISTORY$ Password history with usernames, encrypted passwords, and date stamps. Used to enforce password reuse rules.

Understanding Database Authorization Methods Once a user is authenticated with the database, the next step is to determine what types of objects, privileges, and resources the user is permitted to access or use. In this section, we review how profiles can control not only how passwords are managed but also how profiles can put limits on various
types of system resources.
In addition, we review the two types of privileges in an Oracle database: system privileges and object privileges. You can assign both of these privileges directly to users, or indirectly through roles, another mechanism that can make a DBA’s job easier when assigning privileges to users.

Profile Management

There never seems to be enough CPU power or disk space or I/O bandwidth to run a user’s query. Because all these resources are inherently limited, Oracle provides a mechanism to control how much of these resources a user can use. An Oracle profile is a named set of resource limits providing this mechanism.
In addition, you can use profiles as an authorization mechanism to control how user passwords are created, reused, and validated. For example, you may wish to enforce a minimum password length, along with a requirement that at least one uppercase and lowercase letter appear in the pass- word. In this section, we talk about how profiles manage passwords and resources.
For your installation of Oracle Database XE, you may have only one user or a handful of users; most
likely, you will not need to use profiles at all. If you are using Oracle Database XE as a development tool, or you are the only user, profiles will not be that useful until you start using Oracle Database XE as a production database, or more likely, when you migrate the application to another version of Oracle Database.

The CREATE PROFILE Command

The CREATE PROFILE command controls many different restrictions and user resources. First, you can create a profile to limit the connect time for a user to 120 minutes:

create profile lim_connect limit connect_time 120;
Similarly, you can limit the number of consecutive times a login can fail before the account is locked:

create profile lim_fail_login limit failed_login_attempts 8;

Or you can combine both types of limits in a single profile:

create profile lim_connectime_faillog limit connect_time 120
failed_login_attempts 8;

How Oracle responds to a resource limit being exceeded depends on the type of limit. When one of the connect time or idle time limits is reached (such as CPU_PER_SESSION), the transaction in progress is rolled back and the session is disconnected. For most other resource limits (such as PRIVATE_SGA), the current statement is rolled back, an error is returned to the user, and the user has the option to commit or roll back the rest of the transaction. If an operation exceeds a limit for a single call (such as LOGICAL_READS_PER_CALL), the operation is aborted, the current statement is rolled back, and an error is returned to the user. The rest of the transaction remains intact. The user can then roll back, commit, or attempt to complete the transaction without exceeding statement limits.
Oracle provides the DEFAULT profile, which is applied to any new user if no other profile is specified. This query against the data dictionary view DBA_PROFILES reveals the limits for the DEFAULT profile:

SQL> select * from dba_profiles where profile = 'DEFAULT';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
--------------- -------------------------- --------------- ------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

16 rows selected.

The only real restriction in the DEFAULT profile is the number of consecutive unsuccessful login attempts before the account is locked, which is ten. In addition, no password verification function is enabled.

Profiles and Password Control

Table 31-3 shows the password-related profile parameters. All units of time are specified in days
(e.g., to specify any of these parameters in minutes, divide by 1440):

SQL> create profile lim_lock limit password_lock_time 5/1440; Profile created.

In this example, an account will only be locked for five minutes after the specified number of login failures.

Table 31-3. Password-Related Profile Parameters

Password Parameter Description

FAILED_LOGIN_ATTEMPTS The number of failed login attempts before the account is locked.

PASSWORD_LIFE_TIME The number of days the password can be used before it must be changed. If it is not changed within PASSWORD_GRACE_TIME, the password must be changed before logins are allowed.

PASSWORD_REUSE_TIME The number of days a user must wait before reusing a password;
this parameter is used in conjunction with PASSWORD_REUSE_MAX.

PASSWORD_REUSE_MAX The number of password changes that have to occur before a password can be reused; this parameter is used in conjunction with PASSWORD_REUSE_TIME.

PASSWORD_LOCK_TIME The number of days the account is locked after FAILED_LOGIN_ ATTEMPTS attempts. After this time period, the account is auto- matically unlocked.

PASSWORD_GRACE_TIME The number of days after which an expired password must be changed. If it is not changed within this time period, the account is expired and the password must be changed before the user can log in successfully.

PASSWORD_VERIFY_FUNCTION A PL/SQL script to provide an advanced password-verification routine. If NULL is specified (the default), no password verifica- tion is performed.

A parameter value of UNLIMITED means that there is no limit on how much of the given resource can be used. DEFAULT means that this parameter takes its values from the DEFAULT profile.
The parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be used together; setting one without the other has no useful effect. In the following example, we create a profile that sets PASSWORD_REUSE_TIME to 20 days and PASSWORD_REUSE_MAX to 5:
create profile lim_reuse_pass limit password_reuse_time 20 password_reuse_max 5;

Users with this profile can reuse their passwords after 20 days if the password has been changed at least five times. If you specify a value for PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX, and UNLIMITED for the other, a user can never reuse a password.
If you want to provide tighter control over how passwords are created and reused, such as using a mixture of uppercase and lowercase characters in every password, you need to enable the PASSWORD_VERIFY_FUNCTION limit in each applicable profile. Oracle provides a template for enforcing an organization’s password policy. It’s located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. The script provides the following functionality for password complexity:

• Ensures that the password is not the same as the username

• Ensures that the password is at least four characters long

• Checks to make sure the password is not a simple, obvious word, such as ORACLE or DATABASE

• Requires that the password contains one letter, one digit, and one punctuation mark

• Ensures that the password is different from the previous password by at least three characters

To use this policy, the first step is to make your own custom changes to this script. For example, you may wish to have several different verify functions, one for each country or business unit, to match the database password complexity requirements to that of the operating systems in use in a particular country or business unit. Therefore, you can rename this function as VERIFY_FUNCTION_US_ WESTCOAST, for example. In addition, you might want to change the list of simple words to include names of departments or buildings at your company.

Profiles and Resource Control

The list of resource-control profile options that can appear after CREATE PROFILE profilename LIMIT
are explained in Table 31-4. Each of these parameters can either be an integer, UNLIMITED, or DEFAULT.

Table 31-4. Resource-Related Profile Parameters

Resource Parameter Description

SESSIONS_PER_USER Maximum number of sessions a user can simultaneously have

CPU_PER_SESSION Maximum CPU time allowed per session, in hundredths of a second

CPU_PER_CALL Maximum CPU time for a statement parse, execute, or fetch operation, in hundredths of a second

CONNECT_TIME Maximum total elapsed time, in minutes

IDLE_TIME Maximum continuous inactive time in a session, in minutes, while a query or other operation is not in progress

LOGICAL_READS_PER_SESSION Total number of data blocks read per session, either from memory or disk

LOGICAL_READS_PER_CALL Maximum number of data blocks read for a statement parse, execute, or fetch operation

COMPOSITE_LIMIT Total resource cost, in service units, computed as a composite weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_ READS_PER_SESSION, and PRIVATE_SGA

PRIVATE_SGA Maximum amount of memory a session can allocate in the shared pool, in bytes, kilobytes, or megabytes

As with the password-related parameters, UNLIMITED means that there is no bound on how much of the given resource can be used. DEFAULT means that this parameter takes its values from the DEFAULT profile.
The COMPOSITE_LIMIT parameter allows you to control a group of resource limits when the types of resources typically used varies widely by type; it allows a user to use a lot of CPU time but not much disk I/O during one session, and vice versa, during another session, without being disconnected by the policy.
By default, all resource costs are zero:

SQL> select * from resource_cost;

RESOURCE_NAME UNIT_COST
-------------------------------- ---------- CPU_PER_SESSION 0
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 0
PRIVATE_SGA 0
4 rows selected.

To adjust the resource cost weights, use the ALTER RESOURCE COST command. In this example, we change the weightings so that CPU_PER_SESSION favors CPU usage over connect time by a factor of
25 to 1; in other words, a user will be disconnected more likely because of CPU usage than because of connect time:

SQL> alter resource cost
2 cpu_per_session 50
3 connect_time 2; Resource cost altered.
SQL> select * from resource_cost;

RESOURCE_NAME UNIT_COST
-------------------------------- ---------- CPU_PER_SESSION 50
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 2
PRIVATE_SGA 0
4 rows selected.

The next step is to create a new profile or modify an existing profile to use a composite limit:

SQL> create profile lim_comp_cpu_conn limit
2 composite_limit 250; Profile created.
As a result, users assigned to the profile LIM_COMP_CPU_CONN will have their session resources limited using the following formula to calculate cost:

composite_cost = (50 * CPU_PER_SESSION) + (2 * CONNECT_TIME);

In Table 31-5, we provide some examples of resource usage to see if the composite limit of 250 is exceeded.

Table 31-5. Resource Usage Scenarios

CPU (Seconds) Connect (Seconds) Composite Cost Exceeded?
0.05 100 (50 * 5) + (2 * 100) = 450 Yes
0.02 30 (50 * 2) + (2 * 30) = 160 No
0.01 150 (50 * 1) + (2 * 150) = 350 Yes
0.02 5 (50 * 2) + (2 * 5) = 110 No

The parameters PRIVATE_SGA and LOGICAL_READS_PER_SESSION are not used in this particular example, so unless they are specified otherwise in the profile definition, they default to whatever their value is in the DEFAULT profile. The goal of using composite limits is to give users some leeway in the types of queries or DML they run. On some days, they may run a lot of queries that perform numerous calculations but don’t access a lot of table rows; on other days, they may do a lot of full table scans but don’t stay connected very long. In these situations, you don’t want to limit a user by a single parameter, but instead by total resource usage weighted by the availability of each resource on the server.

Using System Privileges

A system privilege is a right to perform an action on any object in the database, as well as other actions that do not involve objects at all but rather such tasks as running batch jobs, altering system parameters, creating roles, and even connecting to the database itself. There are 166 system privi- leges in Oracle Database XE. You can find them in the data dictionary table SYSTEM_PRIVILEGE_MAP.
Table 31-6 lists some of the more common system privileges, along with a brief description of each.

Table 31-6. Common System Privileges

System Privilege Capability

ALTER DATABASE Make changes to the database, such as changing the state of the database from MOUNT to OPEN, or recovering a database.

ALTER SYSTEM Issue ALTER SYSTEM statements: switch to the next redo log group and change system-initialization parameters in the SPFILE.

AUDIT SYSTEM Issue AUDIT statements.

CREATE DATABASE LINK Create database links to remote databases.

CREATE ANY INDEX Create an index in any schema; CREATE INDEX is granted along with
CREATE TABLE for the user’s schema.

CREATE PROFILE Create a resource/password profile.

CREATE PROCEDURE Create a function, procedure, or package in your own schema. CREATE ANY PROCEDURE Create a function, procedure, or package in any schema. CREATE SESSION Connect to the database.
CREATE SYNONYM Create a private synonym in your own schema. CREATE ANY SYNONYM Create a private synonym in any schema. CREATE PUBLIC SYNONYM Create a public synonym.
DROP ANY SYNONYM Drop a private synonym in any schema.

DROP PUBLIC SYNONYM Drop a public synonym.

CREATE TABLE Create a table in your own schema.

CREATE ANY TABLE Create a table in any schema.

CREATE TABLESPACE Create a new tablespace in the database.

CREATE USER Create a user account/schema.

ALTER USER Make changes to a user account/schema.

Table 31-6. Common System Privileges (Continued)

System Privilege Capability

CREATE VIEW Create a view in your own schema.

SYSDBA Create an entry in the external password file, if enabled; also, perform startup/shutdown, alter a database, create a database, recover a database, create an SPFILE, and connect when the database is in RESTRICTED SESSION mode.

SYSOPER Create an entry in the external password file, if enabled; also, perform startup/shutdown, alter a database, recover a database, create an SPFILE, and connect when the database is in RESTRICTED SESSION mode.

Granting System Privileges

You grant privileges to a user, role, or PUBLIC using the GRANT command. Privileges are revoked using the REVOKE command. PUBLIC is a special group that includes all database users, and it’s convenient shorthand for granting privileges to everyone in the database.
To grant the user KELLYC the ability to create stored procedures and synonyms, you can use a command like the following:

SQL> grant create procedure, create synonym to kellyc; Grant succeeded.
Revoking privileges is just as easy:

SQL> revoke create synonym from kellyc; Revoke succeeded.
If you wish to allow grantees the right to grant the same privilege to someone else, you include WITH ADMIN OPTION when you grant the privilege. In the preceding example, you want the user KELLYC to be able to grant the CREATE PROCEDURE privilege to other users. To accomplish this, you need to regrant the CREATE PROCEDURE privilege:
SQL> grant create procedure to kellyc with admin option; Grant succeeded.

Now KELLYC may in turn issue the GRANT CREATE PROCEDURE command to another user. Note that if KELLYC’s permission to grant her privileges to others is revoked, the users she has granted privileges to retain the privileges.

System Privilege Data Dictionary Views

Table 31-7 contains the data dictionary views related to system privileges.

Table 31-7. System Privilege Data Dictionary Views

Data Dictionary View Description
DBA_SYS_PRIVS System privileges assigned to roles and users

SESSION_PRIVS All system privileges in effect for this user for the session, granted directly or via a role

ROLE_SYS_PRIVS Current session privileges granted to a user via a role

Using Object Privileges

In contrast to a system privilege, an object privilege is a right to perform a particular type of action on a specific object, such as a table or a sequence that is not in the user’s own schema. As with system privileges, you use the GRANT and REVOKE commands to grant and revoke privileges on objects. Also with system privileges, you can grant object privileges to PUBLIC, and users with object privileges may pass them on to others by granting them with the WITH GRANT OPTION clause.
A user with objects in his own schema automatically has all object privileges on those objects and can grant any object privilege on these objects to any user or another role, with or without the WITH GRANT OPTION clause.
Table 31-8 shows the object privileges available for different types of objects; some privileges
are only applicable to certain types of objects. For example, the INSERT privilege only makes sense with tables, views, and materialized views; the EXECUTE privilege, on the other hand, is applicable to functions, procedures, and packages, but not tables.

Table 31-8. Object Privileges

Object Privilege Capability

ALTER Can alter a table or sequence definition.

DELETE Can delete rows from a table, view, or materialized view.

EXECUTE Can execute a function or procedure, with or without a package.

DEBUG Can allow access to PL/SQL code in triggers defined on a table, or SQL statements that reference a table. For object types, this privilege allows access to all public and private variables, methods, and types defined on the object type.

FLASHBACK Can allow flashback queries on tables, views, and materialized views using retained undo information.

INDEX Can create an index on a table.

INSERT Can insert rows into a table, view, or materialized view.

ON COMMIT REFRESH Can create a refresh-on-commit materialized view based on a table.

QUERY REWRITE Can create a materialized view that can be used by Oracle to rewrite a query based on a table.

READ Can read the contents of an operating system directory using an Oracle
DIRECTORY definition.

REFERENCES Can create a foreign key constraint that references another table’s primary key or unique key.

SELECT Can read rows from a table, view, or materialized view, in addition to reading current or next values from a sequence.

UNDER Can create a view based on an existing view.

UPDATE Can update rows in a table, view, or materialized view.

WRITE Can write information to an operating system directory using an Oracle
DIRECTORY definition.

Some of these object privileges overlap with system privileges. For example, if you don’t have the FLASHBACK object privilege on a table, you can still perform flashback queries if you have the FLASHBACK ANY TABLE system privilege.
In the following example, the DBA grants KELLYC full access to the table HR.EMPLOYEES, but only allows KELLYC to pass on the SELECT object privilege to other users:

SQL> grant insert, update, delete on hr.employees to kellyc; Grant succeeded.
SQL> grant select on hr.employees to kellyc with grant option; Grant succeeded.

Note that if the SELECT privilege on the table HR.EMPLOYEES is revoked from KELLYC, the SELECT
privilege is also revoked from anyone she granted the privilege.

Table Privileges

The types of privileges that can be granted on a table fall into two broad categories: DML operations and DDL operations. DML operations include DELETE, INSERT, SELECT, and UPDATE, whereas DDL operations include adding, dropping, and changing columns in the table as well as creating indexes on the table.
When granting DML operations on a table, it is possible to restrict those operations only to certain columns. For example, you may want to allow KELLYC to see and update all the rows and columns in the HR.EMPLOYEES table except for the SALARY column. To do this, you first need to revoke the existing SELECT privilege on the table:
SQL> revoke update on hr.employees from kellyc; Revoke succeeded.

Next, you will let KELLYC update all the columns except for the SALARY column:

SQL> grant update (employee_id, first_name, last_name, email,
2 phone_number, hire_date, job_id, commission_pct,
3 manager_id, department_id)
4 on hr.employees to kellyc; Grant succeeded.
KELLYC will be able to update all columns in the HR.EMPLOYEES table except for the SALARY
column:

SQL> update hr.employees set first_name = 'Stephen' where employee_id = 100;
1 row updated.
SQL> update hr.employees set salary = 150000 where employee_id = 203;
update hr.employees set salary = 150000 where employee_id = 203
*
ERROR at line 1:
ORA-01031: insufficient privileges

View Privileges

Privileges on views are similar to those granted on tables. (We show you how to create views in Chapter 35.) Rows in a view can be selected, updated, deleted, or inserted, assuming that the view is updatable. To create a view, first you need either the CREATE VIEW system privilege (to create a view in your own schema) or the CREATE ANY VIEW system privilege (to create a view in any schema). Even to create the view, you must also have at least SELECT object privileges on the underlying tables of the view, along with INSERT, UPDATE, and DELETE, if you wish to perform those operations on the view and the view is updatable. Alternatively, you can have the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE privileges if the underlying objects are not in your schema.
To allow others to use your view, you must also have permissions on the view’s base tables with the GRANT OPTION, or you must have the system privileges with the ADMIN OPTION. For example, if you are creating a view against the HR.EMPLOYEES table, you must have been granted the SELECT object privilege WITH GRANT OPTION on HR.EMPLOYEES, or you must have the SELECT ANY TABLE system privilege WITH ADMIN OPTION.

Object Privilege Data Dictionary Views

A number of data dictionary views contain information about object privileges assigned to users. Table 31-9 lists the most important views containing object privilege information.

Table 31-9. Object Privilege Data Dictionary Views

Data Dictionary View Description

DBA_TAB_PRIVS Table privileges granted to roles and users. Includes the user who granted the privilege to the role or user, with or without GRANT OPTION.

DBA_COL_PRIVS Column privileges granted to roles or users, containing the column name and the type of privilege on the column.

ROLE_TAB_PRIVS For the current session, privileges granted on tables via roles.

Creating, Assigning, and Maintaining Roles

A role is a named group of privileges, either system privileges or object privileges or a combination of the two, that helps to ease the administration of privileges. Rather than granting system or object privileges individually to each user, you can grant the group of system or object privileges to a role, and in turn the role can be granted to the user instead. This tremendously reduces the amount of administrative overhead involved in maintaining privileges for users. Figure 31-2 shows how a role can reduce the number of GRANT commands (and ultimately REVOKE commands) that you need to execute when roles are used to group privileges.
If the privileges for a group of people authorized by a role need to change, only the privileges of the role need to be changed, and the capabilities of the users with that role automatically use the new or changed privileges. Roles may selectively be enabled by a user; some roles may automatically
be enabled at login. In addition, passwords can be used to protect a role, adding another level of authentication to the capabilities in the database.

Figure 31-2. Using roles to manage privileges

Table 31-10 shows the most common roles that Oracle automatically provides with the data- base, along with a brief description of the privileges that come with each role.

Table 31-10. Predefined Oracle Roles

Role Name Definition

CONNECT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW. Gives a user the right to connect to the database and create tables, indexes, and views.

RESOURCE CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. Gives application developers privileges needed to code PL/SQL procedures and functions.

Table 31-10. Predefined Oracle Roles

Role Name Definition

DBA Allows a person with the DBA role to grant system privileges to others. This is the equivalent of giving a user all system privileges WITH ADMIN OPTION.

DELETE_CATALOG_ROLE Allows a user to remove audit records from the audit trail for regular or fine-grained auditing. Provides only object privileges (DELETE) on SYS.AUD$ and FGA_LOG$.

EXECUTE_CATALOG_ROLE Gives privileges on various system packages, procedures, and functions, such as DBMS_FGA and DBMS_RLS.

SELECT_CATALOG_ROLE Provides the SELECT object privilege on 1,638 data dictionary tables.

EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE, and system privi- leges such as BACKUP ANY TABLE and RESUMABLE. Allows a user with this role to export all objects in the database.

IMP_FULL_DATABASE Allows the import of a previously exported full database; similar to EXP_FULL_DATABASE, with many more system privileges, such as CREATE ANY TABLE.

AQ_USER_ROLE Provides execute access on routines needed for Advanced
Queuing, such as DBMS_AQ.

AQ_ADMINISTRATOR_ROLE Manages Advanced Queuing queues.

SNMPAGENT Provides required privileges for the Enterprise Manager
Intelligent Agent.

RECOVERY_CATALOG_OWNER Provides privileges to a user who owns a recovery catalog for
RMAN backup and recovery.

HS_ADMIN_ROLE Provides access to the tables HS_* and the package DBMS_HS for administering Oracle Heterogeneous Services.

SCHEDULER_ADMIN Provides access to the DBMS_SCHEDULER package, along with privileges to create batch jobs.

The roles CONNECT, RESOURCE, and DBA are provided mainly for compatibility with previous versions of Oracle; they may not exist in future versions of Oracle. You should create custom roles using the privileges granted to these roles as a starting point, as in this example:

SQL> create role night_dba; Role created.
SQL> grant create session, create table, exp_full_database
2 to night_dba; Grant succeeded. SQL>

Creating just enough privileges for various classes of users and administrators rather than a blanket DBA role (with access to all privileges) enhances the security of your environment: each user or administrator gets exactly the privileges they need, and nothing more.

Creating or Dropping a Role

To create a role, you use the CREATE ROLE command, and you must have the CREATE ROLE system priv- ilege. Typically, you grant this only to database administrators or application administrators. Here’s an example:
SQL> create role hr_admin; Role created.

By default, no password or authentication is required to enable or use an assigned role. As with creating users, you can authorize use of a role by requiring a password for the role (creating the role with the IDENTIFIED BY PASSWORD clause), using the operating system’s authentication methods (creating the role with the IDENTIFIED EXTERNALLY clause), or using a network or directory service (creating the role with the IDENTIFIED GLOBALLY clause).

Granting Privileges to a Role

Assigning a privilege to a role is very straightforward; you use the GRANT command just as you would assign a privilege to a user:

SQL> grant select on hr.employees to hr_clerk; Grant succeeded.
SQL> grant create trigger to hr_clerk; Grant succeeded.
In this example, we’ve assigned an object privilege and a system privilege to the HR_CLERK role.

Assigning or Revoking Roles

Once you have the desired system and object privileges assigned to the role, you can assign the role to a user, using familiar syntax:

SQL> grant hr_clerk to kellyc; Grant succeeded.
Any other privileges granted to the HR_CLERK role in the future will automatically be useable by
KELLYC because KELLYC has been granted the role.
Roles may be granted to other roles; this allows a DBA to have a hierarchy of roles, making role administration easier. For example, there could be roles named DEPT30, DEPT50, and DEPT100, each having object privileges to tables owned by each of those departments. An employee in department 30 would be assigned the DEPT30 role, and so forth. The president of the company would like to see tables in all departments; but rather than assigning individual object privileges to the role ALL_DEPTS, you can assign the individual department roles to ALL_DEPTS:

SQL> create role all_depts; Role created.
SQL> grant dept30, dept50, dept100 to all_depts;
Grant succeeded.
SQL> grant all_depts to kellyc; Grant succeeded.

The role ALL_DEPTS may also contain individual object and system privileges that do not apply to individual departments, such as object privileges on order entry tables or accounts receivable tables.
Revoking a role from a user is very similar to revoking privileges from a user:

SQL> revoke all_depts from kellyc; Revoke succeeded.

The privileges revoked will no longer be available to the user the next time they connect to the database. However, it is worth noting that if another role contains privileges on the same objects as the dropped role, or privileges on the objects are granted directly, the user retains these privileges on the objects until these and all other grants are explicitly revoked.

Default Roles

By default, all roles granted to a user are enabled when the user connects. If a role is going to be used only within the context of an application, the role can start out disabled when the user is logged in; then it can be enabled and disabled within the application. If the user KELLYC has CONNECT, RESOURCE, HR_CLERK, and DEPT30 roles, and you want to specify that HR_CLERK and DEPT30 are not enabled by default, you can use something like the following:
SQL> alter user kellyc default role all
2> except hr_clerk, dept30; User altered.
When KELLYC connects to the database, she automatically has all privileges granted with all roles except for HR_CLERK and DEPT30. The user KELLYC may explicitly enable a role in her session by using SET ROLE:

SQL> set role dept30; Role set.

When she is done accessing the tables for department 30, she can disable the privileges provided by the role in this session:

SQL> set role all except dept30; Role set.

Password-Enabled Roles

To enhance security in the database, you can assign a password to a role. The password is assigned to the role when it’s created:

SQL> create role dept99 identified by is183le; Role created.
SQL> grant dept99 to kellyc;
Grant succeeded.
SQL> alter user kellyc default role all except hr_clerk, dept30, dept99; User altered.
When the user KELLYC is connected to the database, either the application she is using will provide or prompt for a password, or she can enter the password when she enables the role:

SQL> set role dept99 identified by is183le; Role set.

To prevent the user from extracting a role password from a PL/SQL application, you can either encrypt the PL/SQL procedure itself or store the role password in a database table that only the

procedure has access to. As a result, the application user cannot retrieve the role password itself and must use the application to obtain authorization through the role.

Using Database Auditing

Oracle provides a number of different auditing methods for you to monitor what kinds of privileges are being used as well as what objects are being accessed. Auditing does not prevent the use of priv- ileges but it can provide useful information to uncover abuse or misuse of privileges.
In Table 31-11, we summarize the different types of auditing in an Oracle database.

Table 31-11. Auditing Types

Auditing Type Description

Statement auditing Audits SQL statements by the type of statement regardless of the specific schema objects being accessed. One or more users can also be specified in the database to be audited for a particular statement.

Privilege auditing Audits system privileges, such as CREATE TABLE or ALTER INDEX. As with statement auditing, privilege auditing can specify one or more particular users as the target of the audit.

Schema object auditing Audits specific statements operating on a specific schema object (e.g., UPDATE statements on the DEPARTMENTS table). Schema object auditing always applies to all users in the database.

Fine-grained auditing Audits table access and privileges based on the content of the objects being accessed. Uses the package DBMS_FGA to set up a policy on a particular table.

In the next few sections, we’ll review how a DBA can manage audits of both system and object privilege use. When the granularity is required, a DBA can use fine-grained auditing to monitor access to certain rows or columns of a table, not just whether the table was accessed.

Auditing Locations

Audit records can be sent to either the SYS.AUD$ database table or an operating system file. To enable auditing and specify the location where the database saves audit records, set the initialization parameter AUDIT_TRAIL to one of the four values in Table 31-12.

Table 31-12. Auditing Options

Parameter Value Action
NONE, FALSE Disable auditing.
OS Enable auditing. Send audit records to an operating system file. DB, TRUE Enable auditing. Send audit records to the SYS.AUD$ table. DB_EXTENDED Enable auditing. Send audit records to the SYS.AUD$ table, and record
additional information in the CLOB columns SQLBIND and SQLTEXT.

The parameter AUDIT_TRAIL is not dynamic; you must shut down and restart the database for a change in the AUDIT_TRAIL parameter to take effect. When sending audit information to the SYS.AUD$ table, the size of the table should be carefully monitored so as not to impact the space requirements for other objects in the SYS tablespace. It is recommended that the rows in SYS.AUD$ be periodically archived and the table truncated. Oracle provides the role DELETE_CATALOG_ROLE to use with a special account in a batch job to archive and truncate the audit table.

Statement Auditing

No matter what type of auditing you wish to do, you use the AUDIT command to turn on auditing and NOAUDIT to turn off auditing. For statement auditing, the format of the AUDIT command looks some- thing like the following:

AUDIT sql_statement_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL;
The sql_statement_clause contains a number of different pieces of information, such as the type of SQL statement you want to audit and who you are auditing. In addition, you want to either audit the action every time it happens (BY ACCESS) or only once (BY SESSION). The default is BY SESSION.
Sometimes you want to audit successful actions—statements that did not generate an error message. For these statements, you add WHENEVER SUCCESSFUL. Other times you only care if the commands using the audited statements fail, either due to privilege violations, running out of space in the tablespace, or syntax errors. For these you use WHENEVER NOT SUCCESSFUL.
For most categories of auditing methods, you can specify ALL instead of individual statement types or objects if you truly want all types of access to a table or any privileges by a certain user to be audited.
The types of statements you can audit, with the statements that are covered in each category, are listed in Table 31-13. If ALL is specified, any statement in this list is audited. However, the types of statements in Table 31-14 do not fall into the ALL category when enabling auditing; they must be explicitly specified in any AUDIT commands.

Table 31-13. Auditable Statements Included in the ALL Category

Statement Option SQL Operations
CLUSTER CREATE, ALTER, DROP, or TRUNCATE a cluster

CONTEXT CREATE or DROP a CONTEXT DATABASE LINK CREATE or DROP a database link DIMENSION CREATE, ALTER, or DROP a dimension DIRECTORY CREATE or DROP a dimension
INDEX CREATE, ALTER, or DROP an index

MATERIALIZED VIEW CREATE, ALTER, or DROP a materialized view

NOT EXISTS Failure of SQL statement due to nonexistent referenced objects PROCEDURE CREATE or DROP FUNCTION, LIBRARY, PACKAGE, PACKAGE BODY, or PROCEDURE PROFILE CREATE, ALTER, or DROP a profile
PUBLIC DATABASE LINK CREATE or DROP a public database link

PUBLIC SYNONYM CREATE or DROP a public synonym

Table 31-13. Auditable Statements Included in the ALL Category (Continued)

Statement Option SQL Operations

ROLE CREATE, ALTER, DROP, or SET a role

ROLLBACK SEGMENT CREATE, ALTER, or DROP a rollback segment

SEQUENCE CREATE or DROP a sequence SESSION Logons and logoffs SYNONYM CREATE or DROP synonyms
SYSTEM AUDIT AUDIT or NOAUDIT of system privileges SYSTEM GRANT GRANT or REVOKE system privileges and roles TABLE CREATE, DROP, or TRUNCATE a table TABLESPACE CREATE, ALTER, or DROP a tablespace
TRIGGER CREATE, ALTER (enable/disable), DROP triggers; ALTER TABLE with either
ENABLE ALL TRIGGERS or DISABLE ALL TRIGGERS TYPE CREATE, ALTER, or DROP types and type bodies USER CREATE, ALTER, or DROP a user
VIEW CREATE or DROP a view

Table 31-14. Explicitly Specified Statement Types

Statement Option SQL Operations

ALTER SEQUENCE Any ALTER SEQUENCE command

ALTER TABLE Any ALTER TABLE command

COMMENT TABLE Add a comment to a table, view, materialized view, or any of their columns

DELETE TABLE Delete rows from a table or view

EXECUTE PROCEDURE Execute a procedure, function, or any variables or cursors within a package

GRANT DIRECTORY GRANT or REVOKE a privilege on a DIRECTORY object

GRANT PROCEDURE GRANT or REVOKE a privilege on a procedure, function, or package

GRANT SEQUENCE GRANT or REVOKE a privilege on a sequence

GRANT TABLE GRANT or REVOKE a privilege on a table, view, or materialized view

GRANT TYPE GRANT or REVOKE a privilege on a TYPE INSERT TABLE INSERT INTO a table or view
LOCK TABLE LOCK TABLE command on a table or view

SELECT SEQUENCE Any command referencing the sequence’s CURRVAL or NEXTVAL SELECT TABLE SELECT FROM a table, view, or materialized view
UPDATE TABLE Execute UPDATE on a table or view

Some examples will help make all these options a lot clearer. In our sample database, the user KELLYC has all privileges on the tables in the HR schema and other schemas. KELLYC is allowed to create indexes on some of these tables, but we want to know when the indexes are created in case we have some performance issues related to execution plans changing. We can audit index creation by KELLYC with the following command:

SQL> audit index by kellyc whenever successful; Audit succeeded.
Later that day, KELLYC creates an index on the HR.JOBS table:

SQL> create index job_title_idx on hr.jobs(job_title); Index created.
Checking the audit trail in the data dictionary view DBA_AUDIT_TRAIL, we see that KELLYC did indeed create an index at 9:21 p.m. on September 17:

SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp,
2 obj_name, action_name, sql_text from dba_audit_trail
3 where username = 'KELLYC';

USERNAME TIMESTAMP OBJ_NAME ACTION_NAME SQL_TEXT
---------- -------------- ---------------- ---------------- --------------- KELLYC 09/17/06 21:21 JOB_TITLE_IDX CREATE INDEX create index j ob_title_idx on
hr.jobs(job_ti tle)
1 row selected.

To turn off auditing for KELLYC for indexing operations, use the NOAUDIT command, as follows:

SQL> noaudit index by kellyc; Noaudit succeeded.

You also may wish to routinely audit both successful and unsuccessful logins. This requires two
AUDIT commands:

SQL> audit session whenever successful; Audit succeeded.
SQL> audit session whenever not successful; Audit succeeded.
Statement auditing also includes startup and shutdown operations. Although you can audit the command SHUTDOWN IMMEDIATE in the SYS.AUD$ table, it is not possible to audit the STARTUP command in SYS.AUD$ because the database has to be started before rows can be added to this table. For these cases, you can look in the directory $ORACLE_HOME/rdbms/audit to see a record of a startup operation performed by a system administrator.

Privilege Auditing

Auditing system privileges using the AUDIT command has the same basic syntax as statement auditing, except that system privileges are specified in the sql_statement_clause instead of statements.
For example, you may wish to grant the ALTER TABLESPACE privilege to all your DBAs but you want to generate an audit record when this happens. The command to enable auditing on this privilege looks similar to statement auditing:

SQL> audit alter tablespace by access whenever successful; Audit succeeded.

Every time the ALTER TABLESPACE privilege is successfully used, a row is added to SYS.AUD$. Special auditing is available to enable you to track system administrators’ use of the SYSDBA and
SYSOPER privileges. To enable this extra level of auditing, set the initialization parameter AUDIT_SYS_ OPERATIONS to TRUE. The audit records are sent to the same location as the operating system audit records; therefore, this location is operating-system-dependent. All SQL statements executed while using one of these privileges, as well as any SQL statements executed as the user SYS, are sent to this operating system audit location.

Schema Object Auditing

Auditing access to various schema objects using the AUDIT command looks similar to statement and privilege auditing:

AUDIT schema_object_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL;
The schema_object_clause specifies a type of object access and the object being accessed. You can audit 13 different types of operations on specific objects; they are listed in Table 31-15.

Table 31-15. Object Auditing Options

Object Option Description

ALTER Alters a table, sequence, or materialized view

AUDIT Audits commands on any object

COMMENT Adds comments to tables, views, or materialized views DELETE Deletes rows from a table, view, or materialized view FLASHBACK Performs flashback operation on a table or view
GRANT Grants privileges on any type of object

INDEX Creates an index on a table or materialized view INSERT Inserts rows into a table, view, or materialized view LOCK Locks a table, view, or materialized view
READ Performs a read operation on the contents of a DIRECTORY object

RENAME Renames a table, view, or procedure

SELECT Selects rows from a table, view, sequence, or materialized view

UPDATE Updates a table, view, or materialized view

If you wish to audit all INSERT and UPDATE commands on the HR.JOBS table, regardless of who is doing the update, every time the action occurs, you can use the AUDIT command as follows:
SQL> audit insert, update on hr.jobs by access whenever successful; Audit successful.

Protecting the Audit Trail

The audit trail itself needs to be protected, especially if nonsystem users must access the table SYS.AUD$. The built-in role DELETE_ANY_CATALOG is one of the ways that non-SYS users can have access to the audit trail (e.g., to archive and truncate the audit trail to ensure that it does not impact the space requirements for other objects in the SYS tablespace).
To set up auditing on the audit trail itself, connect as SYSDBA and run the following command:

SQL> audit all on sys.aud$ by access; Audit succeeded.

Now all actions against the table SYS.AUD$, including SELECT, INSERT, UPDATE, and DELETE, will be recorded in SYS.AUD$ itself. But, you may ask, what if someone deletes the audit records identifying access to the table SYS.AUD$? The rows in the table are deleted, but then another row is inserted, recording the deletion of the rows. Therefore, there will always be some evidence of activity, inten- tional or accidental, against the SYS.AUD$ table. In addition, if AUDIT_SYS_OPERATIONS is set to TRUE, any sessions using AS SYSDBA or AS SYSOPER, or connecting as SYS itself will be logged into the oper- ating system audit location, which presumably even the Oracle DBAs would not have access to. As a result, you have many safeguards in place to ensure that you record all privileged activity in the database, along with any attempts to hide this activity.

Summary

As a DBA, you want to make sure that your application environment is secure. This chapter provided you with the tools to enhance and refine the security options available in Oracle Database XE. While you can protect your enterprise data using Oracle’s built-in security, you are free to add another layer of protection in your Web-based PHP applications as well. Any robust security policy implements more than one layer of security to ensure that users are who they say they are (authentication), and that they are allowed to access various resources in your environment (authorization).
In the next chapter, we tie together your PHP applications with Oracle and show you how easy it is to connect to Oracle Database XE, query and modify database tables, retrieve database metadata, and format your database’s data to look good in a PHP application.