Home » RDBMS Server » Security » VPD Policy Function Issue.
VPD Policy Function Issue. [message #587632] Mon, 17 June 2013 10:15 Go to next message
VIP2013
Messages: 84
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
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 #587767 is a reply to message #587634] Tue, 18 June 2013 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Any feedback? I'm interested to know why it does not work for you.

Regards
Michel
Re: VPD Policy Function Issue. [message #588570 is a reply to message #587767] Wed, 26 June 2013 10:22 Go to previous messageGo to next message
VIP2013
Messages: 84
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 #588582 is a reply to message #588570] Wed, 26 June 2013 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
hen SYS user should also be restricted from viewing the column data.


It is not possible unless you buy Database Vault (and then you don't need VPD).
"EXEMPT ACCESS POLICY" is exactly that VPD does not apply to the accounts that have it. It is its purpose.

Regards
Michel
Re: VPD Policy Function Issue. [message #588638 is a reply to message #588582] Thu, 27 June 2013 04:23 Go to previous messageGo to next message
VIP2013
Messages: 84
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 #588667 is a reply to message #588638] Thu, 27 June 2013 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can't, your only solution is application level encryption.

Regards
Michel
Re: VPD Policy Function Issue. [message #588696 is a reply to message #588667] Thu, 27 June 2013 08:17 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Thanks Michel. Appreciate your quick reply.

[Updated on: Thu, 27 June 2013 08:17]

Report message to a moderator

Re: VPD Policy Function Issue. [message #588887 is a reply to message #588696] Sat, 29 June 2013 08:51 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Hi michel, Below is my actual problem, Please advice:

We are having column called 'Comment' in some of the tables, but sometimes specific private data being fed into the column but not always.
'e.g a/c no - 02345566777 has been deactivated." so we want these data to be encrypted and only visible to functional users again it should also visible to Support people, as because they use this column extensively for "case" analysis. So below are my concern in both ways.

1. If i will go for TDE+VPD, my data will be secured in OS level , also i acn restrict other users too see the column using column level VPD policy, but it will also restrict the Support users in viewing the data which i dont want.(So i cant provide the support user exemp acces policy privilege as i dont want them to see other private data in other columns/tables having VPD applied)
2. if i will go by application encryption, my whole column will be encrypted which will again restrict the support users to see the column data in clear text.
3. Using database vault will put restriction on table level which i dont need.

Please advice.
Re: VPD Policy Function Issue. [message #588891 is a reply to message #588887] Sat, 29 June 2013 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
create new column to hold private data only
Re: VPD Policy Function Issue. [message #588906 is a reply to message #588891] Sat, 29 June 2013 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How easy is BlackSwan's solution.

Regards
Michel
Re: VPD Policy Function Issue. [message #588936 is a reply to message #588906] Sun, 30 June 2013 04:42 Go to previous messageGo to next message
VIP2013
Messages: 84
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
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 Go to previous messageGo to next message
VIP2013
Messages: 84
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 #588943 is a reply to message #588941] Sun, 30 June 2013 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You only have 2 possibilities:
1/ Too bad for the confidential stuff, we live with that
2/ Fix the design, even if it is expensive, cost does not matter.
Now you have to choose your poison.
If you'd have something in the data that allow to hardly know what is private and what is confidential you could create a view upon the table and no more use the table.

Regards
Michel
Re: VPD Policy Function Issue. [message #590648 is a reply to message #588943] Sun, 21 July 2013 01:03 Go to previous message
VIP2013
Messages: 84
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.
Previous Topic: Oracle Wallet Open Failed
Next Topic: Wallets
Goto Forum:
  


Current Time: Wed Aug 20 18:03:07 CDT 2014

Total time taken to generate the page: 0.18188 seconds