Oracle Auditing Examples

 
-- * 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;

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…