Release 2 (9.2)
Part Number A96590-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Fundamentals Release 2 (9.2) Part Number A96590-01 |
|
This chapter provides a basic understanding of application and database security policies. The following security policy topics are included in this chapter:
This section briefly introduces 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 different 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, one 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.
Oracle9i 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 information security policies to address the following important issues:
The following elements of Oracle9i enable you to address security issues of a technical nature:
To avoid potential problems, use the following recommended practices when implementing database roles. Each practice is explained in detail below.
Enable the proper role when the application starts, and disable it when the application terminates. To do this, you must take the following approach:
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 database 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.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.
| See Also:
"Ways to Use Application Context with Fine-Grained Access Control" "Using Secure Application Role to Ensure Database Connection" The appropriate tool manual, such as the SQL*Plus User's Guide and Reference |
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.
| See Also:
"Example 3: Event Triggers, Application Context, Fine-Grained Access Control, and Encapsulation of Privileges" |
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, you should 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.
A user with access to application code could potentially find the password that is embedded in the application. This security by obscurity is not a good security practice. Embedding a password in the application protects against a user who wants to bypass the application (a lazy user). It does not protect against the user who deliberately wants to misuse privileges by accessing data and bypassing the application (a malicious user). Since client code can be decompiled and the embedded password recovered, you should only use the embedded password method to protect against the lazy users.
To use the stored procedure to retrieve the role password from a database table, a user would need EXECUTE permission, then execute the procedure, retrieve the password, and use the role outside of the application.
In three-tier systems, it is possible to enable a role only when the user accesses the database through a middle-tier application. This requires the use of proxy authentication and a secure application role. Proxy authentication distinguishes between a middle tier creating a session on behalf of a user and the user connecting directly. Both the proxy user (the middle tier) and the real user information are captured in the user session. A secure application role, which is implemented by a package, can do desired validation before allowing the user to assume the privileges in the role. When the application uses proxy authentication, the secure application role can validate that the user session was created by proxy, and that the user is connecting to the database through an application, and not directly.
Consider a situation in which you want to restrict use of an HR administration role to users accessing the database (by proxy) through the middle tier HRSERVER. You could create the following secure access role:
CREATE ROLE admin_role IDENTIFIED USING hr.admin;
Here, hr.admin is a package which performs desired validation. The package can determine if a user is connected by proxy using SYS_CONTEXT ('userenv', 'proxy_userid'), or SYS_CONTEXT (userenv', 'proxy_user'), or both return the ID and name of the proxy user (HRSERVER, in this case). If the user attempts to connect directly to the database, the hr.admin package will not allow the role to be set.
The secure application role can use additional information in the user session in order to restrict access. IP-address based security is not foolproof, since IP addresses can be spoofed. Therefore, you should never use IP address to make primary access control decisions, but you could use IP address to further restrict access, in addition to other controls. For example, you may want to ensure that a user session was created by proxy and that a middle tier user connecting from a particular IP address created the user session. Of course, the middle tier must authenticate itself to the database before creating a lightweight session, and the database ensures that the middle tier has privilege to create a session on behalf of the user. Your secure application role could verify the IP address of the incoming connection to ensure that the HRSERVER connection (or the lightweight user session) is coming from the appropriate IP address using SYS_CONTEXT (userenv',' 'ip_address') before allowing SET ROLE to succeed. This provides an additional layer of security.
In this scenario, you combine server-enforced fine-grained access control and, through application context, session-based attributes.
You should draft security policies for each database application. For example, each database application should have one or more database roles that provide different levels of security when executing the application. The database 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 policies:
There are many issues to consider when you formulate and implement application security. Two of the main considerations are these:
Oracle Corporation 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.
For example, use of the following features is compromised by the One Big Application User model:
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 (including fine-grained auditing). To the extent possible, Oracle recommends that applications utilize the security enforcement mechanisms of the database.
When security is enforced in the database itself, rather than in the application, it cannot be bypassed. 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 of the security measures in the application.
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.
This approach means that each and every application which accesses data must reimplement security. For example, if an organization implements a new report-writing tool, then it must also implement security to ensure that users do not get more data access through the report-writing tool than they would 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.
In large database systems with many applications, you may decide to have application administrators. An application administrator is responsible for the following:
Most database applications involve different privileges on 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. This section provides some features to managing application privileges. Managing application privileges includes the following:
To simplify application privilege management, you can 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 greater or lesser 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. To best manage this application, 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.Database access is based on privileges, which are often grouped into roles. Once grouped, the roles are granted to the application user. In previous releases, one would embed a password inside the application to ensure that users only enable the granted roles within the application. Roles secured by embedding passwords inside their applications are called application roles.
In Oracle9i, application developers no longer need to secure a role by embedding passwords inside applications. They can create application roles and specify which PL/SQL package is authorized to enable the roles. These application roles, those enabled by PL/SQL packages, are called secure application roles.
Within the package that implements the secure application role:
SYS_CONTEXT ('userenv', <session_attribute>). The accessible information indicates the way the user was authenticated, the IP address of the client, and whether the user was proxied.DBMS_SESSION.SET ROLE).Topics in this section include:
|
Note: Because users can not change security domain inside Definer's Right procedures, secure application roles can only be enabled inside Invoker's Right procedures. |
To create a secure application role:
CREATE ROLE admin_role IDENTIFIED USING hr.hr_admin; CREATE ROLE staff_role IDENTIFIED USING hr.hr_admin;
CREATE OR REPLACE PACKAGE hr_admin AUTHID CURRENT_USER IS PROCEDURE hr_app_report; END; / CREATE OR REPLACE PACKAGE BODY hr_admin IS PROCEDURE hr_app_report IS BEGIN
/* set application context in 'responsibility' namespace */ hr_logon.hr_set_responsibility; /* authentication check here */ if (Hr.MySecurityCheck = TRUE) then
/* check 'responsibility' being set, then enable the roles without supplying the password */ if (sys_context('hr','role') = 'admin' )
then
dbms_session.set_role('admin_role');
else
dbms_session.set_role('staff_role'); end if;
end if;
END; END; /* Create a dedicated authentication function for manageability so that changes in authentication policies would not affect the source code of the application - this design is up the application developers */ /* the only policy in this function is that current user must have been authenticated using the proxy user 'SCOTT' */ CREATE OR REPLACE FUNCTION hr.MySecurityCheck RETURN BOOLEAN AS BEGIN
/* a simple check to see if current session is authenticated by the proxy user 'SCOTT' */ if (sys_context('userenv','proxy_user') = 'SCOTT') then
return TRUE;
else
return FALSE;
end IF;
END;
When enabling the secure application role, Oracle verifies that the authorized PL/SQL package is on the calling stack. This step verifies that the authorized PL/SQL package is issuing the command to enable the role. Also, when enabling the user's default roles, no checking will be performed for application roles.
Since a secure application role is a role implemented by a package, the package can do desired validation, such as ensuring that users can connect to the database through a middle tier or from a specific IP address. In this way, users are prevented from accessing data outside an application. They are forced to work within the framework of the application privileges that they have been granted.
A single user can use many applications and associated roles. However, you should ensure that the user has only the privileges associated with the running database role. Consider the following scenario:
ORDER role (for the Order application) contains the UPDATE privilege for the INVENTORY tableINVENTORY role (for the Inventory application) contains the SELECT privilege for the INVENTORY tableORDER and INVENTORY rolesIn 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. You can also use the secure application role feature to allow roles to be set based on criteria you define.
Topics in this section include:
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 simplifies privilege management. In addition to letting you control what information a user can access, it allows you to control when a user can access it. The SET ROLE statement also keeps users operating in a well-defined privilege domain. If a user obtains privileges only from roles, 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 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; the database only ensures that a user of the procedure has EXECUTE privilege on the procedure.
In cases where the database checks privileges at execution time rather than at 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 the appropriate privileges (that is, that the user has been granted the role that is being set).
Because PL/SQL performs 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.
This section shows how static and dynamic SQL affect the assignment of roles.
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 AUTHID CURRENT_USER AS 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;
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 through 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 given 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 through the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET CURRENT_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 (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 shared schema 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 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 searches for a full DN mapping. If it does not find a full DN mapping, then it searches for a partial DN. A full DN mapping thus takes precedence over a partial one.
For users authenticated by SSL to the database, or whose X.509 certificate or DN from a certificate is proxied to the database, the database uses the DN to search for the user in the directory. For password-authenticated enterprise users, the DN is obtained from the directory. That is, when a username is presented to the database for authentication (for example, JANE), the database searches internally to find if there is a local user Jane. If not, the database searches the directory for Jane, and retrieves an associated DN for Jane. Afterwards, the database refers to a mapping object as above to determine the correct shared schema to which Jane connects.
If a set of privileges should 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 that the user needs to access. These schema-independent users thus need not have a dedicated database schema on each database. Shared schemas 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. This section covers:
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.
Notes:
As you implement and test your application, you should create each necessary role. 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.
Table 11-2 lists the SQL statements permitted by the object privileges shown in Table 11-1.
This section explains how to create a new role and protect its use. This section covers:
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, from a network authentication service, or from an LDAP-based directory. 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 statements can be used to enable one database role, and disable all other roles of a user. This way, the user cannot use privileges (from a role) which were intended for use with 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.
A secure application role can incorporate additional logic to determine under what conditions the role is enabled. The conditions can reference any information available in the user session. This means information accessible through the USERENV application context namespace, such as the IP address from which the session connected, the method of authentication, and whether the user was proxied or not (that is, connected through a middle tier).
.
See Also:
|
When a user has been 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. Topics in this section include:
In general, a user's security domain should permit him to perform the task at hand, yet limit him 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 him; therefore, directly granted privileges cannot be selectively enabled and disabled, depending on his current task. By contrast, privileges granted to a role can be selectively made available to any user granted the role. The enabling of roles never affects privileges explicitly granted to the 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. 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 particular user exceeds this maximum, then errors are returned when the user attempts a connection, and the 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 her is automatically added to her list of default roles. Only subsequent modification of the user's default role list can remove newly granted roles from her 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 its password in the SET ROLE statement. If the role is not protected by a password, then it can be enabled with a simple SET ROLE statement.
The following examples illustrate how roles can be enabled and disabled.
Assume that user Morris' security domain is as follows:
PAYROLL_CLERK (password BICENTENNIAL)
ACCTS_PAY (password GARFIELD)
ACCTS_REC (identified externally)
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 through an explicit grant of another role. Morris can thus 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. Such a role is ignored, even if a it has been granted using a GRANT statement.
When OS_ROLES is set to TRUE, a user can enable as many roles as are specified by the initialization parameter MAX_ENABLED_ROLES.
| See Also:
Oracle9i Database 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 through 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.
You can 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. A grantee with this option has several expanded capabilities:
ADMIN OPTION.A grantee without the ADMIN OPTION cannot perform the above operations. Note also that this option is not valid when granting a role to another role.
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;
Not only can the user MICHAEL use all of the privileges implicit in the NEW_DBA role, but he 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. To do so, you must first revoke the privilege or role, and then grant it 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 who revokes a privilege or role need not be the user who originally granted it. Additionally, any user with the GRANT ANY ROLE privilege 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).
You can 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 shortcut. For example:
GRANT ALL ON Salary TO wallen;
System privileges and roles cannot be granted along with schema object privileges in the same GRANT statement.
The following section explains granting and revoking schema object privileges. It includes:
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, the schema object privileges in question, orGRANT ANY OBJECT PRIVILEGE system privilege, which enables delegation of an owner's right to grant and revoke object privileges.The system privilege to GRANT ANY OBJECT PRIVILEGE facilitates certain provisioning and configuration tasks covering multiple schemas. Actions taken using this privilege are performed as if the owner of the object had done it, though the audit record indicates the real user.
A user possessing the system privilege named GRANT ANY OBJECT PRIVILEGE is allowed to grant any object privilege to another user. If the user making the grant already possesses the specified object privilege with the GRANT OPTION, the grant is performed in the usual way. If the user making the grant did not already possess the specific object privilege being granted, the grant is done as if the owner of the object performed it.
As with other system privileges, GRANT ANY OBJECT PRIVILEGE can only be granted by a user possessing it WITH ADMIN OPTION.
Schema object privileges can be revoked using the SQL command REVOKE. For example, the original grantor can revoke the SELECT and INSERT privileges on the EMP_TAB table from the users JWARD and TSMITH by entering:
REVOKE SELECT, INSERT ON Emp_tab FROM jward, tsmith;
For the table DEPT_TAB, a grantor could also revoke all privileges that he or she granted to the role HUMAN_RESOURCES by entering the following statement:
REVOKE ALL ON Dept_tab FROM human_resources;
The statement is valid even if only one privilege was granted. Note that 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 himself.
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, view, or synonym, 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;
When the CASCADE CONSTRAINTS option is specified, any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped.
To revoke a schema object privilege, the revoker must normally be the original grantor of the object privilege being revoked. However, a different revoker with the system privilege GRANT ANY OBJECT PRIVILEGE can also succeed, assuming the object privilege being revoked was granted by the original owner of the object. If the object privilege being revoked was granted by someone else, it can be revoked only by that entity, because the system privilege GRANT ANY OBJECT PRIVILEGE only makes it possible to grant/revoke as the original owner.
A user possessing the GRANT ANY OBJECT PRIVILEGE system privilege can revoke any specified object privilege granted by the owner, or on behalf of the owner, such as a grant made by some user holding the GRANT ANY OBJECT PRIVILEGE. However, the privileged user is not allowed to revoke grants made by other arbitrary users.
Grants that exercise the GRANT ANY OBJECT PRIVILEGE system privilege appear to be executed by the object owner. If a grantor performs an object privilege grant using the GRANT ANY OBJECT PRIVILEGE and this system privilege is later revoked, the grantor will not be able to revoke the granted object privilege. It can, however, still be revoked by the owner or by other users holding the GRANT ANY OBJECT PRIVILEGE.
The normal SQL REVOKE syntax specifies the grantee of the privilege to be revoked but not the granter, as this is always assumed to be the user executing the REVOKE. With the introduction of the GRANT ANY OBJECT PRIVILEGE system privilege, the implied granter could be either the user executing the REVOKE or the owner of the object. In a situation where the object privilege has been granted by both the owner and the user executing the REVOKE, we will only revoke the object privilege granted by the user issuing the REVOKE. For example, assume that a user SCOTT possessing the GRANT ANY OBJECT PRIVILEGE system privilege attempts to revoke an object privilege from another user. If SCOTT previously granted this same object privilege, the grant by SCOTT will be revoked. If the owner granted the object privilege, but SCOTT did not, then the object privilege from the owner is revoked. Otherwise, the REVOKE will not be allowed.
Revoking a schema object privilege can have several 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, 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.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 that she 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, the foreign key constraint on the DEPTNO column of the EMP_TAB table is dropped in the same operation.GRANT OPTION are 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 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.
This section explains granting and revoking from the user group PUBLIC. It includes:
All unnecessary privileges, grants, and roles should be revoked from PUBLIC. Any database user can exercise privileges that are granted to PUBLIC. Such privileges include EXECUTE on various PL/SQL packages. These packages may allow minimally privileged users to access and execute packages that they may not have permit to access directly. The more potentially security vulnerable packages include:
These packages are useful to the applications that need them and warrant proper configuration and usage, but they may not be suitable or required for other applications. If necessary, revoke the package from PUBLIC and database users.
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, functions, and packages in the database 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.| See Also:
"Listing Privilege and Role Information" in the Oracle9i Database Administrator's Guide |
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|