simple 7 Steps to learn Fine grained auditing.
Download as DOCX, PDF
0 likes250 views
Oracle Database Administration, one of the important utility FGA for auditing in database. Use 7 simple steps to learn FGA.
simple 7 Steps to learn Fine grained auditing.
- 1. Fine GrainedAuditing Step1. Createuserzoe assign10monuser. SQL> create user zoe identified by zoe 2 default tablespace users 3 temporary tablespace temp 4 quota 10m on users 5 account unlock; User created. SQL> grant connect,create table,resource tozoe; Grant succeeded. Step2. Make a table toys, and insertsome rows. SQL> conn zoe/zoe; Connected. SQL> create table toy 2 (toynonumber(2), 3 name varchar(10), 4 cost number(5), 5 manf char(10) ); Table created. SQL> desc toy; Name Null? Type ----------------------------------------- -------- ---------------------------- TOYNO NUMBER(2) NAME VARCHAR2(10) COST NUMBER(5) MANF CHAR(10) SQL> insertintotoy values 2 (01, 'bugati',2500,'italy'); 1 rowcreated. SQL> insertintotoy values 2 (02,'cameross', 2000, 'chevy'); 1 rowcreated. SQL> commit; ---- insertsome more rows. Commitcomplete.
- 2. SQL> select * from toy; TOYNONAME COST MANF ---------------------------------------- 3 charger 1500 dodge 4 plymounth 1200 plymounth 5 landcruser 2000 toyota 6 audi ax 1400 audi 7 mustang 2200 ford 8 trino 1800 ford 9 challenger 1900 dodge 10 mursilago 2800 lamorghini 11 golorado 2300 lamorghini 12 civic 1200 honda 1 bugati 2500 italy TOYNONAME COST MANF ---------------------------------------- 2 camero ss 2000 chevy 12 rows selected. Step3. AssignFGAprivilegeto zoe. SQL> conn / as sysdba Connected. SQL> grant EXECUTE ONdbms_fgato zoe; Grant succeeded. Step4. Createusersasha, jiyaandassign5m each onuser. SQL> showuser USER is"SYS" SQL> create usersashaidentifiedbysasha 2 defaulttablespace users 3 temporarytablespace temp 4 quota5m onusers 5 account unlock; User created. SQL> create userjiyaidentifiedbyjiya 2 defaulttablespace users 3 temporarytablespace temp 4 quota5m onusers 5 account unlock;
- 3. User created. SQL> grant connect,resource to sasha; Grant succeeded. SQL> grant connect,resource to jiya; Grant succeeded. Step5. GrantTOY tableto sasha, jiya. SQL> conn zoe/zoe; Connected. SQL> grant select, insert, update on toy to jiya; Grant succeeded. SQL> grant select , insert, update on toy to sasha; Grant succeeded. SQL> conn sasha/sasha Connected. SQL> select* from zoe.toy; ---sashacan accesstoy table fromzoe. TOYNONAME COST MANF ---------- -------------------- ---------- 3 charger 1500 dodge 4 plymounth 1200 plymounth 5 landcruser 2000 toyota 6 audi ax 1400 audi 7 mustang 2200 ford
- 4. 8 trino 1800 ford 9 challenger 1900 dodge 10 mursilago 2800 lamorghini 11 golorado 2300 lamorghini 12 civic 1200 honda 1 bugati 2500 italy 2 cameross 2000 chevy 12 rows selected. SQL> conn jiya/jiya Connected. SQL> select* from zoe.toy; --- jiyacan alsoaccesstoy table fromzoe TOYNONAME COST MANF ---------- -------------------- ---------- 3 charger 1500 dodge 4 plymounth 1200 plymounth 5 landcruser 2000 toyota 6 audi ax 1400 audi 7 mustang 2200 ford
- 5. 8 trino 1800 ford 9 challenger 1900 dodge 10 mursilago 2800 lamorghini 11 golorado 2300 lamorghini 12 civic 1200 honda 1 bugati 2500 italy 2 cameross 2000 chevy 12 rows selected. Step6. Enablefinegrainaduitontable TOY byuser zoe. SQL> conn / as sysdba Connected. SQL> select* from dba_fga_audit_trail; no rowsselected --- table dba_fga_audit_trail will containthe auditreportafterexecutingdbms.fga --- NowZoe will executea procedureto assignthe conditionforauditing. SQL> conn zoe/zoe Connected. SQL> execDBMS_FGA.ADD_POLICY( object_schema=>'ZOE', object_name =>'TOY', policy_name => 'expensive_cars',audit_condition=>'cost>=1500', audit_column=> 'name',statement_types=>'select, insert,update,delete',handler_schema=>null,handler_module=>null,enable =>true ); PL/SQL procedure successfullycompleted.
- 6. --- Zoe put the conditionto auditthe query which return the nameof car which cost>=1500. Every select commandfor that condition, auditwill alsorecord insert, updateand delete commandissue againstthe table TOY. ---Checkingtheaudittable. SQL> conn / as sysdba Connected. SQL> select* from dba_fga_audit_trail; no rowsselected -- as we can see dba_fga_audit_trail table doesnotcontainanyauditrecord -- Nowbelowsql querywill hitthe auditconditions,that will save audit recordinthe table dba_fga_audit_trial, letshavea look. SQL> conn jiya/jiya Connected. SQL> select* from zoe.toy; --- 1st Query issue by jiya TOYNONAME COST MANF ---------- -------------------- ---------- 3 charger 1500 dodge 4 plymounth 1200 plymounth 5 landcruser 2000 toyota 6 audi ax 1400 audi 7 mustang 2200 ford
- 7. 8 trino 1800 ford 9 challenger 1900 dodge 10 mursilago 2800 lamorghini 11 golorado 2300 lamorghini 12 civic 1200 honda 1 bugati 2500 italy 2 cameross 2000 chevy 12 rows selected. SQL> insertintozoe.toyvalues(13,'viper',1700,'dodge'); --- 2nd query 1 rowcreated. SQL> commit; --- insertisinthe audit condition. SQL> select* from zoe.toywhere cost>1600; ---3rd query TOYNONAME COST MANF ---------- -------------------- ---------- 5 landcruser 2000 toyota 7 mustang 2200 ford 8 trino 1800 ford 9 challenger 1900 dodge
- 8. 10 mursilago 2800 lamorghini 11 golorado 2300 lamorghini 1 bugati 2500 italy 2 cameross 2000 chevy 13 viper 1700 dodge 9 rowsselected. SQL> conn sasha/sasha --- 4th query fromsasha Connected. SQL> select* from zoe.toywhere cost>1800; TOYNONAME COST MANF ---------- -------------------- ---------- 5 landcruser 2000 toyota 7 mustang 2200 ford 9 challenger 1900 dodge 10 mursilago 2800 lamorghini 11 golorado 2300 lamorghini 1 bugati 2500 italy 2 cameross 2000 chevy 7 rowsselected.
- 9. SQL> conn sasha/sasha Connected. SQL> selectname,manf fromzoe.toywhere cost>1700; ---5th query NAME MANF ---------- ---------- landcrusertoyota mustang ford trino ford challengerdodge mursilago lamorghini golorado lamorghini bugati italy cameross chevy 8 rowsselected. SQL> conn jiya/jiya Connected. SQL> selecttoyno,name,manf fromzoe.toywhere cost>2000; ---6th query TOYNONAME MANF ---------- --------------------
- 10. 7 mustang ford 10 mursilago lamorghini 11 golorado lamorghini 1 bugati italy Step7. Nowletscheckwhat do wegot in ouraudittable i.e. DBA_FGA_AUDIR_TRAIL SQL> conn / as sysdba Connected. SQL> select timestamp, db_user, os_user, object_schema,object_name, policy_name,sql_textfrom dba_fga_audit_trailorder by timestamp; TIMESTAMP DB_USER --------------------------------------- OS_USER ----------------------------------------------------------------------------------- ------------------------------------------------------------------- OBJECT_SCHEMA ------------------------------ OBJECT_NAME ----------------------------------------------------------------------------------- --------------------------------------------- POLICY_NAME ------------------------------
- 11. SQL_TEXT ----------------------------------------------------------------------------------- ------------------------------------------------------------------- 09-APR-15 JIYA oracle ZOE TOY EXPENSIVE_CARS select * from zoe.toy -- 1st query audited 09-APR-15 JIYA oracle ZOE TOY EXPENSIVE_CARS insert into zoe.toy values (13, 'viper',1700,'dodge') --2nd query audited 09-APR-15 JIYA oracle
- 12. ZOE TOY EXPENSIVE_CARS select * from zoe.toy where cost>1600 --3rd query audited 09-APR-15 SASHA oracle ZOE TOY EXPENSIVE_CARS select * from zoe.toy where cost>1800 --4th query aduited 09-APR-15 SASHA oracle ZOE TOY EXPENSIVE_CARS select name, manf from zoe.toy where cost> 1700 --5th query audited
- 13. 09-APR-15 JIYA oracle ZOE TOY EXPENSIVE_CARS select toyno, name, manf from zoe.toy where cost>2000 --6th query audited 8 rows selected.