Database Security -Exercise and Case study Solution
Unit 5- Audit Exercise Solution
1. Enable the standard audit trial.
Ans:
alter system set audit_sys_operations=TRUE scope=SPFILE;
alter system set audit_trail=’DB_EXTENDED’ scope=SPFILE;
shutdown immediate
startup
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. Don’t do any audit to current session for user Induja
Ans:
NOAUDIT session BY Induja;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3. Do audit for unsuccessful DML STATEMENTS.
Ans:
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4. Audit INSERT, DELETE privileges in new object which will create in future
Ans:
AUDIT insert, delete ON DEFAULT BY ACCESS WHENEVER NOT SUCCESSFUL;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
5. Remove audit from user Induja whose created procedures.
Ans:
NOAUDIT EXECUTE PROCEDURE BY Induja;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6. Do audit for select, insert table of user Lakshmi.
Ans:
AUDIT SELECT TABLE, INSERT TABLE by Lakshmi BY ACCESS;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7. Audit entire statement in the current session of all users.
Ans:
AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8. Remove all privileges in auditing.
Ans:
NOAUDIT ALL privileges;
-----------------------------------------------------------------------------------------------------------
9. Don't do any audit for Hr. employees table.
Ans:
NOAUDIT ALL ON hr.employees;
------------------------------------------------------------------------------------
10. Audit all the update on future table.
Ans:
AUDIT update TABLE BY ACCESS;
---------------------------------------------------------------------------------------------
11. Create view “empview” using hr.employees table, do audit using select privilege on empview.
Ans:
Create view empview as select * from hr.employees;
Audit select on empview by access;
---------------------------------------------------------------------------------------------------------------------------------------------
Q12) Create FGA audit policy as “sh_policy” for the shopping table using condition as the amount should be greater than 1200. Create the below given shopping table in HR schema
CREATE TABLE SHOPPING(CARDNO NUMBER(10),CID NUMBER(10),AMOUNT NUMBER(5));
INSERT INTO SHOPPING VALUES(1,11,1000);
INSERT INTO SHOPPING VALUES(2,11,2000);
INSERT INTO SHOPPING VALUES(3,12,1500);
INSERT INTO SHOPPING VALUES(4,12,2500);
Ans:
SQL> CONN SYS AS SYSDBA
Enter password:SYS
Connected.
begin
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA=>'hr',
OBJECT_NAME=>'shopping',
POLICY_NAME=>'sh_pol',
AUDIT_CONDITION=>'amount>1200’,
AUDIT_COLUMN=>'amount',
ENABLE=>TRUE,
STATEMENT_TYPES=>'INSERT,UPDATE,SELECT,DELETE',
AUDIT_TRAIL=>DBMS_FGA.DB);
END;
BEGIN
DBMS_FGA.DROP_POLICY(
OBJECT_SCHEMA=>'hr',
OBJECT_NAME=>'shopping',
POLICY_NAME=>'Sh_pol');
END;