Oracle8i Application Developer's Guide - Fundamentals Release 2 (8.1.6) A76939-01 |
|
This chapter provides guidance on developing security policies. It includes:
This section provides an introduction to discretionary security policies. It covers:
Organizations should create a written security policy to enumerate the security threats they are trying to guard against, and the specific measures the organization must take. Security threats can be addressed with types of measures:
Consider whether the appropriate response to a threat is procedural, physical, technical, or personnel-related, or whether the threat can be met by a combination of the above measures.
For example, a possible security threat is disruption of critical business systems caused by a malicious person damaging a computer. A physical response to this threat is to secure key business computers in a locked facility. A procedural response is to create system backups at regular intervals. Personnel measures could include background checks on employees who access or manage key business systems.
Oracle8i offers many mechanisms which can implement the technical measures of a good security policy.
In addition to requirements unique to your environment, you should design and implement discretionary security policies to determine the following technical issues:
This chapter discusses several elements of Oracle8i which you can use in establishing security policies:
Draft security policies for each database application. For example, each 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 (through tools such as SQL*Plus) also need security policies that prevent malicious access to confidential or important schema objects.
This section describes the following aspects of application security:
There are many issues to consider when you formulate and implement application security. Two of the main considerations are these:
Oracle recommends that, where possible, you build applications in which application users are database users. In this way you can leverage the intrinsic security mechanisms of the database.
For many commercial packaged applications, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged user. We will call this the "One Big Application User" model.
Applications built in this fashion generally cannot use many of the intrinsic security features of the database, because the identity of the user is not known to the database.
Examples of features whose use is compromised by the One Big Application User model include:
Oracle recommends that, to the extent possible, applications utilize the security enforcement mechanisms of the database. When security is enforced in the database itself rather than the application, it cannot be bypassed by using another application to access data.
Applications whose users are also database users can either build security into the application, or rely upon intrinsic database security mechanisms such as granular privileges, virtual private database (fine-grained access control with application context), roles, stored procedures, and auditing.
Applications that use the One Big Application User model must build security enforcement into the application rather than using database security mechanisms. In this case, since it is the application--and not the database--which recognizes users, the application must enforce any per-user security measures itself.
If applications enforce their own security mechanisms, it means that each application that accesses data must reimplement security. For example, if an organization implements a new report-writing tool, then the organization must implement security to ensure that users do not get more data access through the report-writing tool than they have in the application itself. Security becomes expensive because organizations must implement the same security policies in multiple applications; each new application requires an expensive reimplementation.
The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can execute queries without going through the Human Resources application. The user thus bypasses all the security measures in the application.
In large database systems with many applications, you may decide 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
statement 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. In this way, 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 these privileges to perform unauthorized operations.
The PL/SQL package DBMS_SESSION.SET_ROLE
is functionally equivalent to the SET ROLE statement in SQL.
A limitation of roles is the inability to SET ROLE
within a definer's rights procedure. The reason is that, for a definer's rights procedure, the database checks privileges at compilation time, not at execution time. That is, the database verifies that the owner of the procedure has necessary privileges--granted to him directly, not through a role--at the time the procedure is compiled. A SET ROLE
statement does not work because the role is not enabled at compilation time, when the database checks privileges. At execution time, when the role is to be enabled, the database does not check the owner's privileges; instead, the database merely ensures that a user of the procedure has EXECUTE
privilege on the procedure.
In cases where the database checks privileges at execution time instead of compilation time, it is possible to issue a SET ROLE
. Thus, the DBMS_SESSION.SET_ROLE
command can be called from the following
In both the above cases, the database checks privileges at execution time, not at compilation time. Therefore, the database can validate that a user has appropriate privileges (that is, that the user has been granted the role that is being set).
Note: If you use DBMS_SESSION.SET_ROLE
within an invoker's rights procedure, the role remains in effect until you specifically disable it. In keeping with the principle of "least privilege," (that users should have the fewest privileges they need to do their jobs), you should explicitly disable roles set within an invoker's rights procedure, at the end of the procedure.
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.
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;
The block fails because the security check which verifies that you have the SELECT
privilege on table JOE
.FINANCE
occurs at compile time. At compile time, however, the ACCT
role is not yet enabled. The role is not enabled until the block is executed.
The DBMS_SQL
package, by contrast, 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. By contrast, 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.
This section describes:
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.
Oracle8i offers some capability to limit what roles a user accesses through an application, via the PRODUCT_USER_PROFILE
table.
DBAs can use PRODUCT_USER_PROFILE
to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus--not Oracle--enforces this security. DBAs can even restrict access to the GRANT
, REVOKE
, and SET ROLE
commands in order to control users' ability to change their database privileges.
The PRODUCT_USER_PROFILE
table enables you to list roles which you do not want users to activate with an application. You can also explicitly disable use of various commands, such as SET ROLE
. For example, you could create an entry in the PRODUCT_USER_PROFILE
table to:
Suppose user Jane connects to the database using SQL*Plus. Jane has the CLERK, MANAGER
, and ANALYST
roles. As a result of the above entry in PRODUCT_USER_PROFILE
, Jane is only able to exercise her ANALYST
role with SQL*Plus. Also, when Jane attempts to issue a SET ROLE
statement, she is explicitly prevented from doing so because of the entry in the PRODUCT_USER_PROFILE
table prohibiting use of SET ROLE
.
Use of the PRODUCT_USER_PROFILE
table does not completely guarantee security, for multiple reasons. In the above example, while SET ROLE
is disallowed with SQL*Plus, if Jane had other privileges granted to her directly, she could exercise these using SQL*Plus.
See Also:
SQL*Plus User's Guide and Reference for more information about the |
Oracle8i allows user to enforce security--to a fine level of granularity--directly on tables or views using virtual private database: the combination of server-enforced fine-grained access control and application context. Because security policies are attached directly to tables or views and automatically applied whenever a user access data, there is no way to bypass security.
Strong security policies, centrally managed and applied directly to data, enable security to be enforced no matter how a user gets to the data, whether through an application, by a query, or by using a report-writing tool.
With virtual private database, a user directly or indirectly accessing a table or view having a security policy associated with it, causes the server to dynamically modify the statement based on a WHERE
condition (known as a predicate) returned by a function which implements the security policy. The user's SQL statement is modified dynamically, transparently to the user, using any condition which can be expressed in, or returned by a function.
Functions which return predicates can also include callouts to other functions. You could embed a C or Java callout within your PL/SQL package that could either access operating system information or return WHERE
clauses from an operating system file or central policy store. A policy function can return different predicates for each user, each group of users, or each application.
Application context enables you to securely access attributes on which you base your security policies. For example, users with the attribute of manager
would have a different security policy than users with the attribute of employee
.
Consider an HR clerk who is only allowed to see employee records in the Aircraft Division. When the user initiates the query
SELECT * FROM emp;
the function implementing the security policy returns the predicate division = `AIRCRAFT'
, and the database transparently rewrites the query. The query actually executed becomes:
SELECT * FROM emp WHERE division = `AIRCRAFT';
The security policy is applied within the database itself, rather than an application, which means that use of a different application will not bypass the security policy. Also, security can be built once, in the database, instead of being reimplemented in multiple applications. Virtual private database therefore provides far stronger security than application-based security, at a lower cost of ownership.
To avoid potential problems, use the following recommended practices when implementing application roles. Each practice is explained in detail below.
Enable the proper role when the application starts, and disable it when the application terminates
One role should contain all privileges necessary to use the application successfully. Depending on the situation, there might be several roles that contain more or fewer privileges to provide tighter or less restrictive security while executing the application. Each application role should be protected by a password (or by operating system authentication) to prevent unauthorized use.
Another role should contain only non-destructive privileges associated with the application (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 by operating system authentication).
SET
ROLE
statement 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.
Note: Roles granted to users can nonetheless be enabled by users outside the application. Such use is not controlled by application-based security. Again, virtual private database is the best way to solve this problem. See "Ways to Use Application Context with Fine Grained Access Control".
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
statement 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. Grant users execute privileges on the procedures, rather than issuing them direct privilege grants. In this way, the logic goes with the privilege.
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.
Grant privileges through roles that require a password unknown to the user.
If there are privileges which the user should use only within the application, you can enable the role by a password known only by the creator of the role. Use the application to issue a SET
ROLE
statement. 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.
This measure discourages users from avoiding use of the application. However, while it does improve application security, it is not foolproof. It still has the following vulnerabilities:
EXECUTE
permission on the stored procedure used to retrieve the password. The user could execute the procedure, retrieve the password, then use the role outside of the application.
In this scenario, you combine server-enforced fine-grained access control and, through application context, session-based attributes.
A schema is a security domain that can contain database objects. The privileges granted to each user or role control access to these database objects. This section covers:
Most schemas can be thought of as usernames: the accounts which enable users to connect to a database and access the database 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 are not granted the CREATE
SESSION
system privilege (either explicitly or via a role). However, you must temporarily grant the CREATE
SESSION
and RESOURCE
privilege to such schemas, if you want to use the CREATE
SCHEMA
statement 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 objects, if they have the privileges to do so. However, no user can connect to the database using the schema set up for the application. This configuration prevents access to the associated objects via the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET SCHEMA
statement to connect the user to the correct application schema.
For many applications, users do not need their own accounts--or their own schemas--in a database. These users merely need to access an application schema. For example, users John, Firuzeh and Jane are all users of the Payroll application, and they need access to the Payroll
schema on the Finance
database. None of them need to create their own objects in the database; in fact, they need only access Payroll objects. To address this issue, Oracle Advanced Security provides enterprise users and schema-independent users.
Enterprise users--users managed in a directory service--can access a shared schema. They do not need to be created as database users; they are schema-independent users of the database. Instead of creating a user account (that is, a user schema) in each database an enterprise user needs to access, as well as creating the user in the directory, an administrator can create an enterprise user once, in the directory, and point the user at a shared schema that many other enterprise users can also access.
In the previous example, if John, Firuzeh and Jane all access the Sales
database as well as the Finance
database, an administrator need only create a single schema in the Sales
database, which all three users can access--instead of creating an account for each user on the Sales
database. In this case, the DBA for the Sales
database creates a shared schema called sales_application
, as follows:
CREATE USER sales_application IDENTIFIED GLOBALLY AS ` `;
The mapping between enterprise users and a schema is done in the directory by means of one or more mapping objects. A mapping object maps the Distinguished Name (DN) of a user, contained in the user's X.509 certificate, to a database schema that the user will access. This can be done in one of two ways:
When the database tries to determine the enterprise user's schema in the directory (that is, the schema to which the database will connect the user) it first looks for a full DN mapping. If it does not find a full DN mapping, then it looks for a partial one. A full DN mapping thus takes precedence over a partial one.
If a set of privileges need to be granted to a group of users, this can be done by granting roles and privileges to a shared schema. Every user sharing the schema gets these local roles and local privileges in addition to the enterprise roles.
Each enterprise user can be mapped to a shared schema on each database she needs to access; these schema-independent users thus need not have a dedicated database schema on each database. User-schema separation therefore lowers the cost of managing users in an enterprise.
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 they need 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.
End users are typically granted object privileges. An object privilege allows a user to perform a particular action on a specific table, view, sequence, procedure, function, or package. Table 11-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 |
|
|
Notes:
1 |
Stand-alone stored procedures, functions, and public package constructs |
2 |
Privilege that cannot be granted to a role |
3 |
Can also be granted for snapshots |
Table 11-2 lists the SQL statements permitted by the object privileges shown in Table 11-1.
As you implement and test your application, you should create each of these roles. 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, coordinate with the administrator of the application to ensure that each user is assigned the proper roles.
This section explains how to create a new role and protect its use.
To create a role, you must have the CREATE
ROLE
system privilege.
The name of a new role 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 it privileges or other roles.
You can create roles such that their use is authorized using information from the operating system or from a network authentication service. This enables role management to be centralized.
Central management of roles provides many benefits. If an employee leaves, for example, all of her roles and permissions can be changed in a single place.
The use of a role can be protected by an associated password. For example:
CREATE ROLE Clerk IDENTIFIED BY Bicentennial;
A user who is granted a role protected by a password can enable or disable the role only by supplying the proper password for the role using a SET
ROLE
statement. If a role is created without any protection, then any grantee can enable or disable it.
Separate SET ROLE
statments can be used to enable one 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.
See Also:
"Explicitly Enabling Roles" and "Restricting Application Roles from Tool Users" Oracle8i Administrator's GuideFor information about network authentication services, see Oracle Advanced Security Administrator's Guide. |
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 in which a user can have roles enabled or disabled.
In general, a user's security domain should permit the user to perform the task at hand, yet limit the user from having privileges which are not necessary 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. By contrast, 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 automatically enabled for a user when the user creates a session. A user's list of default roles should include those which correspond to his or her typical job function.
Each user has a list of zero, 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.
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 statement 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.
Any user (or application) can use the SET
ROLE
statement to enable any granted roles, provided the grantee supplies role passwords, when necessary.
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. The ALL
and ALL
EXCEPT
options of the SET
ROLE
statement 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 the SET
ROLE
statement. 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
or ALL
EXCEPT
options of the SET
ROLE
statement, 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 the SET
ROLE
ALL
or ALL
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;
If OS_ROLES
is set to TRUE
, then any role granted by the operating system can be dynamically enabled using the SET
ROLE
statement. However, any role not identified in a user's operating system account cannot be specified in a SET
ROLE
statement (it is ignored), even if a role has been granted using a GRANT
statement.
When OS_ROLES
is set to TRUE
, a user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES
.
See Also:
Oracle8i Administrator's Guide for more information about use of the operating system for role authorization. |
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 statement DROP
ROLE
. For example:
DROP ROLE clerk;
To drop a role, you must have the DROP
ANY
ROLE
system privilege or have been granted the role with the ADMIN
OPTION
.
The following sections explain how to grant and revoke 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.
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.
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.
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
.
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.
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).
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.
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
.
To grant a schema object privilege, the grantor must either
GRANT
OPTION
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.
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
.
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.
To revoke a schema object privilege, the revoker must be the original grantor of the object privilege being revoked.
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 USER2
. Subsequently, the SELECT
privilege is revoked from USER1
. This revoke is cascaded to USER2
as well. Any schema objects that depended on USER1
's and USER2
's revoked SELECT
privilege can also be affected.
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.
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.
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 the user's statement--transparently to the user--so that the statement implements the correct access control.
This section covers:
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.
Note that all policies applied to a table are enforced with AND
syntax. Thus, if you have three policies applied to the CUSTOMERS
table, each of these policies is applied to any access of the table. You cannot partition the policies by application.
With fine-grained access control, each policy function for a given query is evaluated only once, at statement parse time. Also, 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.
Note: For performance reasons, parsed static SQL statements in instantiated PL/SQL packages may not get re-parsed. If you want to force the re-evaluation of the policy function, Oracle recommends that you use dynamic SQL. For more information, see "Dynamic SQL Recommended for Use with SYS_CONTEXT". |
The DBMS_RLS
package enables you to administer security policies. This package includes four procedures:
These procedures allow you to specify the table or view to which you are adding a policy, the name of the policy, the function which implements the policy, the type of statement to which the policy applies (that is, SELECT
, INSERT
, UPDATE
, or DELETE
), and additional information.
For example, the ADD_POLICY
procedure includes an UPDATE_CHECK
parameter to check the policy before and after insert (or update). If you cannot see it after update or insert, then the update or insert is not allowed. Oracle recommends that you use the UPDATE_CHECK
parameter to spare users the frustration of being able to insert records which they cannot later select.
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 function 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 PL/SQL Packages Reference.
See Also:
Application context allows you to write applications which draw upon certain aspects of a user's session information. This is especially useful in developing secure applications based on a user's access privileges. It provides a way to define, set, and access attributes that an application can use to enforce access control--specifically, fine-grained access control.
Most applications contain information about the basis on which access is to be limited. In an order entry application, for example, customers would be limited to access their own orders (ORDER_NUMBER
) and customer number (CUSTOMER_NUMBER
). These can be used as security attributes.
Consider a user running the Oracle Human Resource application. Part of the application's initialization process is to determine the kind of responsibility that the user can 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.
This section explains the use of application context. It includes:
Application context provides important security features:
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,
SET_OF_BOOKS
and TITLE.
CUSTOMER_NUMBER.
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 set 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 which implements the context, changes the attribute.
Oracle8i provides a built-in application context namespace, USERENV
, which provides access to predefined attributes (session primitives--information which the database captures regarding a user's session). For example, the IP address from which a user connected, the username, and a proxy username (in cases where a user connection is proxied through a middle tier), are all available as predefined attributes through the USERENV
application context.
Predefined attributes can be very useful for access control. For example, if you are using a three-tier application which creates lightweight user sessions through OCI, you can access the PROXY_USER
attribute in the USERENV
application context to determine whether the user's session was created by a middle tier application. Your policy function could allow a user to access data only for connections where the user is proxied. If not (that is, in cases where the user is connecting directly to the database), the user would not be able to access any data.
Predefined attributes can be accessed through the USERENV
application context, but cannot not be changed. They are listed in Table 11-4; see SYS_CONTEXT
in the Oracle8i SQL Reference for complete details about the USERENV
namespace and its predefined attributes.
Use the following syntax to return information about the current session.
SYS_CONTEXT('userenv', 'attribute')
Note: The USERENV
application context namespace is intended to replace the USERENV
function provided in earlier database releases.
Fine-grained access control with application context was designed to handle static applications; that is, those for which the security attributes contained within application contexts are static within the user sessions. In other words, the feature is best used for applications in which the user logs in, his application context is set for the session, and the context does not change until the user logs off. This design principle enables application context to be highly scalable, because many users can share the same fully-parsed, optimized statement.
If an application needs to change a context attribute within a session, then you may need to programmatically ensure that new application context attributes are reflected in the cursor which the user executes. For example, suppose an application wishes to change a user's position
attribute within the Human Resources context to allow the user to view more data within her session. The application may need to force a cursor reparse to ensure that the new position
attribute is used to limit access to data.
There are at least three ways to force a cursor reparse:
The administrative interface to fine-grained access control is the DBMS_RLS
package. You can use the DBMS_RLS.REFRESH_POLICY
procedure to refresh the security policy. This will force a commit of the current transaction. A drawback of this approach is that it will flush all cursors associated with the specified table or view, including cursors of users whose application context has not changed. This approach will achieve the desired affect, but it may degrade performance.
Force an explicit reparse of the cursor. For applications using the Oracle Call Interface, you can explicitly reparse an open cursor by issuing a REPARSE
statement. This forces a SQL statement to be reparsed, which then picks up any change in a users' application context and applies it to the (newly-parsed) statement. PL/SQL, however, does not allow an explicit reparse of a statement, so this approach will not work for PL/SQL statements.
For PL/SQL statements, you can use dynamic SQL to create a package associated with a unique context for your application. This approach is described in the following section.
To make the implementation of a security policy easier, you have the option of using application context within a fine-grained access control function. Virtual private database (VPD) is the term used for a combination of fine-grained access control with application context.
Application context can be used in very specific ways with fine-grained access control:
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.
Application context is especially helpful if your security policy is based on multiple security attributes. For example, a policy function which bases a predicate on four attributes (such as employee number, cost center, position, spending limit), would have to execute multiple subqueries to retrieve this information. If all of this data is already available through application context, then performance will be much faster.
You can use application context to return the correct predicate--that is, the correct security policy.
An order entry application enforces the policy "customers only see their own orders and clerks see all orders for all customers." Here you have two different policies. You could define an application context with a position
attribute, and this attribute could be accessed within the policy function to return the correct predicate, depending on the value of the 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 function 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');
Continuing with the example above, suppose you have 50,000 customers, and you do not want to have a different predicate returned for each customer. Customers all share the same policy (that is, they can only see their own orders); it is merely their customer numbers which are different.
Using application context, you can return one predicate within a policy function which applies to 50,000 customers. As a result, there is one shared cursor which nonetheless executes differently for each customer, because the bind variable (the customer number) is evaluated at execution time. This variable is, of course, different for every customer. Use of application context in this case provides optimum performance, as well as fine-grained security.
To use application context, you perform the following tasks:
Begin by creating a PL/SQL package with functions that set the context for your application. This section presents an example, followed by a discussion of SQL_CONTEXT syntax and behavior.
Note:
A login trigger is highly recommended because the user's context (information such as |
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', [length])
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.
See Also:
"USERENV Application Context Namespace for Access to Predefined Attributes" on . Also see Oracle8i SQL Reference for details about attributes. |
During a session in which you expect a change in policy between executions of a given query, that query must use dynamic SQL.
This is because static SQL and dynamic SQL parse statements differently. With static SQL, statements are parsed at compile time; for performance reasons, they are not reparsed at execution. With dynamic SQL, by contrast, statements are parsed every time they are executed.
Consider a situation in which policy A is in force when you compile a SQL statement--and then you switch to policy B and execute the statement. With static SQL, policy A remains in force: the statement is parsed at compile time and not reparsed upon execution. With dynamic SQL, however, the statement is parsed upon execution, and so the switch to policy B is carried through.
For example, consider the following policy:
EMPLOYEE_NAME = SYS_CONTEXT ('userenv', 'session_user')
The policy "Employee name matches database user name" is represented in the form of a SQL predicate: the predicate is basically a policy. If the predicate changes, the statement must be reparsed in order to produce the correct result.
If SYS_CONTEXT
is used inside a SQL function which is embedded in a parallel query, the function will not be able to pick up the application context. The application context exists only in the user session.
Consider, for example, a user-defined function within a SQL statement, which sets the user's ID to 5:
CREATE FUNC proc1 AS RETURN NUMBER; BEGIN IF SYS_CONTEXT ('hr', 'id') = 5 THEN RETURN 1; ELSE RETURN 2; END END;
Now consider the statement:
SELECT * FROM EMP WHERE proc1( ) = 1;
If this statement is run as a single query (that is, if one process is used to run the entire query), there will be no problem.
However, if this statement is run as a parallel query, the parallel execution servers (query slave processes) do not have access to the user session which contains the application context information. The query will not produce the expected results.
By contrast, if you use the SYS_CONTEXT
function within a query, there is no problem. For example,
SELECT * FROM EMP WHERE SYS_CONTEXT ('hr', 'id') = 5
In this way, it works like a bind variable: the query coordinator can access the application context information and pass it on to the parallel execution servers.
When you execute a statement, Oracle8i takes a snapshot of the entire application context being set up by SYS_CONTEXT
. Within the duration of a query, the context remains the same for all fetches of the query.
If you (or a function) attempt to change the context within a query, the change will not take effect in the current query. In this way, SYS_CONTEXT
enables you to store variables in a session.
To perform this task, you use the CREATE
CONTEXT
statement. Each context must have a unique attribute and belong to a namespace. That is, context names must be unique within the database, not just within a schema. Contexts are always owned by the schema SYS
.
For example:
CREATE CONTEXT order_entry USING oe_context;
where order_entry
is the context namespace, and oe_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
statement. This prevents a malicious user from changing context attributes without proper attribute validation.
Always use an event trigger on login to pull session information into the context. This sets the user's security-limiting attributes for the database to evaluate, and thus enables it to make the appropriate security decisions.
Other considerations come into play if you have a changing set of books, or if positions change constantly. In these cases, the new attribute values may not be picked up right away, and you must force a cursor reparse to pick it up.
Now that you have set up the context and the PL/SQL package, you can go ahead and have your policy functions use the application context to make policy decisions based on different context values.
This section provides three examples, each using application context within a fine-grained access control function.
This simple example uses application context to implement the policy, "Customers can see their own orders only." This example guides you through the following steps in building the application:
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 function.
See Also:
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. |
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 predefined attributes--such as session user--by using For more information, see Oracle8i SQL Reference. |
Create an application context by entering:
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:
In this example, we associate a security policy with the table called DIRECTORY
which has the following columns:
|
identification number for each employee |
|
employee identification number for the manager of each employee |
|
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, we create a definer's right package in the Human Resources schema (HR
) to do SELECT
on the table.
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;
The growth of three-tier systems (for example, browser to application server to database) has increased dramatically with the growth of the Internet. Indeed, three-tier applications are often referred to as the "Internet computing model."
Oracle8i n-tier authentication addresses many security difficulties that arise in three-tier applications. It enables organizations to reap the benefits of Internet computing, while minimizing the security risks of three-tier systems. This section includes:
Three-tier systems provide many benefits to organizations.
In addition, Oracle n-tier authentication delivers the following security benefits:
Note: Oracle8i supports the above functionality in three tiers only, not across multiple middle tiers. |
While three-tier computing provides many benefits, it raises a number of new security issues:
Most organizations want to know the identity of the actual user who is accessing the database, for reasons of access control or auditing. User accountability is diminished if the identity of the users cannot be traced through all tiers of the application.
Furthermore, if only the application server knows who the user is, then all per-user security enforcement must be done by the application itself. Application-based security is very expensive. If each application that accesses the data must enforce security, then security must be reimplemented in each and every application. It is often preferable to build security on the data itself, with per-user accountability enforced within the database.
Some organizations are willing to accept three-tier systems within the enterprise, in which "all-privileged" middle tiers, such as transaction processing (TP) monitors, can perform all actions for all users. In this architecture, the middle tier connects to the database as the same user for all application users. It therefore needs to have all privileges that application users need to do their jobs.
This computing model may be undesirable in the Internet, where the middle tier resides outside, on, or just inside a firewall. More desirable, in this context, is a limited trust model, in which the identity of the real client is known to the data server, and the application server (or other middle tier) has a restricted privilege set.
Also useful is the ability to limit the users on whose behalf a middle tier can connect, and the roles the middle tier can assume for the user. For example, many organizations would prefer that users have different privileges depending on where they are connecting from. A user connecting to a web server or application server on the firewall might only be able to use very minimal privileges to access data, whereas a user connecting to a web server or application server within the enterprise might be able to exercise all privileges she is otherwise entitled to have.
Accountability through auditing is a basic principle of information security. Most organizations want to know on whose behalf a transaction was accomplished, not just that a particular application server performed a transaction. A system must therefore be able to differentiate between a user performing a transaction, and an application server performing a transaction on behalf of a user.
Auditing in three-tier systems should be tied to the issue of knowing the real user: if you cannot preserve the user's identity through the middle tier of a three-tier application, you cannot audit actions on behalf of the user.
In client/server systems, authentication tends to be straightforward: the client authenticates to the server. In three-tier systems authentication is more difficult, because there are several potential authentications.
Client authentication to the middle tier is clearly required if a system is to conform with basic security principles. The middle tier is typically the first gateway to useful information that the user can access. Users must, therefore, authenticate to the middle tier. Note that such authentication may be mutual; that is, the middle tier authenticates to the client just as the client authenticates to the middle tier.
Since the middle tier must typically initiate a connection to a database to retrieve data (whether on its own behalf or on behalf of the user), this connection clearly must be authenticated. In fact, the Oracle8i database does not allow unauthenticated connections. Again, middle tier to database authentication may also be mutual.
Client reauthentication from the middle tier to the database is problematic in three-tier systems. The username may not be the same on the middle tier and the database. In this case, users may need to reenter a username and password, which the middle tier uses to connect on their behalf. Or, more commonly, the middle tier may need to map the username provided, to a database username. This mapping is often done in an LDAP-compliant directory service, such as Oracle Internet Directory.
For the client to reauthenticate himself to the database, the middle tier either needs to ask the user for a password (which it then must be trusted to pass to the database), or the middle tier must retrieve a password for the user and use that to authenticate the user. Both approaches involve security risks, because the middle tier is trusted to handle the user's password properly, and not use it maliciously.
One of the only cases for which reauthentication does not involve trusting the middle tier occurs when a middle tier downloads an applet to a client, and the client connects directly to the database via the applet. In this case, the application server is literally just that: it serves the application (applet) to the user, and has no part in further authentication of the user.
Reauthenticating the client to the back-end database is not always beneficial. First, two sets of authentication handshakes per user involves considerable network overhead. Second, you must trust the middle tier to have authenticated the user. (You clearly must trust the middle tier if it retrieves or otherwise is privy to the user's password.) It is therefore not unreasonable for the database to simply accept that the middle tier has performed proper authentication. In other words, the database accepts the identity of the real client without requiring the real client to authenticate herself.
For some authentication protocols, client reauthentication is just not possible. For example, many browsers and application servers support the Secure Sockets Layer (SSL) protocol. Both the Oracle8i database (through Oracle Advanced Security) and Oracle Application Server support the use of SSL for client authentication. However, SSL is a point-to-point protocol, not an end-to-end protocol. It cannot be used to reauthenticate a browser client (through the middle tier) to the database.
The reason for this is that a user cannot securely give up his private key to the middle tier in order for the reauthentication of the client to occur. Once the user's private key is compromised, the user's very identity is compromised. In addition, there is no way to "tunnel" through a middle tier so that the authentication of the browser client to the database can occur directly.
In short, organizations deploying three-tier systems require flexibility as regards reauthentication of the client. In some cases, they cannot reauthenticate the client; in other cases, they may choose whether or not to reauthenticate the client.
The following sections explain how Oracle8i addresses each of the challenges listed above.
Many organizations want to know who the real user is through all tiers of an application, without sacrificing the benefits of a middle tier. Oracle8i provides the ability to preserve client identity through the Oracle Call Interface (OCI).
OCI enables a middle tier to set up, within a single database connection, a number of "lightweight" user sessions, each of which uniquely identifies a connected user. These lightweight sessions reduce the network overhead of creating separate network connections from the middle tier to the database. The application can switch between these sessions as required to process transactions on behalf of users.
The full authentication sequence from the client to the middle tier to the database occurs as follows:
OCISessionBegin
function. Prior to calling OCISessionBegin
, the OCIAttrSet
function is called to provide the needed information about the client to the middle tier server. It is called in turn with the following attributes:
The OCISessionBegin
call will fail if the application server is not allowed to proxy on behalf of the client by the administrator, or if the application server is not allowed to activate the specified roles.
"Least privilege" is the principle that users should have the fewest privileges necessary to perform their duties, and no more. As applied to middle tier applications, this means that the middle tier should not have more privileges than it needs. Oracle8i enables you to limit the middle tier such that it can connect only on behalf of certain users, using only specific roles.
For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv
(which is also a database user). Sarah has multiple roles, but it is desirable to restrict the middle tier to exercise only the clerk
role on her behalf.
A DBA could effectively grant permission for appsrv
to initiate connections on behalf of Sarah using her clerk
role only, using the following syntax:
ALTER USER Sarah GRANT CONNECT THROUGH appsrv WITH ROLE clerk;
By default, the middle tier cannot create connections for any client. The permission must be granted on a per-user basis.
To allow appsrv
to use all of the roles granted to the client Sarah, the following statement would be used:
ALTER USER sarah GRANT CONNECT THROUGH appsrv WITH ROLE ALL;
Each time a middle tier initiates a lightweight (OCI) session for another database user, the database verifies that the middle tier is privileged to connect for that user, using the role specified.
As described above, it is not always beneficial to reauthenticate users to the database after they have been authenticated by the middle tier. However, if you wish to do this for an added measure of security, you can pass the database the user's password using the OCI_ATTR_PASSWORD
attribute of the OCIAttrSet
call.
See Also:
For more information about security in three-tier architectures, see the Oracle Call Interface Programmer's Guide. |
The n-tier authentication features of Oracle8i enable you audit actions a middle tier performs on behalf of a user. For example, suppose an application server hrappserver
creates multiple lightweight sessions for users Ajit and Jane. A DBA could enable auditing for SELECT
s on the bonus
table that hrappserver
initiates for Jane as follows:
AUDIT SELECT ON bonuses BY hrappserver ON BEHALF OF Jane;
Alternatively, the DBA could enable auditing on behalf of multiple users (in this case, both Jane and Ajit) connecting through a middle tier as follows:
AUDIT SELECT ON bonuses BY hrappserver ON BEHALF OF ANY;
This auditing option only audits SELECT
statements being initiated by hrappserver
on behalf of other users. A DBA can enable separate auditing options to capture SELECT
s against the bonus
table from clients connecting directly to the database:
AUDIT SELECT ON bonuses;
For certain applications, you may decide to encrypt data as an additional measure of security.
Most issues of data security can be handled by appropriate authentication and access control, ensuring that only properly identified and authorized users can access data. Data in the database, however, cannot normally be secured against the database administrator's access, since a DBA has all privileges. Likewise, organizations may have concerns about securing sensitive data stored offline, such as backup files stored with a third party.
Information which may be especially sensitive and warrant encryption could include credit card numbers; national identity numbers in countries with strict privacy laws; or trade secrets, such as industrial formulas. Applications for which a user is authenticated to the application, rather than to the database, may also use encryption to protect the application user password or cookie.
For applications dealing with this highly sensitive data, Oracle provides the DBMS_OBFUSCATION_TOOLKIT
PL/SQL package to encrypt and decrypt data, including string inputs and raw inputs. The function is limited to selected algorithms, such as the Data Encryption Standard (DES). Developers may not plug in their own encryption algorithms, and the key length is also fixed. The function prohibits making multiple passes of encryption; that is, you cannot nest encryption calls, thereby encrypting an encrypted value. These restrictions are required by U.S. laws governing the export of cryptographic products.
The cryptographic functions provide encrypt and decrypt services only and do not provide any built-in key management services (such as automatic key recovery). Developers using the package must handle key storage programmatically. For example, encryption keys could be stored apart from the database if the intention is to prevent the DBA from seeing encrypted data.
In applications using cryptography, encryption must not interfere with other access controls. That is, it must not prevent users from accessing an object they are otherwise privileged to access. Otherwise, their ability to perform their jobs is impaired. For example, a user who has SELECT
privilege on EMP
should not be limited by the encryption mechanism from seeing all the data he is otherwise privileged to see. There is little benefit to encrypting part of a table with one key and part of a table with another key if users need to see all encrypted data in the table; it merely adds to the overhead of decrypting data before users can read it.
Encrypting indexed data is not supported.
Prudent security practice dictates periodically changing an encryption key to mitigate the threat of a compromised key. Changing the key requires that the encrypted object (or objects) be decrypted and reencrypted using the new key or keys. This process may be time-consuming and would probably have to be done when the data is not being accessed.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|