-- * A secure system ensures the confidentiality of the data that it contains. -- * There are several aspects of security: -- 1. Restricting access to data and services -- 2. Authenticating users -- 3. Monitoring for suspicious activity -- Audit Types: -- 1. Mandatory Auditing -- Regardless of whether database auditing is enabled, Oracle Database always audits -- certain database-related operations and writes them to the operating system audit file. -- This fact is called mandatory auditing, and it includes the following operations: -- 1. Connections to the instance with administrator privileges -- An audit record is generated that lists the operating system user connecting to Oracle -- Database as SYSOPER or SYSDBA. -- This provides for accountability of users with administrative privileges. -- 2. Database startup -- An audit record is generated that lists the operating system user starting the instance -- and the date and time stamp. -- This data is stored in the operating system audit trail because the database -- audit trail is not available until after the startup has successfully completed. -- 3. Database shutdown -- An audit record is generated that lists the operating system user shutting down the -- instance and the date and time stamp. -- *(win) Start => Control Panel => Performance and Maintenance => Administrative Tools => -- Computer Management => Event Viewer, Application Log (Log viewer) on Windows. -- *(unix) AUDIT_FILE_DEST directory -- -- 2. Standard DB auditing -- - Using the AUDIT command. ("AUDIT DROP TABLE") -- - Audits according to privileges. -- - Can be "By Access" or "By Session" -- (Defaults: SYSTEM privs - By Access, OBJECT privs - By Session) -- - Can be "Whenever Successful" or "Whenever Not Successful" (Default is both) -- - Must be enabled manually via static AUDIT_TRAIL parameter.(=DB / DB_EXTENDED / OS / NONE) -- - DB : DBA_AUDIT_TRAIL -- - OS : Event Log (windows) or Audit_file_dest (Unix) -- - XML: XML file in Audit_file_dest. (Can view with V$XML_AUDIT_TRAIL) -- - AUDIT_TRAIL=DB_EXTENDED: -- - Stores also full SQL + Binds -- - Stores Transaction identifier (To group related statements) -- - 11g behavior: -- - DBCA sets AUDIT_TRAIL=DB -- - Audits Many ANY privileges, GRANTs, CREATE USER etc. by default. -- -- 3. Triggers - Value based auditing -- - Captures the actual values that changed. (Old and New) -- - Uses PL/SQL Triggers. -- - Fires at row level. ("For Each Row") -- -- 4. FGA - Fine Grained Auditing -- - Conditional: -- - Can specify which columns I want to audit. (And specify ANY/ALL) -- - Can specify conditions for auditing. -- - Null columns & Conditions will audit all columns and all rows. -- - Monitors the statements actual result (affected rows), Not the statement's original -- predicate. -- (Accept for SELECT statement which captures the statement itself) -- - Captures the actual SQL statement + Binds. -- - Can execute a procedure. ("Handler Module") -- - Create and maintain Audit Policies via DBMS_FGA. (DBMS_FGA.ADD_POLICY) -- - Works at statement level. (Not ROW-Level like Value-Based triggers) -- -- 5. SYSDBA auditing -- - Stores the audit data also outside the DB, in an OS trail, to track the DBA. -- - Can set the parameter "audit_sys_operations" to TRUE. (Defualt is FALSE) -- * Three relevant tables: -- 1. DBA_AUDIT_TRAIL - Standard Auditing. -- 2. DBA_FGA_AUDIT_TRAIL - FGA -- 3. DBA_COMMON_AUDIT_TRAIL - Combines them both to One central location. ------------------------------------------------------------------------------------------- -- STANDARD DB AUDITING (AUDIT / AUDIT_TRAIL / DBA_AUDIT_TRAIL) ------------------------------------------------------------------------------------------- -- 1. Activate AUDITING (In 11g enabled by default) -- 2. Specify audit options -- 3. Review the audited actions -- 4. Maintenance DROP TABLE hr.emp2; CREATE TABLE hr.emp2 AS SELECT * FROM hr.employees; -- 1. Activate AUDITING (In 11g enabled by default) --------------------------------------------------- show parameter AUDIT_TRAIL -- 1.None -- (or FALSE) Database auditing is disabled, no matter what auditing -- you attempt to configure. -- 2.OS -- Audit records will be written to the operating system’s audit trail: -- *(win) Start => Control Panel => Performance and Maintenance => Administrative Tools => -- Computer Management => Event Viewer, Application Log (Log viewer) on Windows. -- *(unix) AUDIT_FILE_DEST directory -- 3. DB -- (or TRUE) The audit records are written to a data dictionary table, -- SYS.AUD$. There are views that let you see the contents of this table. -- 4. DB_EXTENDED -- (11g) - As DB, but including information on the SQL statements -- with bind variables that generated the audit records. -- show parameter audit_trail -- NONE ALTER SYSTEM SET audit_trail = FALSE SCOPE=SPFILE ; ALTER SYSTEM SET audit_trail = NONE SCOPE=SPFILE ; -- OS ALTER SYSTEM SET audit_trail = OS SCOPE=SPFILE ; -- DB ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE ; -- DB_EXTENDED ALTER SYSTEM SET audit_trail = DB_EXTENDED SCOPE=SPFILE ; ----------------- SHUTDOWN IMMEDIATE STARTUP -- 2. Specify audit options --------------------------- Privilege Auditing ------------------ -- * Enables you to audit the use of powerful system privileges that enable corresponding actions, -- such as AUDIT CREATE TABLE. -- You can set privilege auditing to audit a selected user or every user in the database. -- * Can be "By Access" or "By Session" -- Defaults: SYSTEM privs - By Access, OBJECT privs - By Session -- * Can be "Whenever Successful" or "Whenever Not Successful" -- Default is both -- AUDIT privilege_name [BY username] [by SESSION | ACCESS] [WHENEVER SUCCESSFUL | NOT SUCCESSFUL] NOAUDIT CREATE SESSION ; NOAUDIT CREATE SESSION BY HR; NOAUDIT UPDATE ANY TABLE ; NOAUDIT UPDATE ANY TABLE BY hr ; set lines 200 col audit_option for a30 SELECT user_name, audit_option, success, failure FROM DBA_STMT_AUDIT_OPTS; -- Basic audit AUDIT CREATE SESSION ; SELECT user_name, audit_option, success, failure FROM DBA_STMT_AUDIT_OPTS WHERE audit_option LIKE '%SESSION%'; -- Specify the user AUDIT CREATE SESSION BY hr BY ACCESS ; SELECT user_name, audit_option, success, failure FROM DBA_STMT_AUDIT_OPTS WHERE audit_option LIKE '%SESSION%'; -- audit - (success & failure) by session AUDIT UPDATE ANY TABLE BY hr BY SESSION ; SELECT user_name, audit_option, success, failure FROM DBA_STMT_AUDIT_OPTS WHERE audit_option LIKE '%UPDATE%'; -- Change the audit - (success only) by access AUDIT UPDATE ANY TABLE BY hr BY ACCESS WHENEVER SUCCESSFUL ; -- Change the audit - (failure only) by access AUDIT UPDATE ANY TABLE BY hr BY ACCESS WHENEVER NOT SUCCESSFUL ; -- Change the audit - (failure only) by session AUDIT UPDATE ANY TABLE BY hr BY SESSION WHENEVER NOT SUCCESSFUL ; -- NOAUDIT Options ------------------ NOAUDIT CREATE SESSION ; NOAUDIT CREATE SESSION BY hr ; NOAUDIT UPDATE ANY TABLE ; NOAUDIT UPDATE ANY TABLE BY hr; NOAUDIT UPDATE ANY TABLE BY hr WHENEVER SUCCESSFUL ; -- Schema Object Auditing ------------------------- -- * Enables you to audit specific statements on a particular schema object, -- such as AUDIT SELECT ON employees. -- * Schema object auditing always applies to all users of the database. -- * Can be "By Access" or "By Session" -- Defaults: SYSTEM privs - By Access, OBJECT privs - By Session -- * Can be "Whenever Successful" or "Whenever Not Successful" -- Default is both -- AUDIT object_priv_name ON object_name [BY ACCESS | SESSION][WHENEVER SUCCESSFUL|NOT SUCCESSFUL] CREATE TABLE hr.emp2 AS SELECT * FROM hr.employees ; NOAUDIT DELETE ON hr.emp2 ; col owner for a10 col object_type for a15 col object_name for a15 SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, INS, UPD, DEL FROM DBA_OBJ_AUDIT_OPTS -- basic audit - (success & failure) AUDIT DELETE ON hr.emp2; SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, INS, UPD, DEL FROM DBA_OBJ_AUDIT_OPTS; -- * The character "-" indicates that the audit option is not set. -- * The character "S" indicates that the audit option is set, BY SESSION. -- * The character "A" indicates that the audit option is set, BY ACCESS. -- * Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL -- basic audit - (success & failure) AUDIT DELETE ON hr.emp2; -- audit - (success & failure) by access AUDIT DELETE ON hr.emp2 by access; SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, INS, UPD, DEL FROM DBA_OBJ_AUDIT_OPTS WHERE OBJECT_NAME = 'EMP2'; -- audit - (failure) by session AUDIT DELETE ON hr.emp2 by session Whenever Not Successful; -- audit - (success) by access AUDIT DELETE ON hr.emp2 by access Whenever Successful; -- NOAUDIT OPTIONS ------------------ AUDIT ALL ON hr.emp2 ; NOAUDIT DELETE ON hr.emp2 ; AUDIT ALL ON hr.emp2 ; NOAUDIT ALL ON hr.emp2 ; AUDIT ALL ON hr.emp2 ; NOAUDIT DELETE ON hr.emp2 Whenever Not Successful; -- 3. Review the audited actions -------------------------------- ------------ Demo AUDIT CREATE SESSION Whenever Successful; AUDIT DELETE ON hr.emp2 by access; AUDIT CREATE ANY TABLE; AUDIT UPDATE ON hr.emp2 by access; ----------- Actions grant create any table to hr; conn hr/hr@pdborcl DELETE FROM emp2 WHERE salary > 10000; CREATE TABLE sh.cacthme3(id number); UPDATE hr.emp2 SET salary = salary - 1000 ; conn sys/password@pdborcl as sysdba UPDATE hr.emp2 SET last_name = 'Kishkashta' ; col username for a8 col obj_name for a30 col action_name for a20 col os_username for a15 col priv_used for a17 col obj_name for a15 SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time FROM dba_audit_trail WHERE ACTION_NAME='DELETE'; -- or SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time FROM dba_audit_trail WHERE OBJ_NAME='EMP2'; SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time, PRIV_USED FROM dba_audit_trail WHERE ACTION_NAME='CREATE TABLE' ; SELECT table_name , owner FROM dba_tables WHERE table_name = 'TEST'; -- How to audit actions made by sys ? ------------------------------------- SHOW PARAMETER AUDIT_SYS_OPERATIONS ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE = SPFILE ; STARTUP FORCE UPDATE hr.emp2 SET last_name = 'Kishkashta again' ; SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time FROM dba_audit_trail WHERE OBJ_NAME='EMP2' -- * All audit records for SYS are written to the operating system file that contains -- the audit trail, and not to DBA_AUDIT_TRAIL -- ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = FALSE SCOPE = SPFILE -- How to see the actual SQL command (DB_EXTENDED) -------------------------------------------------- col sql_text for a30 SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time, sql_text FROM dba_audit_trail WHERE OBJ_NAME='EMP2' ; ALTER SYSTEM SET AUDIT_TRAIL=DB_EXTENDED SCOPE=SPFILE; startup force SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time, sql_text FROM dba_audit_trail WHERE OBJ_NAME='EMP2' ; conn hr/hr update emp2 set salary = salary + 10 ; conn / as sysdba SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time, sql_text FROM dba_audit_trail WHERE OBJ_NAME='EMP2' ; ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE; -- Test for failed logon attempts, grouped per day. ---------------------------------------------------- col username for a15 col terminal for a6 col timestamp for a15 col logoff_time for a15 col action_name for a8 col returncode for 9999 col OS_USERNAME for a20 select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY') from dba_audit_session where returncode<>0 group by username,terminal,to_char(timestamp,'DD-MON-YYYY') -- Check for attempts to access the database with non existant users. This could -- indicate someone trying to guess user names and passwords. -------------------------------------------------------------------------------- select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from dba_audit_session where returncode<>0 and not exists (select 'x' from dba_users where dba_users.username=dba_audit_session.username) -- Logons list for specific user : --------------------------------- col userhost for a15 set lines 200 SELECT username , os_username , to_char(timestamp,'DD-MON-YYYY HH:MM:SS') AS "TIME" from dba_audit_session where username = '&username' order by timestamp DESC -------------------------------------------- -- Maintenance -------------------------------------------- -- 1. by a manual delete from SYS.AUD$ -- 2. by using plsql -- 3. by moving / exporting the SYS.AUD$ contents somewhere else DESC SYS.AUD$ DELETE FROM sys.aud$ WHERE obj$name='&table_name'; DELETE FROM sys.aud$ WHERE USERID = '&username'; create or replace procedure purge_audit_trail (days in number) as purge_date date; begin purge_date := trunc(sysdate-days); dbms_output.put_line('AUDIT: Purging Audit Trail until ' || purge_date ||' started'); delete from aud$ where ntimestamp# < purge_date; commit; dbms_output.put_line('AUDIT: Purging Audit Trail until ' || purge_date || ' has completed'); end; ------------------------------------ -- Via OEM ------------------------------------ -- * Can add auditing and view audit trails from: -- Server => (Security) => Audit Settings -- * View the trails at the "Audit Trails" section. ------------------------------------------------------------------------------------------- -- FINE GRAINED AUDITING - DBMS_FGA / DBA_FGA_AUDIT_TRAIL ------------------------------------------------------------------------------------------- -- FGA demo: -- DBMS_FGA (ADD/DROP, ENABLE/DISABLE) desc dbms_fga begin dbms_fga.add_policy ( object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'audit_emps_salary', audit_condition => 'department_id=10', audit_column => 'SALARY,COMMISSION_PCT', enable => TRUE, statement_types => 'SELECT,UPDATE'); end; conn hr/hr@pdborcl SELECT last_name, salary FROM hr.employees WHERE department_id=10; -- Audited? conn sys/password@pdborcl as sysdba desc dba_fga_audit_trail col sql_text for a20 col sql_text for a20 SELECT db_user, sql_text, TO_CHAR(timestamp,'dd/mm/yyyy hh24:mi') FROM dba_fga_audit_trail; exec dbms_fga.drop_policy('HR','EMPLOYEES','audit_emps_salary') TRUNCATE TABLE fga_log$; SELECT sql_text FROM dba_fga_audit_trail; ------------------------------------------------------------------------------------------- -- Value Based Auditing - AFTER DML TRIGGER - log the change that was made ------------------------------------------------------------------------------------------- -- * Database auditing records the inserts, updates, and deletes that have occurred -- in audited objects but does not capture the actual values that are changed. -- * To extend database auditing, value-based auditing leverages database triggers -- (event-driven PL/SQL constructs) to capture the changed values. ------------------------------------------------------------------------------------------- -- STATEMENT LEVEL TRIGGER: -- Disable the last trigger: --ALTER TRIGGER check_time_emp DISABLE; DROP TABLE log_emp; --Create a log table DROP TABLE log_emp; CREATE TABLE log_emp (username VARCHAR2(10), dml_time VARCHAR2(20), action VARCHAR2(10)); --Create the trigger: CREATE OR REPLACE TRIGGER log_emp_trig AFTER INSERT OR UPDATE OR DELETE ON employees DECLARE v_Action VARCHAR2(10); BEGIN IF INSERTING THEN v_Action:='INSERT'; ELSIF UPDATING THEN v_Action:='UPDATE'; ELSE v_Action:='DELETE'; END IF; INSERT INTO log_emp VALUES (USER,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'),v_action); END; --Use it: UPDATE employees SET salary=salary+100; INSERT INTO employees (employee_id,last_name,first_name,hire_date,job_id,email,department_id) VALUES (5555,USER,USER,SYSDATE,'SA_REP',USER||'@HOTMAIL.COM',50); DELETE employees WHERE employee_id=5555; SELECT * FROM log_emp; --Try to delete -> constraint in place, the delete fails and the trigger was not triggered: DELETE employees WHERE employee_id=100; SELECT * FROM log_emp; ------------------------------------------------------------------------------------------- -- FOR EACH ROW DROP TABLE log_Emp; --Create log table CREATE TABLE log_emp (username VARCHAR2(10), dml_time VARCHAR2(20), old_emp_id NUMBER, new_emp_id NUMBER, old_salary NUMBER, new_salary NUMBER); DESC log_Emp --Create trigger CREATE OR REPLACE TRIGGER log_emp_trig AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW BEGIN INSERT INTO log_emp VALUES (USER,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'), :OLD.employee_id,:NEW.employee_id, :OLD.salary,:NEW.salary); END; --Use it SELECT * FROM log_emp; UPDATE employees SET salary=salary+2000 WHERE employee_id BETWEEN 100 AND 105; SELECT * FROM log_emp; --Use it again- not all columns will have values in the log table INSERT INTO employees (employee_id,last_name,first_name,hire_date,job_id,email,department_id) VALUES (5555,USER,USER,SYSDATE,'SA_REP',USER||'@HOTMAIL.COM',50); DELETE employees WHERE employee_id=5555; SELECT * FROM log_emp; ------------------------------------------------------------------------------------------- -- Example for using WHEN CREATE OR REPLACE TRIGGER check_sal_diff BEFORE UPDATE OF salary ON employees FOR EACH ROW WHEN ((NEW.salary - OLD.salary) > 2000) BEGIN RAISE_APPLICATION_ERROR(-20000,'The Difference is too large.'); END; -- Check the trigger update employees set salary=salary +2500; update employees set salary=salary +1500; ------------------------------------------------------------------------------------------- -- autonomous_transaction ------------------------------------------------------------------------------------------- SELECT * FROM log_emp; ROLLBACK; SELECT * FROM log_emp; -- Try to solve it with using commit in the trigger CREATE OR REPLACE TRIGGER log_emp_trig AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW BEGIN INSERT INTO log_emp VALUES (USER,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'), :OLD.employee_id,:NEW.employee_id, :OLD.salary,:NEW.salary); COMMIT; END; / -- Try to use the trigger update employees set salary=salary+500; ------------------------------------------------------------------------------------------- --Solution: PRAGMA AUTONOMOUS_TRANSACTION; CREATE OR REPLACE TRIGGER log_emp_trig AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_emp VALUES (USER,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'), :OLD.employee_id,:NEW.employee_id, :OLD.salary,:NEW.salary); COMMIT; END; --Use it: update employees set salary=salary+500; SELECT salary FROM employees; SELECT * FROM log_emp; --Rollback and see that the changes where saved in the log table: ROLLBACK; SELECT salary FROM employees; SELECT * FROM log_emp; ------------------------------------------------------------------------------------------- -- AUTONOMOUS_TRANSACTION Without Commiting CREATE OR REPLACE TRIGGER log_emp_trig AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_emp VALUES (USER,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'), :OLD.employee_id,:NEW.employee_id, :OLD.salary,:NEW.salary); END; -- Try to Use It update employees set salary=salary+500; ----------------------------------------------- -- If I want to know if succeded or failed: ----------------------------------------------- DROP TABLE log_Emp; DROP SEQUENCE log_emp_seq; DROP FUNCTION log_emp_func; --Create log table CREATE TABLE log_emp (log_id NUMBER, username VARCHAR2(10), dml_time VARCHAR2(20), emp_id NUMBER, old_salary NUMBER, new_salary NUMBER, Status VARCHAR2(10) DEFAULT 'Failed'); CREATE SEQUENCE log_emp_seq; CREATE OR REPLACE FUNCTION log_emp_func (p_user VARCHAR2, p_date VARCHAR2, p_emp_id NUMBER, p_old_salary NUMBER, p_new_salary NUMBER) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; v_log_id NUMBER; BEGIN INSERT INTO log_emp VALUES (log_emp_seq.NEXTVAL, p_user, p_date, p_emp_id, p_old_salary, p_new_salary,DEFAULT) RETURNING log_id INTO v_log_id; COMMIT; RETURN v_log_id; END; / CREATE OR REPLACE TRIGGER log_emp_trig AFTER UPDATE OF salary ON employees FOR EACH ROW DECLARE v_log_id NUMBER; BEGIN v_log_id := log_emp_func(USER,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'), :OLD.employee_id, :OLD.salary,:NEW.salary); UPDATE log_emp SET status = 'Success' WHERE log_id = v_log_id; END; / UPDATE employees SET salary=salary+500 WHERE employee_id BETWEEN 100 AND 103; SELECT salary FROM employees WHERE employee_id BETWEEN 100 AND 103; SELECT * FROM log_emp; --Rollback and see that the changes where saved in the log table: ROLLBACK;
