Variables, Operacionalización

Overview

In this practice, as the AUDMGR user you will create an audit policy to monitor activity in the HR.JOBS table and apply it to multiple users.

Assumptions

The AUDMGR user has been created. Several users with DML privileges on HR.JOBS have been created.

Tasks

1. Invoke SQL*Plus and connect to the orcl database as the AUDMGR user. Create a policy named JOBS_AUDIT_UPD that audits all auditable statements for the HR.JOBS table. a. Set the environment for the orcl database by using oraenv.

b. Connect to the orcl database as the AUDMGR user by using SQL*Plus. $ sqlplus audmgr

Enter password: oracle_4U …

SQL>

c. Create an audit policy to track UPDATE commands issued against the HR.JOBS table. SQL> CREATE AUDIT POLICY jobs_audit_upd

2 ACTIONS update ON hr.jobs;

Audit policy created.

d. Verify the creation of the JOBS_AUDIT_UPD policy.

SQL> SELECT audit_option, audit_option_type, object_schema, object_name

2 FROM audit_unified_policies

3 WHERE policy_name = 'JOBS_AUDIT_UPD';

AUDIT_OPTION AUDIT_OPTION_TYPE OBJECT_SCHEMA OBJECT_NAME --- --- --- --- UPDATE OBJECT ACTION HR JOBS

Question: If you had multiple databases with the same users and data, such as a QA and

development databases, how would you make sure that this policy is applied in all the databases?

Answer: There are two ways that have been shown: 1) Create a SQL script and run the

script in the other databases. 2) Use Cloud Control to run in multiple databases. A third option is to re-create the other databases from the production database after the changes have been applied. This technique is out of the scope of this course.

2. Assign the policy to all users.

SQL> AUDIT POLICY jobs_audit_upd;

Audit succeeded.

3. View information about the audit policy.

SQL> column POLICY_NAME format A20 SQL> column USER_NAME format A20

SQL> SELECT policy_name, enabled_opt, 2> user_name, success, failure

3> FROM audit_unified_enabled_policies;

POLICY_NAME ENABLED_ USER_NAME SUC FAI --- --- --- --- --- ORA_SECURECONFIG BY ALL USERS YES YES ORA_LOGON_FAILURES BY ALL USERS NO YES JOBS_AUDIT_UPD BY ALL USERS YES YES

SQL>

4. Test the audit policy by connecting as a user that has privileges to update rows in the HR.JOBS table.

a. Connect as the DHAMBY user and update MAX_SALARY of the President to $50000. SQL> connect DHAMBY

Enter password: Connected.

SQL> desc hr.jobs

Name Null? Type

--- --- --- JOB_ID NOT NULL VARCHAR2(10)

JOB_TITLE NOT NULL VARCHAR2(35) MIN_SALARY NUMBER(6) MAX_SALARY NUMBER(6)

SQL> select * from hr.jobs where job_title = 'President';

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY

--- --- --- ---

AD_PRES President 20080 40000

SQL> update hr.jobs set max_salary = 50000 2> where JOB_ID = 'AD_PRES';

1 row updated.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Practices for Lesson 11: Implementing Oracle Database Auditing

SQL> exit

b. Connect as the AUDMGR user and view the audit trail records for this change. Note: Your output may vary from what is shown depending on how many times you have logged on and logged off as the DHAMBY user. For this practice, you are interested in the row for the JOBS_AUDIT_UPD policy.

$ sqlplus audmgr Enter password:

SQL> col unified_audit_policies format a25 SQL> col action_name format a10

SQL> col object_schema format a10 SQL> col object_name format a10

SQL> select unified_audit_policies, action_name, 2 object_schema, object_name

3 from unified_audit_trail 4 where dbusername = 'DHAMBY';

UNIFIED_AUDIT_POLICIES ACTION_NAM OBJECT_SCH OBJECT_NAM --- --- --- --- JOBS_AUDIT_UPD UPDATE HR JOBS

ORA_SECURECONFIG LOGON

c. If you did not see any rows as a result of the query in step 4b, flush the audit records. Note: The default behavior of the Unified Audit Engine is to queue the audit records and write them to the Unified Audit trail as the queue fills. The

DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL procedure forces the records in the queue to be written to disk. The audit records are not visible until they are written to the audit trail.

SQL> EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; PL/SQL procedure successfully completed.

SQL>

d. View the audit trail records.

SQL> select unified_audit_policies, action_name, 2 object_schema, object_name

3 from unified_audit_trail 4 where dbusername = 'DHAMBY';

UNIFIED_AUDIT_POLICIES ACTION_NAM OBJECT_SCH OBJECT_NAM --- --- --- --- JOBS_AUDIT_UPD UPDATE HR JOBS

ORA_SECURECONFIG LOGON ORA_SECURECONFIG LOGON ORA_SECURECONFIG LOGOFF

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Practices for Lesson 11: Implementing Oracle Database Auditing

Practices for Lesson 12: