1Computer SecurityCS 426Lecture 19Database SecurityVPDElisa BertinoPurdue UniversityIN, [email protected] VPD Virtual Private Database (VPD)– Fine-grained access control: associate security policies with database objects– Application Context: define and access application or session attributes and use them in access control, for example for implementing temporal access control By combining these two features, VPD enables administrators to define and enforce row-level access control policies based on session attributes• Scalability – Table Customers contains 1,000 customer records. Suppose we want customers to access their own records only. Using views, we need to create 1,000 views. Using VPD, it can be done with a single policy function.• Simplicity– Say, we have a table T and many views are based on T. Suppose we want to restrict access to some information in T. Without VPD, all view definitions have to be changed. Using VPD, it can be done by attaching a policy function to T; as the policy is enforced in T, the policy is also enforced for all theviews that are based on T. • Security– Server-enforced security (as opposed to application-enforced).Why VPD How does it work?When a user accesses a table (or view or synonym) which is protected by a VPD policy (function):1. The Oracle server invokes the policy function.2. The policy function returns a predicate, based on session attributes or database contents.3. The server dynamically rewrites the submitted query by appending the returned predicate to the WHERE clause.4. The modified SQL query is executed.Oracle VPD Suppose Alice has (is the owner of) the following table.my_table(owner varchar2(30), data varchar2(30)); Suppose that we want to implement the following policy:– Users can access only data that refer to themselves. However Admins should be able to access any data without restrictions.Oracle VPD - Example1. Create a policy functionCreate function sec_function (object_schema varchar2, object_name varchar2)Return varchar2As user VARCHAR2(100);Begin if ( SYS_CONTEXT(‘userenv’, ‘ISDBA’) ) thenreturn ‘ ’; else user := SYS_CONTEXT(‘userenv’, ‘SESSION_USER’); return ‘owner = ‘ || user;end if;End;userenv is the pre-defined application contextobject_name is the name of table or view to which the policy will applyobject_schema is the schema owning the table or viewOracle VPD - ExampleSYS_CONTEXT In Oracle/PLSQL, the sys_context function is used to retrieve information about the Oracle environment. The syntax for the sys_context function is:sys_context( namespace, parameter, [ length ] ) namespace is an Oracle namespace that has already been created. If the namespace is 'USERENV', attributes describing the currentOracle session can be returned. parameter is a valid attribute that has been set using the DBMS_SESSION.set_context procedure. length is optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytesUSERENV namespace valid parametersUSERENV namespace valid parameters2. Attach the policy function to my_tableexecute dbms_rls.add_policy (object_schema => ‘Alice’,object_name => ‘my_table’,policy_name => ‘my_policy’,function_schema => ‘Alice’,policy_function => ‘sec_function’,statement_types => ‘select, update, insert’,update_check => TRUE );– The VPD security model uses the Oracle dbms_rls package (RLS stands for row-level security)– update_check: Optional argument for INSERT or UPDATE statement types. The default is FALSE. Setting update_check to TRUE causes the server to also check the policy against the value after insert or update.Oracle VPD - ExampleDBMS_RLS.ADD_POLICY syntaxDBMS_RLS.ADD_POLICY ( object schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, function_schema IN VARCHAR2 NULL, policy_function IN VARCHAR2, statement_types IN VARCHAR2 NULL, update_check IN BOOLEAN FALSE, enable IN BOOLEAN TRUE, static_policy IN BOOLEAN FALSE, policy_type IN BINARY_INTEGER NULL, long_predicate IN BOOLEAN FALSE, sec_relevant_cols IN VARCHAR2, sec_relevant_cols_opt IN BINARY_INTEGER NULL);3. Bob accesses my_tableselect * from my_table; => select * from my_table where owner = ‘bob’;: only shows the rows such that owner is ‘bob’insert into my_table values(‘Some data’, ‘bob’); OK!insert into my_table values(‘Other data’, ‘alice’); NOT OK!= because of the check option.Oracle VPD - ExamplePolicy Commands• ADD_POLICY – creates a new policy• DROP_POLICY – drops a policyDBMS_RLS.DROP_POLICY ( object schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2);• ENABLE_POLICY – enables or disables a fine-grained access control policyDBMS_RLS.ENABLE_POLICY ( object schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2,enable IN BOOLEAN );Enable - TRUE to enable the policy, FALSE to disable the policyColumn-level VPD• Instead of attaching a policy to a whole table or a view, attach a policy only to security-relevant columns– Default behavior: restricts the number of rows returned by a query.– Masking behavior: returns all rows, but returns NULL values for the columns that contain sensitive information.• Restrictions– Applies only to ‘select’ statements– The predicate must be a simple Boolean expression.Column-level VPD: Example• Suppose Alice has (is the owner of) the following table.Employees (e_id number(2), name varchar2(10), salary number(3));• Policy: Users can access e_id’s and names without any restriction. But users can access only their own salary information.e_id Name Salary1Alice802Bob603Carl991. Create a policy functionCreate function sec_function (object_schema varchar2, object_name varchar2)Return varchar2As user VARCHAR2(100);Begin user := SYS_CONTEXT(‘userenv’, ‘SESSION_USER’); return ‘name = ‘ || user;End;Column-level VPD: Example2. Attach the policy function to Employees (default behavior)execute dbms_rls.add_policy (object_schema => ‘Alice’,object_name => ‘employees’,policy_name => ‘my_policy’,function_schema => ‘Alice’,policy_function => ‘sec_function’,sec_relevant_cols=>’salary’);Column-level VPD: Example3. Bob accesses table Employees (with the default behavior). REMEMBER: default behavior restricts the number of rows returned by a querya) select