Oracle virtual private database(VPD) enables to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic
WHERE
clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a
WHERE
condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function.Simple Example:
Oracle Virtual Private Database policies can be applied to
SELECT
, INSERT
, UPDATE
, INDEX
and DELETE
statements. Benefits of Using Virtual Private Database(VPD):
Ø VPD policies can restrict access by comparing the value of an attribute in an individual row with an application context value. Global application context allows an application context to be accessed across multiple database sessions, reducing or eliminating the need to create a separate application context for each user session.
Ø VPD is used when the standard object privileges and associated database roles are insufficient to meet application security requirements. VPD policies can be simple or complex depending on your security requirements. VPD can be used in combination with the "application context" feature to enforce sophisticated row and/or column level security requirements for privacy and regulatory compliance.
Ø Oracle VPD is provided at no cost with the Oracle Enterprise Edition.
Advantages of VPD:
Ø Security: Associating a policy with a database table, view, or synonym can solve a potentially serious application security problem. Suppose a user is authorized to use an application, and then drawing on the privileges associated with that application, wrongfully modifies the database by using an ad hoc query tool, such as SQL*Plus. By attaching security policies directly to tables, views, or synonyms, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data.
Ø Simplicity: You add the security policy to a table, view, or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications.
Ø Flexibility: . You can have one security policy for
SELECT
statements, another for INSERT
statements, and still others for UPDATE
and DELETE
statements.Controlling How Oracle Database Evaluates Policy Functions:
Running policy functions multiple times can affect performance. You can control the performance of policy functions by configuring how Oracle Database caches the Oracle Virtual Private Database predicates. The following options are available:
v Evaluate the policy once for each query (static policies).
v Evaluate the policy only when an application context within the policy function changes (context-sensitive policies).
v Evaluate the policy each time it is run (dynamic policies).
Which Privileges Are Used to Run Oracle Virtual Private Database Policy Functions?
For greater security, the Oracle Virtual Private Database policy function runs as if it had been declared with definer's rights. Do not declare it as invoker's rights because this can confuse yourself and other users who maintain the code.
Definer rights:
A PL/SQL program must be compiled and stored in the database before it can be executed. Any
reference in the code is resolved at compile time. This means the program must have directly
granted access to these objects. They are not resolved using roles granted to the definer or
owner of the program. Although the references are checked against the rights the owner has,
you can still grant execute rights to different schemas or users. Keep in mind though that when
another user executes the code, it runs under the authority of the definer of the code. This
means this user has access to all the objects needed by the code, while the user might not have
access to these objects either through directly granted privileges or through a role. This may be
a good thing when you want to make sure the user accesses tabledata only through a defined
interface (Table API) but if you want the user to see certain parts of the data, when different
parts should be hidden from him/her it means either building more code to hide parts of the data
or resort to Virtual Private Databases.
reference in the code is resolved at compile time. This means the program must have directly
granted access to these objects. They are not resolved using roles granted to the definer or
owner of the program. Although the references are checked against the rights the owner has,
you can still grant execute rights to different schemas or users. Keep in mind though that when
another user executes the code, it runs under the authority of the definer of the code. This
means this user has access to all the objects needed by the code, while the user might not have
access to these objects either through directly granted privileges or through a role. This may be
a good thing when you want to make sure the user accesses tabledata only through a defined
interface (Table API) but if you want the user to see certain parts of the data, when different
parts should be hidden from him/her it means either building more code to hide parts of the data
or resort to Virtual Private Databases.
Invoker rights:
The other model available to you is the Invoker Rights model. In this model the code is executed under the authority of the schema (or user) that started the code. This means that all objects needed by the code need to be available to the invoker of the code. References are checked to the references available to the rights the invoker has, instead of the rights the definer of the code has. This means that every user that executes the code can have different objects
available than the ones available to the definer. If the code, for instance, references a table then
this table can be a completely different object than the one used by the compiling schema. This
way you can make sure the user only has access to his own data. If multiple users use the
same code to reference, for instance, a clients table, the different users will see different results.
This way you can easily implement a model where users have access to only their own data,
without the need for Virtual Private Databases or more code. Be aware though that when you
call a Definer Rights defined program from within an Invoker Rights program; from that time
on you will be using the rights for that Definer. If you are to call another Invoker rights program it will be executed under the authority of that Definer, rather then the Invokers authority. If this
program calls an Invoker Rights program it will be executed under the authority of the Definer of
the program that was just called rather than the authority of the original invoker.
available than the ones available to the definer. If the code, for instance, references a table then
this table can be a completely different object than the one used by the compiling schema. This
way you can make sure the user only has access to his own data. If multiple users use the
same code to reference, for instance, a clients table, the different users will see different results.
This way you can easily implement a model where users have access to only their own data,
without the need for Virtual Private Databases or more code. Be aware though that when you
call a Definer Rights defined program from within an Invoker Rights program; from that time
on you will be using the rights for that Definer. If you are to call another Invoker rights program it will be executed under the authority of that Definer, rather then the Invokers authority. If this
program calls an Invoker Rights program it will be executed under the authority of the Definer of
the program that was just called rather than the authority of the original invoker.
Using Oracle Virtual Private Database with an Application Context:
You can use application contexts with Oracle Virtual Private Database policies. When you create an application context, it securely caches user information. Only the designated application package can set the cached environment. It cannot be changed by the user or outside the package. In addition, because the data is cached, performance is increased.
For example, suppose if you want to base access to the ORDERS_TAB table on the customer ID number. Rather than querying the customer ID number for a logged-in user each time you need it, you could store the number in the application context. Then, the customer number is available in the session when you need it.
Ø Definition of application context:An application context is a set of name-valuepairs that Oracle Database stores in memory. The application context has a label called a namespace.
For Example:
v If empno_ctx for an application context that retrieves employee IDs. Inside the context are the name-value pairs (an associative array):
v Name points to a location in memory that holds the value.
v An application can use the application context to access session information about a user, such as the user ID or other user-specific information, or a client ID, and then securely pass this data to the database. You can then use this information to either permit or prevent the user from accessing data through the application.
v You can use application contexts to authenticate both database and non database users.
Where Are the Application Context Values Stored?
Oracle Database stores the application context values in a secure data cache available in the User Global Area (UGA) or the System (sometimes called "Shared") Global Area (SGA).
By this way the application context values are retrieved during the session. Because the application context stores the values in this data cache, it increases performance for your applications.
You can use an application context by itself, with Oracle Virtual Private Databases policies, or with other fine-grained access control policies.
Application contexts are useful for the following purposes:v Enforcing fine-grained access control, for example, in Oracle Virtual Private Database polices
v Preserving user identity across multitier environments
v Enforcing stronger security for your applications, because the application context is controlled by a trusted procedure, not the user
v Increasing performance by serving as a secure data cache for attributes needed by an application for fine-grained auditing or for use in PL/SQL conditional statements or loops.
v Serving as a holding area for name-value pairs that an application can define, modify, and access.
2. Components of an Oracle Virtual Private Database Policy:
To implement Oracle Virtual Private Database, you must create a function to generate the dynamic
WHERE
clause and a policy to attach this function to the objects that you want to protect.Ø Creating a Function to Generate the Dynamic WHERE Clause.
Ø Creating a Policy to Attach the Function to the Objects You Want to Protect.
Creating a Function to Generate the Dynamic WHERE Clause:
To generate the dynamic WHERE clause (predicate), you must create a function (not a procedure) that defines the restrictions that you want to enforce. Usually, the security administrator creates this function in his or her own schema.
The function must have the following behavior:
Ø It must take as arguments a schema name and an object (table, view, or synonym) name as inputs: Define input parameters to hold this information, but do not specify the schema and object name themselves within the function. The policy that you create with the DBMS_RLS package provides the names of the schema, and object to which the policy will apply. You must create the parameter for the schema first, followed by the parameter for the object.
Ø It must provide a return value for the WHERE clause predicate that will be generated: The return value for the WHERE clause is always a VARCHAR2 data type.
Ø It must generate a valid WHERE clause:For designing the
WHERE
clause to be different for each user, each group of users, or each application that accesses the objects you want to protect.Ø It must not select from a table within the associated policy function:
Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.
Creating a Policy to Attach the Function to the Objects You Want to Protect:
After you create the function, you need to create an Oracle Virtual Private Database policy that associates the function with a table, view, or synonym. You create the policy by using the
DBMS_RLS
package. If you are not SYS
, then you must be granted EXECUTE
privileges to use the DBMS_RLS
package. This package contains procedures that enable you to manage the policy and set fine-grained access control.The combination of creating the function and then applying it to a table or view is referred to as creating the Oracle Virtual Private Database policy.
Creating a Simple Oracle Virtual Private Database Policy:
For Example:
If u want to create a simple Oracle Virtual Private Database policy that limits access to all orders in the OE.ORDERS table that were created by Sales Representative 159.
So, the policy translates the following statement:
SELECT * FROM OE.ORDERS;
To the following statement:
SELECT * FROM OE.ORDERS
WHERE SALES_REP_ID = 159;
Step 1: Ensure That the OE User Account Is Active
1. Log on to SQL*Plus as user
SYS
with the SYSDBA
privilege.2. sqlplus sys as sysdba
3. Enter password: password
4. Run the following
SELECT
statement on the DBA_USERS
data dictionary view:5. SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
If the
DBA_USERS
view lists user OE
as locked and expired, then enter the following statement to unlock the OE
account and create a new password:ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;
Replace passwordwith a password that is secure. For greater security, do not reuse the same password that was used in previous releases of Oracle Database.
Step 2: Create a Policy Function
Create the following function, which will append the
WHERE
SALES_REP_ID = 159
clause to any SELECT
statement on the OE.ORDERS
table.1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE FUNCTION auth_orders( schema_var IN VARCHAR2, table_var IN VARCHAR2 ) RETURN VARCHAR2 IS return_val VARCHAR2 (400); BEGIN return_val := 'SALES_REP_ID = 159'; RETURN return_val; END auth_orders; / |
v Lines 2–3: Create input parameters to specify to store the schema name,
OE
, and table name, ORDERS
. First, define the parameter for the schema, and then define the parameter for the object, in this case, a table. Always create them in this order. The Virtual Private Database policy you create will need these parameters to specify the OE.ORDERS
table.v Line 5: Returns the string that will be used for the
WHERE
predicate clause. Remember that return value is always a VARCHAR2
data type.v Lines 6–10: Encompass the creation of the
WHERE SALES_REP_ID = 159
predicate.Step 3: Create the Oracle Virtual Private Database Policy
create the following policy by using the
ADD_POLICY
procedure in the DBMS_RLS
package. 1 2 3 4 5 6 7 8 9 10 11 | BEGIN DBMS_RLS.ADD_POLICY ( object_schema => 'oe', object_name => 'orders', policy_name => 'orders_policy', function_schema => 'sys', policy_function => 'auth_orders', statement_types => 'select, insert, update, delete' ); END; / |
In this Example:
v Line 3: Specifies the schema that you want to protect, that is,
OE
.v Line 4: Specifies the object within the schema to protect, that is, the
ORDERS
table.v Line 5: Names this policy
orders_policy
.v Line 6: Specifies the schema in which the
auth_orders
function was created. In this example, auth_orders
was created in the SYS
schema. But typically, it should be created in the schema of a security administrator.v Line 7: Specifies a function to enforce the policy. Here, you specify the
auth_orders
function that you created in Step 2: Create a Policy Function.v Line 8: Specifies the operations to which the policy applies. In this example, the policy applies to all
SELECT
, INSERT
, UPDATE
, and DELETE
statements the user may perform.Step 4: Test the Policy
After creating the Oracle Virtual Private Database policy, it goes into effect immediately. The next time a user, including the owner of the schema, performs aSELECT
on OE.ORDERS
, only the orders by Sales Representative 159 will be accessed.1. Log on as user OE.
2. Connect oe.
3. Enter password: password.
4. Enter the following select statement.
SELECT COUNT(*) FROM ORDERS;
The following output should appear is:
COUNT(*)--------7
The policy is in effect for user OE.
ORDERS
only the orders by sales representative 159:
As you can see, only 7 of the 105 rows in the orders table are returned.But users with administrative privileges still have access to all the rows in the table. so it will return all 105 rows.
Step 5: Remove the Components(policy and function)
1. As user
SYS
, remove the function and policy as follows:2. DROP FUNCTION auth_orders;
3. EXEC DBMS_RLS.DROP_POLICY('OE','ORDERS','ORDERS_POLICY');
4. If you need to lock and expire the OE
account, then enter the following statement.
5. ALTER USER OE ACCOUNT LOCK PASSWORD EXPIRE;
Five Oracle Virtual Private Database Policy Types
DBMS_RLS.ADD_POLICY Policy Types
Policy Types | When the Policy Function Executes | Usage Example | Shared Across Multiple Objects? |
DYNAMIC | Policy function re-executes every time a policy-protected database object is accessed. | Applications where policy predicates must be generated for each query, such as time-dependent policies where users are denied access to database objects at certain times during the day | No |
STATIC | Once, then the predicate is cached in the SGA | View replacement | No |
SHARED_STATIC | Same as STATIC | Hosting environments, such as data warehouses where the same predicate must be applied to multiple database objects | Yes |
CONTEXT_SENSITIVE | At statement parse time . At statement execution time (When the local application context changed since the last use of the cursor) | Three-tier, session pooling applications where policies enforce two or more predicates for different users or groups | No |
SHARED_CONTEXT_SENSITIVE | First time the object is reference in a database session. Predicates are cached in the private session memory UGA so policy functions can be shared among objects. | Same as CONTEXT_SENSITIVE , but multiple objects can share the policy function from the session UGA | Yes |
3. Configuring an Oracle Virtual Private Database Policy.
This section contains :
A. About Oracle Virtual Private Database Policies.
B. Attaching a Policy a Database Table, View, or Synonym.
C. Enforcing Policies on Specific SQL Statement Types.
D. Controlling the Display of Column Data with Policies.
E. Working with Oracle Virtual Private Database Policy Groups.
F. Optimizing Performance by Using Oracle Virtual Private Database Policy Types.
3.A. About Oracle Virtual Private Database Policies:
After creating the function that defines the actions of the Oracle Virtual Private Database WHERE clause, you must associate this function with the database table to which the VPD action applies. You can do this by configuring an Oracle Virtual Private Database policy. The policy itself is a mechanism for managing the Virtual Private Database function. The policy also enables you to add fine-grained access control, such as specifying the types of SQL statements or particular table columns the policy affects. When a user tries to access the data in this database object, the policy goes into effect automatically.
DBMS_RLS:
The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD). DBMS_RLS is available with the Enterprise Edition only.
Overview of DBMS_RLS:
The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement.
A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface. For example:
DBMS_RLS.ADD_POLICY (
'hr', 'employees', 'emp_policy', 'hr', 'emp_sec', 'select');
Whenever the EMPLOYEEStable, under the HR schema, is referenced in a query or subquery (SELECT), the server calls the EMP_SEC function (under the HRschema). This function returns a predicate specific to the current user for the EMP_POLICY policy. The policy function may generate the predicates based on the session environment variables available during the function call. These variables usually appear in the form of application contexts. The policy can specify any combination of security-relevant columns and of these statement types: INDEX, SELECT, INSERT, UPDATE, or DELETE.
The server then produces a transient view with the text:
SELECT * FROM hr.employees WHERE P1
Here, P1(for example, where SAL > 10000, or even a subquery) is the predicate returned from the EMP_SECfunction. The server treats the EMPLOYEEStable as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary.
If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy-protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users do not require EXECUTE privilege on the policy function, because the server makes the call with the function definer's right.
DBMS_RLS also provides the interface to drop or enable security policies. For example, you can drop or enable the EMP_POLICY with the following PL/SQL statements:
DBMS_RLS.DROP_POLICY('hr', 'employees', 'emp_policy');
DBMS_RLS.ENABLE_ POLICY('hr', 'employees', 'emp_policy', FALSE);
Security Model
A security check is performed when the transient view is created with a subquery. The schema owning the policy function, which generates the dynamic predicate, is the transient view's definer for security check and object lookup.
Operational Notes
TheDBMS_RLS
procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if they are inside a DDL event trigger. With DDL transactions, the DBMS_RLS
procedures are part of the DDL transaction.For example, you may create a trigger for
CREATE
TABLE
. Inside the trigger, you may add a column through ALTER
TABLE
, and you can add a policy through DBMS_RLS
. All these operations are in the same transaction as CREATE
TABLE
, even though each one is a DDL statement. The CREATE
TABLE
succeeds only if the trigger is completed successfully.Views of current cursors and corresponding predicates are available from “
v$vpd_policies”
.DBMS_RLS Procedures:
Procedure | Description |
For Handling Individual Policies | |
Adds a policy to a table, view, or synonym | |
Enables (or disables) a policy you previously added to a table, view, or synonym | |
Invalidates cursors associated with nonstatic policies | |
To drop a policy from a table, view, or synonym | |
For Handling Grouped Policies | |
Creates a policy group | |
Drops a policy group | |
Adds a policy to the specified policy group | |
Enables a policy within a group | |
Parses again the SQL statements associated with a refreshed policy | |
Disables a policy within a group | |
Drops a policy that is a member of the specified group | |
For Handling Application Contexts | |
Adds the context for the active application | |
Drops the context for the application |
3.B. Attaching a Policy a Database Table, View, or Synonym.
DBMS_RLS.ADD_POLICY
procedure. You must specify the table, view, or synonym to which you are adding a policy, and a name for the policy. You can also specify other information, such as the types of statements the policy controls (SELECT
, INSERT
, UPDATE
, DELETE
, CREATE INDEX
, or ALTER INDEX
).This examples shows how to use DBMS_RLS.ADD_POLICY to attach an Oracle Virtual Private Database policy called secure_update to the HR.EMPLOYEES table. The function attached to the policy is check_updates.
Example for Attaching a Simple Oracle Virtual Private Database Policy to a Table
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'secure_update',
policy_function => 'check_updates',
...
If the function was created inside a package, include the package name. For example:policy_function => 'pkg.check_updates',
3. C. Enforcing Policies on Specific SQL Statement Types
You can enforce Oracle Virtual Private Database policies for
SELECT
, INSERT
, UPDATE
, INDEX
and DELETE
statements. If you do not specify a statement type, by default, Oracle Database specifies SELECT
, INSERT
, UPDATE
and DELETE,
but not INDEX
. Enter any combination of these statement types by using the statement_types
parameter in the DBMS_RLS.ADD_POLICY
procedure. Enclose the list in a pair of single quotation marks.Example to show an how to specify the SELECTand INDEX statements for a policy.
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'secure_update',
policy_function => 'check_updates',
statement_types => 'SELECT,INDEX');
END;
/
3. D. Controlling the Display of Column Data with Policies
You can create policies that enforce row-level security when a security-relevant column is referenced in a query.1. Adding Policies for Column-Level Oracle Virtual Private Database.
2. Displaying Only the Column Rows Relevant to the Query.
3. Using Column Masking to Display Sensitive Columns as NULL Values.
3. D.1. Adding Policies for Column-Level Oracle Virtual Private Database
Column-level policies enforce row-level security when a query references a security-relevant column. You can apply a column-level Oracle Virtual Private Database policy to tables and views, but not to synonyms.To apply the policy to a column, specify the security-relevant column by using the
sec_relevant_cols
parameter of the DBMS_RLS.ADD_POLICY
procedure. This parameter applies the security policy whenever the column is referenced, explicitly or implicitly in a query.Example: An Oracle Virtual Private Database policy in which sales department users cannot see the salaries of people outside the department (department number 30) of the sales department users. The relevant columns for this policy are
sal
and comm
. First, the Oracle Virtual Private Database policy function is created, and then it is added by using the DBMS_RLS
PL/SQL package.CREATE OR REPLACE FUNCTION hide_sal_comm (
v_schema IN VARCHAR2,
v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
con := 'deptno=30';
RETURN (con);
END hide_sal_comm;
/
Then configure the policy with the DBMS_RLS.ADD_POLICY
procedure as follows:BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'scott',
object_name => 'emp',
policy_name => 'hide_sal_policy',
policy_function => 'hide_sal_comm',
sec_relevant_cols => 'sal,comm');
END;
/
3.D.2 Displaying Only the Column Rows Relevant to the Query
The default behavior for column-level Oracle Virtual Private Database is to restrict the number of rows returned for a query that references columns containing sensitive information. You specify these security-relevant columns by using the
sec_relevant_columns
parameter of the DBMS_RLS.ADD_POLICY
procedure, as shown above.SELECT
privilege on the emp
table, which is protected with the column-level Oracle Virtual Private Database policy created in above example. The user (for example, user SCOTT
) runs the following query:SELECT ENAME, d.dname, JOB, SAL, COMM
FROM emp e, dept d
WHERE d.deptno = e.deptno;
The database returns the following rows:ENAME DNAME JOB SAL COMM
---------- -------------- --------- ---------- ----------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950
6 rows selected.
The only rows that are displayed are those that the user has privileges to access all columns in the row.3. D. 3 Using Column Masking to Display Sensitive Columns as NULL Values
If a query references a sensitive column, then the default action of column-level Oracle Virtual Private Database restricts the number of rows returned. With column-masking behavior, all rows display, even those that reference sensitive columns. However, the sensitive columns display asNULL
values. To enable column-masking, set the sec_relevant_cols_opt
parameter of the DBMS_RLS.ADD_POLICY
procedure.The default action of column-level Oracle Virtual Private Database, column-masking displays all rows, but returns sensitive column values as
NULL
. To include column-masking in your policy, set the sec_relevant_cols_opt
parameter of the DBMS_RLS.ADD_POLICY
procedure to dbms_rls.ALL_ROWS
.Example for :Adding a Column Masking to an Oracle Virtual Private Database Policy
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'scott',
object_name => 'emp',
policy_name => 'hide_sal_policy',
policy_function => 'hide_sal_comm',
sec_relevant_cols =>' sal,comm',
sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;
/
Assume that a sales department user with SELECT
privilege on the emp
table (such as user SCOTT
) runs the following query:SELECT ENAME, d.dname, job, sal, comm
FROM emp e, dept d
WHERE d.deptno = e.deptno;
The database returns all rows specified in the query, but with certain values masked because of the Oracle Virtual Private Database policy:ENAME DNAME JOB SAL COMM
---------- -------------- --------- ---------- ----------
CLARK ACCOUNTING MANAGER
KING ACCOUNTING PRESIDENT
MILLER ACCOUNTING CLERK
JONES RESEARCH MANAGER
FORD RESEARCH ANALYST
ADAMS RESEARCH CLERK
SMITH RESEARCH CLERK
SCOTT RESEARCH ANALYST
WARD SALES SALESMAN 1250 500
TURNER SALES SALESMAN 1500 0
ALLEN SALES SALESMAN 1600 300
JAMES SALES CLERK 950
BLAKE SALES MANAGER 2850
MARTIN SALES SALESMAN 1250 1400
14 rows selected.
The column-masking returned all rows requested by the sales user query, but made the sal
and comm
columns NULL
for employees outside the sales department.The following considerations apply to column-masking:
v Column-masking applies only to
SELECT
statements.v Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates.
v For applications that perform calculations, or do not expect
NULL
values, use standard column-level Oracle Virtual Private Database, specifying sec_relevant_cols
rather than the sec_relevant_cols_opt
column-masking option.v Column-masking used with
UPDATE AS SELECT
updates only the columns that users are allowed to see.v For some queries, column-masking may prevent some rows from displaying. For example:
SELECT * FROM emp
WHERE sal = 10;
Because the column-masking option was set, this query may not return rows if the
salary
column returns a NULL
value.4.How Oracle Virtual Private Database Works with Other Oracle Features
This section contains:1. Using SELECT FOR UPDATE in User Queries on VPD-Protected Tables
2. How Oracle Virtual Private Database Policies Affect Outer or ANSI Join Operations
3. How Oracle Virtual Private Database Security Policies Work with Applications
4. Using Automatic Reparsing for Fine-Grained Access Control Policy Functions
5. Using Oracle Virtual Private Database Policies and Flashback Query
6. Using Oracle Virtual Private Database and Oracle Label Security
7. User Models and Oracle Virtual Private DatabasE
4.1.Using SELECT FOR UPDATE in User Queries on VPD-Protected Tables
As a general rule, users should not include theFOR 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.The
FOR UPDATE
clause has the following restrictions:· You cannot specify this clause with the following other constructs: the
DISTINCT
operator, CURSOR
expression, set operators, group_by_clause, or aggregate functions.· The tables locked by this clause must all be located on the same database and on the same database as any
LONG
columns and sequences referenced in the same statement.4.2How Oracle Virtual Private Database Policies Affect Outer or ANSI Join Operations
Oracle Virtual Private Database rewrites SQL by using dynamic views. For SQL that contains outer join or ANSI operations, some views may not merge and some indexes may not be used. This problem is a known optimization limitation. To remedy this problem, rewrite the SQL to not use outer joins or ANSI operations.4.3.How Oracle Virtual Private Database Security Policies Work with Applications
An Oracle Virtual Private Database security policy is applied within the database itself, rather than within an application. Hence, a user trying to access data by using a different application cannot bypass the Oracle Virtual Private Database security policy. Another advantage of creating the security policy in the database is that you maintain it in one central place, rather than maintaining individual security policies in multiple applications. Oracle Virtual Private Database provides stronger security than application-based security, at a lower cost of ownership.You may want to enforce different security policies depending on the application that is accessing data. Consider a situation in which two applications, Order Entry and Inventory, both access the
orders
table. You may want to have the Inventory application use a policy that limits access based on type of product. At the same time, you may want to have the Order Entry application use a policy that limits access based on customer number.In this case, you must partition the use of fine-grained access by application. Otherwise, both policies would be automatically concatenated together, which may not be the result that you want. You can specify two or more policy groups, and a driving application context that determines which policy group is in effect for a given transaction. You can also designate default policies that always apply to data access. In a hosted application, for example, data access should be limited by subscriber ID.
4.4.Using Automatic Reparsing for Fine-Grained Access Control Policy Functions
The queries against objects enabled with fine-grained access control run the policy function to ensure that the most current predicate is used for each policy. For example, in the case of a time-based policy function, in which queries are only allowed between 8:00 a.m. and 5:00 p.m., a cursor execution parsed at noon runs the policy function at that time, ensuring that the policy is consulted again for the query. Even if the curser was parsed at 9 a.m., when it runs later on (for example, at noon), then the Virtual Private Database policy function runs again to ensure that the execution of the cursor is still permitted at the current time (noon). This ensures that the security check it must perform is the most recent.Automatic re-execution of the Virtual Private Database policy function does not occur when you set the
DBMS_RLS.ADD_POLICY
setting STATIC_POLICY
to TRUE
while adding the policy. This setting causes the policy function to return the same predicate.4.5.Using Oracle Virtual Private Database Policies and Flashback Query
By default operations on the database use the most recently committed data available. The flashback query feature enables you to query the database at some point in the past. To write an application that uses flashback query, you can use theAS OF
clause in SQL queries to specify either a time or a system change number (SCN), and then query against the committed data from the specified time. You can also use the DBMS_FLASHBACK
PL/SQL package, which requires more code, but enables you to perform multiple operations, all of which refer to the same point in time.However, if you use flashback query against a database object that is protected with Oracle Virtual Private Database policies, then the current policies are applied to the old data. Applying the current Oracle Virtual Private Database policies to flashback query data is more secure because it reflects the most current business policy.
4.6.Using Oracle Virtual Private Database and Oracle Label Security
Oracle label security:
Oracle Label Security is a security option for the Oracle Enterprise Edition database and was introduced with Oracle 8.1.7. Oracle Label Security mediates access to data rows by comparing labels attached to data rows in application tables (sensitivity labels) and a set of user labels (clearance labels).
Oracle Label Security is an out-of-the-box solution for row level security, built on VPD technology. No coding or software development is required, allowing the administrator to focus completely on the policy. Oracle Label Security provides an interface for creating policies, specifying enforcement options, defining data sensitivity labels, establishing user label authorizations, and protecting individual tables or schemas. Data sensitivity labels provide a powerful and flexible method of restricting access to data. For example, data belonging to different organizations or companies can be separated using data sensitivity labels and selectively shared between companies by changing the data sensitivity label.Depending on the complexity of the security policy, Oracle Virtual Private Database (VPD) may be the preferred method for implementing your security policy. In addition, Oracle Label Security is best suited for situations where access control decisions need to be based on the sensitivity of the information.
User Models and Oracle Virtual Private Database
You can use Oracle Virtual Private Database in the following types of user models:· Application users who are also database users. Oracle Database enables applications to enforce fine-grained access control for each user, regardless of whether that user is a database user or an application user unknown to the database. When application users are also database users, Oracle Virtual Private Database enforcement works as follows: users connect to the database, and then the application sets up application contexts for each session. (You can use the default
USERENV
application context namespace, which provides many parameters for retrieve different types of user session data.) As each session is initiated under a different user name, it can enforce different fine-grained access control conditions for each user.· Proxy authentication using OCI or JDBC/OCI. Proxy authentication permits different fine-grained access control for each user, because each session (OCI or JDBC/OCI) is a distinct database session with its own application context.
· Proxy authentication integrated with Enterprise User Security.If you have integrated proxy authentication by using Enterprise User Security, you can retrieve user roles and other attributes from Oracle Internet Directory to enforce Oracle Virtual Private Database policies. (In addition, globally initialized application context can also be retrieved from the directory.)
· Users connecting as One Big Application User. Applications connecting to the database as a single user on behalf of all users can have fine-grained access control for each user. The user for that single session is often called One Big Application User. Within the context of that session, however, an application developer can create a global application context attribute to represent the individual application user (for example,
REALUSER
). Although all database sessions and audit records are created for One Big Application User, the attributes for each session can vary, depending on who the end user is. This model works best for applications with a limited number of users and no reuse of sessions. The scope of roles and database auditing is diminished because each session is created as the same database user.· Web-based applications. Web-based applications typically have hundreds of users. Even when there are persistent connections to the database, supporting data retrieval for many user requests, these connections are not specific to particular Web-based users. Instead, Web-based applications typically set up and reuse connections, to provide scalability, rather than having different sessions for each user. For example, when Web users Jane and Ajit connect to a middle tier application, it may establish a single database session that it uses on behalf of both users. Typically, neither Jane nor Ajit is known to the database. The application is responsible for switching the user name on the connection, so that, at any given time, it is either Jane or Ajit using the session.
Oracle Virtual Private Database helps with connection pooling by allowing multiple connections to access more than one global application context. This ability makes it unnecessary to establish a separate application context for each distinct user session.
Table :summarizes how Oracle Virtual Private Database applies to user models.
User Model Scenario | Individual Database Connection | Separate Application Context per User | Single Database Connection | Application Must Switch User Name |
Application users are also database users | Yes | Yes | No | No |
Proxy authentication using OCI or JDBC/OCI | Yes | Yes | No | No |
Proxy authentication integrated with Enterprise User Security | No | No | Yes | Yes |
One Big Application User | No | No | No | Yes |
Web-based applications | No | No | Yes | Yes |
5.Finding Information About Oracle Virtual Private Database Policies
Data Dictionary Views That Display Information about Virtual Private Database Policies
View | Description |
ALL_POLICIES | Describes all Oracle Virtual Private Database security policies for objects accessible to the current user. |
ALL_POLICY_CONTEXTS | Describes the driving contexts defined for the synonyms, tables, and views accessible to the current user. A driving context is an application context used in an Oracle Virtual Private Database policy. |
ALL_POLICY_GROUPS | Describes the Oracle Virtual Private Database policy groups defined for the synonyms, tables, and views accessible to the current user |
ALL_SEC_RELEVANT_COLS | Describes the security relevant columns of the security policies for the tables and views accessible to the current user |
DBA_POLICIES | Describes all Oracle Virtual Private Database security policies in the database. |
DBA_POLICY_GROUPS | Describes all policy groups in the database. |
DBA_POLICY_CONTEXTS | Describes all driving contexts in the database. Its columns are the same as those in ALL_POLICY_CONTEXTS. |
DBA_SEC_RELEVANT_COLS | Describes the security relevant columns of all security policies in the database |
USER_POLICIES | Describes all Oracle Virtual Private Database security policies associated with objects owned by the current user. This view does not display the OBJECT_OWNER column. |
USER_POLICY_CONTEXTS | Describes the driving contexts defined for the synonyms, tables, and views owned by the current user. Its columns (except for OBJECT_OWNER) are the same as those in ALL_POLICY_CONTEXTS. |
USER_SEC_RELEVANT_COLS | Describes the security relevant columns of the security policies for the tables and views owned by the current user. Its columns (except for OBJECT_OWNER) are the same as those in ALL_SEC_RELEVANT_COLS. |
USER_POLICY_GROUPS | Describes the policy groups defined for the synonyms, tables, and views owned by the current user. This view does not display the OBJECT_OWNER column. |
V$VPD_POLICY | Displays all the fine-grained security policies and predicates associated with the cursors currently in the library cache. This view is useful for finding the policies that were applied to a SQL statement. |
No comments:
Post a Comment