Home » RDBMS Server » Security » dbms_fga.add_policy issue? (11g r2)
| dbms_fga.add_policy issue? [message #625150] |
Thu, 02 October 2014 00:27  |
 |
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
I have created table test1(name varchar2(20),empno number(5));
insert into test1 values ('rajesh',5);
insert into test1 values ('sachin',6);
insert into test1 values ('yuvraj',4);
insert into test1 values ('raidu',8);
insert into test1 values ('dravid',9);
insert into test1 values ('dhoni',1);
commit;
Now i have created the POLICY
DBMS_FGA.ADD_Policy(object_schema =>'test' ,object_name =>'test1',policy_name =>'rajesh3',audit_condition => 'EMPNO > 5',audit_column => 'EMPNO',statement_types => 'INSERT,UPDATE,DELETE',audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
SUCCESSFULLY EXECUTED
now i have updated the record
update test1 set name='ricky' and empno=1 where name='sachin'
commit;
now i got record in select * from fga_log$ table for above transaction...Actually as per the audit condition .it should not come in audit trail ..
Could you please explain how it will work?
|
|
|
|
| Re: dbms_fga.add_policy issue? [message #625151 is a reply to message #625150] |
Thu, 02 October 2014 00:42  |
 |
Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Do not just tell us what you do, SHOW US.
SQL> create table test1(name varchar2(20),empno number(5));
Table created.
SQL> insert into test1 values ('rajesh',5);
1 row created.
SQL> insert into test1 values ('sachin',6);
1 row created.
SQL> insert into test1 values ('yuvraj',4);
1 row created.
SQL> insert into test1 values ('raidu',8);
1 row created.
SQL> insert into test1 values ('dravid',9);
1 row created.
SQL> insert into test1 values ('dhoni',1);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> begin
2 DBMS_FGA.ADD_Policy(object_schema =>'MICHEL', object_name =>'TEST1',
3 policy_name =>'rajesh3',audit_condition => 'EMPNO > 5',
4 statement_types => 'INSERT,UPDATE,DELETE',audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> update test1 set name='ricky' and empno=1 where name='sachin';
update test1 set name='ricky' and empno=1 where name='sachin'
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> update test1 set name='ricky', empno=1 where name='sachin';
1 row updated.
SQL> commit;
Commit complete.
SQL> select sessionid, timestamp#, dbuid, obj$schema, obj$name, policyname, lsqltext from sys.fga_log$;
SESSIONID TIMESTAMP# DBUID OBJ$SCHEMA
---------- ------------------- ------------------------------ ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------------------------------
POLICYNAME
------------------------------
LSQLTEXT
--------------------------------------------------------------------------------------------------------
886000 MICHEL MICHEL
TEST1
RAJESH3
update test1 set name='ricky', empno=1 where name='sachin'
1 row selected.
'sachin' has empno=6 so > 5.
If you replace 'sachin' by 'yuvraj' (empno 4), you have not the row:
SQL> update test1 set name='ricky', empno=1 where name='yuvraj';
1 row updated.
SQL> commit;
Commit complete.
SQL> select sessionid, timestamp#, dbuid, obj$schema, obj$name, policyname, lsqltext from sys.fga_log$;
SESSIONID TIMESTAMP# DBUID OBJ$SCHEMA
---------- ------------------- ------------------------------ ------------------------------
OBJ$NAME
-------------------------------------------------------------------------------------------------------------
POLICYNAME
------------------------------
LSQLTEXT
-------------------------------------------------------------------------------------------------------------
886000 MICHEL MICHEL
TEST1
RAJESH3
update test1 set name='ricky', empno=1 where name='sachin'
Expected behaviour.
Forget: Please How to use [code] tags and make your code easier to read.
[Updated on: Thu, 02 October 2014 00:43] Report message to a moderator |
|
|
|
| Re: dbms_fga.add_policy issue? [message #625152 is a reply to message #625151] |
Thu, 02 October 2014 00:52 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Hi,
I am little bit confusing with the DBMS_FGA.ADD_Policy behaviour,
As you mentioned
************************************************************
SQL> update test1 set name='ricky', empno=1 where name='sachin';
1 row updated.
SQL> commit;
Commit complete.
SQL> select sessionid, timestamp#, dbuid, obj$schema, obj$name, policyname, lsqltext from sys.fga_log$;
SESSIONID TIMESTAMP# DBUID OBJ$SCHEMA
---------- ------------------- ------------------------------ ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------------------------------
POLICYNAME
------------------------------
LSQLTEXT
--------------------------------------------------------------------------------------------------------
886000 MICHEL MICHEL
TEST1
RAJESH3
update test1 set name='ricky', empno=1 where name='sachin'
1 row selected.
'sachin' has empno=6 so > 5.
*******************************************************************************************************
I will agree with you on the above scenario,but if i update
update test1 set name='GILLY', empno=6 where name='dhoni';
COMMIT;
Now also I am getting the record in Audit Table..So while updating it will check both current & updating record values at the same time for same row.If any condition satisfies,it will insert record in Audit Table ?
|
|
|
|
|
|
|
|
| Re: dbms_fga.add_policy issue? [message #625180 is a reply to message #625177] |
Thu, 02 October 2014 05:24 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Hi Michel,
I have tried same in my QA environment.
updating it will check both current & updating record values at the same time for same row.If any condition satisfies,it will insert record in Audit Table.
Regards,
Rajesh
|
|
|
|
Goto Forum:
Current Time: Sat Apr 12 04:40:23 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
