Auditing Database Activity
345
Identifying Enabled Object Auditing Options
The object auditing options that are enabled in the database are recorded in the DBA_OBJ_
AUDIT_OPTS data dictionary view. Unlike the statement and privilege _AUDIT_OPTS views, the
DBA_OBJ_AUDIT_OPTS always has one row for each auditable object in the database. There are
columns for each object privilege that auditing can be enabled on, and in each of these columns,
a code is reported that shows the auditing options. For example, the following report on the
HR.EMPLOYEES table shows that no auditing is enabled for the INSERT object privilege and that
the SELECT object privilege has auditing enabled with one audit entry for each access when the
access is successful and one audit entry for each session when the access is not successful:
SELECT owner, object_name, object_type, ins, sel
FROM dba_obj_audit_opts
WHERE owner='HR'
AND object_name='EMPLOYEE_SALARY';
OWNER OBJECT_NAME OBJECT_TY INS SEL

HR EMPLOYEE_SALARY TABLE -/- A/S
The coding for the object privilege columns contains one of three possible values: a dash (-) to
indicate no auditing is enabled), an A to indicate BY ACCESS, or an S to indicate BY SESSION. The
first code (preceding the slash) denotes the action for successful statements, and the second code
(after the slash) denotes the action for unsuccessful statements.
Disabling Object Auditing
To disable object auditing, use a NOAUDIT statement, which allows the same WHENEVER options
as the AUDIT statement. For example, to disable auditing of unsuccessful SELECT statements
against the HR.EMPLOYEES table, execute the following:
NOAUDIT select ON hr.employee_salary WHENEVER NOT SUCCESSFUL;
Purging the Audit Trail
Database audit records for statement, privilege, and object auditing are stored in the table
SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need
to periodically delete old audit records from this table. The database does not provide an inter-

directly use the SYSDATE, UID, USER, or USERENV functions, it cannot use subqueries or sequences,
nor can it reference the pseudocolumns LEVEL, PRIOR, or ROWNUM.
audit_column This is a comma-delimited list of columns that the database will look to access.
If a column in audit_column is referenced in the SQL statement and the audit_condition is
not FALSE, an audit record is created. Columns appearing in audit_column do not have to also
appear in the audit_condition expression. The default value is NULL, which tells the database
that any column being referenced should trigger the audit record.
handler_schema This is the owner of the event-handler procedure. The default is NULL, which
tells the database to use the current schema.
handler_module This is the name of the event-handler procedure. The default NULL tells the
database to not use an event handler. If the event handler is a packaged procedure, the handler_
module must reference both the package name and program, using dot notation, like this:
UTL_MAIL.SEND_ATTACH_RAW
4367.book Page 346 Wednesday, October 13, 2004 1:18 PM
Auditing Database Activity
347
enable This is a Boolean that tells the database if this policy should be in effect. The default
is TRUE.
statement_types This tells the database which types of statements to monitor. Valid values are
a comma-delimited list of SELECT, INSERT, UPDATE, and DELETE. The default is SELECT.
audit_trail This parameter tells the database whether to record the SQL statement and bind
variables for the triggering SQL in the audit trail. The default value DBMS_FGA.DB_EXTENDED
indicates that the SQL statement and bind variables should be recorded in the audit trail. Set this
parameter to DBMS_FGA.DB to save space by not recording the SQL statement or bind variables
in the audit trail.
audit_column_ops This parameter has only two valid values: DBMS_FGA.ALL_COLUMNS and
DBMS_FGA.ANY_COLUMNS. When set to DBMS_FGA.ALL_COLUMNS, this parameter tells the data-
base that all columns appearing in the audit_column parameter must be referenced in order to
trigger an audit record. The default is DBMS_FGA.ANY_COLUMNS, which tells the database that if
any column appearing in the audit_column also appears in the SQL statement, an audit record

DBMS_FGA.DISABLE_POLICY(object_schema=>'HR'
,object_name=>'EMPLOYEES'
,policy_name=>'COMPENSATION_AUD');
Dropping an FGA Policy
To remove an FGA audit policy, use the DBMS_FGA.DROP_POLICY procedure. For example, to
drop the COMPENSATION_AUD policy used in this section, run this:
DBMS_FGA.DROP_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);
Identifying FGA Policies in the Database
Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in
your database. For example, the following report shows that the policy named COMPENSATION_
AUD on the column SALARY in the table HR.EMPLOYEES is defined, but not enabled:
SELECT policy_name ,object_schema||'.'||
object_name object_name
,policy_column
,enabled ,audit_trail
FROM dba_audit_policies;
POLICY_NAME OBJECT_NAME POLICY ENABLED AUDIT_TRAIL

COMPENSATION_AUD HR.EMPLOYEES SALARY NO DB_EXTENDED
Audit records from this policy, when enabled, capture the standard auditing information as
well as the text of the SQL statement that triggered the auditing (DB_EXTENDED).
Reporting on the FGA Audit Trail Entries
The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries
that have been recorded in the database. The following example shows audit trail entries for the
COMPENSATION_AUD policy, listing the database username and the timestamp of the audit record
and computer from which the database connection was made.
SELECT db_user, timestamp, userhost
FROM dba_fga_audit_trail

Know the differences between the WITH ADMIN OPTION and the WITH GRANT OPTION
keywords. The ADMIN option applies to role or system privileges, but the GRANT option applies
to object privileges
Know how to enable roles. Know when a role needs to be enabled and how to enable it.
Be able to secure your database. Make sure you know how to lock down you database.
Know which packages should be secured and how to secure them.
4367.book Page 349 Wednesday, October 13, 2004 1:18 PM
350
Chapter 6

User Administration and Security
Know how to implement password security. An Oracle 10g database affords you several
standard password security settings. Know what is available in a profile and what needs to be
implemented in a password-verify function.
Know how to enable, disable and identify enabled auditing options. Be able to describe the
types of auditing, how to enable them, and how to report on the audit trail.
4367.book Page 350 Wednesday, October 13, 2004 1:18 PM
Review Questions
351
Review Questions
1. Which of the following statements creates an Oracle account, but lets the operating system
authenticate logons?
A. create user ops$admin identified by os;
B. create user ops$admin identified externally;
C. create user ops$admin nopassword;
D. create user ops$admin authenticated by os;
2. Which of the following types of statements can use a temporary tablespace?
A. An index creation
B. SQL statements with a GROUP BY clause
C. A hash join operation

C. Stuart loses his SELECT privilege if Ian was dropped with the CASCADE REVOKE option.
D. Stuart retains his SELECT privilege if Ian was dropped with the NOCASCADE REVOKE option.
7. User system granted SELECT ANY TABLE to user ian using WITH ADMIN OPTION. Ian then
granted SELECT ANY TABLE to user stuart. Ian has left the company, and his account is
dropped. What happens to Stuart’s privileges?
A. Stuart loses his privileges.
B. Stuart retains his privileges.
C. Stuart loses his privileges if Ian was dropped with the CASCADE REVOKE option.
D. Stuart retains his privileges if Ian was dropped with the NOCASCADE REVOKE option.
8. Which of the following system privileges can allow the grantee to masquerade as another user
and therefore should be granted judiciously?
A. CREATE ANY JOB
B. ALTER USER
C. CREATE ANY PROCEDURE
D. All of the above
9. Which of the following statements enables the role user_admin in the current session?
A. alter session enable role user_admin;
B. alter session set role user_admin;
C. alter role user_admin enable;
D. set role user_admin;
10. Which of the following SQL statements allows user augustin to use the privileges associated
with the password-protected role info_czar, which has been granted to him?
A. set role all;
B. alter user augustin default role all;
C. alter session enable role info_czar;
D. alter session enable info_czar identified by brozo
11. By default, how much tablespace can any account use for a new table?
A. None
B. Up to the current free space in the tablespace
C. Unlimited space, including autoextends

Select the best answer.
A. Password-protected roles require a password before they can become enabled.
B. You can disable any role that you find in your session_roles view.
C. If you execute alter profile student limit idle_time 10; and then execute alter
user scott profile student;, then user scott will be disconnected from future sessions
after 10 minutes of idle time.
D. You can limit a table to a maximum size on disk.
4367.book Page 353 Wednesday, October 13, 2004 1:18 PM
354
Chapter 6

User Administration and Security
17. Which of the following SQL statements limit attempts to guess passwords by locking an account
after three failed logon attempts?
A. alter profile default limit failed_login_attempts 3;
B. alter system set max_logon_failures = 3 scope=both;
C. alter user set failed_login_attempts = 3;
D. alter system set failed_login_attempts = 3 scope=both;
18. Where can the database write audit_trail records?
A. In a database table
B. In a file outside the database
C. Both in the database and in an operating system file
D. Either in the database or in an operating system file
19. Which of the following activities can be audited?
A. Unsuccessful deletions from the audit_trail table
B. Unsuccessful selects from the employee_salary table
C. All GRANT and REVOKE statements on procedures executed by user system
D. All of the above
20. How do you manage fine-grained auditing?
A. With the AUDIT and NOAUDIT statements

the grantee to change a user’s password, connect as that user, and then change the password
back. These are all powerful system privileges and should be restricted to as few administrative
users as practical.
9. D. The SET ROLE statement enables or disables roles in the current session.
10. B. To enable a password-protected role, you need to either execute a SET ROLE statement spec-
ifying the password or alter the user to make the role a default role. Default roles do not require
a set role statement or a password to become enabled.
11. A. By default, user accounts have no quota in any tablespace. Before a user can create a table or
an index, you need to either give the user a quota in one or more specific tablespaces, or grant
the UNLIMITED TABLESPACE system privilege to give unlimited quota (including autoextends) in
all tablespaces.
12. C. Profiles limit the amount of idle time, CPU time, logical reads, or other resource-oriented
session limits. Option C uses the correct syntax to limit idle time for a session to 30 minutes.
4367.book Page 355 Wednesday, October 13, 2004 1:18 PM
356
Chapter 6

User Administration and Security
13. B. Although option D could also work, it involves storing the passwords in a table in the data-
base, which could be a security concern. It also takes a lot more effort to configure and maintain.
The better technique is to use the standard database profile features PASSWORD_RESUSE_TIME
and PASSWORD_REUSE_MAX. Setting one of these profile parameters to UNLIMITED and the other
to a specific value prevents passwords from being reused. If both of these profile parameters are
set to UNLIMITED, these parameters are essentially disabled. There is no initialization parameter
called NO_PASSWORD_REUSE.
14. C. There are no standard password complexity settings in either the initialization parameters or
profiles. A password verify function can validate new passwords against any rules that you can
code in PL/SQL, including regular expression comparisons.
15. D. The object privileges on a table include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX,
REFERENCES, and DEBUG, but not READ. READ is a valid object privilege, but only on a direc-


Managing Data With
SQL, PL/SQL, and
Utilities

ORACLE DATABASE 10

G

:
ADMINISTRATION I EXAM OBJECTIVES
COVERED IN THIS CHAPTER:


Managing Data


Manipulate data through SQL using INSERT, UPDATE,
and DELETE.


Use Data Pump to export data.


Use Data Pump to import data.


Load Data with SQL Loader.



ing, and some functionality is available in both a command-line version or as PL/SQL programs,
such as Data Pump export and Data Pump import. As you gain experience, you will increasingly
rely on using PL/SQL to manage your databases. So you need to have a solid grasp of SQL and
PL/SQL fundamentals to be a successful Oracle10

g

DBA.
In this chapter, you will learn how to create, change, and remove information from an Oracle
database using SQL and PL/SQL.

Manipulating Data through SQL

The Structured Query Language, SQL for short, includes Data Definition Language (DDL)
statements, Data Control Language (DCL) statements, and Data Manipulation Language
(DML) statements. You learned how to create, alter, and delete objects using DDL statements
in Chapter 3, “Database Storage and Schema Objects.” Chapter 6, “User Administration and
Security,” showed you how to use the DCL statements

GRANT

and

REVOKE

to give and take priv-
ileges on database objects. In this section, you will learn how to use the DML statements

INSERT


INSERT

statement to add rows to one or more tables. You can create these rows
with specific data values or copy them from existing tables using a subquery.

Inserting into a Single Table

When using SQL, the only way to add rows to an Oracle10

g

table is with an

INSERT

statement
and the most common variety of

INSERT

statement is the single table insert. Figure 7.1 shows
a diagram of the syntax for the single-table

INSERT

statement.

4367.book Page 358 Wednesday, October 13, 2004 1:18 PM

Manipulating Data through SQL


COLUMN_ID

. While inserting into a table is more common, you can also insert
into a view, as long as the view does not contain one of the following:


A

DISTINCT

operator


A set operator (

UNION

,

MINUS

, and so on)


An aggregate function (

SUM

,

Here are some examples of using the

INSERT

statement to insert rows into a single table. The
following inserts one row, channel 3, in the

channels

table:

INSERT INTO channels (channel_id ,channel_desc
,channel_class ,channel_class_id
,channel_total ,channel_total_id) VALUES
(3 ,'Direct Sales' ,'Direct'

,12 ,'Channel total' ,1);

The following inserts one row, channel 5, in the

channels

table:

INSERT INTO channels VALUES

(5 ,'Catalog' ,'Indirect' ,13 ,'Channel total' ,1);

The following copies zero or more rows from the


;

4367.book Page 359 Wednesday, October 13, 2004 1:18 PM

360

Chapter 7


Managing Data With SQL, PL/SQL, and Utilities

FROM territories@home_office

WHERE class = 'R';

The number and datatypes of values in the

VALUES

list must match the number and datatypes
in the column list. The database will perform implicit datatype conversion if necessary to con-
vert the values into the datatype of the target.

Inserting into Multiple Tables

Most

INSERT

statements are the single-table variety, but Oracle also supports a multiple-table

TRUNC(SYSDATE,'MM') - TO_YMINTERVAL('00-01');
By using this multitable INSERT statement instead of two separate statements, the code makes
a single pass through the policy table instead of two and thus loads the data more efficiently.
4367.book Page 360 Wednesday, October 13, 2004 1:18 PM
Manipulating Data through SQL
361
FIGURE 7.2 The syntax for the multitable INSERT statement
Using UPDATE Statements
You use an UPDATE statement to change existing rows in a table. Figure 7.3 shows a diagram
of the syntax for the UPDATE statement.
The column list can be either a single column or a comma-delimited list of columns. A
single list of columns lets you assign single values—either literals or from a subquery. The
following updates customer XYZ’s phone and fax numbers, and sets their quantity based on
their orders:
UPDATE order_rollup r
SET phone = '3125551212'
,fax = '7735551212'
,qty = (SELECT SUM(d.qty)
FROM order_details d
WHERE d.customer_id = r.customer_id)
WHERE r.customer_id = 'XYZ';
Like the CREATE TABLE and ALTER TABLE statements you saw in Chapter 6, when you use a
comma-delimited list of columns, you must enclose them in parentheses. The comma-delimited
list lets you assign multiple values from a subquery. The following updates both the quantity and
price for customer XYZ for the order they placed on October 1, 2004:
UPDATE order_rollup
SET (qty, price) = (SELECT SUM(qty), SUM(price)
FROM order_details
WHERE customer_id = 'XYZ')
WHERE customer_id = 'XYZ'

4367.book Page 361 Wednesday, October 13, 2004 1:18 PM
362
Chapter 7

Managing Data With SQL, PL/SQL, and Utilities
FIGURE 7.3 The syntax for the UPDATE statement
Assigning multiple values from a single subquery can save you from having to perform mul-
tiple subqueries, thus improving the efficiency of your SQL.
Using DELETE Statements
You use the DELETE statement to remove rows from a table. A diagram of the syntax for the
DELETE statement is shown in Figure 7.4.
Here are some examples of a DELETE statement. The following removes orders from certain
states:
DELETE FROM orders
WHERE state IN ('TX','NY','IL')
AND order_date < TRUNC(SYSDATE) - 90
The following removes customer GOMEZ:
DELETE FROM customers
WHERE customer_id = 'GOMEZ';
The following removes duplicate line_detail_ids. Note that the keyword FROM is not
needed.
DELETE line_details
WHERE rowid NOT IN (SELECT MAX(rowid)
FROM line_detail
GROUP BY line_detail_id)
Remove all rows from the table order_staging
DELETE FROM order_staging;
The WHERE clause is optional, and when it is not present, all rows from the table are removed.
If you need to remove all rows from a table, consider using the TRUNCATE statement. TRUNCATE
is a DDL statement and, unlike the DELETE statement, does not support a ROLLBACK. Using

Full knowledge of the PL/SQL language is well beyond the scope of the OCA/OCP exams, and
more developers than DBAs create PL/SQL programs. But a significant number of database fea-
tures are delivered as PL/SQL programs, and knowledge of how to identify and work with these
programs is crucial to your effectiveness. In this section, you will learn what kinds of PL/SQL pro-
grams are available, when each is appropriate, and what configuration options are applicable to
working with PL/SQL programs.
The exam covers five types of named PL/SQL programs, which are usually stored in the data-
base: functions, procedures, packages, package bodies, and triggers. Each of these program
types is covered in the following sections. The name and source code for each stored PL/SQL
program is available from the data dictionary views DBA_SOURCE and DBA_TRIGGERS, although
some packages are supplied “wrapped,” which means that the source code is a binary form.
You can wrap your programs as well with the wrap utility.
See the PL/SQL Users Guide and Reference for details on using wrap.
Working with Functions
Functions are PL/SQL programs that execute zero or more statements and return a value
through a RETURN statement. Functions can also receive or return zero or more values through
their parameters. Oracle provides several built-in functions such as the commonly used
SYSDATE, COUNT, and SUBSTR functions. There are over 200 SQL functions that come with your
Oracle10g database and hundreds of PL/SQL functions. See the Oracle Database SQL Refer-
ence 10g manual (part B10759-01) for a complete list of these SQL functions and the PL/SQL
Packages and Types Reference 10g manual (part B10802-01) for the functions available with
DELETE
schema .FROM
DBLink@
DBLink@
table
view
conditions
WHERE
;

BEGIN
CASE TO_CHAR(check_date,'DY')
WHEN 'SAT' THEN
RETURN 'YES';
WHEN 'SUN' THEN
RETURN 'YES';
ELSE
RETURN 'NO';
END CASE;
END;
Functions, like all named PL/SQL, have the OR REPLACE keywords available in the
CREATE statement. When present, OR REPLACE tells the database to not raise an exception
if the object already exists. This behavior differs from a DROP and CREATE, in that privileges
are not lost during a REPLACE operation and any objects that reference this object will not
become invalid.
4367.book Page 364 Wednesday, October 13, 2004 1:18 PM
Identifying PL/SQL Objects
365
Working with Procedures
Procedures are PL/SQL programs that execute one or more statements. Procedures can receive
and return values only through their parameter lists. Unlike functions, only a few built-in pro-
cedures, such as RAISE_APPLICATION_ERROR are built into the PL/SQL language.
You create a procedure with the CREATE PROCEDURE statement, like this:
CREATE OR REPLACE PROCEDURE archive_orders
(cust_id IN NUMBER
,retention IN NUMBER) IS
BEGIN
DELETE orders
WHERE customer = cust_id
AND order_date < SYSDATE - retention;

FUNCTION version RETURN VARCHAR2;
PROCEDURE truncate (table_name IN VARCHAR2);
END table_util;
Privileges on a package are granted at the package-spec level. The EXECUTE privilege on a
package allows the grantee to execute any program or use any data structure declared in the
package specification. You cannot grant the EXECUTE privilege on only some of the programs
declared in the spec.
A package body depends on a package spec having the same name. The package body can
only be created after the spec. The package body implements the programs that were declared
in the package spec and can optionally contain private programs and data accessible only from
within the package body.
To create a package body, use the CREATE PACKAGE BODY statement:
CREATE OR REPLACE PACKAGE BODY table_util IS
Here is an example of a private variable that can be referenced only in the package body:
version_string VARCHAR2(8) := '1.0.0';
Here is the code for the version function:
FUNCTION version RETURN VARCHAR2 IS
BEGIN
RETURN version_string;
END;
Here is the code for the truncate procedure:
PROCEDURE truncate (table_name IN VARCHAR2) IS
BEGIN
IF UPPER(table_name) = 'ORDER_STAGE'
OR UPPER(table_name) = 'SALES_ROLLUP'
THEN
EXECUTE IMMEDIATE 'truncate table ' ||
UPPER(table_name);
ELSE
RAISE_APPLICATION_ERROR(-20010

IF INSERTING THEN if fired due to insert
:NEW.create_user := USER;
:NEW.create_ts := SYSTIMESTAMP;
ELSEIF UPDATING THEN if fired due to update
IF :OLD.hire_date <> :NEW.hire_date THEN
RAISE_APPLICATION_ERROR(-20013,
'update of hire_date not allowed');
END IF;
END IF;
END;
4367.book Page 367 Wednesday, October 13, 2004 1:18 PM
368
Chapter 7

Managing Data With SQL, PL/SQL, and Utilities
This trigger will fire once for each row affected, because the keywords FOR EACH ROW are
included. When the triggering event is an INSERT, two columns are forced to the specific values
returned by USER and SYSTIMESTAMP. DML triggers cannot be created on SYS-owned objects.
Table 7.1 shows the DML triggering events.
Multiple triggers on a table fire in the following order:

Before statement triggers

Before row triggers

After row triggers

After statement triggers
DDL Event Triggers
DDL triggers fire either for DDL changes to a specific schema or to all schemas in the database.

||DICTIONARY_OBJ_NAME);
END IF;
END LOOP;
END;
In the preceding example, the DDL event is a GRANT statement issued by user engineering.
The code examines the grantee list, and if it finds the special user/role PUBLIC, an exception is
raised, causing the grant to fail. Table 7.2 shows the DDL triggering events.
TABLE 7.2 DDL Trigger Events
Event When It Fires
ALTER When an ALTER statement changes a database object.
ANALYZE When the database gathers or deletes statistics or validates
the structure of an object.
ASSOCIATE STATISTICS When the database associates a statistic with a database
object with an ASSOCIATE STATISTICS statement.
AUDIT When the database records an audit action (except FGA).
COMMENT When a comment on a table or column is modified.
CREATE When the database object is created.
DDL In conjunction with any of the following: ALTER, ANALYZE,
ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE
STATISTICS, DROP GRANT, NOAUDIT, RENAME, REVOKE, or TRUNCATE.
DISASSOCIATE STATISTICS When a database disassociates a statistic type from a data-
base object with a DISASSOCIATE STATISTICS statement.
DROP When a DROP statement removes an object from the database.
GRANT When a GRANT statement assigns a privilege.
NOAUDIT When a NOAUDIT statement changes database auditing.
4367.book Page 369 Wednesday, October 13, 2004 1:18 PM