Database Support: Security, Access control on tables

Dear Readers,

My name is Franz Devantier, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g) Security DBA. I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks.

I will make a deal with you: If you refer me to a company that needs database support, from a few hours per week to full time, and I am able to sign a contract with them.

Then I will give you 10% of the monthly contract or deal price every month. When the contract ends, and we re-sign the contract, I will again give you 10% of the monthly contract price. This will go on until the company no longer employs or contracts me or my agents to look after their databases.

I can do this, because that 10% is my marketing budget. When we re-sign the contract, in the future, it may depend on you giving the thumbs up again, and that is worth 10% of the monthly contract price, to be given to you as commission.

Contact: Franz

Security, Access control on tables, views, Synonyms or Rows - Part 4

Application Context

Application context helps you to apply fine-grained access control. The reason why we can say this is because, it allows you to link function-based security policies with applications, using application context and applying the fine-grained access control.

There is a built in application context that ships with Oracle; USERENV. USERENV provides access to predefined attributes. These attributes consist of information that the Oracle Database Server automatically captures for a user session, called “Session Primitives”. For example the IP address, the username, the proxy_user_name, if the user came through a middle tier etc, are available as session primitives; and can be queried through the USERENV or default application context.

Each application also has its own application specific context. Users cannot change the values in the application specific context though SQL*Plus or other means. Application specific context attributes are only accessible to the functions that implement the security policies.

To illustrate, the application specific context attributes for a salary application could include attributes like: position, unit, salary_notch, salary_scale, country etc. The attributes available from an accounts receivable application would be quite different and separated from the salary application. To summarize, application contexts include the default application context, USERENV, in which the user environment attributes are automatically captured. Application contexts for specific applications will contain attributes for the specific applications.

In an application context you can:

-> Base predicates on context values

-> Use context values within predicates as bind variables

-> Set user attributes

-> Access user attributes

To define an application context:

1. You can create a PL/SQL package to validate and set the context for the application. You can use a logon trigger to set the initial context for logged in users.

2. The next step is to create a context name and associate it with the PL/SQL package that you created in step 1. You can use the CREATE CONTEXT syntax, making sure that the context name is unique.

3. Now there are two ways to proceed.

3.1 You can now reference the application context with the policy function in order to implement fine-grained access control.

3.2 You can create a logon event trigger to set the initial context for users of the application. This may include querying personnel information, and setting up values in the attributes, such as the ID Number etc.

4. Now you can just reference the application context for application context values. For example you may like to restrict ordinary users to being able to only see their own salary records.

You would use fine-grained access control to dynamically modify the users query from:

SELECT * FROM salary;

This query would now be modified to.

SELECT * FROM salary WHERE id_number=SYS_CONTEXT(‘salaries’,’id_num’);

The result is that the user will only see their own salary details.

Dynamic Contexts

When you create a policy, you specify how the policy will be defined at runtime. This definition will also have an effect on the efficiency of the policy. The different options that are available are:

-> Static

-> Shared

-> Context-sensitive

-> Dynamic

Policy Types & Run Time Efficiencies

Policy Type	Predicate and Policy Function	Description and Operational Explanation
Static	Same predicate string for anyone accessing the object	Executed once and cached in SGA. Policies for statements accessing the same object do not re-execute the policy function, but use the cached predicate instead.
Shared-static	Same as static, except the policy can be shared across multiple objects	Ideal for data partitions in hosting environments because almost all objects share the same function and the policy is static. Executed once and cached in SGA, but the server first looks for a cached predicate generated by the same policy function of the same policy type.
Context-sensitive and shared context-sensitive	Policy function executed when statement parsed, but value returned is not cached	The policy function is not re-evaluated at statement execution time unless the server detects context changes since the last use of the cursor. (For session pooling where multiple clients share a database session, the middle tier must reset context during client switches.) When a context-sensitive policy is labeled shared, the server first looks for a cached predicate generated by the same policy function of the same policy type within the same database session. If the predicate is found in the session memory, then the policy function is not rerun and the cached value is valid until session private application context changes occur.
Dynamic	Policy function always re-executed on each statement parsing or execution	Server assumes the predicate may be affected by any system or session environment at any time. Dynamic is the system default. If no policy type is specified when DBMS_RLS.ADD_POLICY is called, then dynamic is assumed, so it is best to specify.