Home » RDBMS Server » Security » dbms_fga.add_policy issue? (11g r2)
dbms_fga.add_policy issue? [message #625150] Thu, 02 October 2014 00:27 Go to next message
mvrkr44
Messages: 131
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
mvrkr44
Messages: 131
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 #625153 is a reply to message #625152] Thu, 02 October 2014 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Now also I am getting the record in Audit Table.


Prove it, SHOW US.

Quote:
empno=6


So it is greater than 5 and so it is recorded.

Quote:
.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 ?


Correct.
Re: dbms_fga.add_policy issue? [message #625177 is a reply to message #625152] Thu, 02 October 2014 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: dbms_fga.add_policy issue? [message #625180 is a reply to message #625177] Thu, 02 October 2014 05:24 Go to previous message
mvrkr44
Messages: 131
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
Previous Topic: how to set umask value to 022 from sql * plus
Next Topic: how to enforce oracle password special character
Goto Forum:
  


Current Time: Sat Jun 15 18:12:54 CDT 2019