About Virtual Private Database, Fine-Grained Access Control, and Application Context. Virtual Private Database (VPD) combines server-enforced fine-grained access control with a secure storage of application context values in the Oracle database server. VPD enables you to build applications that enforce row-level security policies at the object level. 15 Implementing Application Context and Fine-Grained Access Control. Application context can be used with fine-grained access control as part of Virtual Private Database (VPD) or by itself. Used alone, it enables application developers to define, set, and access application attributes by serving as a data cache. Such usage removes the repeated overhead of querying the database each time access. The script content on this page is for navigation purposes only and does not alter the content in any way. Application context can be used with fine-grained access control as part of Virtual Private Database (VPD) or by itself. Used alone, it enables application developers to define, set, and access application attributes by serving as a data cache. Such usage removes the repeated overhead of querying the database each time access to application attributes is needed. This chapter discusses how to implement application context and fine-grained access control using the following sections: About Using Application ContextApplication context can be useful for the following purposes:
Three types of application context are defined, differentiated by where the context data is stored and by how updates can be done:
Table 15-1 summarizes the different types of application contexts. Table 15-1 Types of Application Contexts
See Also:
The following sections explain secure and client session-based application context: Using Secure Session-Based Application ContextTo use secure session-based application context, you must perform the following tasks: Task 1: Create a PL/SQL Package that Sets the Secure Context for Your ApplicationBegin by creating a PL/SQL package with functions that set the secure context for your application. This section presents the syntax and behavior of the Note: A login trigger can be used because the user context (information such asEMPNO , GROUP , MANAGER ) should be set before the user accesses any data.
SYS_CONTEXT SyntaxThe syntax for this function is: This function returns the value of See Also:
SYS_CONTEXT ExampleThe following example creates the package See Also: PL/SQL Packages and Types Reference for information about theDBMS_SESSION.SET_CONTEXT procedure.
Using Dynamic SQL with SYS_CONTEXTNote: This feature is applicable whenCOMPATIBLE is set to either 8.0 or 8.1.
During a session in which you expect a change in policy between executions of a given query, the 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 run 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: The policy Using SYS_CONTEXT in a Parallel QueryIf Consider a user-defined function within a SQL statement, which sets the user ID to 5: Now consider the statement: 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). Using SYS_CONTEXT with Database LinksSession-based local application context can be accessed by SQL statements within a user session by using the If remote PL/SQL procedure calls are executed over a database link, then any Task 2: Create a Unique Secure Context and Associate It with the PL/SQL PackageTo perform this task, use the For example: Here, After you have created the context, you can set or reset the context attributes by using the You can only set the context attributes inside the trusted procedure you named in the Alternatively, you can use the Oracle Policy Manager graphical user interface (GUI) 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. Task 3: Set the Secure Context Before the User Retrieves DataAlways use an event trigger on login to pull session information into the context. This sets the security-limiting attributes of the user 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. Task 4: Use the Secure Context in a VPD Policy FunctionNow 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. Examples: Secure Application Context Within a Fine-Grained Access Control FunctionThis section provides the following examples that use secure session-based application context within a fine-grained access control function. Example 1: Implementing the PolicyThis example uses secure application context to implement a policy, in which customers can see only their own orders, and builds the application using the following steps: The procedure in this example assumes a one-to-one relationship between users and customers. It finds the user customer number ( Note that you could use a login trigger to set the initial context. Step 1: Create a PL/SQL Package To Set the Secure Context for the ApplicationCreate the package as follows: Note: You may need to set up the following data structures for the following examples to work:Note: This example does not treat error handling.You can access predefined attributes, such as session user, by using For more information, see Table 14-1, 'Key to Predefined Attributes in USERENV Namespace' and Oracle Database SQL Reference. Step 2: Create a Secure Application ContextCreate an application context by entering: Alternatively, you can use Oracle Policy Manager to create an application context. Step 3: Access the Secure Application Context Inside the PackageAccess the secure 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:The package body appends a dynamic predicate to Step 4: Create the New Security PolicyNote: You may need to set up the following data structures for certain examples to work:Create the policy as follows: This statement adds a policy named Now, any to the following: Note the following with regard to this example:
Compare and contrast this example, which uses an application context within the dynamically generated predicate, with 'How Fine-Grained Access Control Works', which uses a subquery in the predicate. Example 2: Controlling User Access with an ApplicationThis example uses secure application context to control user access with a Human Resources application. Each task that you need to perform is described more fully in the sections that follow: In this example, assume that the application context for the Human Resources application is assigned to the Step 1: Create a PL/SQL Package to Set the Secure ContextNote: You may need to set up the following data structures for certain examples to work:Create a PL/SQL package with a number of functions that set the secure context for the application. Step 2: Create the Secure Context and Associate It with the PackageFor example: Step 3: Create the Initialization Script for the ApplicationSuppose that the execute privilege on the The Note: You may need to set up the following data structures for certain examples to work:Example 3: Event Triggers, Secure Application Context, Fine-Grained Access Control, and Encapsulation of PrivilegesThis example illustrates use of the following security features in Oracle Database:
In this example, we associate a security policy with the table called
Note: You may need to set up the following data structures for certain examples to work:The security policy associated with this table has two elements:
The following code implements this example as described: Initializing Secure Application Context ExternallyThis feature lets you specify a special type of namespace that accepts initialization of attribute values from external resources and stores them in the local user session. Allowing secure application context to be initialized externally enhances performance and enables the automatic propagation of attributes from one session to another. Connected user database links are supported only by application contexts initialized from OCI-based external sources. This section contains these topics: Obtaining Default Values from UsersSometimes 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 login triggers to reset the context when users log in. Obtaining Values from Other External ResourcesIn addition to using the designated trusted package, externally initialized secure application contexts can also accept initialization of attributes and values through external resources. Examples include an OCI interface, a job queue process, or a database link. Externally initialized secure application context 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 secure 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. See Also: Initializing Secure Application Context GloballyThis feature uses a centralized location to store the secure application context of the user, enabling applications to set up a user context during initialization based upon user 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 title, organization, or physical location. This section contains these topics: Using Secure Application Context with LDAPSecure session-based application context initialized globally uses the Lightweight Directory Access Protocol (LDAP), 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 uses the directory service, typically Oracle Internet Directory, for authentication and authorization of enterprise users. Note:
The An internal C function is required to retrieve the Note: In this example, HR is the namespace, Title and Project are the attributes, and Manager and Promotion are the values.Figure 15-1 Location of Application Context in LDAP Directory Information Tree Description of 'Figure 15-1 Location of Application Context in LDAP Directory Information Tree' How Globally Initialized Secure Application Context WorksThe administrator configures Enterprise User Security, a feature of Oracle Advanced Security. Then, the administrator sets up the secure application context values for the user 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 global user roles and application context are retrieved from the directory. When the user logs on to the database, the global roles and initial application context are already set up. See Also: Oracle Database Advanced Security Administrator's Guide for a detailed discussion of Enterprise User Security and how to configure this featureExample: Initializing Secure Application Context GloballyThe 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
Using Client Session-Based Application ContextThe Setting a Value in CLIENTCONTEXTFor OCI, use a command of the following form:
For JDBC, use a command of the following form:
Clearing a Particular Setting in CLIENTCONTEXTFor OCI, set the value to NULL or to an empty string by using one of the following command forms:
For JDBC, use the following command:
Clearing all Settings in CLIENTCONTEXTFor OCI, use a command of the following form: For JDBC, use a command of the following form: How to Use Global Application ContextGlobal application context stores context information in the System Global Area (SGA) so that it can be used for applications that 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
Using the DBMS_SESSION Interface to Manage Application Context in Client SessionsThe
The middle-tier application server can use See Also:
Examples: Global Application ContextThis section provides two examples that use global application context. Example 1: Global Application Context ProcessThe following steps outline the global application context process:
Note: After a client identifier in a session is cleared, it takes on aNULL 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.
Example 2: Global Application Context for Lightweight UsersThe following steps outline the global application context process for a lightweight user application:
Note that even if another database user ( This approach enables different users to share the same context. Note: Users must be aware of the security implication of different settings of the global context.NULL in the user name 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: The DBA can see which sessions have the client identifier set by querying the When users want to see the amount of global context area (in bytes) being used, they can use See Also: For more information about using theCLIENT_IDENTIFIER predefined attribute of the USERENV application context:
How Fine-Grained Access Control WorksFine-grained access control is based on dynamically modified statements. Suppose you want to associate the
Upon execution, the function employs the user name returned by See Also: For more information on using fine-grained access control:How to Establish Policy GroupsA policy group is a set of security policies that 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 is also known as policy context) 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: The Default Policy Group: SYS_DEFAULTIn 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 By default, all policies belong to the If, to the Note: Policy groups with identical names are supported. When you select a particular policy group, its associated schema and object name are displayed in the property sheet on the right-hand side of the screen.New Policy GroupsWhen adding the policy to a table, view, or synonym, you can use the If the driving context is not defined, then all policies are executed. Likewise, if the driving context is 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 How to Implement Policy GroupsTo create policy groups, the administrator must do two things:
The following example shows how to perform these tasks. Note: You need to set up the following data structures for the examples in this section to work:Step 1: Set Up a Driving ContextBegin by creating a namespace for the driving context. For example: Create the package that administers the driving context. For example: Define the driving context for the table Step 2: Add a Policy to the Default Policy Group.Create a security function to return a predicate to divide the data by company. Because policies in Step 3: Add a Policy to the HR Policy GroupFirst, create the The following creates the policy group and adds a policy named Step 4: Add a Policy to the FINANCE Policy GroupCreate the Create a policy group named As a result, when the database is accessed, the application initializes the driving context after authentication. For example, with the Validating the Application Used to Connect to the DatabaseThe package implementing the driving context must correctly validate the application that is being used to connect to the database. Although the database always checks the call stack to ensure that the package implementing the driving context sets context attributes, inadequate validation can still occur within the package. For example, in applications where database users or enterprise users are known to the database, the user needs the
Such a user could pass to the driving context package an argument setting the context to the more liberal By contrast, if you implement proxy authentication with VPD, then you can determine the identity of the middle tier (and the application) that is actually connecting to the database on behalf of a user. 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 In this case, when the following query is executed Oracle Database picks up policies from the default policy group ( How to Add a Policy to a Table, View, or SynonymThe Table 15-2 DBMS_RLS Procedures
Alternatively, you can use Oracle Policy Manager to administer security policies. See Also: PL/SQL Packages and Types Reference for information about using the DBMS_RLS package and all of its procedures and parametersDBMS_RLS.ADD_POLICY Procedure Policy TypesThe execution of policy functions can consume a significant amount of system resources. If you can minimize the number of times that policy functions must run, then you can optimize your database server 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 15-3, by setting the Table 15-3 DBMS_RLS.ADD_POLICY Policy Types At a Glance
Footnote 1 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 Static and context sensitive policies enable you to optimize server performance, because they do not run 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 policies are the system default. If you do not specify a policy type with the Example 15-1 Syntax for Enabling Policy Types with DBMS_RLS.ADD_POLICY Note: TheDBMS_RLS.ADD POLICY policy_type parameter is intended to replace the static_policy parameter, which may be deprecated in future releases.
See Also: The following topics for a more detailed discussion of static and context-sensitive policies:Optimizing Performance by Enabling Static and Context Sensitive PoliciesIn previous releases, policies were dynamic, which means that the database runs the policy function for each query or DML statement. In addition to dynamic policies, the current release of Oracle Database provides static and context-sensitive policies. These policy types provide a means to improve server performance, because they do not always rerun policy functions for each DML statement and can be shared across multiple database objects. Note: When using shared static and shared context-sensitive policies, ensure that the policy predicate does not contain attributes that are specific to a particular database object, such as a column name.About Static PoliciesStatic policy predicates are cached in SGA, so policy functions do not rerun 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 For example, suppose you enable a policy as either a Although the predicate does not change for each query, it applies to the query based on session attributes of the You can enable static policies by setting the When to Use Static PoliciesStatic 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, rerunning 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 that are competitors of each other. The warehouse must enforce the policy that each organization can see only their own market research, which is expressed by the predicate About Context-Sensitive PoliciesIn 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 rerun the policy function within the user session. If there has been a change in context, then the server reruns the policy function to ensure that 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 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 When to Use Context-Sensitive PoliciesThis type of policy is useful when a predicate need not change for a user session, but the policy must enforce two or more different predicates for different users or groups. For example, consider a Note: For session pooling where multiple clients share a database session, the middle tier must reset the context during client switches.Adding Policies for Column-Level VPDColumn-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: 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 Example 15-2 Creating and Adding a Column-Level VPD Policy Then the policy is added with the The different behaviors of column-level VPD are discussed in the following sections using Example 15-2 as a starting point for discussion. Default BehaviorThe 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 For an example of column-level VPD default behavior, consider sales department users with the database returns a subset of rows as follows: Only those rows are displayed in which the user should have access to all columns. Column-masking BehaviorIn contrast to the default behavior of column-level VPD, column-masking displays all rows, but returns sensitive column values as Example 15-3 shows column-level VPD column-masking. It uses the same VPD policy as Example 15-2 but with Example 15-3 Adding a Column-level VPD Policy with Column-masking Behavior Assume that a sales department user with The database returns all rows specified in the query, but with certain values masked because of the VPD policy: Note that column-masking has returned all rows requested by the sales user query, but has made the The following considerations apply to column-masking:
See Also: The chapter on theDBMS_RLS package in PL/SQL Packages and Types Reference for a discussion of parameters and usage examples for the DBMS_RLS.ADD_POLICY procedure
Enforcing VPD Policies on Specific SQL Statement TypesVPD policies can be enforced for Enforcing Policies on Index MaintenanceA 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 How to Check for Policies Applied to a SQL Statement
Table 15-4 V$VPD_POLICY
See Also: Oracle Database Reference for more information about theV$VPD_POLICY view
Users Exempt from VPD PoliciesTwo classes of users are exempt from VPD policies: the SYS User Exempted from VPD PoliciesThe database user EXEMPT ACCESS POLICY System PrivilegeThe system privilege Also, regardless of the utility or application that is being used, if a user is granted the Because Automatic ReparseNote: This feature is applicable whenCOMPATIBLE is set to 9.0.1.
Starting from Oracle9i, queries against objects enabled with fine-grained access control always run the policy function to make sure 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 cause the policy function to run, ensuring that the policy is consulted again for the query. Automatic reparse does not occur under the following conditions:
For deployment environments where the latest application context value is always the desired value, the VPD Policies and Flashback QueryBy 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 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:
The script content on this page is for navigation purposes only and does not alter the content in any way. Oracle Database provides the necessary tools to build secure applications. One such tool is Virtual Private Database (VPD), which is the combination of the following:
VPD combines these two features, enabling you to enforce security policies to control access at the row level. This control is based on application or session attributes, which can be made available during execution. The following topics introduce these features and explain how and why you would use them: About Virtual Private Database, Fine-Grained Access Control, and Application ContextVirtual Private Database (VPD) combines server-enforced fine-grained access control with a secure storage of application context values in the Oracle database server. VPD enables you to build applications that enforce row-level security policies at the object level. Policy execution dynamically appends predicates ( The application context feature enables application developers to define, set, and access variable-length application attributes and their values. These attributes can then be used as predicate values for fine-grained access control policies. Two types of application contexts exist:
Although application context is an integral part of VPD, it can be implemented alone, without fine-grained access control. When implemented alone, application context can be used to access session information, such as the client identifier, to preserve user identity across multitiered environments. The remainder of this chapter discusses how VPD works and introduces its main components, fine-grained access control and application context. See Also:
Introduction to VPDVirtual private database (VPD) enables you to enforce security, to a fine level of granularity, directly on tables, views, or synonyms. Because security policies are attached directly to tables, views, or synonyms and automatically applied whenever a user accesses data, there is no way to bypass security. When a user directly or indirectly accesses a table, view, or synonym protected with a VPD policy, the server dynamically modifies the SQL statement of the user. The modification creates a Note: Users need full table access to create table indexes. Consequently, users with privileges to maintain an index can see all the row data even if they do not have full table access under a regular query. To prevent this, apply VPD policies toINDEX statements.
Functions that return predicates can also include calls to other functions. Within your PL/SQL package, you can embed C or Java calls to access operating system information or to return Application context enables you to securely access the attributes on which you base your security policies. For example, users with the position attribute of Consider an HR clerk who is only allowed to see employee records in the Retail Division who initiates the following query: The function implementing the security policy returns the predicate Column-Level VPDColumn-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the For example, users outside of the HR department typically are allowed to view only their own Social Security numbers. When a sales clerk initiates the following query: The function implementing the security policy returns the predicate See Also: 'Adding Policies for Column-Level VPD' for information about how to add column-level VPD policiesColumn-Level VPD with Column-masking BehaviorIf a query references a sensitive column, then the default behavior of column-level VPD restricts the number of rows returned. With column-masking behavior, which can be enabled by using the To illustrate this, consider the results of the sales clerk query, described in the previous example. If column-masking behavior is used, then instead of seeing only the row containing the details and Social Security number of the sales clerk, the clerk would see all rows from See Also: 'Column-masking Behavior' for information about how to add column-level VPD policies with column-masking behavior.VPD Security Policies and ApplicationsThe security policy is applied within the database itself, rather than within an application. This means that the use of a different application will not bypass the security policy. Security can thus be built once, in the database, instead of being implemented again in multiple applications. VPD therefore provides far stronger security than application-based security, at a lower cost of ownership. It may be desirable to enforce different security policies depending on which application is accessing data. Consider a situation in which two applications, Order Entry and Inventory, both access the In this case, you must partition the use of fine-grained access by application. Otherwise, both policies would be automatically Introduction to Fine-Grained Access ControlFine-grained access control enables you to build applications that enforce security policies at a low level of granularity. These policies are also referred to as VPD policies. You can use it, for example, to restrict customers accessing an Oracle database server to see only their own accounts. A physician could be limited to seeing only the records of her own patients, or a manager to seeing only the records of employees who work for him. When you use fine-grained access control, you create security policy functions attached to the table, view, or synonym on which you have based your application. Then, when a user enters a Features of Fine-Grained Access ControlFine-grained access control provides the following capabilities: Security Policies Based on Tables, Views, and SynonymsAttaching security policies to tables, views, or synonyms rather than to applications provides greater security, simplicity, and flexibility. SecurityAssociating a policy with a table, view, or synonym overcomes 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 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. SimplicityAdding the security policy to the table, view, or synonym means that you make the addition only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications. FlexibilityYou can have one security policy for Note: Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.Multiple Policies for Each Table, View, or SynonymYou can establish several policies for the same table, view, or synonym. Suppose, for example, you have a base application for Order Entry, and each division of your company has its own special rules for data access. You can add a division-specific policy function to a table without having to rewrite the policy function of the base application. Note that all policies applied to a table are enforced with Grouping of Security PoliciesBecause multiple applications with multiple security policies, can share the same table, view, or synonym, it is important to identify those policies that should be in effect when the table, view, or synonym is accessed. For example, in a hosting environment, Company A can host the To do this, you can organize security policies into groups. By referring to the application context, the Oracle Database server determines which group of policies should be in effect at run time. The server enforces all the policies that belong to that policy group. High PerformanceWith fine-grained access control, each policy function for a given query is evaluated only once, at statement parse time. Also, the entire dynamically modified query is optimized and the parsed statement can be shared and reused. This means that rewritten queries can take advantage of the high performance features of Oracle Database, such as dictionary caching and shared cursors. Default Security PoliciesWhile partitioning security policies by application is desirable, it is also useful to have security policies that are always in effect. In the previous example, a hosted application can always enforce data separation by See Also: The following topics for information about how to implement fine-grained access control:About Creating a VPD Policy with Oracle Policy ManagerTo implement VPD, developers can use the Alternatively, developers can use the Oracle Policy Manager graphical user interface (GUI), accessed from Oracle Enterprise Manager, to apply security policies to schema objects, such as tables and views, and to create application contexts. Oracle Policy Manager provides an easy-to-use interface to manage security policies and application contexts, and therefore makes VPD easier to develop. To create VPD policies, users must provide the schema name, table (or view or synonym) name, policy name, the function name that generates the predicate, and the statement types to which the policy applies (that is, Oracle Policy Manager is also the administration tool for Oracle Label Security. Oracle Label Security provides a functional, out-of-the-box VPD policy that enhances your ability to implement row-level security. It supplies an infrastructure, which is a label-based access control framework, whereby you can specify labels for users and data. It also enables you to create one or more custom security policies to be used for label access decisions. You can implement these policies without any knowledge of a programming language. There is no need to write additional code, but in a single step you can apply a security policy to a given table. In this way, Oracle Label Security provides a straightforward, efficient way to implement row-level security policies using data labeling technology. Finally, the structure of Oracle Label Security labels provides a degree of granularity and flexibility that cannot easily be derived from the application data alone. Oracle Label Security is thus a generic solution that can be used in many different circumstances. See Also: Oracle Label Security Administrator's Guide for information about using Oracle Policy ManagerIntroduction to Application ContextApplication context enables you to define, set, and access variable-length application attributes and values in a secure data cache available in User Global Area (UGA) and System Global Area (SGA). Most applications contain the kind of information that can be used for access control. For example, in an order entry application, the As another example, consider a user running a human resources application. Part of the application initialization process is to determine the kind of responsibility that the user can assume based on user identity. This ID becomes part of the human resource application context. It affects what data the user can access throughout the session. You configure application context by using the SQL function The following subsections describe application context and how to use it:
Features of Application ContextApplication context provides the following important security features: Specifying Attributes for Each ApplicationEach application can have its own context with its own attributes. Suppose, for example, you have three applications: General Ledger, Order Entry, and Human Resources. You can specify different attributes for each application:
In each case, you can adapt the application context to your precise security needs. Providing Access to Predefined Attributes Through the USERENV NamespaceOracle database server provides a built-in application context namespace ( Predefined attributes are useful for access control. For example, a three-tier application creating lightweight user sessions through OCI or thick JDBC can access the You can use the See Also:
You can access predefined attributes through the Use the following syntax to obtain information about the current session. Note: TheUSERENV application context namespace replaces the USERENV function provided in earlier database releases.
See Also:
Table 14-1 Key to Predefined Attributes in USERENV Namespace
Table 14-2 lists the attributes of namespace Table 14-2 Deprecated Attributes of Namespace USERENV
Externalized Application ContextsMany applications store attributes used for fine-grained access control within a database metadata table. For example, an Note: Enterprise User Security is a feature of Oracle Advanced Security.See Also:
Ways to Use Application Context with Fine-Grained Access ControlTo simplify security policy implementation, you can use application context within a fine-grained access control function. Application context can be used in the following ways with fine-grained access control: Secure Data CachingAccessing an application context inside your fine-grained access control policy function is like writing down an often-used phone number and posting it next to your phone, where you can find it easily rather than looking it up every time you need it. For example, suppose you base access to the Application context is especially helpful if your security policy is based on multiple security attributes. For example, if a policy function bases a predicate on four attributes (such as employee number, cost center, position, spending limit), then multiple subqueries must execute to retrieve this information. Instead, if this data is available through application context, then performance is much faster. Returning a Specific Predicate (Security Policy)You can use application context to return the correct security policy, enforced through a predicate. Consider an order entry application that enforces the following rules: customers only see their own orders, and clerks see all orders for all customers. These are two different policies. You could define an application context with a To design a fine-grained access control policy to return a specific predicate for an attribute, access the application context within the function that implements the policy. For example, to limit customers to seeing only their own records, use fine-grained access control to dynamically modify user queries as from this: to the following query: Providing Attributes Similar to Bind Variables in a PredicateContinuing with the preceding example, suppose you have 50,000 customers, and you do not want to have a different predicate returned for each customer. Customers all share the same predicate, which prescribes that they can only see their own orders. It is merely their customer numbers that are different. Using application context, you can return one predicate within a policy function that applies to 50,000 customers. As a result, there is one shared cursor that executes differently for each customer, because the customer number is evaluated at execution time. This value is different for every customer. Use of application context in this case provides optimum performance, as well as row-level security. Note that the See Also: 'Examples: Secure Application Context Within a Fine-Grained Access Control Function' that also provides a code exampleIntroduction to Global Application ContextIn many application architectures, the middle-tier application is responsible for managing session pooling for application users. Users authenticate themselves to the application, which uses a single identity to log in to the database and maintains all the connections. In this environment, it is not possible to maintain application attributes using session-dependent application context (local application context) because of the sessionless model of the application. Another scenario is when a user is connected to the database through an application (such as Oracle Forms), which then spawns other applications (such as Oracle Reports) to connect to the database. These applications may need to share the session attributes such that they appear to be sharing the same database session. Global application context is a type of secure application context that can be shared among trusted sessions. In addition to driving the enforcement of the fine-grained access control policies, applications (especially middle-tier products) can use this support to manage application attributes securely and globally. Note:
Enforcing Application SecurityThis section contains information about enforcing application security. This section consists of the following topics: Use of Ad Hoc Tools: A Potential Security ProblemPrebuilt database applications explicitly control the potential actions of a user, including the enabling and disabling of user roles while using the application. By contrast, ad hoc query tools such as SQL*Plus, allow a user to submit any SQL statement (which may or may not succeed), including the enabling and disabling of any granted role. Potentially, an application user can exercise the privileges attached to that application to issue destructive SQL statements against database tables by using an ad hoc tool. For example, consider the following scenario:
Now, consider a user who has been granted the Restricting SQL*Plus Users from Using Database RolesThis section presents features that you may use in order to restrict SQL*Plus users from using database roles and thus, prevent serious security problems. These features include the following: Limiting Roles Through PRODUCT_USER_PROFILEDBAs can use The For example, you could create an entry in the
Suppose user Jane connects to the database using SQL*Plus. Jane has the Use of the See Also: SQL*Plus User's Guide and Reference for more information about thePRODUCT_USER_PROFILE table
Using Stored Procedures to Encapsulate Business LogicStored procedures encapsulate use of privileges with business logic so that privileges are only exercised in the context of a well-formed business transaction. For example, an application developer might create a procedure to update employee name and address in the Using VPD for Highest SecurityVirtual Private Database (VPD) provides the benefit of strong security policies, which apply directly to data. When you use VPD, you can enforce security no matter how a user gets to the data: whether through an application, through a query, or by using a report-writing tool. See Also:
VPD and Oracle Label Security Exceptions and ExemptionsVPD and Oracle Label Security are not enforced during Similarly, database users granted the
Note:
See Also: Oracle Label Security Administrator's GuideUser Models and VPDOracle 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, VPD enforcement is relatively simple. Users connect to the database, and the application sets up application contexts for each session. As each session is initiated under a different user name, it is simple to enforce different fine-grained access control conditions for each such user. Even proxy authentication permits different fine-grained access control for each user, because each session (OCI or thick JDBC) is a distinct database session with its own application context. When proxy authentication is integrated with Enterprise User Security, user roles and other attributes can be retrieved from Oracle Internet Directory to enforce VPD. (In addition, globally initialized application context can also be retrieved from the directory.) Applications connecting to the database as a single user on behalf of all users can also 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, Web-based applications typically have hundreds if not thousands 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 Database VPD facilitates connection pooling by allowing multiple connections to access more than one global application context. This capability makes it unnecessary to establish a separate application context for each distinct user session. Table 14-3 summarizes how VPD applies to various user models. Table 14-3 VPD in Different User Models
Footnote 1 User roles and other attributes, including globally initialized application context, can be retrieved from Oracle Internet Directory to enforce VPD. Footnote 2 Application developers can create a global application context attribute representing individual application users (for example,
0 Comments
Leave a Reply. |