VPD Policy Function Issue. [message #587632] |
Mon, 17 June 2013 10:15 |
|
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Hi , I am using 11.2.0.3.0 version of oracle.
I am trying below policy function on one of my column and i am expecting the column value to be shown as null when the table is being queried from USER1 and USER2. But i am not able to get it, its showing all the text values.
(Note-I dont want to put the restriction on SCHEMA(USER1,USER2) level for all objects using default 'EXEMPT ACCESS POLICY', so i have revoked the same from USER1,USER2 and trying to achive the same using below policy function.)
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'USER1',
object_name => 'tde_test',
policy_name => 'test_vpd',
function_schema => 'USER1',
policy_function => 'vpd_function',
sec_relevant_cols => 'COL1',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/
CREATE OR REPLACE FUNCTION vpd_function (obj_owner IN VARCHAR2,
obj_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER1','USER2')
THEN
RETURN '1=2';
ELSE
RETURN NULL;
END IF;
END;
/
[Edit MC: add code tags]
[Updated on: Mon, 17 June 2013 10:28] by Moderator Report message to a moderator
|
|
|
Re: VPD Policy Function Issue. [message #587634 is a reply to message #587632] |
Mon, 17 June 2013 10:38 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
It works for me:
SQL> create table t as
2 select level id, to_char(to_date(level,'J'),'fmjsp') val from dual connect by level <= 10
3 /
Table created.
SQL> select * from t order by id;
ID VAL
---------- ------------------------------------------------------------------------------
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
10 rows selected.
SQL> BEGIN
2 DBMS_RLS.ADD_POLICY (object_schema => 'MICHEL',
3 object_name => 'T',
4 policy_name => 'T_VPD',
5 function_schema => 'MICHEL',
6 policy_function => 'T_VPD_FUNCTION',
7 sec_relevant_cols => 'VAL',
8 sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE FUNCTION T_VPD_FUNCTION (obj_owner IN VARCHAR2,
2 obj_name IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 BEGIN
6 IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER1','USER2')
7 THEN
8 RETURN '1=2';
9 ELSE
10 RETURN NULL;
11 END IF;
12 END;
13 /
Function created.
SQL> create user user1 identified by user1;
User created.
SQL> grant create session to user1;
Grant succeeded.
SQL> grant select on t to user1;
Grant succeeded.
SQL> connect user1/user1
Connected.
USER1> select * from michel.t order by id;
ID V
---------- -
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Regards
Michel
|
|
|
|
Re: VPD Policy Function Issue. [message #588570 is a reply to message #587767] |
Wed, 26 June 2013 10:22 |
|
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
hi Michel,
sorry for late reply. Actually i believe, its due to the policy is applicable to all the users except SYS. I want something like, if i mention something like below in the VPD function, then SYS user should also be restricted from viewing the column data. I mean if USER1 is granted 'EXEMPT ACCESS POLICY' privilege in schema level, still i want to restrict it for specific column of a table using VPD function. Is it possible?
IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('SYS')
THEN
RETURN '1=2';
ELSE
RETURN NULL;
END IF;
[Updated on: Wed, 26 June 2013 11:26] Report message to a moderator
|
|
|
|
Re: VPD Policy Function Issue. [message #588638 is a reply to message #588582] |
Thu, 27 June 2013 04:23 |
|
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Ok. I got one feature i.e REALM specific to database vault by which you can restrict super users like SYS/SYSTEM from accessing objects(TABLES). But my question is whether i can be able to restrict them in column level(Dont want to put any restriction on TABLE level)?, (because i was trying to do the REALM implementation through OEM, and i got no field for mentioning column name, its only showing OBJECT name to apply restriction.)
[Updated on: Thu, 27 June 2013 04:28] Report message to a moderator
|
|
|
|
|
|
|
|
Re: VPD Policy Function Issue. [message #588936 is a reply to message #588906] |
Sun, 30 June 2013 04:42 |
|
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Ofcourse its not easy solution. it will be a total design change, which will have much more impact when i am thinking of total 7 table to apply on them. As because there are hundreds of place from where values are inserted in to the column. Is there any other possible solution?
[Updated on: Sun, 30 June 2013 04:45] Report message to a moderator
|
|
|
Re: VPD Policy Function Issue. [message #588939 is a reply to message #588936] |
Sun, 30 June 2013 05:08 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
In the end, no.
You mix up different kinds of information in the same field.
How to know, WITHOUT QUERYING IT, if it is public or private?
I ask you, here ID=1, is the data public or private, you can't read the field, just tell me public or private?
Regards
Michel
|
|
|
Re: VPD Policy Function Issue. [message #588941 is a reply to message #588939] |
Sun, 30 June 2013 06:29 |
|
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Yes Michel, you r right. we have some info in that field which is private but not all, you may call it as design flaw. so now we want to restrict the acess to user excluding some specific users like support people, who will access that for resolving production issues. as because this is a audit traitrail kind of table and the column named as 'comment' contains useful information for resolving production cases, also some PI data appended with some information
[Updated on: Sun, 30 June 2013 06:31] Report message to a moderator
|
|
|
|
Re: VPD Policy Function Issue. [message #590648 is a reply to message #588943] |
Sun, 21 July 2013 01:03 |
|
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Hey michel, thanks a lot for helping me with the understanding. Actually we are going for not encrypting that column but masking the last/first digits of them when its got inserted from JAVA side. So this column will be will be available to all now in clear text with the PI characters in masking format(e.g. 1234*****). ofcourse we will cleanout the old data those are there in clear text in table.
|
|
|