Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5 A68003-01 |
|
This chapter discusses and provides guidance on developing security policies.
Note: If you are using Trusted Oracle, then see the Trusted Oracle documentation for additional information about establishing an overall system security policy. |
This chapter discusses the elements you can incorporate into security policies:
There are many types of mechanisms available to maintain the security of an Oracle database. In addition to requirements unique to your environment, you should design and implement a discretionary security policy to determine, for example:
This chapter discusses three elements you can use in establishing security policies:
Draft a security policy for each database application. For example, each developed database application should have one or more application roles that provide different levels of security when executing the application. The application roles can be granted to user roles or directly to specific usernames.
Applications that potentially allow unrestricted SQL statement execution (such as SQL*Plus) also need security policies that prevent malicious access to confidential or important schema objects.
In large database systems with many database applications, it may be desirable to have application administrators. An application administrator is responsible for the following:
Because most database applications involve many different privileges on many different schema objects, keeping track of which privileges are required for each application can be complex. In addition, authorizing users to run an application can involve many GRANT
operations. To simplify application privilege management, create a role for each application and grant that role all the privileges a user needs to run the application. In fact, an application might have a number of roles, each granted a specific subset of privileges that allow fewer or more capabilities while running the application.
For example, suppose that every administrative assistant uses the Vacation application to record vacation taken by members of the department. You should:
VACATION
role.
VACATION
role.
VACATION
role to all administrative assistants or to a role named ADMIN_ASSISTS
(if previously defined).
Grouping application privileges in a role aids privilege management. Consider the following administrative options:
ROLE_TAB_PRIVS
and ROLE_SYS_PRIVS
data dictionary views.
DBA_ROLE_PRIVS
data dictionary view.
A single user can use many applications and associated roles. However, you should allow a user to have only the privileges associated with the currently running application role. For example, consider the following scenario:
ORDER
role (for the ORDER
application) contains the UPDATE
privilege for the INVENTORY
table.
INVENTORY
role (for the INVENTORY
application) contains the SELECT
privilege for the INVENTORY
table.
ORDER
and INVENTORY
roles.
In this scenario, an order entry clerk, who has been granted both roles, can presumably use the privileges of the ORDER
role when running the INVENTORY
application to update the INVENTORY
table. The problem is that updating the INVENTORY
table is not an authorized action when using the INVENTORY
application, but only when using the ORDER
application.
To avoid such problems, consider using either the SET
ROLE
command or the SET ROLE
procedure as explained below.
Use a SET
ROLE
statement at the beginning of each application to automatically enable its associated role and, consequently, disable all others. By using the SET ROLE
command, each application dynamically enables particular privileges for a user only when required.
The SET
ROLE
statement facilitates privilege management because, in addition to letting you control what information a user can access, it allows you to control when a user can access it. In addition, the SET
ROLE
statement keeps users operating in a well defined privilege domain.
If a user gets all privileges from roles, then the user cannot combine them to perform unauthorized operations.
The DBMS_SESSIONS
.SET_ROLE
procedure behaves similarly to the SET
ROLE
statement and can be accessed from PL/SQL. You cannot call SET_ROLE
from a stored procedure. This restriction prevents a stored procedure from changing its security domain during its execution. A stored procedure executes under the security domain of the creator of the procedure.
DBMS_SESSION
.SET_ROLE
is callable only from the following:
Because PL/SQL does the security check on SQL when an anonymous block is compiled, SET_ROLE
will not affect the security role (in other words, it will not affect the roles enabled) for embedded SQL statements or procedure calls.
For example, suppose you have a role named ACCT
that has been granted privileges allowing you to select from table FINANCE
in the JOE
schema. In this case, the following block fails:
DECLARE n NUMBER; BEGIN SYS.DBMS_SESSION.SET_ROLE('acct'); SELECT empno INTO n FROM JOE.FINANCE; END;
This block fails because the security check that verifies that you have the SELECT
privilege on table JOE
.FINANCE
happens at compile time. At compile time, you do not have the ACCT
role enabled yet. The role is not enabled until the block is executed.
The DBMS_SQL
package, however, is not subject to this restriction. When you use this package, the security checks are performed at runtime. Thus, a call to SET_ROLE
would affect the SQL executed using calls to the DBMS_SQL
package. The following block is, therefore, successful:
CREATE OR REPLACE PROCEDURE dynSQL_proc IS n NUMBER; BEGIN SYS.DBMS_SESSION.SET_ROLE('acct'); EXECUTE IMMEDIATE 'select empno from joe.finance' INTO n; --other calls to SYS.DBMS_SQL END;
Prebuilt database applications explicitly control the potential actions of a user, including the enabling and disabling of the user's roles while using the application. Alternatively, 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. This can pose a serious security problem: A user of an application could 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:
VACATION
role.
VACATION
role includes the privileges to issue SELECT
, INSERT
, UPDATE
, and DELETE
statements against the EMP_TAB
table.
VACATION
role (the application controls when statements are issued).
Now, consider a user who has been granted the VACATION
role. Suppose, instead of using the Vacation application, the user executes SQL*Plus. At this point, the user is restricted only by the privileges granted to him explicitly or via roles, including the VACATION
role. Because SQL*Plus is an ad hoc query tool, the user is not restricted to a set of predefined actions, as with designed database applications. The user can query or modify data in the EMP_TAB
table as he or she chooses.
To avoid potential problems like the one above, consider the following possible policies for application roles, each of which is explained more fully below:
SELECT
privileges for specific tables or views associated with the application). The read-only role allows the application user to generate custom reports using ad hoc tools, such as SQL*Plus. However, this role does not allow the application user to modify table data outside the application itself. A role designed for an ad hoc query tool may or may not be protected by a password (or operating system authentication).
SET
ROLE
command to enable one of the application roles associated with that application. If a password is used to authorize the role, then the password must be included in the SET
ROLE
statement within the application (encrypted by the application, if possible); if the role is authorized by the operating system, then the system administrator must have set up user accounts and applications so that application users get the appropriate operating system privileges when using the application.
Using this configuration, each application enables the proper role when the application is started, and disables the role when the application terminates. If an application user decides to use an ad hoc tool, then the user can enable only the non-destructive role intended for that tool.
Additionally, you can
PRODUCT_USER_PROFILE
table. This functionality is similar to that of a precompiler or Oracle Call Interface (OCI) application that issues a SET
ROLE
statement to enable specific roles upon application startup.
SET
ROLE
command for SQL*Plus users with the PRODUCT_USER_PROFILE
table. This allows a SQL*Plus user only the privileges associated with the roles enabled when the user started SQL*Plus.
Other ad hoc query and reporting tools can also make use of the PRODUCT_USER_PROFILE
table to restrict the roles and commands that each user can use while running that product.
Another way to restrict users from exercising application privileges by way of ad hoc query tools is to encapsulate privileges into stored procedures, rather than issuing direct privilege grants to users. This allows users to exercise privileges only in the context of well-formed business applications. For example, consider authorizing users to update a table only by executing a stored procedure, rather than by updating the table directly. By doing this, you avoid the problem of the user having the SELECT
privilege and using it outside the application.
See Also:
For an example of encapsulating privileges in stored procedures, see "Example 3: Human Resources Application #2". |
In this scenario, you enable the roles by a password known only by the creator of the role. Use the application to issue a SET
ROLE
command. Because the user does not have the password, either embed the password in the application, or use a stored procedure to retrieve the role password from a database table.
In this scenario, you establish a security policy by securely setting session-based attributes.
A schema is a security domain that can contain database objects. The privileges granted to each user or role controls access to these database objects.
Most schemas can be thought of as usernames -- the accounts set up to allow users to connect to a database and access the database's objects. However, unique schemas do not allow connections to the database, but are used to contain a related set of objects. Schemas of this sort are created as normal users, yet not granted the CREATE
SESSION
system privilege (either explicitly or via a role). However, you must temporarily grant the CREATE
SESSION
privilege to such schemas, if you want to use the CREATE
SCHEMA
command to create multiple tables and views in a single transaction.
For example, the schema objects for a specific application might be owned by a schema. Application users can connect to the database using typical database usernames and use the application and the corresponding object, if they have the privileges to do so. However, no user can connect to the database using the schema set up for the application, thereby preventing access to the associated objects via this schema. This security configuration provides another layer of protection for schema objects.
As part of designing your application, you need to determine the types of users who will be working with the application and the level of access that they must be granted to accomplish their designated tasks. You must categorize these users into role groups, and then determine the privileges that must be granted to each role.
Typically, end users are granted object privileges. An object privilege allows a user to perform a particular action on a specific table, view, sequence, procedure, function, or package. Depending on the type of object, there are different types of object privileges. Table 12-1 summarizes the object privileges available for each type of object.
Object Privilege | Table | View | Sequence | Procedure (1) |
|
3 |
|
3 |
|
|
3 |
3 |
|
|
|
|
|
|
3 |
|
3 (2) |
|
|
|
|
3 |
3 |
|
|
|
3 (2) |
|
|
|
|
3 |
3 (3) |
3 |
|
|
3 |
3 |
|
|
Table 12-2 lists the SQL statements permitted by the object privileges listed in Table 12-1.
As you implement and test your application, you should create each of these roles, and test the usage scenario for each role to be certain that the users of your application will have proper access to the database. After completing your tests, you should coordinate with the administrator of the application that each user is assigned the proper roles.
The use of a role can be protected by an associated password. For example:
CREATE ROLE Clerk IDENTIFIED BY Bicentennial;
If you are granted a role protected by a password, then you can enable or disable the role only by supplying the proper password for the role using a SET
ROLE
command.
Alternatively, roles can be created, so that role use is authorized using information from the operating system or from a network authentication service.
See Also:
Oracle8i Administrator's GuideFor information about network authentication services, see Oracle Advanced Security Administrator's Guide. |
If a role is created without any protection, then any grantee can enable or disable it.
Database applications usually use the role authorization feature to specifically enable an application role, and disable all other roles of a user. This way, the user cannot use privileges (from a role) intended for another application. With ad hoc query tools, such as SQL*Plus or Enterprise Manager, users can explicitly enable only the roles for which they are authorized (in other words, they know the password or are authorized by the operating system).
When you create a new role, the name that you use must be unique among existing usernames and role names of the database. Roles are not contained in the schema of any user.
Immediately after creation, a role has no privileges associated with it. To associate privileges with a new role, you must grant privileges or other roles to the newly created role.
To create a role, you must have the CREATE
ROLE
system privilege.
Although a user can be granted a role, the role must be enabled before the privileges associated with it become available in the user's current session. Some, all, or none of the user's roles can be enabled or disabled. The following sections discuss when roles should be enabled and disabled, and the different ways that a user can have roles enabled or disabled.
In general, a user's security domain should always permit the user to perform the current task at hand, yet limit the user from having unnecessary privileges for the current job. For example, a user should have all the privileges to work with the database application currently in use, but not have any privileges required for any other database applications. Having too many privileges might allow users to access information through unintended methods.
Privileges granted directly to a user are always available to the user; therefore, directly granted privileges cannot be selectively enabled and disabled, depending on a user's current task. Alternatively, privileges granted to a role can be selectively made available for any user granted the role. The enabling of roles never affects privileges explicitly granted to a user. The following sections explain how a user's roles can be selectively enabled (and disabled).
A default role is one that is automatically enabled for a user when the user creates a session. A user's list of default roles should include those roles that correspond to his or her typical job function.
Each user has a list of zero, or one or more default roles. Any role directly granted to a user can potentially be a default role of the user; an indirectly granted role (a role that is granted to a role) cannot be a default role; only directly granted roles can be default roles of a user.
The number of default roles for a user should not exceed the maximum number of enabled roles that are allowed per user (as specified by the initialization parameter MAX_ENABLED_ROLES
); if the number of default roles for a user exceeds this maximum, then errors are returned when the user attempts a connection, and the user's connection is not allowed.
A user's list of default roles can be set and altered using the SQL command ALTER USER
. If the user's list of default roles is specified as ALL
, then every role granted to a user is automatically added to the user's list of default roles. Only subsequent modification of a user's default role list can remove newly granted roles from a user's list of default roles.
Modifications to a user's default role list only apply to sessions created after the alteration or role grant; neither method applies to a session in progress at the time of the user alteration or role grant.
A user (or application) can explicitly enable a role using the SQL command SET
ROLE
. A SET
ROLE
statement enables all specified roles, provided that they have been granted to the user. All roles granted to the user that are not explicitly specified in a SET
ROLE
statement are disabled, including any roles previously enabled.
When you enable a role that contains other roles, all the indirectly granted roles are specifically enabled. Each indirectly granted role can be explicitly enabled or disabled for a user.
If a role is protected by a password, then the role can only be enabled by indicating the role's password in the SET
ROLE
statement. If the role is not protected by a password, then the role can be enabled with a simple SET
ROLE
statement. For example, assume that Morris' security domain is as follows:
PAYROLL_CLERK
(password BICENTENNIAL
)
ACCTS_PAY
(password GARFIELD
)
ACCTS_REC
(identified externally).
The PAYROLL_CLERK
role includes the indirectly granted role PAYROLL_REPORT
(identified externally).
PAYROLL_CLERK
.
Morris' currently enabled roles can be changed from his default role, PAYROLL_CLERK
, to ACCTS_PAY
and ACCTS_REC
, by the following statements:
SET ROLE accts_pay IDENTIFIED BY garfield; SET ROLE accts_pay IDENTIFIED BY accts_rec;Notice that in the first statement, multiple roles can be enabled in a single
SET
ROLE
statement. TheALL
andALL
EXCEPT
options of theSET
ROLE
command also allow several roles granted directly to the user to be enabled in one statement:SET ROLE ALL EXCEPT Payroll_clerk;This statement shows the use of the
ALL
EXCEPT
option of theSET
ROLE
command. Use this option when you want to enable most of a user's roles and only disable one or more. Similarly, all of Morris' roles can be enabled by the following statement:SET ROLE ALL;When using the
ALL
orALL
EXCEPT
options of theSET
ROLE
command, all roles to be enabled either must not require a password, or must be authenticated using the operating system. If a role requires a password, then theSET
ROLE
ALL
orALL
EXCEPT
statement is rolled back and an error is returned.A user can also explicitly enable any indirectly granted roles granted to him or her via an explicit grant of another role. For example, Morris can issue the following statement:
SET ROLE Payroll_report;Privileges Required to Explicitly Enable Roles
Any user can use the
SET
ROLE
command to enable any granted roles, provided the grantee supplies role passwords, when necessary.Enabling and Disabling Roles When OS_ROLES=TRUE
If
OS_ROLES
is set toTRUE
, then any role granted by the operating system can be dynamically enabled using theSET
ROLE
command. However, any role not identified in a user's operating system account cannot be specified in aSET
ROLE
statement (it is ignored), even if a role has been granted using aGRANT
statement.When
OS_ROLES
is set toTRUE
, a user can enable as many roles as specified by the initialization parameterMAX_ENABLED_ROLES
.
See Also: For more information about use of the operating system for role authorization, see Oracle8i Administrator's Guide.
Dropping Roles
When you drop a role, the security domains of all users and roles granted that role are immediately changed to reflect the absence of the dropped role's privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all users' default role lists.
Because the creation of objects is not dependent upon the privileges received via a role, no cascading effects regarding objects need to be considered when dropping a role (for example, tables or other objects are not dropped when a role is dropped).
Drop a role using the SQL command
DROP
ROLE
. For example:DROP ROLE clerk;Privileges Required to Drop Roles
To drop a role, you must have the
DROP
ANY
ROLE
system privilege or have been granted the role with theADMIN
OPTION
.Granting and Revoking Privileges and Roles
The following sections explain how to grant and revoke system privileges, roles, and schema object privileges.
Granting System Privileges and Roles
System privileges and roles can be granted to other roles or users using the SQL command
GRANT
, as shown in the following example:
GRANT CREATE SESSION, Accts_pay TO jward, finance;Schema object privileges cannot be granted along with system privileges and roles in the same
GRANT
statement.The ADMIN OPTION--A system privilege or role can be granted with the
ADMIN
OPTION
. (This option is not valid when granting a role to another role.) A grantee with this option has several expanded capabilities:
ADMIN
OPTION
.
A grantee without the ADMIN
OPTION
cannot perform the above operations.
When a user creates a role, the role is automatically granted to the creator with the ADMIN
OPTION
.
Assume that you grant the NEW_DBA
role to MICHAEL
with the following statement:
GRANT New_dba TO michael WITH ADMIN OPTION;
The user MICHAEL
cannot only use all of the privileges implicit in the NEW_DBA
role, but can grant, revoke, or drop the NEW_DBA
role, as necessary.
Privileges Required to Grant System Privileges or Roles--To grant a system privilege or role, the grantor requires the ADMIN
OPTION
for all system privileges and roles being granted. Additionally, any user with the GRANT
ANY
ROLE
system privilege can grant any role in a database.
Grant schema object privileges to roles or users using the SQL command GRANT
. The following statement grants the SELECT
, INSERT
, and DELETE
object privileges for all columns of the EMP_TAB
table to the users JWARD
and TSMITH
:
GRANT SELECT, INSERT, DELETE ON Emp_tab TO jward, tsmith;
To grant the INSERT
object privilege for only the ENAME
and JOB
columns of the EMP_TAB
table to the users JWARD
and TSMITH
, enter the following statement:
GRANT INSERT(Ename, Job) ON Emp_tab TO jward, tsmith;
To grant all schema object privileges on the SALARY
view to the user WALLEN
, use the ALL
short cut. For example:
GRANT ALL ON Salary TO wallen;
System privileges and roles cannot be granted along with schema object privileges in the same GRANT
statement.
The GRANT OPTION--A schema object privilege can be granted to a user with the GRANT
OPTION
. This special privilege allows the grantee several expanded privileges:
GRANT
OPTION
.
GRANT
OPTION
, and the grantee has the CREATE
VIEW
or the CREATE
ANY
VIEW
system privilege, then the grantee can create views on the table and grant the corresponding privileges on the view to any user or role in the database.
The user whose schema contains an object is automatically granted all associated schema object privileges with the GRANT
OPTION
.
Privileges Required to Grant Schema Object Privileges--To grant a schema object privilege, the grantor must either
GRANT
OPTION
System privileges and roles can be revoked using the SQL command REVOKE
. For example:
REVOKE CREATE TABLE, Accts_rec FROM tsmith, finance;
The ADMIN
OPTION
for a system privilege or role cannot be selectively revoked; the privilege or role must be revoked, and then the privilege or role is regranted without the ADMIN
OPTION
.
Privileges Required to Revoke System Privileges and Roles--Any user with the ADMIN
OPTION
for a system privilege or role can revoke the privilege or role from any other database user or role (the user does not have to be the user that originally granted the privilege or role). Additionally, any user with the GRANT
ANY
ROLE
can revoke any role.
Schema object privileges can be revoked using the SQL command REVOKE
. For example, assuming you are the original grantor, to revoke the SELECT
and INSERT
privileges on the EMP_TAB
table from the users JWARD
and TSMITH
, enter the following statement:
REVOKE SELECT, INSERT ON Emp_tab FROM jward, tsmith;
A grantor could also revoke all privileges on the table DEPT_TAB
(even if only one privilege was granted) that he or she granted to the role HUMAN_RESOURCES
by entering the following statement:
REVOKE ALL ON Dept_tab FROM human_resources;
This statement would only revoke the privileges that the grantor authorized, not the grants made by other users. The GRANT
OPTION
for a schema object privilege cannot be selectively revoked; the schema object privilege must be revoked and then regranted without the GRANT
OPTION
. A user cannot revoke schema object privileges from him or herself.
Revoking Column-Selective Schema Object Privileges--Recall that column-specific INSERT
, UPDATE
, and REFERENCES
privileges can be granted for tables or views; however, it is not possible to revoke column-specific privileges selectively with a similar REVOKE
statement. Instead, the grantor must first revoke the schema object privilege for all columns of a table or view, and then selectively grant the new column-specific privileges again.
For example, assume the role HUMAN_RESOURCES
has been granted the UPDATE
privilege on the DEPTNO
and DNAME
columns of the table DEPT_TAB
. To revoke the UPDATE
privilege on just the DEPTNO
column, enter the following two statements:
REVOKE UPDATE ON Dept_tab FROM human_resources; GRANT UPDATE (Dname) ON Dept_tab TO human_resources;
The REVOKE
statement revokes the UPDATE
privilege on all columns of the DEPT_TAB
table from the role HUMAN_RESOURCES
. The GRANT
statement regrants the UPDATE
privilege on the DNAME
column to the role HUMAN_RESOURCES
.
Revoking the REFERENCES Schema Object Privilege--If the grantee of the REFERENCES
object privilege has used the privilege to create a foreign key constraint (that currently exists), then the grantor can only revoke the privilege by specifying the CASCADE
CONSTRAINTS
option in the REVOKE
statement:
REVOKE REFERENCES ON Dept_tab FROM jward CASCADE CONSTRAINTS;
Any foreign key constraints currently defined that use the revoked REFERENCES
privilege are dropped when the CASCADE
CONSTRAINTS
option is specified.
Privileges Required to Revoke Schema Object Privileges--To revoke a schema object privilege, the revoker must be the original grantor of the object privilege being revoked.
Depending on the type of privilege, there may or may not be cascading effects if a privilege is revoked. The following sections explain several cascading effects.
System Privileges--There are no cascading effects when revoking a system privilege related to DDL operations, regardless of whether the privilege was granted with or without the ADMIN
OPTION
. For example, assume the following:
CREATE
TABLE
system privilege to JWARD
with the WITH
ADMIN
OPTION
.
JWARD
creates a table.
JWARD
grants the CREATE
TABLE
system privilege to TSMITH
.
TSMITH
creates a table.
CREATE
TABLE
privilege from JWARD
.
JWARD
's table continues to exist. TSMITH
continues to have the CREATE
TABLE
system privilege, and his table still exists.
Cascading effects can be observed when revoking a system privilege related to a DML operation. For example, if SELECT
ANY
TABLE
is granted to a user, and if that user has created any procedures, then all procedures contained in the user's schema must be reauthorized before they can be used again (after the revoke).
Schema Object Privileges--Revoking a schema object privilege can have several types of cascading effects that should be investigated before a REVOKE
statement is issued:
TEST
procedure includes a SQL statement that queries data from the EMP_TAB
table. If the SELECT
privilege on the EMP_TAB
table is revoked from the owner of the TEST
procedure, then the procedure can no longer be executed successfully.
ALTER
and INDEX
DDL object privileges are not affected, if the ALTER
or INDEX
object privilege is revoked. For example, if the INDEX
privilege is revoked from a user that created an index on someone else's table, then the index continues to exist after the privilege is revoked.
REFERENCES
privilege for a table is revoked from a user, any foreign key integrity constraints defined by the user that require the dropped REFERENCES
privilege are automatically dropped. For example, assume that the user JWARD
is granted the REFERENCES
privilege for the DEPTNO
column of the DEPT_TAB
table and creates a foreign key on the DEPTNO
column in the EMP_TAB
table that references the DEPTNO
column. If the REFERENCES
privilege on the DEPTNO
column of the DEPT_TAB
table is revoked, then the foreign key constraint on the DEPTNO
column of the EMP_TAB
table is dropped in the same operation.
GRANT
OPTION
are revoked, if a grantor's object privilege is revoked. For example, assume that USER1
is granted the SELECT
object privilege with the GRANT
OPTION
, and grants the SELECT
privilege on EMP_TAB
to USER
2. Subsequently, the SELECT
privilege is revoked from USER
1. This revoke is cascaded to USER
2 as well. Any schema objects that depended on USER1
's and USER2
's revoked SELECT
privilege can also be affected.
Privileges and roles can also be granted to and revoked from the user group PUBLIC
. Because PUBLIC
is accessible to every database user, all privileges and roles granted to PUBLIC
are accessible to every database user.
You should only grant a privilege or role to PUBLIC
if every database user requires the privilege or role. This recommendation restates the general rule that at any given time, each database user should only have the privileges required to successfully accomplish the current task.
Revokes from PUBLIC
can cause significant cascading effects, depending on the privilege that is revoked. If any privilege related to a DML operation is revoked from PUBLIC
(for example, SELECT
ANY
TABLE,
UPDATE
ON
EMP_TAB
), then all procedures in the database (including functions and packages) must be reauthorized before they can be used again. Therefore, use caution when granting DML-related privileges to PUBLIC
.
Depending upon what is granted or revoked, a grant or revoke takes effect at different times:
PUBLIC
are immediately observed.
PUBLIC
are observed only when a current user session issues a SET
ROLE
statement to re-enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.
Issuing a GRANT
statement against a schema object causes the "last DDL time" attribute of the object to change. This can invalidate any dependent schema objects, in particular PL/SQL package bodies that refer to the schema object. These then must be recompiled.
Application context allows you to write applications on certain aspects of a user's session information. This is especially useful in developing secure applications based on a user's access privileges. For example, suppose a user is running the Oracle Human Resource application. Part of the application's initialization process is to determine the kind of responsibility that the user may assume based on the user's identity. This responsibility ID becomes part of the Oracle Human Resource application context; it will affect what data the user can access throughout the session.
Application context provides security tailored to the attributes you specify for each application. It also provides security through validation.
Each application can have its own context with its own attributes. For example, suppose you have three applications: General Ledger, Order Entry, and Human Resources. You can specify different attributes for each application. Thus,
BOOK
and TITLE
CUSTOMER_NUMBER
and
ORGANIZATION_ID
, POSITION
, and COUNTRY
.
In each case, you can adapt the application context to your precise security needs.
Suppose you have a General Ledger application, which has access control based on sets of books. If a user accessing this application changes the set of books he is working on from 01 to 02, the application context can ensure that:
The validation function can check application metadata tables to make this determination and ensure that the attributes in combination are in line with the overall security policy. To restrict a user from changing a context attribute without the above security validation, Oracle verifies that only the designated package implementing the context changes the attribute.
In very basic terms, when you use application context, you perform the following two tasks, each of which is described below.
The following example creates the package app_security_context
.
CREATE OR REPLACE PACKAGE App_security_context IS PROCEDURE Set_empno; END; CREATE OR REPLACE PACKAGE BODY App_security_context IS PROCEDURE Set_empno IS Emp_id NUMBER; BEGIN SELECT Empno INTO Emp_id FROM Emp_tab WHERE Ename = SYS_CONTEXT('USERENV', 'SESSION_USER'); DBMS_SESSION.SET_CONTEXT('app_context', 'empno', Emp_id); END; END;
The syntax for this function is
SYS_CONTEXT ('namespace', 'attribute')
and it returns the value of attribute
as defined in the package currently associated with the context namespace. It is evaluated once for each statement execution, and is treated like a constant during type checking for optimization. You can use the pre-defined namespace USERENV
to access primitive contexts such as userid and NLS parameters.
To do this, you use the CREATE
CONTEXT
command. Each context must have a unique attribute and belong to a namespace. Contexts are always owned by the schema SYS
.
For example:
CREATE CONTEXT App_context USING APP_SECURITY_CONTEXT;
where app_context
is the context namespace, and app_security_context
is the trusted package that can set attributes in the context namespace.
After you have created the context, you can set or reset the context attributes by using the DBMS_SESSION
.SET_CONTEXT
package. The values of the attributes you set remain either until you reset them or until the user ends the session.
You can set the context attributes inside only the trusted procedure you named in the CREATE
CONTEXT
command.
Fine-grained access control allows you to build applications that enforce security policies at a low level of granularity. You can use it, for example, to restrict a customer who is accessing an Oracle server to see only his own account, a physician to see only the records of her own patients, or a manager to see 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 or view on which you have based your application. Then, when a user enters a DML statement (SELECT
, INSERT
, UPDATE
, or DELETE
) on that object, Oracle dynamically modifies that user's statement--transparently to the user--so that the statement implements the correct access control.
Fine-grained access control provides the following capabilities.
Attaching security policies to tables or views, rather than to applications, provides greater security, simplicity, and flexibility.
You can establish several policies for the same table or view. For example, suppose 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.
With fine-grained access control, each policy function for a given query is evaluated only once, namely, at statement parse time. Moreover, 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 such as dictionary caching and shared cursors.
Suppose you want to attach to the ORDERS_TAB
table the following security policy: "Customers can see only their own orders." The process would be as follows.
In this case, you might create a package that adds the following predicate:
Cust_no = (SELECT Custno FROM Customers WHERE Custname = SYS_CONTEXT ('userenv','session_user'))
SELECT * FROM Orders_tab
SELECT * FROM Orders_tab WHERE Custno = ( SELECT Custno FROM Customers WHERE Custname = SYS_CONTEXT('userenv', 'session_user'))
SYS_CONTEXT ('userenv','session_user')
to look up the corresponding customer and to limit the data returned from the ORDERS_TAB
table to that customer's data only.
For more information on using Fine-grained access control, see "Examples" and Oracle8i Supplied Packages Reference
See Also:
To make the implementation of a security policy easier, you have the option of using application context within a fine-grained access control package.
Accessing 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 ORDERS_TAB
table on customer number. Rather than querying the customer number for a logged-in user each time you need it, you could store it in the application context. This way, the customer number is available when you need it.
Suppose an attribute of the Order Entry context is position
. You can return different predicates depending on that attribute. Thus, you can enable a user in the Clerk
position to retrieve all orders, but a user in the Customer
position to see his own records only.
To design a fine-grained access control policy to return a specific predicate for an attribute, access the application context within the package that implements the policy. For example, to limit customers to seeing their own records only, use fine-grained access control to dynamically modify the user's query from this:
SELECT * FROM Orders_tab
to this:
SELECT * FROM Orders_tab WHERE Custno = SYS_CONTEXT ('order_entry', 'cust_num');
This section provides three examples, each using application context within a fine-grained access control package.
This simple example uses application context to implement the policy: 'Customers can see their own orders only.' This example guides you through the following tasks in building the application:
Compare and contrast this example, which uses an application context within the dynamically generated predicate, with "Example of a Dynamically Modified Statement", which uses a subquery in the predicate.
See Also:
The procedure in this example:
Cust_num
)
You can later refer to the cust_num
attribute of your order entry context (order_entry_ctx
) inside the security policy package.
CREATE OR REPLACE PACKAGE apps.oe_ctx AS PROCEDURE set_cust_num ; END; CREATE OR REPLACE PACKAGE BODY apps.oe_ctx AS PROCEDURE set_cust_num IS custnum NUMBER; BEGIN SELECT cust_no INTO custnum FROM customers WHERE username = SYS_CONTEXT('USERENV', 'session_user'); /* SET cust_num attribute in 'order_entry' context */ DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum); DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum); END set_cust_num; END;
Note: This example does not treat error handling.
You can access session primitives--such as session user--by using For more information, see Oracle8i SQL Reference. |
CREATE CONTEXT Order_entry USING Apps.Oe_ctx;
The package body appends a dynamic predicate to SELECT
statements on the ORDERS_TAB
table. This predicate limits the orders returned to those of the user's customer number by accessing the cust_num
context attribute, instead of a subquery to the customers table.
CREATE OR REPLACE PACKAGE BODY Oe_security AS /* limits select statements based on customer number: */ FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 IS D_predicate VARCHAR2 (2000) BEGIN D_predicate = 'cust_no = SYS_CONTEXT("order_entry", "cust_num")'; RETURN D_predicate; END Custnum_sec; END Oe_security;
DBMS_RLS.ADD_POLICY ('scott', 'orders_tab', 'oe_policy', 'secusr', 'oe_security.custnum_sec', 'select')
This statement adds a policy named OE_POLICY
to the ORDERS_TAB
table for viewing in schema SCOTT
. The SECUSR
.OE_SECURITY
.CUSTNUM_SEC
function implements the policy, is stored in the SECUSR
schema, and applies to SELECT
statements only.
Now, any select statement by a customer on the ORDERS_TAB
table automatically returns only that customer's orders. In other words, the dynamic predicate modifies the user's statement from this:
SELECT * FROM Orders_tab;
to this:
SELECT * FROM Orders_tab WHERE Custno = SYS_CONTEXT('order_entry','cust_num');
Note the following with regard to this example:
custnum_sec
function to return different predicates based on the user's position context value.
SELECT * FROM Orders_tab WHERE Custno = SYS_CONTEXT('order_entry', 'cust_num')
This is fully parsed and optimized, but the evaluation of the user's CUST_NUM
attribute value for the ORDER_ENTRY
context takes place at execution. This means that you get the benefit of an optimized statement which executes differently for each user executing the statement.
This example uses application context to control user access by way of a Human Resources application. It guides you through the following three tasks, each of which is described more fully below.
In this example, assume that the application context for the Human Resources application is assigned to the HR_CTX
namespace.
APPS
is the schema owning the package.
CREATE OR REPLACE PACKAGE BODY apps.hr_sec_ctx IS /* function to set responsibility id */ PROCEDURE set_resp_id (respid NUMBER) IS BEGIN /* validate respid based on primitive and other context */ /* validate_respid (respid); */ /* set resp_id attribute under namespace 'hr_ctx'*/ DBMS_SESSION.SET_CONTEXT('hr_ctx', 'resp_id', respid); END set_resp_id; /* function to set organization id */ PROCEDURE set_org_id (orgid NUMBER) IS BEGIN /* validate organization ID */ /* validate_org_id(orgid); /* /* set org_id attribute under namespace 'hr_ctx' */ DBMS_SESSION.SET_CONTEXT('hr_ctx', 'org_id', orgid); END set_org_id; /* more functions to set other attributes for the HR application */ END hr_sec_ctx;
CREATE CONTEXT Hr_ctx USING Apps.Hr_sec_ctx;
Suppose that the execute privilege on the package HR_SEC_CTX
has been granted to the schema running the application. Part of the script will make calls to set various attributes of the HR_CTX
context. Here, we do not show how the context is determined. Normally, it is based on the primitive context or other derived context.
APPS.HR_SEC_CTX.SET_RESP_ID(1); APPS.HR_SEC_CTX.SET_ORG_ID(101);
The SYS_CONTEXT
function can be used for data access control based on this application context. For example, the base table HR_ORGANIZATION_UNIT
can be secured by a view that restricts access to rows based on attribute ORG_ID
:
CREATE VIEW Hr_organization_secv AS SELECT * FROM hr_organization_unit WHERE Organization_id = SYS_CONTEXT('hr_ctx','org_id');
This example illustrates the use of the following security features in Oracle8i release 8.1.4:
In this example, we associate a security policy with the table called DIRECTORY
which has the following columns:
EMPNO
--identification number for each employee
MGRID
--employee identification number for the manager of each employee
RANK
--position of the employee in the corporate hierarchy
The security policy associated with this table has two elements:
MGRID
for a specific EMPNO
.
To implement this:
EMPNO
and application context.
EMPNO
by using a logon trigger.
CONNECT system/manager AS sysdba GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE,CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE ANY TRIGGER TO HR IDENTIFIED BY HR; CONNECT hr/hr; CREATE TABLE Directory (Empno NUMBER(4) NOT NULL, Mgrno NUMBER(4) NOT NULL, Rank NUMBER(7,2) NOT NULL); CREATE TABLE Payroll (Empno NUMBER(4) NOT NULL, Name VARCHAR(30) NOT NULL ); /* seed the tables with a couple of managers: */ INSERT INTO Directory VALUES (1, 1, 1.0); INSERT INTO Payroll VALUES (1, 'KING'); INSERT INTO Directory VALUES (2, 1, 5); INSERT INTO Payroll VALUES (2, 'CLARK'); /* Create the sequence number for EMPNO: */ CREATE SEQUENCE Empno_seq START WITH 5; /* Create the sequence number for RANK: */ CREATE SEQUENCE Rank_seq START WITH 100; CREATE OR REPLACE CONTEXT Hr_app USING Hr.Hr0_pck; CREATE OR REPLACE CONTEXT Hr_sec USING Hr.Hr1_pck; CREATE or REPLACE PACKAGE Hr0_pck IS PROCEDURE adjustrankby1(Empno NUMBER); END; CREATE or REPLACE PACKAGE BODY Hr0_pck IS /* raise the rank of the empno by 1: */ PROCEDURE Adjustrankby1(Empno NUMBER) IS Stmt VARCHAR2(100); BEGIN /*Set context to indicate application state */ DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',1); /* Now we can issue DML statement: */ Stmt := 'UPDATE SET Rank := Rank +1 FROM Directory d WHERE d.Empno = ' || Empno; EXECUTE IMMEDIATE STMT; /* Re-set application state: */ DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',0); END; END; CREATE or REPLACE PACKAGE hr1_pck IS PROCEDURE setid; END; / /* Based on userid, find EMPNO, and set it in application context */ CREATE or REPLACE PACKAGE BODY Hr1_pck IS PROCEDURE setid IS id NUMBER; BEGIN SELECT Empno INTO id FROM Payroll WHERE Name = SYS_CONTEXT('userenv','session_user') ; DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id); DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id); EXCEPTION /* For purposes of demonstration insert into payroll table / so that user can continue on and run example. */ WHEN NO_DATA_FOUND THEN INSERT INTO Payroll (Empno, Name) VALUES (Empno_seq.NEXTVAL, SYS_CONTEXT('userenv','session_user')); INSERT INTO Directory (Empno, Mgrno, Rank) VALUES (Empno_seq.CURRVAL, 2, Rank_seq.NEXTVAL); SELECT Empno INTO id FROM Payroll WHERE Name = sys_context('userenv','session_user') ; DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id); DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id); WHEN OTHERS THEN NULL; /* If this is to be fired via a "logon" trigger, / you need to handle exceptions if you want the user to continue / logging into the database. */ END; END; GRANT EXECUTE ON Hr1_pck TO public; CONNECT system/manager AS sysdba CREATE OR REPLACE TRIGGER Databasetrigger AFTER LOGON ON DATABASE BEGIN hr.Hr1_pck.Setid; END; /* Creates the package for finding the MGRID for a particular EMPNO using definer's right (encapsulated privileges). Note that users are granted EXECUTE privileges only on this package, and not on the table (DIRECTORY) it is querying. */ CREATE or REPLACE PACKAGE hr2_pck IS FUNCTION Findmgr(Empno NUMBER) RETURN NUMBER; END; CREATE or REPLACE PACKAGE BODY hr2_pck IS /* insert a new employee record: */ FUNCTION findmgr(empno number) RETURN NUMBER IS Mgrid NUMBER; BEGIN SELECT mgrno INTO mgrid FROM directory WHERE mgrid = empno; RETURN mgrid; END; END; CREATE or REPLACE FUNCTION secure_updates(ns varchar2,na varchar2) RETURN VARCHAR2 IS Results VARCHAR2(100); BEGIN /* Only allow updates when designated application has set the session state to indicate we are inside it. */ IF (sys_context('hr_sec','adjstate') = 1) THEN results := 'mgr = SYS_CONTEXT("hr_sec","empno")'; ELSE results := '1=2'; END IF; RETURN Results; END; /* Attaches fine-grained access policy to all update operations on hr.directory */ CONNECT system/manager AS sysdba; BEGIN DBMS_RLS.ADD_POLICY('hr','directory_u','secure_update','hr', 'secure_updates','update',TRUE,TRUE); END;