Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_RLS , 13 of 13
This procedure reparses the SQL statements associated with a refreshed policy.
DBMS_RLS.REFRESH_GROUPED_POLICY ( object_schema VARCHAR2, object_name VARCHAR2, group_name VARCHAR2, policy_name VARCHAR2 );
This example illustrates the necessary steps to enforce a fine-grained access control policy.
In an Oracle HR application, PER_PEOPLE
is a view for the PER_ALL_PEOPLE
table, and both objects are under APPS
schema.
CREATE TABLE per_all_people (person_id NUMBER(15), last_name VARCHAR2(30), emp_no VARCHAR2(15), ...); CREATE VIEW per_people AS SELECT * FROM per_all_people;
There should be a security policy that limits access to the PER_PEOPLE
view based on the user's role in the company. The predicates for the policy can be generated by the SECURE_PERSON
function in the HR_SECURITY
package. The package is under schema APPS
and contains functions to support all security policies related to the HR application. Also, all the application contexts are under the APPS_SEC
namespace.
CREATE PACKAGE BODY hr_security IS FUNCTION secure_person(obj_schema VARCHAR2, obj_name VARCHAR2) RETURN VARCHAR2 IS d_predicate VARCHAR2(2000); BEGIN -- for users with HR_ROLE set to EMP, map logon user name -- to employee id. FND_USER table stores relationship -- among database users, application users, -- and people held in the HR person table. IF SYS_CONTEXT('apps_sec', 'hr_role') = 'EMP' THEN d_predicate = 'person_id IN (SELECT employee_id FROM apps.fnd_user WHERE user_name = SYS_CONTEXT(''userenv'', ''session_ user''))'; -- for users with HR_ROLE set to MGR (manager), map -- security profile id to a list of employee id that -- the user can access ELSE IF SYS_CONTEXT('apps_sec', 'hr_role') = 'MGR' THEN d_predicate = 'person_id IN (SELECT ppl.employee_id FROM per_person_list ppl WHERE ppl.security_profile_id = SYS_CONTEXT(''apps_sec'', ''security_profile_id'')) OR EXISTS (SELECT NULL FROM apps.per security_profiles psp WHERE SYS_CONTEXT(''apps_sec'', ''security_profile_id'') = psp.security_profile_id AND psp.view_all_flag = ''Y''))'; ELSE d_predicate = '1=2'; -- deny access to other users, may use something like 'keycol=null' END IF; RETURN d_predicate; END secure_person; END hr_security;
The next step is to associate a policy (here we call it PER_PEOPLE_SEC
) for the PER_PEOPLE
view to the HR_SECURITY
.SECURE_PERSON
function that generates the dynamic predicates:
DBMS_RLS.ADD_POLICY('apps', 'per_people', 'per_people_sec', 'apps' 'hr_security.secure_person', 'select, update, delete');
Now, any SELECT
, UPDATE
, and DELETE
statement with the PER_PEOPLE
view involved will pick up one of the three predicates based on the value of the application context HR_ROLE
.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|