| Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 |
|
|
View PDF |
| Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 |
|
|
View PDF |
Application context can be implemented with fine-grained access control as part of Virtual Private Database (VPD) or by itself to provide application developers a way to define, set, and access application attributes. When used alone, application context can serve as a secure data cache, saving the overhead of multiple queries to the database each time an application needs to access application attributes.
This chapter discusses how to implement application context and fine-grained access control. It contains the following topics:
| Topic Category | Links to Topics |
|---|---|
|
Application Context |
|
|
Fine-Grained Access Control |
Application context can be used for the following purposes:
When application context is used as a secure data cache, applications can use the attributes stored in the context for PL/SQL control structures that use conditional statements or loops, or for fine-grained auditing.
There are two types of application contexts, depending on where the context information is stored:
Session-based application contexts can be initialized from external sources or they can be initialized globally. In either case, the context information is stored in the user session. Those session-based application contexts that are initialized externally can accept initialization of attributes and values through external resources such as an OCI interface, a job queue process, or a connected user database link. Those that are initialized globally can accept initialization of attributes and values from a centralized location, such as an LDAP directory.
Table 14-1 summarizes the different types of application contexts.
See Also:
|
To use application context, you perform the following tasks:
Begin by creating a PL/SQL package with functions that set the context for your application. This section presents an example for creating the PL/SQL package, followed by a discussion of the syntax and behavior of the SYS_CONTEXT SQL function.
|
Note: A logon trigger can be used because the user's context (information such as |
The following example creates the package App_security_context.
CREATE OR REPLACE PACKAGE App_security_context IS PROCEDURE Set_empno; END; CREATE OR REPLACE PACKAGE BODY App_security_context IS PROCEDURE Set_empno IS Emp_id NUMBER; BEGIN SELECT Empno INTO Emp_id FROM Emp WHERE Ename = SYS_CONTEXT('USERENV', 'SESSION_USER'); DBMS_SESSION.SET_CONTEXT('app_context', 'empno', Emp_id); END; END;
| See Also:
PL/SQL Packages and Types Reference for information about the |
The syntax for this function is:
SYS_CONTEXT ('namespace', 'attribute', [length])
This function returns the value of attribute as defined in the package currently associated with the context namespace. It is evaluated once for each statement execution, and is treated like a constant during type checking for optimization. You can use the pre-defined namespace USERENV to access primitive contexts such as userid and Globalization Support parameters.
See Also:
|
During a session in which you expect a change in policy between executions of a given query, that query must use dynamic SQL. You must use dynamic SQL because static SQL and dynamic SQL parse statements differently.
Consider a situation in which policy A is in force when you compile a SQL statement--and then you switch to policy B and execute the statement. With static SQL, policy A remains in force: the statement is parsed at compile time and not reparsed upon execution. With dynamic SQL, the statement is parsed upon execution, and so the switch to policy B takes effect.
For example, consider the following policy:
EMPLOYEE_NAME = SYS_CONTEXT ('USERENV', 'SESSION_USER')
The policy "Employee name matches database user name" is represented in the form of a SQL predicate: the predicate is basically a policy. If the predicate changes, the statement must be reparsed in order to produce the correct result.
If SYS_CONTEXT is used inside a SQL function which is embedded in a parallel query, the function picks up the application context.
Consider a user-defined function within a SQL statement, which sets the user's ID to 5:
CREATE FUNCTION proc1 AS RETURN NUMBER; BEGIN IF SYS_CONTEXT ('hr', 'id') = 5 THEN RETURN 1; ELSE RETURN 2; END END;
Now consider the statement:
SELECT * FROM EMP WHERE proc1( ) = 1;
When this statement is run as a parallel query, the user session, which contains the application context information, is propagated to the parallel execution servers (query slave processes).
Session-based local application context can be accessed by SQL statements within a user session by using the SYS_CONTEXT SQL function. When these SQL statements involve database links, then the SYS_CONTEXT SQL function is executed at the database link's initiating site and captures the context information there (on the initiating site).
If remote PL/SQL procedure calls are executed over a database link, then any SYS_CONTEXT function inside such a procedure is executed at the database link's destination site. In this case, only externally initialized application contexts are available at the database link's destination site. For security reasons, only the externally initialized application context information is propagated to the destination site from the initiating database link site.
To perform this task, use the CREATE CONTEXT statement. Each context must have a unique attribute and belong to a namespace. That is, context names must be unique within the database, not just within a schema. Contexts are always owned by the schema SYS.
For example:
CREATE CONTEXT order_entry USING App_security_context;
where order_entry is the context namespace, and App_security_context is the trusted package that can set attributes in the context namespace.
After you have created the context, you can set or reset the context attributes by using the DBMS_SESSION.SET_CONTEXT package. The values of the attributes you set remain either until you reset them, or until the user ends the session.
You can only set the context attributes inside the trusted procedure you named in the CREATE CONTEXT statement. This prevents a malicious user from changing context attributes without proper attribute validation.
Alternatively, you can use the Oracle Policy Manager graphical user interface to create a context and associate it with a PL/SQL package. Oracle Policy Manager, accessed from Oracle Enterprise Manager, enables you to apply policies to database objects and create application contexts. It also can be used to create and manage Oracle Label Security policies.
Always use an event trigger on login to pull session information into the context. This sets the user's security-limiting attributes for the database to evaluate, and thus enables it to make the appropriate security decisions.
Other considerations come into play if you have a changing set of books, or if positions change constantly. In these cases, the new attribute values may not be picked up right away, and you must force a cursor reparse to pick them up.
Now that you have set up the context and the PL/SQL package, your VPD policy functions can use the application context to make policy decisions based on different context values.
This section provides three examples that use session-based application context within a fine-grained access control function.
This example uses application context to implement the policy, "Customers can see their own orders only."
This example guides you through the following steps in building the application:
The procedure in this example assumes a one-to-one relationship between users and customers. It finds the user's customer number (Cust_num), and caches the customer number in the application context. You can later refer to the cust_num attribute of your order entry context (oe_ctx) inside the security policy function.
Note that you could use a logon trigger to set the initial context.
Create the package as follows:
CREATE OR REPLACE PACKAGE apps.oe_ctx AS PROCEDURE set_cust_num; END; CREATE OR REPLACE PACKAGE BODY apps.oe_ctx AS PROCEDURE set_cust_num IS custnum NUMBER; BEGIN SELECT cust_no INTO custnum FROM customers WHERE cust_name = SYS_CONTEXT('USERENV', 'SESSION_USER'); /* SET cust_num attribute in 'order_entry' context */ DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum); DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum); END set_cust_num; END;
|
Note: This example does not treat error handling. You can access predefined attributes--such as session user--by using For more information, see Table 13-1, " Key to Predefined Attributes in USERENV Namespace" and Oracle Database SQL Reference |
Create an application context by entering:
CREATE CONTEXT Order_entry USING apps.oe_ctx;
Alternatively, you can use Oracle Policy Manager to create an application context.
Access the application context inside the package that implements the security policy on the database object.
|
Note: You may need to set up the following data structures for certain examples to work: CREATE OR REPLACE PACKAGE Oe_security AS FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2; END; |
The package body appends a dynamic predicate to SELECT statements on the ORDERS_TAB table. This predicate limits the orders returned to those of the user's customer number by accessing the cust_num context attribute, instead of a subquery to the customers table.
CREATE OR REPLACE PACKAGE BODY Oe_security AS /* limits select statements based on customer number: */ FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 IS D_predicate VARCHAR2 (2000); BEGIN D_predicate := 'cust_no = SYS_CONTEXT(''order_entry'', ''cust_num'')'; RETURN D_predicate; END Custnum_sec; END Oe_security;
Create the policy as follows:
|
Note: You may need to set up the following data structures for certain examples to work: CONNECT sys/xIcf1T9u AS sysdba; CREATE USER secusr IDENTIFIED BY secusr; |
BEGIN DBMS_RLS.ADD_POLICY ('scott', 'orders_tab', 'oe_policy', 'secusr', 'oe_security.custnum_sec', 'select'); END;
This statement adds a policy named OE_POLICY to the ORDERS_TAB table for viewing in schema SCOTT. The SECUSR.OE_SECURITY.CUSTNUM_SEC function implements the policy, is stored in the SECUSR schema, and applies to SELECT statements only.
Now, any select statement by a customer on the ORDERS_TAB table automatically returns only that customer's orders. In other words, the dynamic predicate modifies the user's statement from this:
SELECT * FROM Orders_tab;
to this:
SELECT * FROM Orders_tab WHERE Custno = SYS_CONTEXT('order_entry','cust_num');
Note the following with regard to this example:
custnum_sec function to return different predicates based on the user's position context value.SELECT * FROM Orders_tab WHERE Custno = SYS_CONTEXT('order_entry', 'cust_num')
This is fully parsed and optimized, but the evaluation of the user's CUST_NUM attribute value for the ORDER_ENTRY context takes place at execution. This means that you get the benefit of an optimized statement which executes differently for each user who executes the statement.
See Also:
|
This example uses application context to control user access by way of a Human Resources application. It guides you through the following three tasks, each of which is described more fully in the following sections.
In this example, assume that the application context for the Human Resources application is assigned to the HR_CTX namespace.
Create a PL/SQL package with a number of functions that set the context for the application
APPS is the schema owning the package.
CREATE OR REPLACE PACKAGE BODY apps.hr_sec_ctx IS /* function to set responsibility id */ PROCEDURE set_resp_id (respid NUMBER) IS BEGIN /* validate respid based on primitive and other context */ /* validate_respid (respid); */ /* set resp_id attribute under namespace 'hr_ctx'*/ DBMS_SESSION.SET_CONTEXT('hr_ctx', 'resp_id', respid); END set_resp_id; /* function to set organization id */ PROCEDURE set_org_id (orgid NUMBER) IS BEGIN /* validate organization ID */ /* validate_org_id(orgid); /* /* set org_id attribute under namespace 'hr_ctx' */ DBMS_SESSION.SET_CONTEXT('hr_ctx', 'org_id', orgid); END set_org_id; /* more functions to set other attributes for the HR application */ END hr_sec_ctx;
For example:
CREATE CONTEXT Hr_ctx USING apps.hr_sec_ctx;
Suppose that the execute privilege on the package HR_SEC_CTX has been granted to the schema running the application. Part of the script will make calls to set various attributes of the HR_CTX context. Here, we do not show how the context is determined. Normally, it is based on the primitive context or other derived context.
APPS.HR_SEC_CTX.SET_RESP_ID(1); APPS.HR_SEC_CTX.SET_ORG_ID(101);
The SYS_CONTEXT function can be used for data access control based on this application context. For example, the base table HR_ORGANIZATION_UNIT can be secured by a view that restricts access to rows based on attribute ORG_ID:
|
Note: You may need to set up data structures for certain examples to work: CREATE TABLE hr_organization_unit (organization_id NUMBER); |
CREATE VIEW Hr_organization_secv AS SELECT * FROM hr_organization_unit WHERE Organization_id = SYS_CONTEXT('hr_ctx','org_id');
This example illustrates use of the following security features in Oracle Database:
In this example, we associate a security policy with the table called DIRECTORY which has the following columns:
| Column | Description |
|---|---|
|
|
identification number for each employee |
|
|
employee identification number for the manager of each employee |
|
|
position of the employee in the corporate hierarchy |
The security policy associated with this table has two elements:
MGRID for a specific EMPNO. To implement this, we create a definer's right package in the human resources schema (HR) to perform SELECT on the table.CONNECT system/yJdg2U1v AS sysdba GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE,CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE ANY TRIGGER TO HR IDENTIFIED BY HR; CONNECT hr/hr; CREATE TABLE Directory (Empno NUMBER(4) NOT NULL, Mgrno NUMBER(4) NOT NULL, Rank NUMBER(7,2) NOT NULL); CREATE TABLE Payroll (Empno NUMBER(4) NOT NULL, Name VARCHAR(30) NOT NULL ); /* seed the tables with a couple of managers: */ INSERT INTO Directory VALUES (1, 1, 1.0); INSERT INTO Payroll VALUES (1, 'KING'); INSERT INTO Directory VALUES (2, 1, 5); INSERT INTO Payroll VALUES (2, 'CLARK'); /* Create the sequence number for EMPNO: */ CREATE SEQUENCE Empno_seq START WITH 5; /* Create the sequence number for RANK: */ CREATE SEQUENCE Rank_seq START WITH 100; CREATE OR REPLACE CONTEXT Hr_app USING Hr.Hr0_pck; CREATE OR REPLACE CONTEXT Hr_sec USING Hr.Hr1_pck; CREATE or REPLACE PACKAGE Hr0_pck IS PROCEDURE adjustrankby1(Empno NUMBER); END; CREATE or REPLACE PACKAGE BODY Hr0_pck IS /* raise the rank of the empno by 1: */ PROCEDURE Adjustrankby1(Empno NUMBER) IS Stmt VARCHAR2(100); BEGIN /*Set context to indicate application state */ DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',1); /* Now we can issue DML statement: */ Stmt := 'UPDATE Directory d SET Rank = Rank + 1 WHERE d.Empno = ' || Empno; EXECUTE IMMEDIATE STMT; /* Re-set application state: */ DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',0); END; END; CREATE or REPLACE PACKAGE hr1_pck IS PROCEDURE setid; END; / /* Based on userid, find EMPNO, and set it in application context */ CREATE or REPLACE PACKAGE BODY Hr1_pck IS PROCEDURE setid IS id NUMBER; BEGIN SELECT Empno INTO id FROM Payroll WHERE Name = SYS_CONTEXT('userenv','session_user') ; DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id); DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id); EXCEPTION /* For purposes of demonstration insert into payroll table / so that user can continue on and run example. */ WHEN NO_DATA_FOUND THEN INSERT INTO Payroll (Empno, Name) VALUES (Empno_seq.NEXTVAL, SYS_CONTEXT('userenv','session_user')); INSERT INTO Directory (Empno, Mgrno, Rank) VALUES (Empno_seq.CURRVAL, 2, Rank_seq.NEXTVAL); SELECT Empno INTO id FROM Payroll WHERE Name = sys_context('userenv','session_user') ; DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id); DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id); WHEN OTHERS THEN NULL; /* If this is to be fired by using a "logon" trigger, / you need to handle exceptions if you want the user to continue / logging into the database. */ END; END; GRANT EXECUTE ON Hr1_pck TO public; CONNECT system/yJdg2U1v AS sysdba CREATE OR REPLACE TRIGGER Databasetrigger AFTER LOGON ON DATABASE BEGIN hr.Hr1_pck.Setid; END; /* Creates the package for finding the MGRID for a particular EMPNO using definer's right (encapsulated privileges). Note that users are granted EXECUTE privileges only on this package, and not on the table (DIRECTORY) it is querying. */ CONNECT hr/hr CREATE or REPLACE PACKAGE hr2_pck IS FUNCTION Findmgr(Empno NUMBER) RETURN NUMBER; END; CREATE or REPLACE PACKAGE BODY hr2_pck IS /* insert a new employee record: */ FUNCTION findmgr(empno number) RETURN NUMBER IS Mgrid NUMBER; BEGIN SELECT mgrno INTO mgrid FROM directory WHERE mgrid = empno; RETURN mgrid; END; END; CREATE or REPLACE FUNCTION secure_updates(ns varchar2,na varchar2) RETURN VARCHAR2 IS Results VARCHAR2(100); BEGIN /* Only allow updates when designated application has set the session state to indicate we are inside it. */ IF (sys_context('hr_app','adjstate') = 1) THEN results := 'mgrno = SYS_CONTEXT("hr_sec","empno")'; ELSE results := '1=2'; END IF; RETURN Results; END; /* Attaches fine-grained access policy to all update operations on hr.directory */ CONNECT system/yJdg2U1v AS sysdba; BEGIN DBMS_RLS.ADD_POLICY('hr','directory','secure_update','hr', 'secure_updates','update',TRUE,TRUE); END;
This feature lets you specify a special type of namespace that accepts initialization of attribute values from external resources and stores them in the user's local session. This enhances performance and enables the automatic propagation of attributes from one session to the other. Only those application contexts initialized from OCI-based external sources support connected user database links.
This section contains these topics:
Sometimes it is desirable to obtain default values from users. Initially, these default values may serve as hints or preferences, and then after validation become trusted contexts. Similarly, it may be more convenient for clients to initialize some default values, and then rely on a login event trigger or applications to validate the values.
For job queues, the job submission routine records the context being set at the time the job is submitted, and restores it when executing the batched job. To maintain the integrity of the context, job queues cannot bypass the designated PL/SQL package to set the context. Rather, externally initialized application context accepts initialization of context values from the job queue process.
Automatic propagation of context to a remote session may create security problems. Developers or administrators can effectively handle this type of context that takes default values from resources other than the designated PL/SQL procedure by using logon triggers to reset the context when users logon.
In addition to using the designated trusted package, externally initialized application context can also accept initialization of attributes and values through external resources such as an OCI interface, a job queue process, or a database link. It provides:
Although this type of namespace can be initialized by any client program using OCI, there are login event triggers that can verify the values. It is up to the application to interpret and trust the values of the attributes.
Middle-tier servers can actually initialize context values on behalf of database users. Context attributes are propagated for the remote session at initialization time, and the remote database accepts the values if the namespace is externally initialized.
This feature uses a centralized location to store the user's application context, enabling applications to set up the user's contexts during initialization based upon the user's identity. In particular, it supports Oracle Label Security labels and privileges. This feature makes it much easier for the administrator to manage contexts for large numbers of users and databases. For example, many organizations want to manage user information centrally, in an LDAP-based directory. Enterprise User Security, a feature of Oracle Advanced Security, supports centralized user and authorization management in Oracle Internet Directory. However, there may be additional attributes an application wishes to retrieve from LDAP to use for VPD enforcement, such as the user's title, organization, or physical location.
This section contains these topics:
Session-based application context initialized globally utilizes the Lightweight Directory Access Protocol (LDAP). LDAP is a standard, extensible, and efficient directory access protocol. The LDAP directory stores a list of users to which this application is assigned. An Oracle database server can use Oracle Internet Directory, or third-party directories such as Microsoft Active Directory and Sun Microsystems iPlanet, as the directory service for authentication and authorization of enterprise users. (Enterprise User Security requires Oracle Advanced Security.)
The LDAP object orclDBApplicationContext (a subclass of groupOfUniqueNames) has been defined to store the application context values in the directory. The location of the application context object is described in Figure 14-1, which is based upon the Human Resources example.
An internal C function is required to retrieve the orclDBApplicationContext value, which returns a list of application context values to the RDBMS.
|
Note: In this example, HR is the namespace, Title and Project are the attributes, and Manager and Promotion are the values. |
Text description of the illustration adfns001.gif
The administrator configures Enterprise User Security, a feature of Oracle Advanced Security. Then she sets up the user's application context values in the database and the directory.
When a global user (enterprise user) connects to the database, the Oracle Advanced Security Enterprise User Security feature performs authentication to verify the identity of the user connecting to the database. After authentication, the user's global roles and application context are retrieved from the directory. When the user logs on to the database, her global roles and initial application context are already set up.
| See Also:
Oracle Advanced Security Administrator's Guide for a complete discussion of Enterprise User Security and how to configure this feature. |
The initial application context for a user, such as department name and title, can be set up and stored in the LDAP directory. The values are retrieved during user login so that the context is set properly. In addition, any information related to the user is retrieved and stored in the application context namespace SYS_USER_DEFAULTS. The following example shows how this is done.
CREATE CONTEXT HR USING hrapps.hr_manage_pkg INITIALIZED GLOBALLY;
An example of the entries added to the LDAP directory follows. These entries create an attribute name Title with attribute value Manager for the application (namespace) HR, and assign usernames user1 and user2.
dn: cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=OracleC ontext,ou=Americas,o=oracle,c=US changetype: add cn: OracleDBAppContext objectclass: top objectclass: orclContainer dn: cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=O racleContext,ou=Americas,o=oracle,c=US changetype: add cn: HR objectclass: top objectclass: orclContainer dn: cn=Title,cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Prod ucts,cn=OracleContext,ou=Americas,o=oracle,c=US changetype: add cn: Title objectclass: top objectclass: orclContainer dn: cn=Manager,cn=Title,cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecur ity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US cn: Manager objectclass: top objectclass: groupofuniquenames objectclass: orclDBApplicationContext uniquemember: CN=user1,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US uniquemember: CN=user2,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
inetOrgPerson object entry exists for the user, the connection will also retrieve all the attributes from inetOrgPerson and assign them to the namespace SYS_LDAP_USER_DEFAULT. The following is an example of an inetOrgPerson entry:
dn: cn=user1,ou=Americas,O=oracle,L=redwoodshores,ST=CA,C=US changetype: add objectClass: top objectClass: person objectClass: organizationalPerson objectClass: inetOrgPerson cn: user1 sn: One givenName: User initials: UO title: manager, product development uid: uone mail: uone@us.oracle.com telephoneNumber: +1 650 123 4567 employeeNumber: 00001 employeeType: full time
When user1 connects to a database that belongs to domain myDomain, user1 will have his Title set to Manager. Any information related to user1 will be retrieved from the LDAP directory. The value can be obtained using the syntax
SYS_CONTEXT('namespace','attribute name')
For example:
DECLARE tmpstr1 VARCHAR2(30); tmpstr2 VARCHAR2(30); BEGIN tmpstr1 = SYS_CONTEXT('HR','TITLE); tmpstr2 = SYS_CONTEXT('SYS_LDAP_USER_DEFAULT','telephoneNumber'); DBMS_OUTPUT.PUT_LINE('Title is ' || tmpstr1); DBMS_OUTPUT.PUT_LINE('Telephone Number is ' || tmpstr2); END;
The output of the preceding example is:
Title is Manager Telephone Number is +1 650 123 4567
Global application context stores context information in the SGA so it can be used for applications which use a sessionless model, such as middle-tier applications in a three-tiered architecture. These applications cannot use session-based application context because users authenticate to the application and then it typically connects to the database as a single identity. Global application context uses the CLIENT_IDENTIFIER USERENV namespace attribute, set with the DBMS_SESSION interface, to associate the database session with a particular user or group. The following sections explain how to use the DBMS_SESSION interface to set the CLIENT_IDENTIFIER and then examples are provided:
| See Also:
"Introduction to Global Application Context" for conceptual information about this feature. |
The DBMS_SESSION interface for managing application context has a client identifier for each application context. In this way, application context can be managed globally, yet each client sees only his or her assigned application context. The following interfaces in DBMS_SESSION enable the administrator to manage application context in client sessions:
SET_CONTEXTCLEAR_CONTEXTCLEAR_ALL_CONTEXT (can also be used with session-based application context)SET_IDENTIFIERCLEAR_IDENTIFIERThe middle-tier application server can use SET_CONTEXT to set application context for a specific client ID. Then, when assigning a database connection to process the client request, the application server needs to issue a SET_IDENTIFIER to denote the ID of the application session. From then on, every time the client invokes SYS_CONTEXT, only the context that was associated with the set identifier is returned.
See Also:
|
This section provides two examples that use global application context.
The following steps outline the global application context process:
AppSvr, that has assigned the client identifier 12345 to client SCOTT. It then issues the following statement to indicate that, for this client identifier, there is an application context RESPONSIBILITY with a value of 13 in the HR namespace.
DBMS_SESSION.SET_CONTEXT( 'HR', 'RESPONSIBILITY' , '13', 'SCOTT', '12345' );
Note that HR must be a global context namespace created as follows:
CREATE CONTEXT hr USING hr.init ACCESSED GLOBALLY;
SCOTT uses AppSvr to connect to the database:
DBMS_SESSION.SET_IDENTIFIER('12345');
SYS_CONTEXT('HR','RESPONSIBILITY') call within the database session, the database engine matches the client identifier 12345 to the global context, and returns the value 13.AppSvr clears the client identifier by issuing:
DBMS_SESSION.CLEAR_IDENTIFIER( );
After a session's client identifier is cleared, it takes on a NULL value. This implies that subsequent SYS_CONTEXT calls only retrieve application contexts with NULL client identifiers, until the client identifier is set again using the SET_IDENTIFIER interface.
The following steps outline the global application context process for a lightweight user application:
CREATE CONTEXT hr USING hr.init ACCESSED GLOBALLY;
HR application server (AppSvr) starts up and establishes multiple connections to the HR database as user APPSMGR.SCOTT logs on to the HR application server.AppSvr authenticates SCOTT to the application.AppSvr assigns a temporary session ID (or simply uses the application user ID), 12345, for this connection.SCOTT's browser as part of a cookie or maintained by AppSvr.
AppSvr initializes application context for this client calling the HR.INIT package, which issues:
DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', 'APPSMGR', 12345 ); DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', 'APPSMGR', 12345 );
AppSvr assigns a database connection to this session, and initializes the session by issuing:
DBMS_SESSION.SET_IDENTIFIER( 12345 );
SYS_CONTEXT calls within this database session will return application context values belonging to the client session only. For example, SYS_CONTEXT('hr','id') will return the value SCOTT.AppSvr can issue the following statement to clean up the client identity:
DBMS_SESSION.CLEAR_IDENTIFIER ( );
Note that even if another database user (ADAMS) had logged into the database, he cannot access the global context set by AppSvr because AppSvr has specified that only the application with logged in user APPSMGR can see it. If AppSvr has used the following, then any user session with client ID set to 12345 can see the global context.
DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', NULL , 12345 ); DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', NULL , 12345 );
This approach enables different users to share the same context.
Users should be aware of the security implication of different settings of the global context. NULL in the username means that any user can access the global context. A NULL client ID in the global context means that only a session with an uninitialized client ID can access the global context.
Users can query the client identifier set in the session as follows:
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
The DBA can see which sessions have the client identifier set by querying the V$SESSION view's CLIENT_IDENTIFIER and USERNAME.
When a user wants to see how much global context area (in bytes) is being used, she can use SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY')
| See Also:
For more information about using the |
Fine-grained access control is based on dynamically modified statements. Suppose you want to attach to the ORDERS_TAB table the following security policy: "Customers can see only their own orders." The process is described in this section.
|
Note: A predicate is the |
In this case, you might create a function that adds the following predicate:
Cust_no = (SELECT Custno FROM Customers WHERE Custname = SYS_CONTEXT ('userenv','session_user'))
SELECT * FROM Orders_tab;
SELECT * FROM Orders_tab WHERE Custno = ( SELECT Custno FROM Customers WHERE Custname = SYS_CONTEXT('userenv', 'session_user'))
Upon execution, the function employs the username returned by SYS_CONTEXT ('userenv','session_user') to look up the corresponding customer and to limit the data returned from the ORDERS_TAB table to that customer's data only.
A policy group is a set of security policies which belong to an application. You can designate an application context (known as a driving context) to indicate the policy group in effect. Then, when the table, view, or synonym column is accessed, the server looks up the driving context (which are also known as policy contexts) to determine the policy group in effect. It enforces all the associated policies which belong to that policy group.
This section contains the following topics:
In the Oracle Policy Manager tree structure, the Fine-Grained Access Control Policies folder contains the Policy Groups folder. The Policy Groups folder contains an icon for each policy group, as well as an icon for the SYS_DEFAULT policy group.
By default, all policies belong to the SYS_DEFAULT policy group. Policies defined in this group for a particular table, view, or synonym will always be executed along with the policy group specified by the driving context. The SYS_DEFAULT policy group may or may not contain policies. If you attempt to drop the SYS_DEFAULT policy group, an error will be raised.
If, to the SYS_DEFAULT policy group, you add policies associated with two or more objects, then each such object will have a separate SYS_DEFAULT policy group associated with it. For example, the EMP table in the SCOTT schema has one SYS_DEFAULT policy group, and the DEPT table in the SCOTT schema has a different SYS_DEFAULT policy group associated with it. These are displayed in the tree structure as follows:
SYS_DEFAULT - policy1 (SCOTT/EMP) - policy3 (SCOTT/EMP) SYS_DEFAULT - policy2 (SCOTT/DEPT)
When adding the policy to a table, view, or synonym, you can use the DBMS_RLS.ADD_GROUPED_POLICY interface to specify the group to which the policy belongs. To specify which policies will be effective, you add a driving context using the DBMS_RLS.ADD_POLICY_CONTEXT interface. If the driving context returns an unknown policy group, an error is returned.
If the driving context is not defined, then all policies are executed. Likewise, if the driving context is NULL, then policies from all policy groups are enforced. In this way, an application accessing the data cannot bypass the security setup module (which sets up application context) to avoid any applicable policies.
You can apply multiple driving contexts to the same table, view, or synonym, and each of them will be processed individually. In this way you can configure multiple active sets of policies to be enforced.
Consider, for example, a hosting company that hosts Benefits and Financial applications, which share some database objects. Both applications are striped for hosting using a SUBSCRIBER policy in the SYS_DEFAULT policy group. Data access is partitioned first by subscriber ID, then by whether the user is accessing the Benefits or Financial applications (determined by a driving context). Suppose that Company A, which uses the hosting services, wants to apply a custom policy which relates only to its own data access. You could add an additional driving context (such as COMPANY A SPECIAL) to ensure that the additional, special policy group is applied for Company A's data access only. You would not apply this under the SUBSCRIBER policy, since the policy relates only to Company A, and it is more efficient to segregate the basic hosting policy from other policies.
To create policy groups, the administrator must do two things:
The following example shows how to perform these tasks.
Begin by creating a namespace for the driving context. For example:
CREATE CONTEXT appsctx USING apps.apps_security_init;
Create the package that administers the driving context. For example:
CREATE OR REPLACE PACKAGE apps.apps_security_init IS
PROCEDURE setctx (policy_group VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY apps.apps_security_init AS
PROCEDURE setctx ( policy_group varchar2 ) IS
BEGIN
REM Do some checking to determine the current application.
REM You can check the proxy if using the proxy authentication feature.
REM Then set the context to indicate the current application.
.
.
.
DBMS_SESSION.SET_CONTEXT('APPSCTX','ACTIVE_APPS', policy_group);
END;
END;
Define the driving context for the table APPS.BENEFIT.
BEGIN
DBMS_RLS.ADD_POLICY_CONTEXT('apps','benefit','APPSCTX','ACTIVE_APPS');
END;
Create a security function to return a predicate to divide the data by company.
CREATE OR REPLACE FUNCTION by_company (sch varchar2, tab varchar2)
RETURN VARCHAR2 AS
BEGIN
RETURN 'COMPANY = SYS_CONTEXT(''ID'',''MY_COMPANY'')';
END;
Since policies in SYS_DEFAULT are always executed (except for SYS, or users with the EXEMPT ACCESS POLICY system privilege), this security policy (named SECURITY_BY_COMPANY), will always be enforced regardless of the application running. This achieves the universal security requirement on the table: namely, that each company should see its own data, regardless of the application running. The function APPS.APPS_SECURITY_INIT.BY_COMPANY returns the predicate to make sure that you can only see your company's data.
BEGIN
DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','SYS_DEFAULT',
'security_by_company',
'apps','by_company');
END;
First, create the HR group:
CREATE OR REPLACE FUNCTION hr.security_policy
RETURN VARCHAR2
AS
BEGIN
RETURN 'SYS_CONTEXT(''ID'',''TITLE'') = ''MANAGER'' ';
END;
The following creates the policy group and adds a policy named HR_SECURITY to the HR policy group. The function HR.SECURITY_POLICY returns the predicate to enforce HR's security on the table APPS.BENEFIT:
BEGIN
DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','HR');
DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','HR',
'hr_security','hr','security_policy');
END;
Create the FINANCE policy:
CREATE OR REPLACE FUNCTION finance.security_policy
RETURN VARCHAR2
AS
BEGIN
RETURN ('SYS_CONTEXT(''ID'',''DEPT'') = ''FINANCE'' ');
END;
Create a policy group named FINANCE and add the FINANCE policy to the FINANCE group:
BEGIN
DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','FINANCE');
DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','FINANCE',
'finance_security','finance', 'security_policy');
END;
As a result, when the database is accessed, the application initializes the driving context after authentication. For example, with the HR application:
execute apps.security_init.setctx('HR');
The package implementing the driving context must correctly validate the application which is being used. Although the database always ensures that the package implementing the driving context sets context attributes (by checking the call stack), this cannot protect against inadequate validation within the package.
For example, in applications where database users or enterprise users are known to the database, the user needs EXECUTE privilege on the package which sets the driving context. Consider a user who knows that:
BENEFITS application allows more liberal access than its HR application, andsetctx procedure (which sets the correct policy group within the driving context) does not perform any validation to determine which application is actually connecting. That is, the procedure does not check the IP address of the incoming connection (for a three-tier system), or the proxy_user attribute of the user session.In this situation, the user could pass to the driving context package an argument which sets the context to the more liberal BENEFITS policy group even though this user will access the HR application. In this way the user can bypass the more restrictive security policy because the package inadequately validates the application.
By contrast, if you implement proxy authentication with VPD, then you can determine the identity of the middle tier (and the application) which is actually connecting to the database on a user's behalf. In this way, the correct policy will be applied for each application to mediate data access. For example, a developer using the proxy authentication feature could determine that the application (the middle tier) connecting to the database is HRAPPSERVER. The package which implements the driving context can thus verify that the proxy_user in the user session is HRAPPSERVER before setting the driving context to use the HR policy group, or can disallow access if proxy_user is not HRAPPSERVER.
In this case, when the following query is executed
SELECT * FROM APPS.BENEFIT;
Oracle picks up policies from the default policy group (SYS_DEFAULT) and active namespace HR. The query is internally rewritten as follows:
SELECT * FROM APPS.BENEFIT WHERE COMPANY = SYS_CONTEXT('ID','MY_COMPANY') and
SYS_CONTEXT('ID','TITLE') = 'MANAGER';
The DBMS_RLS package enables you to administer security policies. This package's procedures allow you to specify the table, view, or synonym to which you are adding a policy and various data pertinent to that policy. These data include the names of the policy, the policy group, the function implementing the policy, and the type of statement the policy controls (SELECT, INSERT, UPDATE, DELETE, CREATE INDEX, or ALTER INDEX). Table 14-2 lists these procedures.
| See Also:
PL/SQL Packages and Types Reference for information about using the DBMS_RLS package and all of its procedures and parameters. |
Alternatively, you can use Oracle Policy Manager to administer security policies.
The execution of policy functions can consume a significant amount of system resources. If you can minimize the number of times policy functions must execute, then you can optimize your database server's performance. To avoid unnecessary policy function execution, you can choose from five different policy types, which enable you to precisely specify how and how often a policy predicate should change. You can enable these different types of policies, which are listed in Table 14-3, by setting the policy_type parameter of the DBMS_RLS.ADD POLICY procedure.
| Policy Types | When Policy Function Executes... | Usage Example | Shared Across Multiple Objects? |
|---|---|---|---|
|
|
Once, then the predicate is cached in the SGA.Foot 1 |
View replacement |
No |
|
|
Same as |
Hosting environments, such as data warehouses where the same predicate must be applied to multiple database objects. |
Yes |
|
|
3-tier, session pooling applications where policies enforce two or more predicates for different users or groups. |
No |
|
|
|
First time the object is reference in a database session. Predicates are cached in the session's private memory UGA so policy functions can be shared among objects. |
Same as |
Yes |
|
|
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 and context sensitive policies enable you to optimize server performance because they do not execute the policy function each time protected database objects are accessed. However, Oracle recommends that before you enable policies as either static or context sensitive, you first test them as DYNAMIC policy types, which execute every time. Testing policy functions as DYNAMIC policies first enables you to observe how the policy function affects each query because nothing is cached. This ensures that the functions work properly before you enable them as static or context sensitive policy types to optimize performance.
Dynamic policies are the system default. If you do not specify a policy type with the DBMS_RLS.ADD_POLICY procedure, then by default your policy will be dynamic. You can specifically configure a policy to be dynamic by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to DYNAMIC. Refer to Example 14-1 for the syntax.
DBMS_RLS.ADD_POLICY ( . . . policy_type => dbms_rls.POLICY_TYPE);
|
Note: The |
| See Also:
The following topics for a more detailed discussion of static and context sensitive policies: |
In previous releases, policies were dynamic, which means the database executes the policy function for each query or DML statement. In addition to dynamic policies, the current release of the Oracle database provides static and context sensitive policies. These policy types provide a means to improve server performance because they do not always reexecute policy functions for each DML statement and can be shared across multiple database objects.
Static policy predicates are cached in SGA, so policy functions do not reexecute for each query, resulting in faster performance. When you specify a static policy, the same predicate is always enforced for all users in the instance. However, each execution of the same cursor could produce a different row set even for the same predicate because the predicate may filter the data differently based on attributes such as SYS_CONTEXT or SYSDATE.
For example, suppose you enable a policy as either a STATIC or SHARED_STATIC policy type, which appends the following predicate to all queries made against policy protected database objects:
where dept=SYS_CONTEXT ('HR_APP','deptno')
Although the predicate does not change for each query, it applies to the query based on session attributes of the SYS_CONTEXT. In the case of the preceding example, the predicate would return only those rows where the department number matches the deptno attribute of the SYS_CONTEXT, which would be the department number of the user who is querying the policy protected database object.
You can enable static policies by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to either STATIC or SHARED_STATIC, depending on whether you want the policy to be shared across multiple objects. (See Example 14-1 for the syntax.)
Static policies are ideal for environments where every query requires the same predicate and fast performance is essential, such as hosting environments. For these situations when the policy function appends the same predicate to every query, reexecuting the policy function each time adds unnecessary overhead to the system. For example, consider a data warehouse that contains market research data for customer organizations who are competitors to one another. The warehouse must enforce the policy that each organization can see only their own market research, which is expressed by the predicate where subscriber_id=SYS_CONTEXT('customer', 'cust_num'). Using SYS_CONTEXT for the application context enables the database to dynamically change which organization's rows are returned. There is no need to reexecute the function, so the predicate can be cached in the SGA, thus conserving system resources and improving performance.
In contrast to static policies, context sensitive policies do not always cache the predicate. With context sensitive policies, the server assumes that the predicate will change after statement parse time. But if there is no change in local application context, the server does not reexecute the policy function within the user session. If there has been a change in context, then the server reexecutes the policy function to ensure it captures any changes to the predicate since the initial parsing. These policies are useful where different predicates should apply depending on which user is executing the query. For example, consider the case where managers should always have the predicate where group=managers and employees should always have the predicate where empno=emp_id.
Shared context sensitive policies operate in the same way as regular context sensitive policies, except they can be shared across multiple database objects. For this policy type, all objects can share the policy function from the UGA, where the predicate is cached until the local session context changes.
You can enable context sensitive policies by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to either CONTEXT_SENSITIVE or SHARED_CONTEXT_SENSITIVE. (See Example 14-1 for the syntax.)
This type of policy is useful when a predicate need not change for a user's session, but the policy must enforce two or more different predicates for different users or groups. For example, consider a SALES_HISTORY table with a single policy of "analysts see only their own products" and "regional employees see only their own region." In this case, the server must reexecute the policy function each time the type of user changes. The performance gain is realized when a user can log in and issue several DML statements against the protected object without causing the server to reexecute the policy function.
|
Note: For session pooling where multiple clients share a database session, the middle tier must reset the context during client switches. |
Column-level VPD, which can be applied to a table or a view, enables you to enforce security when a security-relevant column is referenced in a query, resulting in row-level security. Column-level VPD cannot be applied to a synonym.
It can be configured to produce two distinct behaviors as follows:
Restricts the number of rows returned by queries that reference columns containing sensitive information. Set this behavior by specifying the security-relevant column names with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure.
Returns all rows for queries, but it returns NULL values for the columns that contain sensitive information. Set this behavior by setting the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure to dbms_rls.ALL_ROWS.
The following example shows a VPD policy in which sales department users should not see the salaries of people outside their own department (department number 30). The relevant columns for such a policy are SAL and COMM. First, the VPD policy function is created and then added by using the DBMS_RLS PL/SQL package as shown in Example 14-2:
*/Create a policy function which does not expose salaries of employees outside the sales department (department 30)/* CREATE OR REPLACE FUNCTION pf1 (oowner IN VARCHAR2, ojname IN VARCHAR2) RETURN VARCHAR2 AS con VARCHAR2 (200); BEGIN con := 'deptno=30'; RETURN (con); END pf1;
Then the policy is added with the DBMS_RLS package as follows:
BEGIN
DBMS_RLS.ADD_POLICY (object_schema=>'scott', object_name=>'emp',
policy_name=>'sp', function_schema=>'pol_admin',
policy_function=>'pf1',
sec_relevant_cols=>'sal,comm');
END;
The two different behaviors of column-level VPD are discussed in the following sections using Example 14-2 as a starting point for discussion.
The default behavior for column-level VPD is to restrict the number of rows returned for a query that references columns containing sensitive information. These security-relevant columns are specified with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure.
For an example of column-level VPD default behavior, consider sales department users with SELECT privilege on the emp table, which is protected with the column-level VPD policy created in Example 14-2. When these users perform the following query:
SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d WHERE d.deptno = e.deptno;
the database returns a subset of rows as follows:
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
Only the rows display in which the user should have access to all columns.
In contrast to the default behavior of column-level VPD, the column masking behavior displays all rows, but returns sensitive column values as NULL. To set this behavior set the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure to dbms_rls.ALL_ROWS in addition to setting the default behavior parameter.
For an example of column-level VPD column masking behavior, consider that the same VPD policy (created Example 14-2) applies, but it has been added with the sec_relevant_cols_opt parameter specified also. See Example 14-3.
*/add the ALL_ROWS policy/*
BEGIN
DBMS_RLS.ADD_POLICY(object_schema=>'scott', object_name=>'emp',
policy_name=>'sp', function_schema=>'pol_admin',
policy_function=>'pf1',
sec_relevant_cols=>'sal,comm',
sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
END;
Now a sales department user with SELECT privilege on the emp table, performs 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 certain values are masked because of the VPD policy:
ENAME DNAME JOB SAL COMM -------------- -------------- ------------ ------------ ------------- SMITH RESEARCH CLERK ALLEN SALES SALESMAN 1600 300 WARD SALES SALESMAN 1250 500 JONES RESEARCH MANAGER MARTIN SALES SALESMAN 1250 1400 BLAKE SALES MANAGER 2850 CLARK ACCOUNTING MANAGER SCOTT RESEARCH ANALYST KING ACCOUNTING PRESIDENT TURNER SALES SALESMAN 1500 0 ADAMS RESEARCH CLERK JAMES SALES CLERK 950 FORD RESEARCH ANALYST MILLER ACCOUNTING CLERK
With column masking behavior, sales users see all rows returned by a query, but the SAL and COMM columns become NULL for rows containing information about employees outside the sales department.
Column masking behavior is subject to the following restrictions:
SELECT statementsNULL values, then you should use the default behavior of column-level VPD, which is specified with the sec_relevant_cols parameter.UPDATE AS SELECT with this option, then only the values in the columns you are allowed to see will be updated.select * from employees where salary = 10
This query may not return rows if the salary column returns a NULL value because the column masking option has been set.
| See Also:
The chapter on the |
VPD policies can be enforced for SELECT, INSERT, UPDATE, INDEX, and DELETE statements. Specify any combination of these statement types with the DBMS_RLS.ADD_POLICY procedure statement_types parameter as follows:
DBMS_RLS.ADD_POLICY ( . . .
statement_types=>'SELECT,INDEX');
A user who has privileges to maintain an index can see all the row data even if the user does not have full table access under a regular query, such as SELECT. For example, a user can create a function-based index which contains a user defined function with column values as its arguments. During index creation, the server passes column values of every row into the user function, making the row data available to the user who creates the index. Administrators can enforce VPD policies on index maintenance operations by specifying INDEX with the statement_types parameter as shown in the previous section.
V$VPD_POLICY allows one to perform a dynamic view in order to check what policies are being applied to a SQL statement. When debugging, in your attempt to find which policy corresponds to a particular SQL statement, you should use the following table.
| See Also:
Oracle Database Reference for more information about the |
Two classes of users are exempt from VPD policies: the SYS user is exempt by default, and any other user can be exempt if granted the EXEMPT ACCESS POLICY system privilege. These two cases are discussed in the following sections.
The database user SYS is always exempt from VPD or Oracle Label Security policy enforcement, regardless of the export mode, application, or utility that is used to extract data from the database. However, SYSDBA actions can be audited.
The system privilege EXEMPT ACCESS POLICY allows a user to be exempted from all fine-grained access control policies on any SELECT or DML operation (INSERT, UPDATE, and DELETE). This provides ease of use for such administrative activities as installation, and import and export of the database through a non-SYS schema.
Also, regardless of the utility or application that is being used, if a user is granted the EXEMPT ACCESS POLICY privilege, then the user is exempt from VPD and Oracle Label Security policy enforcement. That is, the user will not have any VPD or Oracle Label Security policies applied to their data access.
Since EXEMPT ACCESS POLICY negates the effect of fine-grained access control, this privilege should only be granted to users who have legitimate reasons for bypassing fine-grained access control enforcement. This privilege should not be granted WITH ADMIN OPTION, so that users cannot pass on the EXEMPT ACCESS POLICY privilege to other users, and thus propagate the ability to bypass fine-grained access control.
Starting from Oracle9i, queries against objects enabled with fine-grained access control always execute the policy function to make sure 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 cause the policy function to execute, ensuring the policy is consulted again for the query.
Automatic reparse does not occur under the following conditions:
STATIC_POLICY=TRUE when adding the policy to indicate that the policy function always returns the same predicate._dynamic_rls_policies parameter to FALSE in the initialization parameters files. Typically, this parameter is set to FALSE for users whose security policies do not return different predicates within a database session to reduce the execution overhead.For deployment environments where the latest application context value is always the desired value, the _app_ctx_vers parameter can be set to FALSE in the initialization parameters file to reduce the overhead of application context scoping. By default, it is set to TRUE and changes of value within a SQL statement are not visible. This default may change in the future, thus developers should be careful not to allow changes of application context values within a SQL statement using a user defined function. In general, you should not depend on the order of SQL statement execution, which can yield inconsistent results depending on query plans.
By default, operations on the database use the most recent committed data available. The flashback query feature enables you to query the database as it was at some time in the past. To write an application that uses flashback query, you can use the AS 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 past time.
Flashback queries return data as it stood at the time specified in the query. However, if you use flashback query against a database object that is protected with VPD policies, then the current policies are applied to the old data. Applying the current VPD policies to flashback query data is more secure because it reflects the most current business policy.
See Also:
|
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|