Previous | Table of Contents | Next

Page 581


Statement Option
SYNONYM CREATE SYNONYM, DROP SYNONYM
SYSTEM AUDIT AUDIT, NOAUDIT
SYSTEM GRANT GRANT system privileges/role TO user/role REVOKE system privileges/role FROM user/role
TABLE CREATE TABLE, ALTER TABLE, DROP TABLE
TABLESPACE CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE
TRIGGER CREATE TRIGGER, ALTER TRIGGER, ENABLE or DISABLE, ALTER TABLE with ENABLE, DISABLE, and DROP clauses
USER CREATE USER, ALTER USER, DROP USER
VIEW CREATE [OR REPLACE] VIEW, DROP VIEW


Auditing DML on Database Objects

You can audit a specific schema object using the following syntax:

AUDIT object_opt ON schema.object
BY SESSION/ACCESS WHENEVER NOT/SUCCESSFUL;

You can specify an object option, such as insert or update, or you can use the keyword ALL to specify all object options.

AUDIT insert,update
ON scott.emp_table
WHENEVER NOT SUCCESSFUL;

AUDIT ALL
ON scott.emp_table
WHENEVER NOT SUCCESSFUL;

Administering Auditing

If you choose to store the audit records on the database, it is a good idea to run reports on the audit table on a daily basis and then delete the data to conserve space. The audit table should have restricted access, and all activity against the SYS.AUD$ table should be recorded by using the following statement:

AUDIT INSERT, UPDATE, DELETE, SELECT
ON sys.aud$
BY ACCESS;

Auditing can create excessive overhead on a database, so be very selective. The BY SESSION clause in the audit statement causes Oracle to write a single record for all SQL statements of the same type that were used in the same session. The BY ACCESS clause in the audit statement causes Oracle to write one record for each audited statement. If you audit data definition language (DDL) statements, Oracle automatically uses BY ACCESS, no matter which clause is

Page 582

specified. To maintain a secure environment, you should implement a policy to decide which actions to audit.

The following AUDIT SQL statement shows how to use some of the more restrictive options:

AUDIT statement_opt/system_privileges
  BY user (optional)
  BY session/access WHENEVER NOT/SUCCESSFUL;

The next code sample shows how to audit statements relating to roles and a session:

AUDIT role;
AUDIT session whenever not successful;.

Protecting Data Integrity

You can protect data integrity through the use of secure user connections and encryption algorithms. Passwords can now be encrypted when users sign on to an Oracle database. The password is encrypted using a modified DES (data encryption standard) algorithm. Encryption is turned on by setting an environment variable on the client machine and the init.ora parameter on the database server. You must set the client machine's environment variable ORA_ENCRYPT_LOGIN to TRUE.

NOTE
This variable varies depending on the operating system. You must also set the DBLINK_ENCRYPT_LOGIN init.ora parameter to TRUE on the database server.n

Oracle will encrypt the password of a userid when a database session is initiated, but it will not encrypt the altering of the password.

Computer vandals can compromise data integrity by modifying the data or DML that travels on a network, which can result in a logical-corrupt database. When the integrity of a database is in question, it is very costly and time-consuming to restore.

Encrypting all your data is the perfect solution to potential security breeches, because if the data is being passively monitored by computer vandals they can't use the data and will probably look for easier prey. Oracle's Advanced Network Services provides this additional level of security and more. The ANO currently offers two encryption algorithms, RSA and DES, with different key lengths. For use in the U.S. and Canada, 56-bit RSA RC4, 56-bit DES, and 128-bit RSA are available; for export outside the U.S. and Canada, 40-bit RSA RC4 and 40-bit DES40 are available. Data integrity is protected by using cryptographic checksums using the MD5 algorithm. This ensures that the data is not tampered with between the time it leaves a client workstation and the time it arrives at the database server.

Hardware Security

Computer hardware needs to be stored in a restricted area where access is limited and where appropriate fire control mechanisms are in place. The electricity should be clean, meaning not

Page 583

subject to power surges or outages. Clean electricity is usually provided by an uninterrupted power supply (UPS).

Recovering Lost Data

In your security plan, you must specify how to recover data lost due to a security breach. There is a saying: "Your system is only as good as your last backup and your ability to recover." That sums up my philosophy on database recovery. Following are some good questions to ask yourself regarding backups:

There are two ways to back up an Oracle database. The first is by using an operating-system backup called a physical backup; the second is by using the Oracle Export utility, which creates a logical backup.

Operating System Backup

An operating-system backup requires a utility that is specific to the operating system and that enables the DBA to restore the database by using an operating-system restore utility. You can perform the operating-system backup with the database down (a cold backup) or with it up (a hot backup). A hot backup is used in shops that require high-database availability. A hot backup enables tablespaces to be backed up while online and available for transaction processing, or while offline and unavailable. A cold backup must include all Oracle data files, control files, and online redo log files (for reference, I call these files the operating-system backup file set). A hot backup is considered a partial backup because it backs up only the files requested.

You can operate the database in ARCHIVELOG mode and in NOARCHIVELOG mode. The ARCHIVELOG mode means that as the online redo logs are filled, their contents are archived, which frees the current redo log. The archive redo logs are used to recover the transactions recorded on them or to redo them in the event of a database recovery. To perform a hot backup, the database must be in ARCHIVELOG mode. The NOARCHIVELOG mode means that online redo logs are not archived when they are full, but are written over by the latest transaction. Therefore, only the current redo logs are available for recovery. If you don't archive, you must make a backup of the operating-system file set, and when the database is restored, you must restore all the files in the operating-system backup file set.

Previous | Table of Contents | Next