USER MANAGEMENT AND DATABASE SECURITY in Font

Print QR-Code in Font USER MANAGEMENT AND DATABASE SECURITY

2. Create a pair of logon and logoff triggers: SQL> CREATE OR REPLACE TRIGGER logon_audit_trig 2 AFTER LOGON 3 ON DATABASE 4 BEGIN 5 INSERT INTO logon_audit 6 VALUES 7 (user, 8 sys_context('userenv', 'sessionid'), 9 sysdate, 10 null, 11 sys_context('userenv', 'host')); 12* END; Trigger created. SQL> CREATE OR REPLACE TRIGGER logoff_audit_trig 2 AFTER LOGON 3 ON DATABASE 4 BEGIN 5 INSERT INTO logon_audit 6 VALUES 7 (user, 8 sys_context('userenv', 'sessionid'), 9 null, 10 sysdate, 11 sys_context('userenv', 'host')); 12* END; Trigger created. SQL> 3. Review your users login/logout details: SQL> SELECT * FROM logon_audit; USER_NAME SESS_ID LOGON_TIME LOGOFF_TIME HOST_NAME ---------------------------------------------------------------------SYSTEM 347 13-JUN-2005 07:00:30 NTL-ALAPATI HR 348 13-JUN-2005 07:10:31 NTL-ALAPATI HR 348 13-JUN-2005 07:32:17 NTL-ALAPATI SQL> You could also use a DDL trigger to capture changes made to objects by users, including modification, creation, and deletion of various types of objects. You can capture a large number of attributes about the event and the user that sets off a DDL trigger. To capture some of the important attributes of a user event, you first need to create a table to log DDL changes. Once you have done that, you can create a DDL trigger like the one shown in Listing 11-16. In this example, the table is named DDL_LOG and the trigger is DDL_LOG_TRIG. Listing 11-16. Using DDL Triggers to Audit Users SQL> 2 3 4 5 CREATE OR REPLACE TRIGGER ddl_log_trig AFTER DDL ON DATABASE BEGIN INSERT INTO ddl_log

6 (username, 7 change_date, 8 object_type, 9 object_owner, 10 database 11 ) 12 VALUES 13 (ora_login_user, 14 sysdate, 15 ora_dict_obj_type, 16 ora_dict_obj_owner, 17 ora_database_name) 16* END; Trigger created. SQL> Once the trigger is in use, you can query the ddl_log table to see the changes. As you can see here, users HR and SYSTEM have made several DDL-based changes to the database: SQL> SELECT * FROM ddl_log; USERNAME CHANGE_DATE OBJECT_TYPE OBJECT_OWNER DATABASE_NAME --------------------------------------------------------------------HR 11-JUN-05 SYNONYM HR NINA SYSTEM 11-JUN-05 OBJECTPRIVILEGE SYSTEM NINA HR 11-JUN-05 TRIGGER HR NINA SQL>

In addition to using the standard Oracle auditing features described in the previous sections, you can also take advantage of Oracle s Flashback capabilities to audit changes made to a table s rows. For example, you can use the Flashback Query feature to examine a table s data at a past point in time. Using the Flashback Transaction Query, you can find out all the changes made to a row since a past point in time or an SCN. The Flashback Versions Query will return each version of a row that existed in the specified period. You can easily identify the user and the specific operation that resulted in any inappropriate or unauthorized modifications to data. Using the transaction details from this query, you can go ahead and identify the entire transaction(s) with the help of another flashback feature, Flashback Transaction Query. The Flashback Query, Flashback Versions Query, and Flashback Transaction Query features rely on undo data and are discussed in detail in 6.

Suppose you re interested in using auditing to find out whether users are viewing data in a table they re not really supposed to access. For example, say a manager is supposed to be able to see salary-related information for employees working for him. Can you tell whether the manager is also looking at the salary information of his superiors Do you need to audit all the SELECT statements done by the manager Auditing all SELECT statements would lead to a colossal amount of audit data, but fortunately there s an easy out. Oracle lets you audit actions in the database on the basis of content. That is, you can specify that the audit records be written not for all SELECT, INSERT, UPDATE, and DELETE statements, but only for statements that meet certain criteria. Instead of trying to determine policy