Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
This chapter explains how you can control users' ability to execute system operations and to access schema objects by using privileges, roles, and security policies. The chapter includes:
A privilege is a right to execute a particular type of SQL statement or to access another user's object. Some examples of privileges include the right to:
You grant privileges to users so these users can accomplish tasks required for their job. You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work. Excessive granting of unnecessary privileges can compromise security. A user can receive a privilege in two different ways:
EMP
table to the user SCOTT.
EMP
table to the role named CLERK,
which in turn you can grant to the users SCOTT
and BRIAN.
Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users.
There are two distinct categories of privileges:
Oracle9i Database Administrator's Guide for a complete list of all system and schema object privileges, as well as instructions for privilege management
See Also:
A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 60 distinct system privileges.
You can grant or revoke system privileges to users and roles. If you grant system privileges to roles, then you can use the roles to manage system privileges. For example, roles permit privileges to be made selectively available.
Use either of the following to grant or revoke system privileges to users and roles:
GRANT
and REVOKE
Only users who have been granted a specific system privilege with the ADMIN OPTION
or users with the GRANT ANY PRIVILEGE
system privilege can grant or revoke system privileges to other users.
A schema object privilege is a privilege or right to perform a particular action on a specific schema object:
Different object privileges are available for different types of schema objects. For example, the privilege to delete rows from the DEPT
table is an object privilege.
Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges. Their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER
system privilege.
A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.
For example, assume there is a table JWARD.EMP
with a synonym named JWARD.EMPLOYEE
and the user JWARD
issues the following statement:
GRANT SELECT ON emp TO swilliams;
The user SWILLIAMS
can query JWARD.EMP
by referencing the table by name or using the synonym JWARD.EMPLOYEE:
SELECT * FROM jward.emp; SELECT * FROM jward.employee;
If you grant object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object, the effect is the same as if no synonym were used. For example, if JWARD
wanted to grant the SELECT
privilege for the EMP
table to SWILLIAMS, JWARD
could issue either of the following statements:
GRANT SELECT ON emp TO swilliams; GRANT SELECT ON employee TO swilliams;
If a synonym is dropped, all grants for the underlying schema object remain in effect, even if the privileges were granted by specifying the dropped synonym.
Schema object privileges can be granted to and revoked from users and roles. If you grant object privileges to roles, you can make the privileges selectively available. Object privileges for users and roles can be granted or revoked using the following:
GRANT
and REVOKE,
respectively
A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. If the grant includes the GRANT OPTION
of the GRANT
statement, the grantee can further grant the object privilege to other users. Otherwise, the grantee can use the privilege but cannot grant it to other users.
Schema object privileges for tables allow table security at the level of DML and DDL operations.
You can grant privileges to use the DELETE, INSERT, SELECT,
and UPDATE
DML operations on a table or view. Grant these privileges only to users and roles that need to query or manipulate a table's data.
You can restrict INSERT
and UPDATE
privileges for a table to specific columns of the table. With selective INSERT,
a privileged user can insert a row with values for the selected columns. All other columns receive NULL
or the column's default value. With selective UPDATE,
a user can update only specific column values of a row. Selective INSERT
and UPDATE
privileges are used to restrict a user's access to sensitive data.
For example, if you do not want data entry users to alter the SAL
column of the employee table, selective INSERT
and/or UPDATE
privileges can be granted that exclude the SAL
column. Alternatively, a view that excludes the SAL
column could satisfy this need for additional security.
The ALTER, INDEX,
and REFERENCES
privileges allow DDL operations to be performed on a table. Because these privileges allow other users to alter or create dependencies on a table, you should grant privileges conservatively. A user attempting to perform a DDL operation on a table may need additional system or object privileges. For example, to create a trigger on a table, the user requires both the ALTER TABLE
object privilege for the table and the CREATE TRIGGER
system privilege.
As with the INSERT
and UPDATE
privileges, the REFERENCES
privilege can be granted on specific columns of a table. The REFERENCES
privilege enables the grantee to use the table on which the grant is made as a parent key to any foreign keys that the grantee wishes to create in his or her own tables. This action is controlled with a special privilege because the presence of foreign keys restricts the data manipulation and table alterations that can be done to the parent key. A column-specific REFERENCES
privilege restricts the grantee to using the named columns (which, of course, must include at least one primary or unique key of the parent table).
See Also:
Chapter 23, "Data Integrity" for more information about primary keys, unique keys, and integrity constraints |
Schema object privileges for views allow various DML operations, which actually affect the base tables from which the view is derived. DML object privileges for tables can be applied similarly to views.
To create a view, you must meet the following requirements:
GRANT OPTION
clause or appropriate system privileges with the ADMIN OPTION
clause. If you have not, grantees cannot access your view.
To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base objects underlying the view.
Views add two more levels of security for tables, column-level security and value-based security:
EMP
table to show only the EMPNO, ENAME,
and MGR
columns:
CREATE VIEW emp_mgr AS SELECT ename, empno, mgr FROM emp;
WHERE
clause in the definition of a view displays only selected rows of base tables. Consider the following two examples:
CREATE VIEW lowsal AS SELECT * FROM emp WHERE sal < 10000;
The LOWSAL
view allows access to all rows of the EMP
table that have a salary value less than 10000. Notice that all columns of the EMP
table are accessible in the LOWSAL
view.
CREATE VIEW own_salary AS SELECT ename, sal FROM emp WHERE ename = USER;
In the OWN_SALARY
view, only the rows with an ENAME
that matches the current user of the view are accessible. The OWN_SALARY
view uses the USER
pseudocolumn, whose values always refer to the current user. This view combines both column-level security and value-based security.
The only schema object privilege for procedures, including standalone procedures and functions as well as packages, is EXECUTE.
Grant this privilege only to users who need to execute a procedure or compile another procedure that calls it.
A user with the EXECUTE
object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure. No runtime privilege check is made when the procedure is called. A user with the EXECUTE ANY PROCEDURE
system privilege can execute any procedure in the database.
A user can be granted privileges through roles to execute procedures.
Additional privileges on referenced objects are required for invoker-rights procedures, but not for definer-rights procedures.
A user of a definer-rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses, because a definer-rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The procedure's owner must have all the necessary object privileges for referenced objects. Fewer privileges have to be granted to users of a definer-rights procedure, resulting in tighter control of database access.
You can use definer-rights procedures to control access to private database objects and add a level of database security. By writing a definer-rights procedure and granting only EXECUTE
privilege to a user, the user can be forced to access the referenced objects only through the procedure.
At runtime, the privileges of the owner of a definer-rights stored procedure are always checked before the procedure is executed. If a necessary privilege on a referenced object has been revoked from the owner of a definer-rights procedure, then the procedure cannot be executed by the owner or any other user.
An invoker-rights procedure executes with all of the invoker's privileges. Roles are enabled unless the invoker-rights procedure was called directly or indirectly by a definer-rights procedure. A user of an invoker-rights procedure needs privileges (either directly or through a role) on objects that the procedure accesses through external references that are resolved in the invoker's schema.
The invoker needs privileges at runtime to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at runtime.
For all other external references, such as direct PL/SQL function calls, the owner's privileges are checked at compile time, and no runtime check is made. Therefore, the user of an invoker-rights procedure needs no privileges on external references outside DML or dynamic SQL statements. Alternatively, the developer of an invoker-rights procedure only needs to grant privileges on the procedure itself, not on all objects directly referenced by the invoker-rights procedure.
Many packages provided by Oracle, such as most of the DBMS_*
packages, run with invoker rights--they do not run as the owner (SYS)
but rather as the current user. However, some exceptions exist such as the DBMS_RLS
package.
You can create a software bundle that consists of multiple program units, some with definer rights and others with invoker rights, and restrict the program entry points (controlled step-in). A user who has the privilege to execute an entry-point procedure can also execute internal program units indirectly, but cannot directly call the internal programs.
See Also:
|
To create a procedure, a user must have the CREATE PROCEDURE
or CREATE ANY PROCEDURE
system privilege. To alter a procedure, that is, to manually recompile a procedure, a user must own the procedure or have the ALTER ANY PROCEDURE
system privilege.
The user who owns the procedure also must have privileges for schema objects referenced in the procedure body. To create a procedure, you must have been explicitly granted the necessary privileges (system or object) on all objects referenced by the procedure. You cannot have obtained the required privileges through roles. This includes the EXECUTE
privilege for any procedures that are called inside the procedure being created.
Triggers also require that privileges to referenced objects be granted explicitly to the trigger owner. Anonymous PL/SQL blocks can use any privilege, whether the privilege is granted explicitly or through a role.
A user with the EXECUTE
object privilege for a package can execute any public procedure or function in the package and access or modify the value of any public package variable. Specific EXECUTE
privileges cannot be granted for a package's constructs. Therefore, you may find it useful to consider two alternatives for establishing security when developing procedures, functions, and packages for a database application. These alternatives are described in the following examples.
This example shows four procedures created in the bodies of two packages.
CREATE PACKAGE BODY hire_fire AS PROCEDURE hire(...) IS BEGIN INSERT INTO emp . . . END hire; PROCEDURE fire(...) IS BEGIN DELETE FROM emp . . . END fire; END hire_fire; CREATE PACKAGE BODY raise_bonus AS PROCEDURE give_raise(...) IS BEGIN UPDATE EMP SET sal = . . . END give_raise; PROCEDURE give_bonus(...) IS BEGIN UPDATE EMP SET bonus = . . . END give_bonus; END raise_bonus;
Access to execute the procedures is given by granting the EXECUTE
privilege for the package, using the following statements:
GRANT EXECUTE ON hire_fire TO big_bosses; GRANT EXECUTE ON raise_bonus TO little_bosses;
Granting EXECUTE
privilege granted for a package provides uniform access to all package objects.
This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.
CREATE PACKAGE BODY employee_changes AS PROCEDURE change_salary(...) IS BEGIN ... END; PROCEDURE change_bonus(...) IS BEGIN ... END; PROCEDURE insert_employee(...) IS BEGIN ... END; PROCEDURE delete_employee(...) IS BEGIN ... END; END employee_changes; CREATE PROCEDURE hire BEGIN employee_changes.insert_employee(...) END hire; CREATE PROCEDURE fire BEGIN employee_changes.delete_employee(...) END fire; PACKAGE raise_bonus IS PROCEDURE give_raise(...) AS BEGIN employee_changes.change_salary(...) END give_raise; PROCEDURE give_bonus(...) BEGIN employee_changes.change_bonus(...) END give_bonus;
Using this method, the procedures that actually do the work (the procedures in the EMPLOYEE_CHANGES
package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring top-level procedures HIRE
and FIRE,
and an additional package RAISE_BONUS,
you can grant selective EXECUTE
privileges on procedures in the main package:
GRANT EXECUTE ON hire, fire TO big_bosses; GRANT EXECUTE ON raise_bonus TO little_bosses;
This section describes privileges for types, methods, and objects.
Oracle9i defines system privileges shown in Table 25-1 for named types (object types, VARRAY
s,
and nested tables):
The CONNECT
and RESOURCE
roles include the CREATE TYPE
system privilege. The DBA role includes all of these privileges.
The only object privilege that applies to named types is EXECUTE.
If the EXECUTE
privilege exists on a named type, a user can use the named type to:
The EXECUTE
privilege permits a user to invoke the type's methods, including the type constructor. This is similar to EXECUTE
privilege on a stored PL/SQL procedure.
Method execution is the same as any other stored PL/SQL procedure.
To create a type, you must meet the following requirements:
CREATE TYPE
system privilege to create a type in your schema or the CREATE ANY TYPE
system privilege to create a type in another user's schema. These privileges can be acquired explicitly or through a role.
EXECUTE
object privileges to access all other types referenced within the definition of the type, or have been granted the EXECUTE ANY TYPE
system privilege. The owner cannot have obtained the required privileges through roles.
EXECUTE
privileges to the referenced types with the GRANT OPTION
or the EXECUTE ANY TYPE
system privilege with the ADMIN OPTION.
If not, the type owner has insufficient privileges to grant access on the type to other users.
To create a table using types, you must meet the requirements for creating a table and these additional requirements:
EXECUTE
object privileges to access all types referenced by the table, or have been granted the EXECUTE ANY TYPE
system privilege. The owner cannot have obtained the required privileges through roles.
EXECUTE
privileges to the referenced types with the GRANT OPTION
or the EXECUTE ANY TYPE
system privilege with the ADMIN OPTION.
If not, the table owner has insufficient privileges to grant access on the type to other users.
Assume that three users exist with the CONNECT
and RESOURCE
roles:
USER1
performs the following DDL in his schema:
CREATE TYPE type1 AS OBJECT ( attr1 NUMBER); CREATE TYPE type2 AS OBJECT ( attr2 NUMBER); GRANT EXECUTE ON type1 TO user2; GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;
USER2
performs the following DDL in his schema:
CREATE TABLE tab1 OF user1.type1; CREATE TYPE type3 AS OBJECT ( attr3 user1.type2); CREATE TABLE tab2 ( col1 user1.type2);
The following statements succeed because USER2
has EXECUTE
privilege on USER1
's TYPE2
with the GRANT OPTION:
GRANT EXECUTE ON type3 TO user3; GRANT SELECT on tab2 TO user3;
However, the following grant fails because USER2
does not have EXECUTE
privilege on USER1
's TYPE1
with the GRANT OPTION:
GRANT SELECT ON tab1 TO user3;
USER3
can successfully perform the following statements:
CREATE TYPE type4 AS OBJECT ( attr4 user2.type3); CREATE TABLE tab3 OF type4;
Existing column-level and table-level privileges for DML commands apply to both column objects and row objects. Oracle9i defines the privileges shown in Table 25-2 for object tables:
Similar table privileges and column privileges apply to column objects. Retrieving instances does not in itself reveal type information. However, clients must access named type information in order to interpret the type instance images. When a client requests such type information, Oracle checks for EXECUTE
privilege on the type.
Consider the following schema:
CREATE TYPE emp_type ( eno NUMBER, ename CHAR(31), eaddr addr_t); CREATE TABLE emp OF emp_t;
and the following two queries:
SELECT VALUE(emp) FROM emp; SELECT eno, ename FROM emp;
For either query, Oracle checks the user's SELECT
privilege for the EMP
table. For the first query, the user needs to obtain the EMP_TYPE
type information to interpret the data. When the query accesses the EMP_TYPE
type, Oracle checks the user's EXECUTE
privilege.
Execution of the second query, however, does not involve named types, so Oracle does not check type privileges.
Additionally, using the schema from the previous section, USER3
can perform the following queries:
SELECT tab1.col1.attr2 FROM user2.tab1 tab1; SELECT attr4.attr3.attr2 FROM tab3;
Note that in both SELECT
statements, USER3
does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the GRANT OPTION.
Oracle checks privileges on the following events, and returns an error if the client does not have the privilege for the action:
REF
value causes Oracle to check SELECT
privilege on the containing object table.
UPDATE
privilege on the destination object table.
INSERT
privilege on the destination object table.
DELETE
privilege on the destination table.
EXECUTE
privilege on the object.
Modifying an object's attributes in a client 3GL application causes Oracle to update the entire object. Hence, the user needs UPDATE
privilege on the object table. UPDATE
privilege on only certain columns of the object table is not sufficient, even if the application only modifies attributes corresponding to those columns. Therefore, Oracle does not support column level privileges for object tables.
As with stored objects such as procedures and tables, types being referenced by other objects are called dependencies. There are some special issues for types depended upon by tables. Because a table contains data that relies on the type definition for access, any change to the type causes all stored data to become inaccessible. Changes that can cause this effect are when necessary privileges required by the type are revoked or the type or dependent types are dropped. If either of these actions occur, then the table becomes invalid and cannot be accessed.
A table that is invalid because of missing privileges can automatically become valid and accessible if the required privileges are granted again. A table that is invalid because a dependent type has been dropped can never be accessed again, and the only permissible action is to drop the table.
Because of the severe effects which revoking a privilege on a type or dropping a type can cause, the SQL statements REVOKE
and DROP TYPE
by default implement a restrict semantics. This means that if the named type in either statement has table or type dependents, then an error is received and the statement aborts. However, if the FORCE
clause for either statement is used, the statement always succeeds, and if there are depended-upon tables, they are invalidated.
See Also:
Oracle9i Database Reference for details about using the |
Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles. Roles are designed to ease the administration of end-user system and schema object privileges. However, roles are not meant to be used for application developers, because the privileges to access schema objects within stored programmatic constructs need to be granted directly.
These properties of roles allow for easier privilege management within a database:
Database administrators often create roles for a database application. The DBA grants a secure application role all privileges necessary to run the application. The DBA then grants the secure application role to other roles or users. An application can have several different roles, each granted a different set of privileges that allow for more or less data access while using the application.
The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role. Typically, an application is designed so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application's role.
See Also:
|
In general, you create a role to serve one of two purposes:
Figure 25-1 and the sections that follow describe the two uses of roles.
You grant a secure application role all privileges necessary to run a given database application. Then, you grant the secure application role to other roles or to specific users. An application can have several different roles, with each role assigned a different set of privileges that allow for more or less data access while using the application.
You create a user role for a group of database users with common privilege requirements. You manage user privileges by granting secure application roles and privileges to the user role and then granting the user role to appropriate users.
Database roles have the following functionality:
A
cannot be granted to role B
if role B
has previously been granted to role A.
You grant or revoke roles from users or other roles using the following options:
GRANT
and REVOKE
Privileges are granted to and revoked from roles using the same options. Roles can also be granted to and revoked from users using the operating system that executes Oracle, or through network services.
Any user with the GRANT ANY ROLE
system privilege can grant or revoke any role except a global role to or from other users or roles of the database. You should grant this system privilege conservatively because it is very powerful.
Any user granted a role with the ADMIN OPTION
can grant or revoke that role to or from other users or roles of the database. This option allows administrative powers for roles on a selective basis.
Within a database, each role name must be unique, and no username and role name can be the same. Unlike schema objects, roles are not contained in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.
Each role and user has its own unique security domain. A role's security domain includes the privileges granted to the role plus those privileges granted to any roles that are granted to the role.
A user's security domain includes privileges on all schema objects in the corresponding schema, the privileges granted to the user, and the privileges of roles granted to the user that are currently enabled. (A role can be simultaneously enabled for one user and disabled for another.) A user's security domain also includes the privileges and roles granted to the user group PUBLIC.
The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer rights or invoker rights.
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer rights. Roles are not used for privilege checking and you cannot set roles within a definer-rights procedure.
The SESSION_ROLES
view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer rights queries SESSION_ROLES,
the query does not return any rows.
Named PL/SQL blocks that execute with invoker rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker-rights PL/SQL block, and you can use dynamic SQL to set a role in the session.
See Also:
|
A user requires one or more privileges to successfully execute a data definition language (DDL) statement, depending on the statement. For example, to create a table, the user must have the CREATE TABLE
or CREATE ANY TABLE
system privilege. To create a view of another user's table, the creator requires the CREATE VIEW
or CREATE ANY VIEW
system privilege and either the SELECT
object
privilege for the table or the SELECT ANY TABLE
system privilege.
Oracle avoids the dependencies on privileges received by way of roles by restricting the use of specific privileges in certain DDL statements. The following rules outline these privilege restrictions concerning DDL statements:
CREATE TABLE, CREATE VIEW
and CREATE PROCEDURE
privileges.
ALTER
and INDEX
privileges for a table.
Exception: The REFERENCES
object privilege for a table cannot be used to define a table's foreign key if the privilege is received through a role.
The following example further clarifies the permitted and restricted uses of privileges received through roles:
Assume that a user is:
CREATE VIEW
system privilege
SELECT
object
privilege for the EMP
table, but the user is indirectly granted the SELECT
object
privilege for the EMP
table
SELECT
object
privilege for the DEPT
table
Given these directly and indirectly granted privileges:
SELECT
statements on both the EMP
and DEPT
tables.
CREATE VIEW
and SELECT
privilege for the EMP
table through a role, the user cannot create a usable view on the EMP
table, because the SELECT
object
privilege for the EMP
table was granted through a role. Any views created will produce errors when accessed.
DEPT
table, because the user has the CREATE VIEW
privilege through a role and the SELECT
privilege for the DEPT
table directly.
The following roles are defined automatically for Oracle databases:
These roles are provided for backward compatibility to earlier versions of Oracle and can be modified in the same manner as any other role in an Oracle database.
In some environments, you can administer database security using the operating system. The operating system can be used to manage the granting (and revoking) of database roles and to manage their password authentication. This capability is not available on all operating systems.
When you use roles in a distributed database environment, you must ensure that all needed roles are set as the default roles for a distributed (remote) session. You cannot enable roles when connecting to a remote database from within a local database session. For example, you cannot execute a remote procedure that attempts to enable a role at the remote site.
Fine-grained access control allows you to implement security policies with functions and then associate those security policies with tables or views. The database server automatically enforces those security policies, no matter how the data is accessed (for example, by ad hoc queries).
You can:
SELECT, INSERT, UPDATE, and DELETE.
Policy groups were introduced in Oracle9i, Release 1 (9.0.1). The database administrator designates an application context, called a driving context, to indicate the policy group in effect. When tables or views are accessed, the fine-grained access control engine looks up the driving context to determine the policy group in effect and enforces all the associated policies that belong to that policy group.
The PL/SQL package DBMS_RLS
allows you to administer your security policies. Using this package, you can add, drop, enable, disable, and refresh the policies you create.
See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for information about package implementation |
The function or package that implements the security policy you create returns a predicate (a WHERE
condition). This predicate controls access as set out by the policy. Rewritten queries are fully optimized and shareable.
Consider the following security policy example.
In a human resources application called HR, EMPLOYEES
is a view for the ALL_EMPLOYEES
table and both objects are under the APPS
schema. Following are the statements to create the table and the view:
CREATE TABLE all_employees (employee_id NUMBER(15), emp_name VARCHAR2(30), mgr_id NUMBER(15), user_name VARCHAR2(8), .... ); CREATE VIEW employees AS SELECT * FROM all_employees;
You want to create a security policy function that limits access to the EMPLOYEES
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_ACCESS
package. The package is under the 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. Following is the statement to create the application context for this example:
CREATE CONTEXT hr_role USING apps_sec.hr_role
Following are the statements to create the security policy function:
CREATE PACKAGE BODY hr_access ISFUNCTION secure_person(obj_schema VARCHAR2, obj_name VARCHAR2)RETURN VARCHAR2 IS d_predicate VARCHAR2(2000);BEGINIF SYS_CONTEXT ('apps_sec', 'hr_role') = 'EMP' THENd_predicate = 'emp_name = sys_context(''userenv'', ''user'')';IF SYS_CONTEXT ('apps_sec', 'hr_role') = 'MGR' THENd_predicate = 'mgr_id = sys_context(''userenv'', ''uid''))';ELSEd_predicate = '1=2'; -- deny access to other users, -- may use something like 'keycol=null'RETURN d_predicate;END secure_person;END hr_access;
The next step is to associate a policy called PER_PEOPLE_SEC
for the EMPLOYEES
view to the HR_ACCESS.SECURE_PERSON
function that generates the dynamic predicates:
DBMS_RLS.ADD_POLICY('apps', 'employees', 'per_people_sec', 'apps' 'hr_access.secure_person', 'select, update, delete');
Now any SELECT, UPDATE,
and DELETE
statement with the EMPLOYEES
view involved will pick up one of the three predicates based on the value of the application context HR_ROLE.
Note that the same security policy function that secured the ALL_EMPLOYEES
table can also be used to generate the dynamic predicates to secure the ADDRESSES
table because they have the same policy to limit access to data.
See Also:
Oracle9i Application Developer's Guide - Fundamentals for details about establishing security policies |
Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).
Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include "position," "organizational unit," and "country," whereas attributes for an order-entry control might be "customer number" and "sales region".
You can:
To define an application context:
CREATE CONTEXT
to specify a unique context name and associate it with the PL/SQL package you created.
Oracle9i, Release 1 (9.0.1), provides secure application roles, roles that can be enabled only by authorized PL/SQL packages. This mechanism restricts the enabling of roles to the invoking application.
In previous releases, passwords were either embedded in the source code or stored in a table. Application developers no longer need to secure a role by embedding passwords inside applications. Instead, they create a secure application role and specify which PL/SQL package is authorized to enable the role. Package identity is used to determine whether there are sufficient privileges to enable the roles. The application performs authentication before enabling the role.
The application can perform customized authorization, such as checking whether the user has connected through a proxy, before enabling the role.
Secure application roles are created by using the CREATE ROLE ... IDENTIFIED USING
statement. Here is an example:
CREATE ROLE admin_role IDENTIFIED USING hr.admin;
This indicates the following:
admin_role
to be created is a secure application role.
hr.admin.
You must have the system privilege CREATE ROLE
to execute this command.
Roles that are enabled inside an Invoker's Right procedure remain in effect even after the procedure exits. Therefore, you can have a dedicated procedure that deals with enabling the role for the rest of the session to use.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|