0608-0611

Previous Table of Contents Next

Page 608

can be defragmented at the DBA's discretion. If many people have access to the schema ownership account, it is often a good idea to revoke quotas on the tablespace from the schema until such a time as needed. This depends largely on the user community that is using the account.

Figure 24.1.
Tablespace fragmenta-
tion.

By limiting the amount of access to production tablespaces, the DBA can help reduce ” although not eliminate ”tablespace fragmentation. This helps eliminate the amount of crisis mode management that a DBA must undertake.

Defining Database Roles

In versions of Oracle prior to Oracle7, explicit grants from the system and the object privilege level are the rule. At first, this was not much of a hindrance because of the size of most databases. Large databases were the exception; small databases were the rule. As databases grew in size , many DBAs began to see how cumbersome and difficult that method of access grants was. For example, in a database system with 40 database tables and views and 100 database users, over 4,000 separate grants must be processed . This is a relatively small database, so it is easy to

Page 609

see the cumulative effects of adding users and objects on larger databases systems. Likewise, if a change in privilege needs to be made or a table is re-created, all the privileges must be made again.

Most DBAs found avenues around this problem by creating SQL scripts or by writing programs that handle the cumbersome grant process. Oracle responded to this problem by providing its user community with database roles.

At the simplest level, roles are simply groups of system or object privileges that can be assigned to database users. Grants on the desired privileges, such as CREATE SESSION or SELECT ON SCOTT.EMP, are made to a role. This role is then granted to a user. The database user has all the privileges that have been assigned to the role. There is no limit to the number of people who can have the role assigned to them, and changes are replicated to all the database users by making a change in the grants to the role.

Creating Roles

Aside from defining the privileges necessary for each role and selecting appropriately descriptive names , the process of creating a role is simple. The syntax is similar to creating a database user. For example:

 % sqlplus system Password: ........ Connected. SQL> create role global_mis; Role created. 

Modifying Roles

Making changes to database roles consists of changing the database privileges ”object or system ”to which the role has access. This is done through standard SQL grant and revoke statements. For example:

 % sqlplus system Password: ........ Connected. SQL> alter role global_mis identified by universe; Role altered. 

It is not necessary to replicate the change for each database user. Once the grant or revoke is successfully performed on the role, it immediately takes effect for all the database users who have the role assigned to them. There are no special steps or cumbersome processes. In the example database, only 100 grants ”the total number of users ”must be performed, unless multiple roles are assigned to a single user. For example, if the DBA needs to change access on a table from UPDATE to SELECT, he would issue the appropriate revoke command on the role. The change takes effect on all the database users with that role. However, if the role is dropped and re-created, the role must be reassigned to each appropriate user.

Page 610

CAUTION
Although it is possible to grant roles to other roles ”which is called nesting ”there is a limit to the number of active roles that can be in effect for a single user at any one time. This is determined by the value of the INIT.ORA parameter MAX_ENABLED_ROLES. The database does not permit more than the limit set by this value to be enabled.

Deleting Roles

The DBA can delete a role from the database. This operation removes information pertaining to the role from all the users and roles currently in existence. As with revoking a system privilege, deleting a role does not affect existing database objects. For example:

 % sqlplus system Password: ........ Connected. SQL> drop role admin; Role dropped. 

Setting and Changing the Default Role

Whenever a role is granted to a user, the privileges within the role do not take effect until the role has been set as the default role. The default role tells the database that it is the role whose privileges are currently being used. The default role determines which database role the user uses when he first connects to the database. For example:

 % sqlplus system Password: ........ Connected. SQL> alter user amy default role admin; Statement processed. 
CAUTION
When roles are granted to a user, Oracle automatically sets all roles as the default role. When setting specific roles to default, keep in mind that additional roles granted to the user in the future might also have to be set as the default. Otherwise, the user will have to know how to set other roles. If a role is not the default or if the role has not been manually set by the user, it will appear that the user does not have the access that he or she expects.

Page 611

The user or the DBA can also change the current default role. At the discretion of the DBA, every role assigned to a user can be set as the default role. In this way, a user does not have to switch between roles. Instead, he can benefit from the system and object privileges of all of the active roles at a given time without being forced to change default roles each time. For example:

 % sqlplus system Password: ....... Connected. SQL> alter user logan default role all; Statement processed. 

The DBA should carefully evaluate the ramifications of setting all the roles as default roles before he institutes this option.

Password Protecting Roles

At some sites, users are grouped into different privilege levels that require a decision before they perform a certain task. Take, for example, a user in an OLTP system who has three roles ”ORD_ENTRY, MANAGER, and SUPERUSER. Each level might have different levels of privileges. MANAGER might have SELECT, INSERT, UPDATE, and DELETE capabilities on key tables that are not accessible to ORD_ENTR. MANAGER might lack certain system privileges that belong to SUPERUSER. Whatever the case, the DBA might require a password for each level.

This password helps protect the privilege levels by keeping others out of the role, and it also forces the user to know what role he is currently using. In theory, he would remember which password he most recently entered. Likewise, the password enables the DBA to keep users out of certain privilege groups by changing the password associated with the role.

Switching between roles is the same as switching between roles that have no passwords. The sole difference is that a correct password must be given before Oracle will accept the role change. For example:

 % sqlplus jordan Password: ........ Connected. SQL> set role lawyer; Enter password: ........ Statement processed. 

Defining Roles at Operating System Level

It is possible to grant roles dynamically at the operating system level. For this to take place, a few additional steps must be performed when the roles are created. The benefit is that roles are always identified at the operating system level ”in /etc/groups in UNIX, for example. They can easily be changed by someone who has privilege to make modifications ”usually the system administrator.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net