Column Level VPD
Vinny, July 30, 2006 - 9:23 am UTC
Thanks Tom. This is an extremely useful feature. You no longer have to create boat loads of views- maintaining these views (updating the views when columns that need to be protected are added/removed to tables and ensuring users have access to the right view, removing/adding access to the correct view as users change their role in the organization etc.) is a nightmare.
Vinny
Row level or Column Level
Vinayak Awasthi, July 31, 2006 - 6:05 am UTC
Hi Tom,
Does this means that at any point of time either row level or column level security is applied by Oracle.(in one sense column level VPD supercedes row level VPD).As shown in example in docs, where result set returned all records(but not just of dept = 30) but the columns were masked as nulls for non 30 depts.
July 31, 2006 - 8:26 am UTC
you may have multiple policies in place on any given table/view.
move the SYS.AUD$ table to another tablespace
Dawar, January 12, 2007 - 1:27 pm UTC
Hello Tom,
There are two main features to stored audit trail.
¿ Storing the audit trail to the database
¿ Storing the audit trail to the operating system.
Which one you prefer & why?
I can see advantage and disadvantage in both cases.
** Storing the audit trail to the database
Storing the audit trail to the database is easier because the data is held in a table in the sys schema. This means that standard SQL tools can be used to query the data and look for anomalies in the data. There are also a number of standard views provided by Oracle to read the data.
But using the SYS.AUD$ table to store the audit trail can cause spacing and sizing issues. A denial of service is therefore possible when using the SYS.AUD$ table as the target.
Does Oracle support to move the SYS.AUD$ table to another tablespace?
** Storing the audit trail to the operating system.
This is platform dependent, and some platforms do not support this option. The common platforms of UNIX, Linux, and Windows do. The audit trail on Windows, for instance, is written to the Windows event log and can be views in the event log viewer. On other platforms, the audit trail is written to trace files that are located in a directory that we specified. A separate file is created for each process accessing the database. This can cause problems if hundreds of users are logging on and off because there will literally be thousands of separate files. Also if shared accounts are used, users will access the DB through one account and audit records will be held in one file for each shared session. Secondly there are no standard tools available to analyze the trail after we created. It has only one big advantage, it is easier to secure the audit trail from hacker access, so deleting one's steps and actions just become harder.
Again, which one you prefer & why?
cheers,
Dawar
move the SYS.AUD$ table to another tablespace
Dawar, January 12, 2007 - 1:27 pm UTC
Hello Tom,
There are two main features to stored audit trail.
¿ Storing the audit trail to the database
¿ Storing the audit trail to the operating system.
Which one you prefer & why?
I can see advantage and disadvantage in both cases.
** Storing the audit trail to the database
Storing the audit trail to the database is easier because the data is held in a table in the sys schema. This means that standard SQL tools can be used to query the data and look for anomalies in the data. There are also a number of standard views provided by Oracle to read the data.
But using the SYS.AUD$ table to store the audit trail can cause spacing and sizing issues. A denial of service is therefore possible when using the SYS.AUD$ table as the target.
Does Oracle support to move the SYS.AUD$ table to another tablespace?
** Storing the audit trail to the operating system.
This is platform dependent, and some platforms do not support this option. The common platforms of UNIX, Linux, and Windows do. The audit trail on Windows, for instance, is written to the Windows event log and can be views in the event log viewer. On other platforms, the audit trail is written to trace files that are located in a directory that we specified. A separate file is created for each process accessing the database. This can cause problems if hundreds of users are logging on and off because there will literally be thousands of separate files. Also if shared accounts are used, users will access the DB through one account and audit records will be held in one file for each shared session. Secondly there are no standard tools available to analyze the trail after we created. It has only one big advantage, it is easier to secure the audit trail from hacker access, so deleting one's steps and actions just become harder.
Again, which one you prefer & why?
Sorry I enetered wrong email id earlier.
cheers,
Dawar
Virtual Private Database
Laurie Murray, August 10, 2011 - 2:35 pm UTC
Hi, Tom -
I am implementing VPD for several thousand tables in my database. They all will use the same pl/sql function for the rls policy. That function references a table called ps_job. I need the policy and function applied to the ps_job table as well. All fine and good, however, all the policies/functions applied to tables that are NOT ps_job, error out when I add this policy to ps_job. I get: "ORA-28108: circular security policies detected"
What I'd like to do is modify my function for all the non ps_job tables so that it negates the rls policy on ps_job. So the rls policy on ps_job would only take effect if the restricted user DIRECTLY queried ps_job. Is this possible? Here is the code that I wrote for my function:
CREATE OR REPLACE FUNCTION ps_earnings_bal_RLS_FUNCTION(
schema_var IN VARCHAR2,
table_var IN VARCHAR2
)
RETURN VARCHAR2
IS
return_val VARCHAR2(600);
BEGIN
if sys_context('USERENV', 'SESSION_USER')='JANET' then
return_val :=
' emplid in (select emplid from PS_JOB A
where A.REG_REGION in (''USA'',''ARG'',''BRA'',''CAN'',''CHL'',''COL'',''CRI'',''MEX'',''PER'',''VEN'')
and A.effdt=(select max(A2.effdt) from ps_job A2
where A2.emplid=A.emplid))'
;
else
return_val:=
'1=1';
end if;
RETURN return_val;
END ps_earnings_bal_RLS_FUNCTION;
August 13, 2011 - 4:30 pm UTC
need full example
create tables, everything
as small as possible - it won't take a thousand tables to reproduce - maybe 2 or 3 at most..
Virtual Private Database
Laurie Murray, August 10, 2011 - 4:55 pm UTC
trying to do something like this, minus the compilation errors:
SQL> CREATE OR REPLACE FUNCTION ps_earnings_bal_RLS_FUNCTION(
schema_var IN VARCHAR2,
table_var IN VARCHAR2
)
RETURN VARCHAR2
IS
return_val VARCHAR2(600);
BEGIN
if sys_context('USERENV', 'SESSION_USER')='JANET' then
DBMS_RLS.ENABLE_POLICY('SYSADM', 'ps_job', 'ps_job_RLS_POLICY', 'FALSE');
return_val :=
' emplid in (select emplid from PS_JOB A
where A.REG_REGION in (''USA'',''ARG'',''BRA'',''CAN'',''CHL'',''COL'',''CRI'',''MEX'',''PER'',
''VEN'')
and A.effdt=(select max(A2.effdt) from ps_job A2
where A2.emplid=A.emplid))'
;
DBMS_RLS.ENABLE_POLICY('SYSADM', 'ps_job', 'ps_job_RLS_POLICY', 'TRUE');
else
return_val:=
'1=1';
end if;
RETURN return_val;
END ps_earnings_bal_RLS_FUNCTION;
/
Warning: Function created with compilation errors.
Virtual Private Database
Laurie Murray, August 10, 2011 - 11:40 pm UTC
That plan, if it were to compile, would leave ps_job unprotected sometimes. not so good. Back to drawing board...
Using VPD
DVN, November 06, 2013 - 3:14 am UTC
Hi Tom..for one of our bank application enhancement we are doing whitelabeling i.e.,serving other banks using same application by enhancing.To restrict data and behavior of application independently based on bank, we are adding new bank code column to most of the static tables and transaction table to decide behavior of product and identify bank code of transaction so for the same we are doing below approach at high level
Three Primary Questions I have:
1)Change Primary Key for tables with addition of bank code.Currently for many of Static DB tables primary key is country+branch+product type/country+branch+charge type/country+branch+party type/country+branch+account number etc etc. so now we are planning to change to bank_code+country+branch+product type/...
We have around 500 SDB tables and 15-20 transaction tables we are changing primary key and corresponding foreign key tables for the same.I remember you said in one of the article changing primary key is very bad so can you suggest if doing above is wrong.Any better alternate.As I said we need to have additional identifier bank code to determine properties, behaviour and identify bank code of transaction
2) For filtering data (update,select and delete statements) in existing SQLs we are planning to use Virtual Private Database.Add policies to changed tables so data is selected for only particular bank code based on user.Based on user profile we are planning to capture bank code and keep it in system context and then pass it to DBML_RLS package.Same way for our Pro*C code..once transaction started we will store bank_code in system context.Is it correct way in terms of workability,performance and solution wise
3) Is there a way to inserting bank code dynamically.When user is login bank code will be retrieved from user session table and we want this value to be inserted into tables automatically when a new row is inserted for that session
Please let me know.Your inputs are very valuable and important
November 07, 2013 - 3:26 pm UTC
1) i wrote that updating a primary key is bad.
I've never written that changing your application in a version upgrade is bad.
This is not updating a primary key, this is changing your data model during an application upgrade. This is what upgrades do.
2) yes, try to use a static policy if at all possible (and it sounds like it will be)
3) you can default it.
ops$tkyte%ORA11GR2> create or replace context my_context using my_procedure;
Context created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure my_procedure
2 as
3 begin
4 dbms_session.set_context( 'my_context', 'my_value', 'hello world' );
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t ( x int, y varchar2(30) default sys_context( 'my_context', 'my_value' ) not null );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t (x) values ( 1 );
insert into t (x) values ( 1 )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TKYTE"."T"."Y")
ops$tkyte%ORA11GR2> exec my_procedure
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> insert into t (x) values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> select * from t;
X Y
---------- ------------------------------
1 hello world
Using SYS_CONTEXT for default argument
A reader, November 07, 2013 - 3:56 pm UTC
Tom
When I try to alter using sys_context for default value
got below error
Alter table transactions add (proc_locn_code1 varchar2(3) default sys_context( 'proc_locn_code','cod_proc_locn' ) not null)
ORA-00600: internal error code, arguments: [kkdlSetColDTypeDfltProps0], [], [], [], [], [], [], [], [], [], [], []
Can you help me?
November 11, 2013 - 9:08 am UTC
what version?
have you filed the SR with support?
I cannot reproduce
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2> alter table t add (y varchar2(3) default sys_context( 'x', 'y' ));
Table altered.
ops$tkyte%ORA12CR1> create table t ( x int );
Table created.
ops$tkyte%ORA12CR1> alter table t add (y varchar2(3) default sys_context( 'x', 'y' ));
Table altered.
Sorry ...Understand the issue
A reader, November 07, 2013 - 3:58 pm UTC
It is due to existing data and I am trying not null.resolved now thanks
not able to figure out this behaviour
A reader, November 11, 2013 - 8:35 am UTC
Hi tom,
I have following privilage on schema scott. create any table, create session.
when I try to create trigger on scott it gives me insufficient privilages but if i create the view on scott by system user it succeed then if i go back to scott user and try to drop the view, i succeed.
if scott dont let me create trigger, it should not let me drop it. since there is no drop privilage to scott.
kindly help to resolve my confusion
Using IN clause for predicate
A reader, December 18, 2013 - 3:46 am UTC
Hi Tom,
Thanks for your earlier inputs and we are now on the implementation of VPD in our department for the first time.
Can you let me know below please?
1) How to use IN clause as part of policy function predicate? Below is what I tried with no success
Policy Function as below:
CREATE OR REPLACE FUNCTION policy_function (obj_schema varchar2, obj_name varchar2)
RETURN VARCHAR2 AS
BEGIN
RETURN 'PROC_LOCN_CODE IN (sys_context( ''APP_CONTEXT'',''PROC_LOCN_CODE''))';
END policy_function;
Context is set as below:
CREATE OR REPLACE PACKAGE BODY PKG_CTX
AS
PROCEDURE SET_CTX(P_KEY IN VARCHAR2, P_VALUE IN VARCHAR2)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT( 'APP_CONTEXT', P_KEY, P_VALUE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100, '%ContextSetFailed%%%%');
END SET_CTX;
END PKG_CTX;
If I set context as below it is working fine as I set only for one bank code (i.e.,proc_locn_code)
BEGIN
PKG_CTX.SET_CTX('PROC_LOCN_CODE','TAM');
END;
but below is not working as I am setting for two so no data returning
BEGIN
PKG_CTX.SET_CTX('PROC_LOCN_CODE','TAM'',''PPC');
END;
Can you help what is the issue and how to make IN clause to work?
2) I read FOR UPDATE,Nested Queries and Outer joins should not be used with VPD as they don't work fine. I tried running some of stored procedures and queries in my application but couldn't see any issue with FOR update and outer joins. From documentation I couldn't get clear picture of what is the issue if used along with VPD? Can you tell me what kind of problem we can expect and better alternate to existing programs using FOR UPDATE,Nested Queries and Outer Joins
3) Is there a V$table where we can see the query that got executed along with predicate
VPD using IN clause, restrictions and system table to see queries executed
DVN, December 20, 2013 - 5:30 am UTC
Hi Tom,
Thanks for your earlier inputs and we are now on the implementation of VPD in our department for
the first time.
Can you let me know below please?
1) How to use IN clause as part of policy function predicate? Below is what I tried with no success
Policy Function as below:
CREATE OR REPLACE FUNCTION policy_function (obj_schema varchar2, obj_name varchar2)
RETURN VARCHAR2 AS
BEGIN
RETURN 'PROC_LOCN_CODE IN (sys_context( ''APP_CONTEXT'',''PROC_LOCN_CODE''))';
END policy_function;
Context is set as below:
CREATE OR REPLACE PACKAGE BODY PKG_CTX
AS
PROCEDURE SET_CTX(P_KEY IN VARCHAR2, P_VALUE IN VARCHAR2)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT( 'APP_CONTEXT', P_KEY, P_VALUE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100, '%ContextSetFailed%%%%');
END SET_CTX;
END PKG_CTX;
If I set context as below it is working fine as I set only for one bank code (i.e.,proc_locn_code)
BEGIN
PKG_CTX.SET_CTX('PROC_LOCN_CODE','TAM');
END;
but below is not working as I am setting for two so no data returning
BEGIN
PKG_CTX.SET_CTX('PROC_LOCN_CODE','TAM'',''PPC');
END;
Can you help what is the issue and how to make IN clause to work?
2) I read FOR UPDATE,Nested Queries and Outer joins should not be used with VPD as they don't work
fine. I tried running some of stored procedures and queries in my application but couldn't see any
issue with FOR update and outer joins. From documentation I couldn't get clear picture of what is
the issue if used along with VPD? Can you tell me what kind of problem we can expect and better
alternate to existing programs using FOR UPDATE,Nested Queries and Outer Joins
3) Is there a V$table where we can see the query that got executed along with predicate
Note:-
Sorry for repeating question as I forgot to put my name and details
Using IN clause in VPD
DVN, January 15, 2014 - 2:18 am UTC
Hi Tom,
Sorry to bother you...Still waiting for your reply?Can you please help
January 15, 2014 - 8:14 pm UTC
I took some time off from asktom during november/december (first real time in 14 years :)). I did not go back and read everything posted while I was taking time off.
1) I would suggest setting values "PROC_LOCN_CODE_1", "PROC_LOCN_CODE_2" .... in your context (an in-list item per value) and then use session_context in your where clause:
ops$tkyte%ORA11GR2> create or replace context myctx using myproc
2 /
Context created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure myproc( p_name in varchar2, p_val in varchar2 )
2 as
3 begin
4 dbms_session.set_context( 'myctx', p_name, p_val );
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec myproc( 'var_1', 'val1' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec myproc( 'var_2', 'val2' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec myproc( 'var_3', 'val3' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select value
2 from session_context
3 where namespace = 'MYCTX'
4 and attribute like 'VAR\_%' escape '\'
5 /
VALUE
-------------------------------------------------------------------------------
val3
val2
val1
otherwise, you'd have to do a trick like:
http://asktom.oracle.com/Misc/varying-in-lists.html 2) The documentation says "there are cases where it will not work - will cause some queries to fail - IF the generated predicate is not supported by the construct"
for example:
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG98282 <quote>
As a general rule, users should not include the FOR UPDATE clause when querying Virtual Private Database-protected tables. The Virtual Private Database technology depends on rewriting the user's query against an inline view that includes the VPD predicate generated by the VPD policy function. Because of this, the same limitations on views also apply to VPD-protected tables. If a user's query against a VPD-protected table includes the FOR UPDATE clause in a SELECT statement, in most cases, the query may not work. However, the user's query may work in some situations if the inline view generated by VPD is sufficiently simple.</quote>
</quote>
that is just saying "hey, if you use VPD, you might return a predicate that would be incompatible with FOR UPDATE (then again, you might not! but you might have a VPD policy that for *some* users returns an OK predicate for FOR UPDATE - but for other users - does not)
that chapter goes through the other things you might need to think about with regards to outer joins and such.
it does not say "they are forbidden", but rather "the end result MIGHT not be to your liking"
3) not really, not until version 12
ops$tkyte%ORA12CR1> create or replace
2 function my_security_function( p_schema in varchar2,
3 p_object in varchar2 )
4 return varchar2
5 as
6 begin
7 return 'owner = USER';
8 end;
9 /
Function created.
ops$tkyte%ORA12CR1> create table my_table
2 ( data varchar2(30),
3 OWNER varchar2(30) default USER
4 )
5 /
Table created.
ops$tkyte%ORA12CR1> begin
2 dbms_rls.add_policy
3 ( object_schema => user,
4 object_name => 'MY_TABLE',
5 policy_name => 'MY_POLICY',
6 function_schema => user,
7 policy_function => 'My_Security_Function',
8 statement_types => 'select, insert, update, delete' ,
9 update_check => TRUE );
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA12CR1> begin
2 dbms_utility.expand_sql_text
3 ( input_sql_text => 'select * from my_table',
4 output_sql_text => :x );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA12CR1> print x
X
--------------------------------------------------------------------------------
SELECT "A1"."DATA" "DATA","A1"."OWNER" "OWNER" FROM (SELECT "A2"."DATA" "DATA",
"A2"."OWNER" "OWNER" FROM "OPS$TKYTE"."MY_TABLE" "A2" WHERE "A2"."OWNER"=USER@!)
"A1"
before that, you can use a 10053 trace to see it:
ops$tkyte%ORA11GR2> alter session set events '10053 trace name context forever, level 1';
Session altered.
ops$tkyte%ORA11GR2> select * from my_table;
no rows selected
resulting trace file had:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "MY_TABLE"."DATA" "DATA","MY_TABLE"."OWNER" "OWNER" FROM "OPS$TKYTE"."MY_TABLE" "MY_TABLE" WHERE "MY_TABLE"."OWNER"=USER@!
kkoqbc: optimizing query block SEL$F5BB74E1 (#0)
ORA-28112: failed to execute policy function
DVN, February 06, 2014 - 8:35 pm UTC
Hi Tom,
As per your suggestion we have done the changes using VPD.It was working all fine but today we got an error saying that ORA-28112: failed to execute policy function
When checked the trace file below is the error:
*** 2014-02-06 13:51:09.077
----------------------------------------------------------
Policy function execution error:
Logon user : TEST2S
Table/View : TEST.WORKUNIT
Policy name : TRADE_POLICY
Policy function: TEST.TRADE_POLICY_DYNAMIC_FUNC
ORA-01000: maximum open cursors exceeded
Max open_cursors is set as 300
Total open cursors are only 38
select a.value, s.username, s.sid, s.serial#,s.program
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current' AND username = 'TEST2S' order by value desc;
Below is the statement that program tried to run after which causing the issue. This is common statement and was working fine with other programs with out any issue:
SELECT 'A'
INTO :txt_Dummy
FROM workunit
WHERE workunit_id = :id_Workunit AND
workunit_seq = :seq_Workunit AND
cur_proc_id = :wf_cur_proc_id AND
cur_proc_step_id = :wf_cur_proc_step_id AND
proc_user_id is null
FOR UPDATE NOWAIT;
Can you give me some insights what exactly is the issue
Additional Information-Nw feature in Oracle 12c
Nikita Sindwani, November 15, 2017 - 5:53 am UTC
In Oracle 12c there is a feature of masking.This feature is known as Data Redaction feature.
It allows for the masking of sensitive data from the end-user layer. Prior to Oracle 12c, you had to create views to "hide" sensitive column (pay rate, social_security_number, credit card numbers, etc.), but in 12c and beyond you can use the data redaction feature.
</>
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'scott',
object_name => 'emp',
column_name => 'social_security_number',
policy_name => 'emp_ssn',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => '*,1,5',
expression => '1=1');
END;
</>
November 15, 2017 - 11:04 am UTC
Yep. This also enables you to mask parts of column's output using regular expressions. VPD is all-or-nothing.