0602-0605

Previous Table of Contents Next

Page 602

 CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW DROP PROFILE DROP PUBLIC DATABASE LINK DROP PUBLIC SYNONYM DROP ROLLBACK SEGMENT DROP TABLESPACE DROP USER FORCE TRANSACTION MANAGE TABLESPACE READUP RESTRICTED SESSION UNLIMITED TABLESPACE 

Like object privileges, system privileges are given to users through the grant SQL command. The following code segment demonstrates how a system privilege grant is done:

 % sqlplus system Password: ........ Connected. SQL> grant create session, alter session to anna; Grant succeeded. 

The revoke SQL command takes away system or object privileges that were given through the grant command. It is important to note that revoking a privilege does not destroy a database object. In the following example, the table remains even though the privilege to create new tables has been revoked :

 % sqlplus fred Password: ...... Connected. SQL> select * from cat; TABLE_NAME                       TABLE_TYPE ------------------------------   ---------- SPORTS                           TABLE 1 rows selected. 

Page 603

 SQL> connect system Enter password: ........ Connected. SQL> revoke create table from fred; Revoke succeeded. SQL> connect fred Enter password: ........ Connected. SQL> select * from cat; TABLE_NAME                       TABLE_TYPE ------------------------------   ---------- SPORTS                           TABLE 1 rows selected. 

The WITH ADMIN OPTION Option

WITH ADMIN OPTION is to database system privileges what WITH GRANT OPTION is to database object privileges. By making a grant WITH ADMIN OPTION, the DBA enables a user to grant the system privilege to another user. For example:

 % sqlplus system Password: ........ SQL> grant create user to helpdesk with admin option; Grant succeeded. 

ANY Privileges

The ANY privileges are a special class of privileges within the database system privileges. They are enhanced system privileges that grant the user the ability to perform specified actions without restrictions. If a user has these system privileges, he can override normal default security. Therefore, he has access to other database objects, regardless of whether an object-level grant is made. The following is a list of the ANY privileges. They are described in detail in the Oracle7 Server Administrator's Guide:

 ALTER ANY CLUSTER ALTER ANY INDEX ALTER ANY PROCEDURE ALTER ANY ROLE ALTER ANY SEQUENCE ALTER ANY SNAPSHOT ALTER ANY TABLE ALTER ANY TRIGGER BACKUP ANY TABLE 

Page 604

 COMMENT ANY TABLE CREATE ANY CLUSTER CREATE ANY INDEX CREATE ANY PROCEDURE CREATE ANY SEQUENCE CREATE ANY SNAPSHOT CREATE ANY SYNONYM CREATE ANY TABLE CREATE ANY TRIGGER CREATE ANY VIEW DELETE ANY TABLE DROP ANY CLUSTER DROP ANY INDEX DROP ANY PROCEDURE DROP ANY ROLE DROP ANY SEQUENCE DROP ANY SNAPSHOT DROP ANY SYNONYM DROP ANY TABLE DROP ANY TRIGGER DROP ANY VIEW EXECUTE ANY PROCEDURE FORCE ANY TRANSACTION GRANT ANY PRIVILEGE GRANT ANY ROLE INSERT ANY TABLE LOCK ANY TABLE SELECT ANY SEQUENCE SELECT ANY TABLE UPDATE ANY TABLE 

The DBA should be careful when granting system privileges, especially the ANY class of privileges. Some of them are not meant for public use. They put too much power in the hands of users if they are not adequately managed. Although the privileges are more scalable than under previous Oracle versions, the DBA should treat them as mini-DBA privileges when determining who should receive them.

Page 605

Consider, for example, granting DROP ANY TABLE along with many other system privileges to Fred. Suppose that another schema is created within the same database in which Fred has these privileges, he is granted SELECT access only on these new tables, and a public synonym is created for each table. Little does the owner of these tables know that Fred is capable of dropping any table in the database. One day ”inadvertently ”Fred drops the wrong table, only to realize later that the table falls under the new schema where he should have had only SELECT access. As the DBA, you now need to start making plans to restore the dropped table, its indexes, views, and any other objects associated with it. Hopefully, you have a good backup.

A final issue regarding protecting system privileges is what effect these privileges have within a secure database. In most databases, some tables contain information that should not be distributed to the general public, such as payroll information. A user with some of the ANY privileges, such as SELECT ANY or UPDATE ANY, has access to the tables even without an explicit grant.

Grants to PUBLIC

It is possible to make grants on both system and object privileges to PUBLIC. This is a special Oracle account to which all other accounts have access. Any grant made to PUBLIC is accessible by any database user. For example:

 % sqlplus system Password: ........ Connected. SQL> grant select on hr.emp_name to public; Grant succeeded. 

The DBA can use the PUBLIC account to set up a common set of grants ”such as the CREATE SESSION privilege, which permits connection to the database ”and grant them to PUBLIC. In doing this, the DBA removes the need to make explicit grants to every user when a new account is added.

TIP

The DBA can use PUBLIC to lock groups of users out of the database without having to restart the instance in RESTRICT mode. For example, he can grant CREATE SESSION to PUBLIC and make explicit CREATE SESSION grants to key users, such as IS personnel. In the event that activity needs to be done on the database, the following command can be executed from SQL*Plus or Oracle Server Manager:

 revoke create session from public; 

 grant create session to public; 

Previous Table of Contents Next