Release 8.0
A58241-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
|
Given the many types of mechanisms available to maintain the security of an Oracle database, a discretionary security policy should be designed and implemented to determine
These topics and guidance on developing security policies are discussed in this chapter. If you are using Trusted Oracle, see the Trusted Oracle documentation for additional information about establishing an overall system security policy.
Draft a security policy for each database application. For example, each developed database application (such as a precompiler program or Oracle Forms form) 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 or SQL*ReportWriter) also need tight control to prevent malicious access to confidential or important schema objects.
In large database systems with many database applications (such as precompiler applications or Oracle Forms applications), it may be desirable to have application administrators. An application administrator is responsible for
As the application developer, you might also assume the responsibilities of the application administrator. However, these jobs might be designated to another individual familiar with the database applications.
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, a role should be created and granted all the privileges required to run each 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.
Assume that every administrative assistant uses the Vacation application to record vacation taken by members of the department. You should
Grouping application privileges in a role aids privilege management. Consider the following administrative options:
A single user can use many applications and associated roles. However, you should only allow a user to have the privileges associated with the currently running application role. For example, consider the following scenario:
Therefore, 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. However, update modification to the INVENTORY table is not an authorized action when using the INVENTORY application, but only when using the ORDER application.
To avoid such problems, issue 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. A user can make use of an application's privileges only when running a given application, and is prevented from intentionally or unintentionally using them outside the context of an application.
The SET ROLE statement facilitates privilege management in that, 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, the user cannot combine them to perform unauthorized operations; see "Enabling and Disabling Roles" on page 17-10 for more information.
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 only callable from anonymous PL/SQL blocks. Because PL/SQL does the security check on SQL when an anonymous block is compiled, SET_ROLE will not affect the security role (that is, will not affect the roles enabled) for embedded SQL statements or procedure calls.
For example, if you have a role named ACCT that has been granted privileges that allow you to select from table FINANCE in the JOE schema, the following block will fail:
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 would therefore be successful:
DECLARE n NUMBER BEGIN SYS.DBMS_SESSION.SET_ROLE('acct'); SYS.DBMS_SQL.PARSE ('SELECT empno FROM JOE.FINANCE'); . . . --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. If you do not take precautions, an application user could have the ability to intentionally or unintentionally issue destructive SQL statements against database tables while using an ad hoc tool, using the privileges obtained through an application role.
For example, consider the following scenario:
Now consider a user who has been granted the VACATION role. However, 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 table as he or she chooses.
To avoid potential problems like the one above, consider the following policy for application roles:
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, the user can only enable the non-destructive role intended for that tool.
Additionally, you can
Other ad hoc query and reporting tools, such as SQL*ReportWriter, SQL*Graph, etc., 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. For more information about these features, see the appropriate tool manual.
Each database username is said to be a schema-a security domain that can contain schema objects. The access to the database and its objects is controlled by the privileges granted to each schema.
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 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, 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 17-1 summarizes the object privileges available for each type of object.
| Object Privilege | Table | View | Sequence | Procedure (1)) |
|
ALTER |
3 |
|
3 |
|
|
DELETE |
3 |
3 |
|
|
|
EXECUTE |
|
|
|
3 |
|
INDEX |
3 (2) |
|
|
|
|
INSERT |
3 |
3 |
|
|
|
REFERENCES |
3 (2) |
|
|
|
|
SELECT |
3 |
3 (3) |
3 |
|
|
UPDATE |
3 |
3 |
|
|
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 to ensure that each user is assigned the proper roles.
The use of a role can be protected by an associated password, as in the example below.
CREATE ROLE clerk IDENTIFIED BY bicentennial;
If you are granted a role protected by a password, you can enable or disable the role only by supplying the proper password for the role using a SET ROLE command (see "Explicitly Enabling Roles" on page 17-11 for more information). Alternatively, roles can be created so that role use is authorized using information from the operating system. For more information about use of the operating system for role authorization, see Oracle8 Administrator's Guide.
If a role is created without any protection, the role can be enabled or disabled by any grantee.
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 (that is, they know the password or are authorized by the operating system). See "Restricting Application Roles from Tool Users" on page 17-5 for more information.
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, 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, 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, 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, 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), and 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, accts_rec;
Notice in the first statement that multiple roles can be enabled in a single SET ROLE statement. The ALL and ALL EXCEPT options of the SET 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 the SET 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 or ALL EXCEPT options of the SET 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, 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;
Any user can use the SET ROLE command to enable any granted roles, provided the grantee supplies role passwords, when necessary.
If OS_ROLES is set to TRUE, any role granted by the operating system can be dynamically enabled using the SET ROLE command. 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. For more information about use of the operating system for role authorization, see Oracle8 Administrator's Guide.
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, as shown in the following 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, roles, and schema object privileges.
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.
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 table to the users JWARD and TSMITH:
GRANT select, insert, delete ON emp TO jward, tsmith;
To grant the INSERT object privilege for only the ENAME and JOB columns of the EMP table to the users JWARD and TSMITH, enter the following statement:
GRANT insert(ename, job) ON emp TO jward, tsmith;
To grant all schema object privileges on the SALARY view to the user WALLEN, use the ALL short cut, as in
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, 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
System privileges and roles can be revoked using the SQL command REVOKE, as shown in the following 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 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.
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 table from the users JWARD and TSMITH, enter the following statement:
REVOKE select, insert ON emp FROM jward, tsmith;
A grantor could also revoke all privileges on the table DEPT (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 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. To revoke the UPDATE privilege on just the DEPTNO column, enter the following two statements:
REVOKE UPDATE ON dept FROM human_resources; GRANT UPDATE (dname) ON dept TO human_resources;
The REVOKE statement revokes the UPDATE privilege on all columns of the DEPT 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), the grantor can only revoke the privilege by specifying the CASCADE CONSTRAINTS option in the REVOKE statement:
REVOKE REFERENCES ON dept 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.
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.
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 OPTION.
JWARD creates a table.
JWARD grants the CREATE TABLE system privilege to TSMITH.
TSMITH creates a table.
CREATE TABLE system 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 that user has created any procedures, all procedures contained in the user's schema must be reauthorized before they can be used again (after the revoke).
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 table. If the SELECT privilege on the EMP table is revoked from the owner of the TEST procedure, 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, 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 table and creates a foreign key on the DEPTNO column in the EMP table that references the DEPTNO column. If the REFERENCES privilege on the DEPTNO column of the DEPT table is revoked, the foreign key constraint on the DEPTNO column of the EMP 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 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.
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), 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 only observed 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.
