Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

CREATE_PENDING_AREA Syntax Oracle Tips by Burleson

This procedure lets you make changes to resource manager objects.

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

All changes to the plan schema must be done within a pending area. The pending area can be thought of as a “scratch” area for plan schema changes. The administrator creates this pending area, makes changes as necessary, and possibly validates these changes. Only when the submit is completed do these changes become active.

You may, at any time while the pending area is active, view the current plan schema with your changes by selecting from the appropriate user views.

At any time, you may clear the pending area if you want to stop the current changes. You may also call the VALIDATE procedure to confirm whether the changes you have made are valid. You do not have to do your changes in a given order to maintain a consistent group of entries. These checks are also implicitly done when the pending area is submitted.

Tip: Oracle allows “orphan” consumer groups (i.e., consumer groups that have no plan directives that refer to them). This anticipates that an administrator may want to create a consumer group for future use.

VALIDATE_PENDING_AREA Syntax

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

The following rules must be adhered to validate a pending resource plan area;  they are checked whenever the VALIDATE or SUBMIT procedures are executed:

* No plan schema may contain any loops.

* All plans and consumer groups referred to by plan directives must exist.

* All plans must have directives that refer to either plans or consumer groups.

* All the percentages in any given level may not add up to greater than 100 for the emphasis resource allocation method.

* No plan that is currently being used as a top plan by an active instance may be deleted.

* Under Oracle8i, the plan directive parameter, parallel_degree_limit_p1, may appear only in plan directives that refer to consumer groups (i.e., not at subplans).

* There  may not be more than 32 plan directives coming from any given plan (i.e., no plan can have more than 32 children).

* There may not be more than 32 consumer groups in any active plan schema.

* Plans and consumer groups use the same namespace; therefore, no plan may have the same name as any consumer group.

* There must be a plan directive for OTHER_GROUPS somewhere in any active plan schema. This ensures that a session not covered by the currently active plan is allocated resources as specified by the OTHER_GROUPS directive.

If any of the above rules are broken when checked by the VALIDATE or SUBMIT procedures, an informative error message is returned. You may then make changes to fix the problem(s) and reissue the VALIDATE or SUBMIT procedures.

CLEAR_PENDING_AREA Syntax

This procedure has no arguments it is simply called using:

       DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

SUBMIT_PENDING_AREA Syntax

This procedure has no arguments it is simply called using:

      DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

SET_INITIAL_CONSUMER_GROUP Syntax

DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP (
   user           IN VARCHAR2,
   consumer_group IN VARCHAR2);

where:

IOT Overflow. Gives the name of the IOT tables overflow table.

LOG. Does this table use redo logging?

SWITCH_CONSUMER_GROUP_FOR_SESS Syntax

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(
  SESSION_ID      IN NUMBER,
  SESSION_SERIAL  IN NUMBER,
  CONSUMER_GROUP  IN VARCHAR2);

where:

session_id. SID column from the view V$SESSION.

serial. SERIAL# column from the view V$SESSION.

consumer_group. Name of the consumer group of which to switch.

SWITCH_CONSUMER_GROUP_FOR_USER Syntax

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (
   user           IN VARCHAR2,
   consumer_group IN VARCHAR2);

where:

user. Name of the user.

consumer_group. Name of the consumer group to switch to.

DBMS_RESOURCE_MANAGER_PRIVS Package  

The DBMS_RESOURCE_MANAGER package has a companion package that grants privileges in the realm of the resource consumer option: DBMS_RESOURCE_MANAGER_ PRIVS. Its procedures are documented in Table 9.6.

Table 9.6 DBMS_RESOURCE_MANAGER_PRIVS Procedures

DBMS_RESOURCE_MANAGER_PRIVS Procedure Syntax 

The calling syntaxes for all DBMS_RESOURCE_MANAGER_PRIVS packages are presented in the following subsections.

GRANT_SYSTEM_PRIVILEGE Syntax

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (
   grantee_name   IN VARCHAR2,
   privilege_name IN VARCHAR2 DEFAULT 'ADMINISTER_RESOURCE_MANAGER',            
   admin_option   IN BOOLEAN);

where:

grantee_name. Name of the user or role to whom privilege is to be granted.

privilege_name. Name of the privilege to be granted.

admin_option. TRUE if the grant is with admin_option; FALSE otherwise.

REVOKE_SYSTEM_PRIVILEGE Syntax

DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SYSTEM_PRIVILEGE (
   revokee_name   IN VARCHAR2,
   privilege_name IN VARCHAR2 DEFAULT 'ADMINISTER_RESOURCE_MANAGER');

where:

revokee_name. Name of the user or role from whom privilege is to be revoked.

privilege_name. Name of the privilege to be revoked.

GRANT_SWITCH_CONSUMER_GROUP Syntax

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   grantee_name   IN VARCHAR2,
   consumer_group IN VARCHAR2,
   grant_option   IN BOOLEAN);

where:

grantee_name. Name of the user or role to whom privilege is to be granted.

consumer_group. Name of consumer group.

grant_option. TRUE if grantee should be allowed to grant access; FALSE otherwise.

When using the GRANT_SWITCH_CONSUMER_GROUP procedure, the following usage notes apply:

* If you grant permission to a user to switch to a particular consumer group, then that user can immediately switch his or her current consumer group to the new consumer group.

* If you grant permission to a role to switch to a particular consumer group, then any users who have been granted that role and have enabled that role can immediately switch their current consumer group to the new consumer group.

* If you grant permission to switch a particular consumer group to PUBLIC, then any user can switch to that consumer group.

* If the grant_option parameter is TRUE, then users granted switch privilege for the consumer group may also grant switch privileges for that consumer group to others.

* In order to set the initial consumer group of a user, you must grant to the user the switch privilege for that group.

REVOKE_SWITCH_CONSUMER_GROUP Syntax

DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
   revokee_name   IN VARCHAR2,
   consumer_group IN VARCHAR2);

where:

revokee_name. Name of user/role from which to revoke access.

consumer_group. Name of consumer group.

When using the REVOKE_SWITCH_CONSUMER_GROUP procedure, the following usage notes apply.

* If you revoke a user’s switch privilege for a particular consumer group, then any subsequent attempts by that user to switch to that consumer group will fail.

* If you revoke the initial consumer group from a user, then that user, upon logging in, will automatically be part of the DEFAULT_CONSUMER_GROUP consumer group.

* If you revoke the switch privilege for a consumer group from a role, then any users who only had switch privilege for the consumer group via that role will be subsequently unable to switch to that consumer group.

* If you revoke the switch privilege for a consumer group from PUBLIC, then any users who could previously only use the consumer group via PUBLIC will not be subsequently able to switch to that consumer group.

Using OEM to Manage Resource Groups and Plans

As an alternative to using manual procedures to create and maintain resource plans and groups, Oracle has created a GUI interface in the OEM that allows the DBA to create and maintain simple resource plans. The OEM Resource Manager interface allows the DBA to create plans and groups, assign CPU and parallel process allocations, and assign users to resource plans. The DBA can also schedule when a resource plan is active via OEM.

Tip: In SuSE 7.2 Linux, I had to edit the oemapp script to remove the "native"  setting of the command-line argument since that isn't supported on SuSE. To get OEM working on Linux, search for "native" and comment out the section of the if command where it is set to "native.".

You can invoke OEM on Linux from a display window using this command:

oracle@tuscgalinux> oemapp console

The keyword console tells OEM that you are not using a service manager. Once OEM starts, you will have to select the Launch Standalone option to show the top-level display that is shown in Figure 9.3

Figure 9.3 Example top-level OEM display.

By clicking on the cross next to the Databases icon, you will see a list of databases. Click on the database icon that corresponds to the database in which you wish to create a resource plan. You will be prompted for the user name and password for the database. Connect as SYS since you will be using DBMS_RESOURCE_MANAGER, and remember to select the “SYSDBA connect as” option. Once all of the icons for the database you chose are displayed, select the Instance icon. From the Instance menu, select the Resource Plans icon; the display for maintaining resource plans will be displayed. The Resource Plans display is shown in Figure 9.4.

Figure 9.4 OEM resource plan display.

By selecting the Object menu item from the top menu bar on the OEM screen, you can choose Create Object, which will allow you to fill in the required dialog boxes to create a resource plan, assign it groups, and assign these groups to users and  roles. The top-level display for this resource plan creation wizard is shown in Figure 9.5.

Figure 9.5 Example of the Create Resource Plan wizard dialog box.

I won't go through an entire resource plan creation using OEM, as the steps are self-explanatory using the wizard, and you shouldn't have any problems with it.

Back in the Instance menu, there is an icon for Resource Consumer Groups. The top-level Resource Consumer Group display is shown in Figure 9.6. Notice that you can choose the group, the users, and roles that belong to it.

Figure 9.6 OEM Resource Manager group display.

If you choose the Object menu item, you can create new resource groups. An example of the Create Resource Consumer Group dialog box is shown in Figure 9.7.

Figure 9.7 OEM Create Resource Consumer Group dialog box.

Notice the option at the bottom of the screen in Figure 9.7 that allows the user to see the SQL that will be used to create the resource group. As you can see, I had already selected the user and role from the pick lists under their tabs when the screenshot was taken, as is evident in the resulting SQL commands. These commands should look familiar after stepping through the manual creation method at the start of this section.

When you have completed adding users and roles, you simply click the Create button to generate your new Resource Group. Once you have created a resource group, you will need to assign resources to it; you do this in the Resource Plans area when the group is assigned to a plan.

Managing Row-Level Access

Row-level access, also known as fine-grained security and virtual private databases, is managed using a combination of Oracle8i or Oracle contexts, stored procedures, database-level triggers, and the DBMS_RLS package. Row-level access is used to restrict SELECT, INSERT, UPDATE, and DELETE on table rows based on specific values contained in the rows or specific system-environment-related variables. Generally speaking, a policy will require:

* A context

* A procedure to implement the context

* A database- (Oracle8i or Oracle) level trigger that monitors login activity

* A security procedure to implement the policy

* A policy declaration

A row-level access control depends on certain environment variables known as contexts to be set. The DBMS_CONTEXT package is used to set the various context variables used by the RLS policy.

Figure 9.8 Steps to implement a security policy.

Figure 9.8 shows a flowchart of how to implement a simple security policy. As you can see, the process is not complex. Let’s examine each step and see what is really involved.

In the first step, a context package or procedure is developed that will then be used by a login trigger to set each user’s context variables. This step is vital in that if the context variables aren’t set, it is many times more difficult to implement row-level security using the DBMS_RLS package. The package or procedure used to set the context variables should resemble the one shown in Source 9.3.

SOURCE 9.3 Example of a context-setting procedure.

CREATE OR REPLACE PACKAGE graphics_app AUTHID DEFINER AS
PROCEDURE get_graphics_function(usern IN VARCHAR2, graphics_function OUT VARCHAR2);
PROCEDURE set_graphics_context(usern IN VARCHAR2);
END;
/
SET ARRAYSIZE 1
SHO ERR
CREATE OR REPLACE PACKAGE BODY graphics_app AS
graphics_user VARCHAR2(32);
graphics_function VARCHAR2(32);
PROCEDURE get_graphics_function(usern IN VARCHAR2, graphics_function OUT VARCHAR2)
IS
BEGIN
SELECT user_function INTO graphics_function FROM graphics_dba.graphics_users
WHERE username=usern;
END get_graphics_function;
PROCEDURE set_graphics_context(usern IN VARCHAR2) IS
BEGIN
graphics_app.get_graphics_function(usern,graphics_function);
DBMS_SESSION.SET_CONTEXT('GRAPHICS_SEC','GRAPHICS_FUNCTION',graphics_function);
DBMS_SESSION.SET_CONTEXT('GRAPHICS_SEC','GRAPHICS_USER',usern);
END set_graphics_context;
END graphics_app;
/
SHOW ERR

In the package in Source 9.3 are two procedures, one that retrieves a user’s graphics function from a pre-built and populated table, and the other that is used to set the user’s context variables based on using the DBMS_SESSION.SET_CONTEXT procedure provided by Oracle.

Of course, the procedures in Source 9.3 wouldn’t be much use without a trigger that could run it whenever a user logged on to the system. Until Oracle8i, this would have involved setting auditing on for login, moving the aud$ table from SYS ownership and setting the ownership to another user, resetting all of the synonyms pointing to aud$, and then building an insert trigger to perform the actual work. Since Oracle8i, all we have to do is build a trigger similar to the one shown in Source 9.4.

SOURCE 9.4 Example of a database login trigger.

CREATE OR REPLACE TRIGGER set_graphics_context AFTER LOGON ON DATABASE
DECLARE
username VARCHAR2(30);
BEGIN
username:=SYS_CONTEXT('USERENV','SESSION_USER');
graphics_app.set_graphics_context(username);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Once we have an operating context set package and database login trigger, we can proceed to create the required context-checking package and the context it checks. Source 9.5 shows an example context-checking package.

SOURCE 9.5 Example of a context-checking package.

CREATE OR REPLACE PACKAGE graphics_sec AUTHID DEFINER AS
FUNCTION graphics_check(obj_schema VARCHAR2, obj_name VARCHAR2)
 RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(GRAPHICS_CHECK,WNDS);
END;
/
SET ARRAYSIZE 1
SHOW ERR
CREATE OR REPLACE PACKAGE BODY graphics_sec AS
FUNCTION graphics_check(obj_schema VARCHAR2, obj_name VARCHAR2)
 RETURN VARCHAR2 AS
d_predicate VARCHAR2(2000);
user_context VARCHAR2(32);
BEGIN
   user_context:=SYS_CONTEXT('graphics_sec','graphics_function');
   IF user_context = 'ADMIN' THEN
     d_predicate:=' 1=1';
dbms_output.put_line(d_predicate);
   ELSIF user_context = 'GENERAL USER' THEN
     d_predicate:=' graphics_usage='||chr(39)||'UNRESTRICTED'||chr(39);
dbms_output.put_line(d_predicate);
   ELSIF user_context='DEVELOPER' THEN
     d_predicate:=' 1=1';
dbms_output.put_line(d_predicate);
   ELSIF user_context IS NULL THEN
     d_predicate:='1=2';
   END IF;
   RETURN d_predicate;
END graphics_check;
END;
/
SHOW ERR

The entire purpose of the package in Source 9.5 is to return a d_predicate value based on a user’s graphics_function context value. The d_predicate value is appended to whichever WHERE clause is included with the user’s command or is appended as a WHERE clause whenever there is no preexisting clause. It is also possible to use a role granted to the user to determine row-level access restrictions, but it can result in unpredictable results and should be used with care.

The creation of our graphics security context is rather simple once we have finished the preliminary work. It boils down to one command:

CREATE OR REPLACE CONTEXT graphics_sec USING sys.graphics_app;

The final step is to set the policy into the database. This is done with the DBMS_RLS package, using the procedure ADD_POLICY:

BEGIN
 dbms_rls.add_policy('GRAPHICS_DBA','INTERNAL_GRAPHICS','GRAPHICS_POLICY', 'GRAPHICS_DBA','GRAPHICS_SEC.GRAPHICS_CHECK','SELECT,INSERT,UPDATE,DELETE',TRUE);
END;

This policy simply ties the components we previously defined to a coherent entity called GRAPHICS_POLICY and implements this policy against the table INTERNAL_GRAPHICS, which is in the schema GRAPHICS_DBA. The policy GRAPHICS_POLICY is owned by GRAPHICS_DBA and uses the procedure GRAPHICS_SEC. GRAPHICS_CHECK to verify that users can perform SELECT, INSERT, UPDATE, and DELETE operations.

Note that the final parameter in the add_policy call is TRUE: It corresponds to the update_check parameter and defaults to FALSE if not set to TRUE. If update_check is set to FALSE, the validity of the INSERT and UPDATE statements will not be verified and no errors or warnings will be generated. The status of the update_check setting for existing policies can be verified by looking in the SYS.EXU81RLS table (named the same in 8i  and 9i) and checking the CHKOPT column: a value of 0 means update_check is set to FALSE; a value of 1 indicates it is set to TRUE. You can also use the DBA_POLICIES view column CHK_OPTION, which will be either read YES or NO.

The table graphics_users is required in the above example. It contains the user name and the user’s graphics function.

Note: In version 9.0.1.0, if you create a fine-grained access application and do full restriction, as shown in the preceding example (restrict INSERT, UPDATE, DELETE, and SELECT), any attempt to EXPORT the table(s) involved will fail due to bug 1802004, and you will have to patch the database to use EXPORT.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.