mkdir -p /var/log/oracledb/ touch /var/log/oracledb/oracledb.log
local1.info /var/log/oracledb/oracledb.log
Note: The separator between local1.info and /var/log/oracledb/oracledb.log must be tab, not space.
service rsyslog stop
service rsyslog start
BEGIN dbms_fga.add_policy ( object_schema=>'CUST', object_name=>'CUST_DETAILS', policy_name=>'TEST_AUDIT', audit_column => 'PASSPORT', statement_types => 'UPDATE, DELETE, SELECT', audit_condition => 'PASSPORT IS NOT NULL' ); END;
Here we are asking to audit PASSPORT column in CUST_DETAILS table for SELECT, UPDATE & DELETE statements.
It will not audit operations, where PASSPORT is NULL.
Policy name for this access control is TEST_AUDIT.
STEP 2
Check the policy details from
SQL> select policy_name, object_name, object_schema, policy_text, policy_column from dba_audit_policies
/
STEP 3
Now if anyone uses statement containing PASSPORT column
SQL> select * from cust_details
/
SQL> select PASSPORT from CUST_DETAILS
/
It will be logged in sys.fga_log$ OR dba_fga_audit_trail views
SQL> select timestamp, db_user, os_user,object_schema, object_name,sql_text from dba_fga_audit_trail
/
STEP 4
To delete the audit log
SQL> delete from sys.fga_log$
OR
SQL> delete from dba_fga_audit_trail
STEP 5
To delete the policy
begin
DBMS_FGA.DROP_POLICY
(
object_schema => ‘CUST’,
object_name => ‘CUST_DETAILS’,
policy_name => ‘TEST_AUDIT’
);
end;
SQL> ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE
SCOPE=SPFILE;
SQL> ALTER SYSTEM SET AUDIT_SYSLOG_LEVEL=’local1.info’
SCOPE=SPFILE;
Sample output of syslog:-
Dec 21 22:06:26 localhost Oracle Audit[32231]: LENGTH: “313” SESSIONID:[7] “4902919” ENTRYID:[1] “9” STATEMENT:[2] “35” USERID:[2] “CW” USERHOST:[7] “GSYC622” TERMINAL:[7] “unknown” ACTION:[3] “103” RETURNCODE:[1] “0” OBJ$CREATOR:[2] “CW” OBJ$NAME:[15] “CW_CONFIG_AUDIT” SES$ACTIONS:[16] “——S———” SES$TID:[5] “56505” OS$USERID:[7] “eprppak” DBID:[9] “673878994”
over the counter medication meaning prescription pain medication nausea best natural remedy for flatulence
Your comment is awaiting moderation.