According to the industry requirement of security policies based on password complexity rules Oracle 10g database, can incorporate these rules into a password verify function.
Here is an example of three password-complexity requirements and how they are satisfied through a password verify function named MY_PASSWORD_VERIFY.
If the new password fails any of these tests, the function raises an exception, and the password change fails.
After creating this function as user SYS, assign it to a profile, like this: ALTER PROFILE student LIMIT password_verify_function my_password_verify;
Any user having the student profile will have to abide by the password rules enforced by the my_password_verify function:
CREATE OR REPLACE FUNCTION my_password_verify
) RETURN BOOLEAN IS
-Check for the minimum length of the password
IF LENGTH(password) < 6 THEN
'Password must be at least 6 characters long'); END IF;
-Check that the password does not contain any
-upper/lowercase version of either the user name
-or the keyword PASSWORD
IF ( regexp_like(password,username,'i')
OR regexp_like(password,'password','i')) THEN
'Password cannot contain username or PASSWORD');
-Check that the password contains at least one letter,
-one digit and one punctuation character
IF NOT( regexp_like(password,'[[:digit:]]') AND regexp_like(password,'[[:
alpha:]]') AND regexp_like(password,'[[:punct:]]')
'Password must contain at least one digit '||
'and one letter and one punctuation character'); END IF;
-password is okey dokey
Audit records can be stored in the database or in operating system files for greater security. This location can be indicated by setting the initialization parameter AUDIT_TRAIL.
You cannot change this parameter in memory, only in your PFILE or SPFILE. For example, the following statement will change the location of audit records in the SPFILE:
ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;
After changing the audit_trail parameter, you will need to bounce (shut down and start up) your database instance for the change to take effect.
When recorded in the database, most audit entries are recorded in the SYS.AUD$ table. On Unix systems, operating system audit records are written into files in the directory specified by the initialization parameter audit_file_dest (which defaults to $ORACLE_HOME/rdbms/audit).
On Microsoft Windows systems, these audit records are written to the Event Viewer log file.
Statement auditing involves monitoring and recording the execution of specific types of SQL statements. In the following sections, you will learn how to enable and disable statement auditing as well as identify what statement auditing options are enabled.
You enable auditing of specific SQL statements with an AUDIT statement. For example, to audit the SQL statements CREATE TABLE, DROP TABLE, or TRUNCATE TABLE, use the TABLE audit option like this:
To record audit entries for specific users only, include a BY USER clause in the AUDIT statement. For example, to audit CREATE, DROP, or TRUNCATE TABLE statements for user juanita only, execute the following:
AUDIT table BY juanita;
Frequently, you want to record only attempts that fail—perhaps to look for users who are probing the system to see what they can get away with. To further limit auditing to only these unsuccessful executions, use a WHENEVER clause like this:
AUDIT table BY juanita WHENEVER NOT SUCCESSFUL;
You can alternately specify WHENEVER SUCCESSFUL to record only successful statements. If you do not include a WHENEVER clause, both successful and unsuccessful statements trigger audit records.
You can further configure non-DDL statements to record one audit entry for the triggering session or one entry for each auditable action during the session. Specify BY ACCESS or BY SESSION in the AUDIT statement, like this:
AUDIT INSERT TABLE BY juanita BY ACCESS;
There are many auditing options other than TABLE or INSERT TABLE.
You can identify the statement auditing options that have been enabled in your database by querying the DBA_STMT_AUDIT_OPTS data dictionary view.
To disable auditing of a specific SQL statement, use a NOAUDIT statement, which allows the same BY and WHENEVER options as the AUDIT statement. If you enable auditing for a specific user, specify that user in the NOAUDIT statement as well. However, it is not necessary to include the WHENEVER NOT SUCCESSFUL clause in the NOAUDIT statement.
Statement, privilege, and object audit records are written to the SYS.AUD$ table and made available via the data dictionary views DBA_AUDIT_TRAIL and USER_AUDIT_TRAIL. These data dictionary views may not contain values for every record because this view is used for three different types of audit records.
If AUDIT SESSION is enabled, the database creates one audit record when a user logs on and updates that record when the user logs off successfully. These session audit records contain some valuable information that can help you narrow the focus of your tuning efforts. Among the information recorded in the audit records are the username, logon time, logoff time, and the number of physical reads and logical reads performed during the session. By looking for sessions with high counts of logical or physical reads, you can identify high-resource-consuming jobs and narrow the focus of your tuning efforts.
Privilege auditing involves monitoring and recording the execution of SQL statements that require a specific system privilege, such as SELECT ANY TABLE or GRANT ANY PRIVILEGE. You can audit any system privilege. In the following section, you will learn how to enable and disable privilege auditing as well as identify which privilege auditing options are enabled in your database.
You enable privilege auditing with an AUDIT statement, specifying the system privilege that you want to monitor. For example, to audit statements that require the system privilege CREATE ANY TABLE, execute the following:
AUDIT create any table;
To record audit entries for specific users only, include a BY USER clause in the AUDIT statement. For example, to audit SQL statements made by user juanita that require the CREATE ANY TABLE privilege, execute the following:
AUDIT create any table BY juanita;
Just as you do with statement auditing, you can further configure non-DDL privileges to record one audit entry for the triggering session or one for each auditable action during the session by specifying BY ACCESS or BY SESSION in the AUDIT statement, like this:
AUDIT DELETE ANY TABLE BY juanita BY ACCESS;
You can report on the privilege auditing that has been enabled in your database by querying the DBA_PRIV_AUDIT_OPTS data dictionary view.
To disable auditing of a system privilege, use a NOAUDIT statement. The NOAUDIT statement allows the same BY options as the AUDIT statement. If you enable auditing for a specific user, you need to specify that user in the NOAUDIT statement.
Object auditing involves monitoring and recording the execution of SQL statements that require a specific object privilege, such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE.
Unlike either statement or system privilege auditing, schema object auditing cannot be restricted to specific users—it is enabled for all users or no users. In the following sections, you will learn how to enable and disable object auditing options as well as identify which object auditing options are enabled.
You enable object auditing with an AUDIT statement, specifying both the object and object privilege that you want to monitor.
You can further configure these audit records to record one audit entry for the triggering session or one for each auditable action during the session by specifying BY ACCESS or BY SESSION in the AUDIT statement. This access/session configuration can be defined differently for successful or unsuccessful executions.
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.
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.
To disable object auditing, use a NOAUDIT statement, which allows the same WHENEVER options as the AUDIT statement.
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 interface to assist in deleting rows from the audit table, so you will need to do so yourself. To purge audit records older than 90 days, execute the following as user SYS:
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;
The audit table does not have a self-managing purge job and will grow without bounds. To keep your SYSTEM tablespace from getting too large, you should regularly delete old entries from the sys.aud$ table.
Fine-grained auditing (FGA) lets you monitor and record data access based on the content of the data. With FGA, you define an audit policy on a table and optionally a column. When the specified condition evaluates to TRUE, an audit record is created, and an optional event-handler program is called. You use the PL/SQL package DBMS_FGA to configure and manage FGA.
To create a new FGA policy, use the packaged procedure DBMS_FGA.ADD_POLICY. This procedure has the following parameters: object_schema This is the owner of the object to be audited. The default is NULL, which tells the database to use the current user.
object_name This is the name of the object to be monitored.
policy_name This is a unique name for the new policy.
audit_condition This is a SQL expression that evaluates to a Boolean. When this condition evaluates to either TRUE or NULL (the default), an audit record can be created. This condition cannot 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.
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:
This is a Boolean that tells the database if this policy should be in effect. The default is TRUE.
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.
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.
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 database 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 should be created.
To create a new disabled audit policy named COMPENSATION_AUD that looks for SELECT statements that access the HR.EMPLOYEES table and references either SALARY or COMMISSION_ PCT, execute the following:
Use the procedure DBMS_FGA.ENABLE_POLICY to enable an FGA policy. This procedure will not raise an exception if the policy is already enabled.
To turn off a fine-grained access policy, use the DBMS_FGA.DISABLE_POLICY procedure.
Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in your database.
The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries that have been recorded in the database.