SQL coding
fidiofei
Lab3.pdf
1
Database Security
Lab 3 – Application Data and Database Activities Auditing
Part I -- DDL Statements Auditing by FGA
Objective: learn how to audit DML statements by Oracle FGA mechanism.
What to submit: Answers to Steps 4 and 5.
1. Log on as DBSEC and execute the following statements:
drop table CUSTOMERS cascade constraints;
create table CUSTOMERS (
customerID number primary key,
customername varchar2(20),
creditlimit number
);
insert into customers values(100,'Tom Jones',2000);
insert into customers values(101,'Joan Collins',1000);
2. Log on as SYS
a. Specify the auditing policies: (each command should be written in a single line)
EXEC DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'DBSEC',OBJECT_NAME =>
'CUSTOMERS', POLICY_NAME=>'AUDIT_POLICY_1_SELECT', AUDIT_CONDITION
=> NULL, AUDIT_COLUMN => NULL, HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL, ENABLE => TRUE, STATEMENT_TYPES =>
'SELECT');
EXEC DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'DBSEC',OBJECT_NAME =>
'CUSTOMERS',POLICY_NAME =>'AUDIT_POLICY_2_CREDIT', AUDIT_CONDITION
=> 'CREDITLIMIT >= 10000', AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL, HANDLER_MODULE => NULL, ENABLE =>
TRUE, STATEMENT_TYPES => 'INSERT, UPDATE');
EXEC DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'DBSEC',OBJECT_NAME =>
'CUSTOMERS',POLICY_NAME => 'AUDIT_POLICY_3_DELETE',
AUDIT_CONDITION => NULL, AUDIT_COLUMN => NULL, HANDLER_SCHEMA
=> NULL, HANDLER_MODULE => NULL, ENABLE => TRUE,STATEMENT_TYPES =>
'DELETE');
b. Query dba_audit_policies view to see the policies:
select object_name, policy_name, policy_text, sel, ins, upd, del
from dba_audit_policies;
c. Start auditing:
2
audit select, insert, update, delete on dbsec.CUSTOMERS by access;
3. Logon as DBSEC and execute the following statements:
a. select * from customers;
b. update customers
set creditlimit=11000
where customerID=101;
c. update customers
set creditlimit=5000
where customerID=101;
d. insert into customers values(102, 'Jim Lee', 50000);
e. insert into customers values(103, 'Linda Evans', 2000);
f. delete customers where customerid=103;
g. commit;
h. select * from customers;
4. Which of the above SQL statements that will be audited based on the auditing policies stated in step 2?
5. Log on as SYS, query the DBA_FGA_AUDIT_TRAILS view and show its result.
select object_name,policy_name,sql_text, statement_type
from DBA_FGA_AUDIT_TRAIL
6. Rollback the changes of this lab:
a. Execute the following statements as SYS
noaudit select, insert, update, delete on dbsec.CUSTOMERS;
EXEC DBMS_FGA.drop_POLICY(OBJECT_SCHEMA => 'DBSEC',OBJECT_NAME =>
'CUSTOMERS', POLICY_NAME=>'AUDIT_POLICY_1_SELECT');
EXEC DBMS_FGA.drop_POLICY(OBJECT_SCHEMA => 'DBSEC',OBJECT_NAME =>
'CUSTOMERS',POLICY_NAME =>'AUDIT_POLICY_2_CREDIT');
EXEC DBMS_FGA.drop_POLICY(OBJECT_SCHEMA => 'DBSEC',OBJECT_NAME =>
'CUSTOMERS',POLICY_NAME => 'AUDIT_POLICY_3_DELETE');
Delete from DBA_FGA_AUDIT_TRAIL;
select object_name, policy_name, policy_text, sel, ins, upd, del
from dba_audit_policies;
3
alter system set recyclebin = OFF DEFERRED;
b. Execute the following statements as DBSEC
Drop table customers;
4
Part II-- User Defined DML Statements Auditing 1
Objective: learn how to implement user defined DML statements auditing using triggers
What to submit: Answers to Steps 2 and 3.
1. Log on as DBSEC
a. Create DEPARTMENTS and APP_AUDIT_DATA tables
CREATE TABLE DEPARTMENTS
(
DEPARTMENT_ID NUMBER(2) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(20),
CITY VARCHAR2(30),
STATE CHAR(2)
);
CREATE TABLE APP_AUDIT_DATA
(
AUDIT_DATA_ID NUMBER PRIMARY KEY,
AUDIT_OBJECT VARCHAR2(30),
AUDIT_OPERATION VARCHAR2(20),
AUD_INS_DTTM DATE,
AUD_UPD_USER VARCHAR2(30),
AUD_REC_STAT VARCHAR2(1)
);
b. Create a sequence object
CREATE SEQUENCE SEQ_APP_AUDIT_DATA
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
c. Create a trigger that will audit DML operations
CREATE OR REPLACE TRIGGER TRG_DEPARTMENT_AIUD
AFTER INSERT OR UPDATE OR DELETE
ON DEPARTMENTS
DECLARE
V_OPR VARCHAR2(20);
BEGIN
IF INSERTING THEN
V_OPR := 'INSERT';
ELSIF UPDATING THEN
V_OPR := 'UPDATE';
ELSE
V_OPR := 'DELETE';
END IF;
5
INSERT INTO APP_AUDIT_DATA VALUES (SEQ_APP_AUDIT_DATA.NEXTVAL,
'DEPARTMENTS', V_OPR, SYSDATE, USER, 'A');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
d. Test implementation
Insert into DEPARTMENTS values (10, 'Accounting', 'Boston', 'NV');
Insert into DEPARTMENTS values(11, 'Production', 'Redlands', 'CA');
update DEPARTMENTS
set city = 'Dallas',
state ='TX'
where DEPARTMENT_ID=10;
delete from DEPARTMENTS
where DEPARTMENT_ID = 11;
SELECT AUDIT_DATA_ID ID, AUDIT_OBJECT TABLE_NAME, AUDIT_OPERATION
OPERATION, TO_CHAR(AUD_INS_DTTM,’DD-MON-YYYY HH24:MI:SS’) CREATE_DATE,
AUD_UPD_USER USERNAME, AUD_REC_STAT ROW_STATUS
FROM APP_AUDIT_DATA;
2. Revise the code of this lab so that only the delete statement is audited.
3. Rollback the changes
a. List the statements that will rollback all changes made by this lab.
6
Part III-- History Auditing Model 1
Objective: learn how to implement user defined DML statements auditing using triggers
What to submit: Answers to Steps 2(c) and 2(d) .
1. Log on as DBSEC
(a) Create CUSTOMERS and CUSTOMERS_HISTORY table
CREATE TABLE CUSTOMERS (
CUSTOMER_ID NUMBER(8) NOT NULL,
CUSTOMER_SSN VARCHAR2(9),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
SALES_REP_ID NUMBER(4),
ADDR_LINE VARCHAR2(80),
CITY VARCHAR2(30),
STATE VARCHAR2(30),
ZIP_CODE VARCHAR2(9),
CTL_INS_DTTM DATE,
CTL_UPD_DTTM DATE,
CTL_UPD_USER VARCHAR2(30),
CTL_REC_STAT VARCHAR2(1) );
CREATE TABLE CUSTOMERS_HISTORY (
CUSTOMER_ID NUMBER(8) NOT NULL,
CUSTOMER_SSN VARCHAR2(9),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
SALES_REP_ID NUMBER(4),
ADDR_LINE VARCHAR2(80),
CITY VARCHAR2(30),
STATE VARCHAR2(30),
ZIP_CODE VARCHAR2(9),
CTL_INS_DTTM DATE,
CTL_UPD_DTTM DATE,
CTL_UPD_USER VARCHAR2(30),
CTL_REC_STAT VARCHAR2(1),
HST_INS_DTTM DATE,
HST_OPR_TYPE VARCHAR2(20) );
(b) Create Trigger for auditing
CREATE OR REPLACE TRIGGER TRG_CUSTOMERS_BIUR
BEFORE UPDATE OR INSERT OR DELETE ON CUSTOMERS
FOR EACH ROW
DECLARE
V_CUSTOMER_ID CUSTOMERS_HISTORY.CUSTOMER_ID%TYPE;
V_CUSTOMER_SSN CUSTOMERS_HISTORY.CUSTOMER_SSN%TYPE;
V_FIRST_NAME CUSTOMERS_HISTORY.FIRST_NAME%TYPE;
V_LAST_NAME CUSTOMERS_HISTORY.LAST_NAME%TYPE;
V_SALES_REP_ID CUSTOMERS_HISTORY.SALES_REP_ID%TYPE;
7
V_ADDR_LINE CUSTOMERS_HISTORY.ADDR_LINE%TYPE;
V_CITY CUSTOMERS_HISTORY.CITY%TYPE;
V_STATE CUSTOMERS_HISTORY.STATE%TYPE;
V_ZIP_CODE CUSTOMERS_HISTORY.ZIP_CODE%TYPE;
V_CTL_INS_DTTM CUSTOMERS_HISTORY.CTL_INS_DTTM%TYPE;
V_CTL_UPD_DTTM CUSTOMERS_HISTORY.CTL_UPD_DTTM%TYPE;
V_CTL_UPD_USER CUSTOMERS_HISTORY.CTL_UPD_USER%TYPE;
V_CTL_REC_STAT CUSTOMERS_HISTORY.CTL_REC_STAT%TYPE;
V_HST_OPR_TYPE CUSTOMERS_HISTORY.HST_OPR_TYPE%TYPE;
BEGIN
IF INSERTING THEN
:NEW.CTL_INS_DTTM := SYSDATE;
:NEW.CTL_UPD_DTTM := NULL;
:NEW.CTL_REC_STAT := 'N';
V_HST_OPR_TYPE := 'INSERT';
ELSIF UPDATING THEN
:NEW.CTL_UPD_DTTM := SYSDATE;
V_CTL_UPD_DTTM := :NEW.CTL_UPD_DTTM;
V_HST_OPR_TYPE := 'UPDATE';
ELSIF DELETING THEN
V_CUSTOMER_ID := :OLD.CUSTOMER_ID;
V_CUSTOMER_SSN := :OLD.CUSTOMER_SSN;
V_FIRST_NAME := :OLD.FIRST_NAME;
V_LAST_NAME := :OLD.LAST_NAME;
V_SALES_REP_ID := :OLD.SALES_REP_ID;
V_ADDR_LINE := :OLD.ADDR_LINE;
V_CITY := :OLD.CITY;
V_STATE := :OLD.STATE;
V_ZIP_CODE := :OLD.ZIP_CODE;
V_CTL_INS_DTTM := :OLD.CTL_INS_DTTM;
V_CTL_UPD_DTTM := :OLD.CTL_UPD_DTTM;
V_CTL_UPD_USER := :OLD.CTL_UPD_USER;
V_CTL_REC_STAT := :OLD.CTL_REC_STAT;
V_HST_OPR_TYPE := 'DELETE';
END IF;
IF INSERTING OR UPDATING THEN
8
:NEW.CTL_UPD_USER := USER;
V_CUSTOMER_ID := :NEW.CUSTOMER_ID;
V_CUSTOMER_SSN := :NEW.CUSTOMER_SSN;
V_FIRST_NAME := :NEW.FIRST_NAME;
V_LAST_NAME := :NEW.LAST_NAME;
V_SALES_REP_ID := :NEW.SALES_REP_ID;
V_ADDR_LINE := :NEW.ADDR_LINE;
V_CITY := :NEW.CITY;
V_STATE := :NEW.STATE;
V_ZIP_CODE := :NEW.ZIP_CODE;
V_CTL_INS_DTTM := :NEW.CTL_INS_DTTM;
V_CTL_UPD_DTTM := :NEW.CTL_UPD_DTTM;
V_CTL_UPD_USER := :NEW.CTL_UPD_USER;
V_CTL_REC_STAT := :NEW.CTL_REC_STAT;
END IF;
insert into CUSTOMERS_HISTORY(CUSTOMER_ID,CUSTOMER_SSN,
FIRST_NAME, LAST_NAME, SALES_REP_ID, ADDR_LINE, CITY, STATE, ZIP_CODE,
CTL_INS_DTTM, CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT, HST_INS_DTTM,
HST_OPR_TYPE)
values(V_CUSTOMER_ID, V_CUSTOMER_SSN, V_FIRST_NAME, V_LAST_NAME,
V_SALES_REP_ID, V_ADDR_LINE, V_CITY, V_STATE, V_ZIP_CODE, V_CTL_INS_DTTM,
V_CTL_UPD_DTTM, V_CTL_UPD_USER, V_CTL_REC_STAT,SYSDATE, V_HST_OPR_TYPE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END;
2. Testing the implementation as DBSEC.
(a) Insert two rows into CUSTOMER table:
INSERT INTO CUSTOMERS (CUSTOMER_ID,CUSTOMER_SSN, FIRST_NAME, LAST_NAME,
SALES_REP_ID, ADDR_LINE, CITY, STATE, ZIP_CODE)
VALUES (201340, '99999999', 'Jeffrey', 'Antoine', 6459, '9938 Moreno
St.', 'Champagne', 'SD', '43172');
INSERT INTO CUSTOMERS (CUSTOMER_ID,CUSTOMER_SSN, FIRST_NAME, LAST_NAME,
SALES_REP_ID, ADDR_LINE, CITY, STATE, ZIP_CODE)
VALUES (801349, '111111111', 'Cordell', 'Ayres', 2200, '37 Noyes
Street', 'Narod', 'NC', '15199');
9
(b) Retrieve rows from the CUSTOMERS table and CUSTOMERS_HISTORY table
SELECT CUSTOMER_ID, FIRST_NAME FNAME, LAST_NAME LNAME,
TO_CHAR(CTL_INS_DTTM, 'dd-mm-yyyy HH:MM:SS AM')CTL_INS_DTTM,
CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT
FROM CUSTOMERS;
(c) Write a SQL statement to delete one row from the CUSTOMER table.
(d) Show the contents of both CUSTOMER and CUSTOMERS_HISTORY tables.
3. Rollback the changes (as DBSEC):
DROP TABLE CUSTOMERS;
DROP TABLE CUSTOMERS_HISTORY;
DROP TRIGGER TRG_CUSTOMERS_BIUR;
10
Part IV: Auditing Application Errors
Objective: Learn how to audit application errors
What to submit: Answer to questions in Step 2.
1. Logon as DBSEC and execute the following SQL statements:
a. Create and populate CUSTOMERS table:
CREATE TABLE CUSTOMERS (
ID NUMBER,
NAME VARCHAR2(10),
CREDIT_LIMIT NUMBER
);
INSERT INTO CUSTOMERS VALUES (1, 'Tom Jones',1000);
b. Create application error auditing table APP_AUDIT_ERRORS:
CREATE TABLE APP_AUDIT_ERRORS (
TABLE_NAME VARCHAR2(30) NOT NULL,
ERROR_CODE NUMBER NOT NULL,
ERROR_MSG VARCHAR2(2000) NOT NULL,
ROW_VALUES VARCHAR2(4000) NOT NULL,
CTL_INS_DTTM DATE,
CTL_INS_USER VARCHAR2(30),
CTL_OPS_USER VARCHAR2(30),
CTL_IP_ADDR VARCHAR2(255)
);
c. Create a stored package to perform the UPDATE statement
CREATE OR REPLACE PACKAGE APP_AUDIT_DML IS
PROCEDURE CUSTOMERS_UPDATE(
P_ID NUMBER,
P_NAME VARCHAR2,
P_CREDIT_LIMIT NUMBER,
P_COMMIT BOOLEAN DEFAULT TRUE
);
PROCEDURE INSERT_ERROR (
P_TABLE VARCHAR2,
P_CODE NUMBER,
P_MSG VARCHAR2,
P_VALS VARCHAR2
);
END;
CREATE OR REPLACE PACKAGE BODY APP_AUDIT_DML IS
PROCEDURE CUSTOMERS_UPDATE(
P_ID NUMBER,
P_NAME VARCHAR2,
P_CREDIT_LIMIT NUMBER,
P_COMMIT BOOLEAN DEFAULT TRUE) IS
V_STMT VARCHAR2(4000);
E_ID_NULL EXCEPTION;
11
PRAGMA EXCEPTION_INIT(E_ID_NULL, -200001);
BEGIN
IF P_ID IS NULL THEN
RAISE E_ID_NULL;
END IF;
UPDATE CUSTOMERS
SET ID = NVL(P_ID, ID),
NAME = NVL(P_NAME, NAME),
CREDIT_LIMIT = NVL(P_CREDIT_LIMIT, CREDIT_LIMIT)
WHERE ID = P_ID;
IF P_COMMIT THEN
COMMIT;
END IF;
EXCEPTION
WHEN E_ID_NULL THEN
V_STMT := P_ID || '|'|| P_NAME || '|' || P_CREDIT_LIMIT;
INSERT_ERROR('CUSTOMERS',SQLCODE, SQLERRM, V_STMT);
RAISE_APPLICATION_ERROR(-20001, 'Error: ' || SQLERRM);
WHEN OTHERS THEN
V_STMT := P_ID|| '|' || P_NAME || '|' || P_CREDIT_LIMIT;
INSERT_ERROR('CUSTOMERS',SQLCODE, SQLERRM, V_STMT);
RAISE_APPLICATION_ERROR(-20002, 'Error: ' || SQLERRM);
END;
PROCEDURE INSERT_ERROR (
P_TABLE VARCHAR2,
P_CODE NUMBER,
P_MSG VARCHAR2,
P_VALS VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO APP_AUDIT_ERRORS VALUES
(P_TABLE, P_CODE, P_MSG, P_VALS, SYSDATE, USER, (SELECT
SYS_CONTEXT('USERENV','OS_USER') FROM DUAL), (SELECT
SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL) );
COMMIT;
END;
END;
2. Testing the implementation
a. Perform an update using the CUSOMTERS_UPDATE procedure
EXEC APP_AUDIT_DML.CUSTOMERS_UPDATE(1, 'Tom Jones Jr.', null);
b. What is the output of 2(a)?
c. Perform an update using the CUSOMTERS_UPDATE procedure
EXEC APP_AUDIT_DML.CUSTOMERS_UPDATE(1, NULL, 10000);
d. The procedure in 2(c) attempts to reset the values of both NAME and CREDIT_LIMIT of the tuple with ID=1. Is it successful in resetting those values?
e. Verify the contents of APP_AUDIT_ERRORS by the SQL statement:
12
SELECT TABLE_NAME, ERROR_MSG, ROW_VALUES,
TO_CHAR(CTL_INS_DTTM,'DD-MON-YYYY HH24:MI:SS')
DATE_CREATED, CTL_IP_ADDR
FROM APP_AUDIT_ERRORS;
f. What is the output of 2(e)? Is it correct?
3. Rollback the changes by executing the following SQL statements:
DROP TABLE CUSTOMERS;
DROP TABLE APP_AUDIT_ERRORS;
DROP PACKAGE APP_AUDIT_DML;
13
Part V: Auditing LOGON and LOG OFF activities:
Objective: Learn how to audit logon and logoff database events
What to submit: Answer to questions 3(b), and 4.
1. Logon as SYS and execute the following SQL statements:
a. Create audit table APP_AUDIT_LOGINS:
CREATE TABLE APP_AUDIT_LOGINS (
LOGINS_ID NUMBER,
SESSION_ID NUMBER,
USERNAME VARCHAR2(15),
LOGON_TIME DATE,
LOGOFF_TIME DATE,
IP_ADDRESS VARCHAR2(25),
AUD_INS_DTTM DATE,
AUD_UPD_DTTM DATE );
b. Create a sequence:
CREATE SEQUENCE SEQ_LOGIN_ID;
c. Create a LOGON trigger:
CREATE OR REPLACE TRIGGER TRG_AFTER_LOGON
AFTER LOGON ON DATABASE
BEGIN
IF INSTR(UPPER(USER),'SYSMAN') =0 THEN
INSERT INTO APP_AUDIT_LOGINS VALUES
(SEQ_LOGIN_ID.NEXTVAL,
SYS_CONTEXT('USERENV', 'SESSIONID'),
USER,
SYSDATE,
NULL,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYSDATE,
NULL
);
END IF;
END;
d. Create a LOGOFF trigger:
create or replace trigger TRG_BEFORE_LOGOFF
before LOGOFF on DATABASE
begin
IF INSTR(UPPER(USER),'SYSMAN') =0 THEN
update APP_AUDIT_LOGINS
set LOGOFF_TIME = SYSDATE,
AUD_UPD_DTTM = SYSDATE
where SESSION_ID = SYS_CONTEXT('USERENV','SESSIONID')
and USERNAME = USER and LOGOFF_TIME IS NULL;
END IF;
EXCEPTION
when others then
dbms_output.put_line('something is wrong');
14
raise_application_error(-20001, 'Error: ' || SQLERRM);
end;
2. Logon as DBSEC, wait for a while, and then log off.
3. As SYS
a. Verify the APP_AUDIT_LOGINS table:
SELECT logins_id, session_id, username,
to_char(logon_time,'yyyy-mm-dd HH:MM:SS AM') onTime,
to_char(logoff_time,'yyyy-mm-dd HH:MM:SS AM') offTime,
to_char(aud_ins_dttm,'yyyy-mm-dd HH:MM:SS AM')
audInsTime,to_char(aud_upd_dttm,'yyyy-mm-dd HH:MM:SS AM')
audUpdDttm
FROM APP_AUDIT_LOGINS;
b. What is the output of 3(a)? Is it correct?
4. Write Codes to roll back the changes by dropping all tables and triggers.
15
Part VI: Auditing Database Activities with Oracle
Objective: Learn how to use Oracle Auditing
What to submit: Answer to questions 3(b), 4(b), 6, and 7.
1. As DBSEC,
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
ID NUMBER,
NAME VARCHAR2(20),
CR_LIMIT NUMBER
);
INSERT INTO CUSTOMER (ID, NAME, CR_LIMIT) VALUES (1, 'TOM', 200);
INSERT INTO CUSTOMER (ID, NAME, CR_LIMIT) VALUES (2, 'SUSAN', 130);
INSERT INTO CUSTOMER (ID, NAME, CR_LIMIT) VALUES (3, 'LINDA', 230);
2. Log on as SYSTEM or SYS to enable auditing.
AUDIT ALTER ON DBSEC.CUSTOMER BY ACCESS WHENEVER SUCCESSFUL;
AUDIT DELETE ON DBSEC.CUSTOMER BY ACCESS WHENEVER SUCCESSFUL;
3. As DBSEC
a) Run the following commands;
DELETE FROM CUSTOMER WHERE ID =2 ;
ALTER TABLE CUSTOMER MODIFY NAME VARCHAR2(30)
a) Query DBA_AUDIT_TRAIL to show the auditing is working properly.
4. As Sys, run the following auditing command:
a) Audit ALL by DBSEC;
b) What does the above command do?
5. As DBSEC, run the following commands;
SELECT * FROM CAT;
SELECT * FROM CUSTOMER;
UPDATE CUSTOMER
SET CR_LIMIT=CR_LIMIT*1.1;
ALTER TABLE CUSTOMER ADD upd_user varchar2(50) default NULL;
6. Query DBA_AUDIT_TRAIL to show the auditing is working properly.
7. Write the codes to roll back changes by turning off auditing.
