DATABASE Rename

CREATE SCHEMA

Purpose

Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.

To execute a CREATE SCHEMA statement, then Oracle Database executes each included statement. If all statements execute successfully, then the database commits the transaction. If any statement results in an error, then the database rolls back all the statements.

Note: This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER ). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

Prerequisites

The CREATE SCHEMA statement can include CREATE TABLE, CREATE VIEW, and GRANT statements. To issue a CREATE SCHEMA statement, you must have the privileges necessary to issue the included statements.

Syntax

create_schema::=

Description of the illustration create_schema.gif

Keyword and Parameters

schema

Specify the name of the schema. The schema name must be the same as your Oracle Database username.

create_table_statement

Specify a CREATE TABLE statement to be issued as part of this CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).

See Also: CREATE TABLE

create_view_statement

Specify a CREATE VIEW statement to be issued as part of this CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character).

See Also: CREATE VIEW

grant_statement

Specify a GRANT statement to be issued as part of this CREATE SCHEMA statement. Do not end this statement with a semicolon (or other terminator character). You can use this clause to grant object privileges on objects you own to other users. You can also grant system privileges to other users if you were granted those privileges WITH ADMIN OPTION.

See Also: GRANT

The CREATE SCHEMA statement supports the syntax of these statements only as defined by standard SQL, rather than the complete syntax supported by Oracle Database.

The order in which you list the CREATE TABLE, CREATE VIEW, and GRANT statements is unimportant. The statements within a CREATE SCHEMA statement can reference existing objects or objects you create in other statements within the same CREATE SCHEMA statement.

Restriction on Granting Privileges to a Schema

The syntax of the parallel_clause is allowed for a CREATE TABLE statement in CREATE SCHEMA, but parallelism is not used when creating the objects.

See Also: the parallel_clause in the CREATE TABLE documentation

Example

Creating a Schema: Example

The following statement creates a schema named oe for the sample order entry user oe, creates the table new_product, creates the view new_product_view, and grants the SELECT object privilege on new_product_view to the sample human resources user hr.

CREATE SCHEMA AUTHORIZATION oe

   CREATE TABLE new_product 

      (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER) 

   CREATE VIEW new_product_view 

      AS SELECT color, quantity FROM new_product WHERE color = 'RED' 

   GRANT select ON new_product_view TO hr; 

How to rename an Oracle database ORACLE_SID? Oracle Tips by Burleson Consulting April 21, 2008

Question: I want to re-name my database to change the ORACLE_SID value.  I have changed the instance_name parameter in parameter file also but its still at database said showing same when we select instance_name from v$instance?  What is the proper way to re-name an Oracle instance?

Answer:  To completely re-name a database you need to change all of the components where the instance name is located:

• External files: sqlnet.ora, tnsnames.ora, init.ora (spfile)
• Internal locations:  Data dictionary

Here are the "traditional" steps to re-name an Oracle database (note new procedure for renaming an instance in 9i and beyond later on this page):

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 2: Shutdown the old database

STEP 3: Change all references to the old instance name in all locations for sqlnet.ora, protocol.ora, tnsnames.ora and the init.ora (or spfile for the database).

STEP 4: Change the database name in the file to reference the new ORACLE_SID instance name:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NEWLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/newlsq/log1a.dbf',
'/u03/oradata/newlsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/newlsq/log2a.dbf',
'/u04/oradata/newlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
;
# Re-named database can now be opened normally.
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ''/u04/oradata/newlsq/log2a.dbf/temp.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;
 

A new method for a database rename instance

For Oracle9i and beyond, Oracle author Dr. Tim Hall has this procedure to rename an Oracle database using the new dbnewid (also called nid, for new ID) utility:

• STEP 1: Backup the database.
   
• STEP 2: Mount the database after a clean shutdown: SHUTDOWN IMMEDIATE
• STARTUP MOUNT
  
• STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege: nid TARGET=sys/password@TSH1 DBNAME=TSH2
  
  Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like: 
• C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
• DBNEWID: Release 9.2.0.3.0 - Production
• Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
• Connected to database TSH1 (DBID=1024166118)
• Control Files in database:
•     C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
•     C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
•     C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL
• Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y
• Proceeding with operation
• Changing database ID from 1024166118 to 1317278975
• Changing database name from TSH1 to TSH2
•     Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified
•     Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
•     Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified
•     Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
•     Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
•     Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
•     Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
•     Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
•     Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name
• Database name changed to TSH2.
• Modify parameter file and generate a new password file before restarting.
• Database ID for database TSH2 changed to 1317278975.
• All previous backups and archived redo logs for this database are unusable.
• Shut down database and open with RESETLOGS option.
• Succesfully changed database name and ID.
• DBNEWID - Completed succesfully.
  
• STEP 4: Shutdown the database: SHUTDOWN IMMEDIATE
  
• STEP 5: Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.
• STARTUP MOUNT
• ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
• SHUTDOWN IMMEDIATE
  
• STEP 6: Create a new password file: orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10
  
• STEP 7: Rename the SPFILE to match the new DBNAME.
   
• STEP 8: If you are using Windows you must recreate the service so the correct name and parameter file are used:
    oradim -delete -sid TSH1
• oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora
  
  If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
    ORACLE_SID=TSH2; export ORACLE_SID
  
• STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
    lsnrctl reload
  
• STEP 10: Open the database with RESETLOGS:
    STARTUP MOUNT
• ALTER DATABASE OPEN RESETLOGS;
  
• STEP 11: Backup the database.

24
Auditing

This chapter discusses the auditing feature of Oracle. It includes:

• Introduction to Auditing
• Statement Auditing
• Privilege Auditing
• Schema Object Auditing
• Fine-Grained Auditing
• Focus Statement, Privilege, and Schema Object Auditing
• Audit in a Multitier Environment

Introduction to Auditing

Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to:

• Investigate suspicious activity. For example, if an unauthorized user is deleting data from tables, the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
• Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.

Features of Auditing

This section outlines the features of the Oracle auditing mechanism.

Types of Auditing

Oracle supports three general types of auditing:

Type of Auditing	Description
Statement auditing	The selective auditing of SQL statements with respect to only the type of statement, not the specific schema objects on which it operates. Statement auditing options are typically broad, auditing the use of several types of related actions for each option. For example, AUDIT TABLE tracks several DDL statements regardless of the table on which they are issued. You can set statement auditing to audit selected users or every user in the database.
Privilege auditing	The selective auditing of the use of powerful system privileges to perform corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing because it audits only the use of the target privilege. You can set privilege auditing to audit a selected user or every user in the database.
Schema object auditing	The selective auditing of specific statements on a particular schema object, such as AUDIT SELECT ON employees. Schema object auditing is very focused, auditing only a specific statement on a specific schema object. Schema object auditing always applies to all users of the database.
Fine-grained auditing	Fine-grained auditing allows the monitoring of data access based on content.

Focus of Auditing

Oracle allows audit options to be focused or broad. You can audit:

• Successful statement executions, unsuccessful statement executions, or both
• Statement executions once in each user session or once every time the statement is executed
• Activities of all users or of a specific user

Audit Records and the Audit Trail

Audit records include information such as the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or an operating system audit trail.

The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use the information in this table.

The audit trail records can contain different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record, if the information is meaningful to the particular audit action:

• The user name
• The session identifier
• The terminal identifier
• The name of the schema object accessed
• The operation performed or attempted
• The completion code of the operation
• The date and time stamp
• The system privileges used

The operating system audit trail is encoded and not readable, but it is decoded in data dictionary files and error messages.

• Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.
• Privileges used describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes and their descriptions.
• Completion code describes the result of the attempted operation. Successful operations return a value of zero, and unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. 

See Also: Oracle9i Database Administrator's Guide for instructions for creating and using predefined views Oracle9i Database Error Messages for a list of completion codes

Mechanisms for Auditing

This section explains the mechanisms used by the Oracle auditing features.

When Are Audit Records Generated?

The recording of audit information can be enabled or disabled. This functionality allows any authorized database user to set audit options at any time but reserves control of recording audit information for the security administrator.

When auditing is enabled in the database, an audit record is generated during the execute phase of statement execution.

SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.

The generation and insertion of an audit trail record is independent of a user's transaction. Therefore, even if a user's transaction is rolled back, the audit trail record remains committed.

Note: Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Successful SQL statements from SYS are audited indiscriminately. The audit records for sessions established by the user SYS or connections with administrative privileges are sent to an operating system location. Sending them to a location separate from the usual database audit trail in the SYS schema provides for greater auditing security.

See Also: Oracle9i Database Administrator's Guide for instructions on enabling and disabling auditing Chapter 14, "SQL, PL/SQL, and Java" for information about the different phases of SQL statement processing and shared SQL

Events Always Audited to the Operating System Audit Trail

Regardless of whether database auditing is enabled, Oracle always records some database-related actions into the operating system audit trail:

• At instance startup, an audit record is generated that details the operating system user starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This information is recorded into the operating system audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup further prevents an administrator from restarting a database with database auditing disabled so that they are able to perform unaudited actions.
• At instance shutdown, an audit record is generated that details the operating system user shutting down the instance, the user's terminal identifier, the date and time stamp.
• During connections with administrator privileges, an audit record is generated that details the operating system user connecting to Oracle with administrator privileges. This provides accountability of users connected with administrator privileges.

On operating systems that do not make an audit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as background process trace files.

See Also: Your operating system specific Oracle documentation for more information about the operating system audit trail

When Do Audit Options Take Effect?

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. A session does not see the effects of statement or privilege audit options being set or changed. The modified statement or privilege audit options take effect only when the current session is ended and a new session is created. In contrast, changes to schema object audit options become effective for current sessions immediately.

Audit in a Distributed Database

Auditing is site autonomous. An instance audits only the statements issued by directly connected users. A local Oracle node cannot audit actions that take place in a remote database. Because remote connections are established through the user account of a database link, the remote Oracle node audits the statements issued through the database link's connection.

See Also: Oracle9i Database Administrator's Guide

Audit to the Operating System Audit Trail

Oracle allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle. On some other operating systems, these audit records are written to a file outside the database, with a format similar to other Oracle trace files.

See Also: Your operating system specific Oracle documentation, to see if this feature has been implemented on your operating system

Oracle allows certain actions that are always audited to continue, even when the operating system audit trail (or the operating system file containing audit records) is unable to record the audit record. The usual cause of this is that the operating system audit trail or the file system is full and unable to accept new records.

System administrators configuring operating system auditing should ensure that the audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to ensure this does not occur. Note, however, that configuring auditing to use the database audit trail removes this vulnerability, because the Oracle server prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.

Statement Auditing

Statement auditing is the selective auditing of related groups of statements that fall into two categories:

• DDL statements, regarding a particular type of database structure or schema object, but not a specifically named structure or schema object (for example, AUDIT TABLE audits all CREATE and DROP TABLE statements)
• DML statements, regarding a particular type of database structure or schema object, but not a specifically named structure or schema object (for example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view)

Statement auditing can be broad or focused, auditing the activities of all database users or the activities of only a select list of database users.

Privilege Auditing

Privilege auditing is the selective auditing of the statements allowed using a system privilege. For example, auditing of the SELECT ANY TABLE system privilege audits users' statements that are executed using the SELECT ANY TABLE system privilege. You can audit the use of any system privilege.

In all cases of privilege auditing, owner privileges and schema object privileges are checked before system privileges. If the owner and schema object privileges suffice to permit the action, the action is not audited.

If similar statement and privilege audit options are both set, only a single audit record is generated. For example, if the statement clause TABLE and the system privilege CREATE TABLE are both audited, only a single audit record is generated each time a table is created.

Privilege auditing is more focused than statement auditing because each option audits only specific types of statements, not a related list of statements. For example, the statement auditing clause TABLE audits CREATE TABLE, ALTER TABLE, and DROP TABLE statements, while the privilege auditing option CREATE TABLE audits only CREATE TABLE statements. This is because only the CREATE TABLE statement requires the CREATE TABLE privilege.

Like statement auditing, privilege auditing can audit the activities of all database users or the activities of a select list of database users.

Schema Object Auditing

Schema object auditing is the selective auditing of specific DML statements (including queries) and GRANT and REVOKE statements for specific schema objects. Schema object auditing audits the operations permitted by schema object privileges, such as SELECT or DELETE statements on a given table, as well as the GRANT and REVOKE statements that control those privileges.

You can audit statements that reference tables, views, sequences, standalone stored procedures and functions, and packages. Procedures in packages cannot be audited individually.

Statements that reference clusters, database links, indexes, or synonyms are not audited directly. However, you can audit access to these schema objects indirectly by auditing the operations that affect the base table.

Schema object audit options are always set for all users of the database. These options cannot be set for a specific list of users. You can set default schema object audit options for all auditable schema objects.

See Also: Oracle9i SQL Reference for information about auditable schema objects

Schema Object Audit Options for Views and Procedures

Views and procedures (including stored functions, packages, and triggers) reference underlying schema objects in their definition. Therefore, auditing with respect to views and procedures has several unique characteristics. Multiple audit records can be generated as the result of using a view or a procedure: The use of the view or procedure is subject to enabled audit options, and the SQL statements issued as a result of using the view or procedure are subject to the enabled audit options of the base schema objects (including default audit options).

Consider the following series of SQL statements:

AUDIT SELECT ON employees; 

CREATE VIEW employees_departments AS 

  SELECT employee_id, last_name, department_id

    FROM employees, departments

    WHERE employees.department_id = departments.department_id;

AUDIT SELECT ON employees_departments; 

SELECT * FROM employees_departments; 

As a result of the query on employees_departments, two audit records are generated: one for the query on the employees_departments view and one for the query on the base table employees (indirectly through the employees_departments view). The query on the base table departments does not generate an audit record because the SELECT audit option for this table is not enabled. All audit records pertain to the user that queried the employees_departments view.

The audit options for a view or procedure are determined when the view or procedure is first used and placed in the shared pool. These audit options remain set until the view or procedure is flushed from, and subsequently replaced in, the shared pool. Auditing a schema object invalidates that schema object in the cache and causes it to be reloaded. Any changes to the audit options of base schema objects are not observed by views and procedures in the shared pool.

Continuing with the previous example, if auditing of SELECT statements is turned off for the employees table, use of the employees_departments view no longer generates an audit record for the employees table.

Fine-Grained Auditing

Fine-grained auditing allows the monitoring of data access based on content. A built-in audit mechanism in the database prevents users from by-passing the audit. Oracle triggers can potentially monitor DML actions such as INSERT, UPDATE, and DELETE. However, monitoring on SELECT is costly and might not work for certain cases. In addition, users might want to define their own alert action in addition to just inserting an audit record into the audit trail. This feature provides an extensible interface to audit SELECT statements on tables and views.

The DBMS_FGA package administers these value-based audit policies. Using DBMS_FGA, the security administrator creates an audit policy on the target table. If any of the rows returned from a query block matches the audit condition (these rows are referred to as interested rows), then an audit event entry, including username, SQL text, bind variable, policy name, session ID, time stamp, and other attributes, is inserted into the audit trail. As part of the extensibility framework, administrators can also optionally define an appropriate event handler, an audit event handler, to process the event; for example, the audit event handler could send an alert page to the administrator.

See Also: Oracle9i Application Developer's Guide - Fundamentals

Focus Statement, Privilege, and Schema Object Auditing

Oracle lets you focus statement, privilege, and schema object auditing in three areas:

• Successful and unsuccessful executions of the audited SQL statement
• BY SESSION and BY ACCESS auditing
• For specific users or for all users in the database (statement and privilege auditing only)

Successful and Unsuccessful Statement Executions Auditing

For statement, privilege, and schema object auditing, Oracle allows the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. Therefore, you can monitor actions even if the audited statements do not complete successfully.

You can audit an unsuccessful statement execution only if a valid SQL statement is issued but fails because of lack of proper authorization or because it references a nonexistent schema object. Statements that failed to execute because they simply were not valid cannot be audited. For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but have failed for other reasons (such as when CREATE TABLE is set but a CREATE TABLE statement fails due to lack of quota for the specified tablespace).

Using either form of the AUDIT statement, you can include:

• The WHENEVER SUCCESSFUL clause, to audit only successful executions of the audited statement
• The WHENEVER NOT SUCCESSFUL clause, to audit only unsuccessful executions of the audited statement
• Neither of the previous clauses, to audit both successful and unsuccessful executions of the audited statement

BY SESSION and BY ACCESS Clauses of Audit Statement

Most auditing options can be set to indicate how audit records should be generated if the audited statement is issued multiple times in a single user session. This section describes the distinction between the BY SESSION and BY ACCESS clauses of the AUDIT statement.

See Also: Oracle9i SQL Reference

BY SESSION

For any type of audit (schema object, statement, or privilege), BY SESSION inserts only one audit record in the audit trail, for each user and schema object, during the session that includes an audited action.

A session is the time between when a user connects to and disconnects from an Oracle database.

BY SESSION Example 1

Assume the following:

• The SELECT TABLE statement auditing option is set BY SESSION.
• JWARD connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database.
• SWILLIAMS connects to the database and issues three SELECT statements against the table employees and then disconnects from the database.

In this case, the audit trail contains two audit records for the eight SELECT statements-- one for each session that issued a SELECT statement.

BY SESSION Example 2

Alternatively, assume the following:

• The SELECT TABLE statement auditing option is set BY SESSION.
• JWARD connects to the database and issues five SELECT statements against the table named departments, and three SELECT statements against the table employees, and then disconnects from the database.

In this case, the audit trail contains two records--one for each schema object against which the user issued a SELECT statement in a session.

Note: If you use the BY SESSION clause when directing audit records to the operating system audit trail, Oracle generates and stores an audit record each time an access is made. Therefore, in this auditing configuration, BY SESSION is equivalent to BY ACCESS.

BY ACCESS

Setting audit BY ACCESS inserts one audit record into the audit trail for each execution of an auditable operation within a cursor. Events that cause cursors to be reused include the following:

• An application, such as Oracle Forms, holding a cursor open for reuse
• Subsequent execution of a cursor using new bind variables
• Statements executed within PL/SQL loops where the PL/SQL engine optimizes the statements to reuse a single cursor

Note that auditing is not affected by whether a cursor is shared. Each user creates her or his own audit trail records on first execution of the cursor.

For example, assume that:

• The SELECT TABLE statement auditing option is set BY ACCESS.
• JWARD connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database.
• SWILLIAMS connects to the database and issues three SELECT statements against the table departments and then disconnects from the database.

The single audit trail contains eight records for the eight SELECT statements.

Defaults and Excluded Operations

The AUDIT statement lets you specify either BY SESSION or BY ACCESS. However, several audit options can be set only BY ACCESS, including:

• All statement audit options that audit DDL statements
• All privilege audit options that audit DDL statements

For all other audit options, BY SESSION is used by default.

Audit By User

Statement and privilege audit options can audit statements issued by any user or statements issued by a specific list of users. By focusing on specific users, you can minimize the number of audit records generated.

Audit By User Example

To audit statements by the users SCOTT and BLAKE that query or update a table or view, issue the following statements:

AUDIT SELECT TABLE, UPDATE TABLE 

     BY scott, blake;

See Also: Oracle9i SQL Reference for more information about auditing by user

Audit in a Multitier Environment

In a multitier environment, Oracle preserves the identity of a client through all tiers. This enables auditing of actions taken on behalf of the client. To do so, use the BY proxy clause in your AUDIT statement.

This clause allows you a few options. You can:

• Audit SQL statements issued by the specific proxy on its own behalf
• Audit statements executed on behalf of a specified user or users
• Audit all statements executed on behalf of any user

The middle tier can set the light-weight user identity in a database session so that it will show up in audit trail. You use OCI or PL/SQL to set the client identifier.