0598-0601

Previous Table of Contents Next

Page 598

SELECT enables the user to view rows of information within a database object.
UPDATE enables the user to modify existing rows within a database object.
ALL gives the user all the previous privileges on a database object.

It is possible to combine multiple database privileges within a single grant option. For example:

 % sqlplus ashley Password: ........ SQL> grant select, insert, update on treat_table to frisko; Grant succeeded. 

These privileges are valid only for the lifetime of a single database object. Even if a database object is re-created with the same object name , the privileges are not recovered. For example:

 % sqlplus rhett Password: ........ SQL> create table bonnie   2>    (   3>    pony_column varchar2(15)   4>    )   5> tablespace users; Table created. SQL> grant select, insert, update on bonnie to scarlett; Grant succeeded. SQL> connect scarlett Enter password: ...... SQL> select count(*) from rhett.bonnie;   COUNT(*) ----------          0 SQL> connect rhett Enter password: ........ Connected. SQL> drop table bonnie; Table dropped. SQL> create table bonnie   2>    (   3>    pony_column varchar2(15)   4>    )   5> tablespace users; Table created. SQL> connect scarlett Enter password: ...... Connected. 

Page 599

 SQL> select count(*) from rhett.bonnie; Error at line 1: ORA-00942: table or view does not exist 

RHETT owns the table BONNIE, and he has granted SCARLETT access to the table. If BONNIE is dropped by RHETT and re-created with the same object name, SCARLETT no longer has access to the table. This is true until RHETT makes the grant again. If there were multiple grants, each grant must be made for each user on each database object. This can be quite cumbersome.

The WITH GRANT OPTION Option

In many environments, it is beneficial to have users other than the DBA perform grants. For example, the DBA might want to allow a project leader to grant rights on database objects to people working on his project. Thus, the DBA has to do the grants only once; then it falls to the project leader to make further grants as necessary.

Oracle provides a mechanism for doing this: GRANT OPTION of the grant SQL command. With it, a user can issue grant commands just as though he were the actual owner of the database object. For example:

 % sqlplus aimee Password: ........ Connected. SQL> grant select on order to jason with grant option; Grant succeeded. SQL> connect jason Enter password: ........ Connected. SQL> grant select on aimee.order to lucinda; Grant succeeded. 

A database user who has received a grant with the ALL privilege does not receive GRANT OPTION automatically; it must be explicitly granted. As specified with object privileges, GRANT OPTION remains in effect only until an object is dropped.

NOTE

SCARLETT owns a view of another database view called GEORGIA. This view is based on a table owned by another user, RHETT, and is called CHARLESTON. SCARLETT must have WITH GRANT OPTION on GEORGIA. If this is not done, grants of this view to other users will not work properly.

Page 600

Resolving Object Synonyms

A synonym is simply a designation for a database object that enables it to be referenced by another name. There are two types of these synonyms: private and public. A private synonym is a synonym created by the user that only he uses; no one other than the user who created the synonym can use it. A public synonym is accessible to all users in the database.

Suppose, for example, that a user, TAL, has a table named HOCKEY for which two synonyms exist ”ICE, a private synonym, and SPORT, a public synonym. This gives five distinct methods for accessing this particular table:

• As TAL, using the actual table name, HOCKEY.
• As TAL, using the private synonym name, ICE.
• As TAL, using the public synonym name, SPORT.
• As another user, using the database object owner and object name, TAL.HOCKEY.
• As another user, using the public synonym name, SPORT.

This example assumes that the appropriate grants have been made on the HOCKEY table to permit access. In dealing with synonyms, it is important to understand the order in which the database resolves naming. This is important when you test programs for which there is a global table and a local table. Consider the following SQL statement:

 select * from emp; 

CAUTION

Use caution when creating public synonyms. Although this might seem harmless in a single-schema database environment, consider a database with multiple schemas. In a multischema environment, naming conflicts can exist between synonyms in one schema with tables in another schema having the same name.

When the database attempts to resolve a statement, it first checks whether a database object ”such as a table, view, or database link ”exists and is owned by the current database user. If the database finds a match, it stops. If a match does not exist, the database checks for a private synonym that will direct it to a specific database object. If no private synonym exists, the database checks for a public synonym that will point it toward an existing database object. If no resolution is found, if the database objects referenced by the synonyms do not exist, or if the user has no privileges on the object in question, an error condition occurs.

System Security

Object privileges deal with what a user can do to database objects, and system privileges deal with what actions a user can perform against the database itself. The actions include capabilities such as connecting to the database, creating database tables, and dropping an entire tablespace

Page 601

(with all the database objects in it). The functionality of Oracle7 makes the system privileges far more scalable than in Oracle6.

Under Oracle6, the Oracle RDBMS resembled UNIX in its overall security scheme. UNIX maintains that an account is either the root user or a regular user. Admittedly, UNIX has evolved to enable a greater deal of scalability by using things such as access control lists (ACLs) and root set userid (suid) programs. Oracle6 is set up so that all users are either the DBA or not the DBA. With the release of Oracle7, Oracle moves away from this methodology. It is now possible to grant specific privileges to non-DBA users, thereby enabling them to perform certain applications without giving them full DBA access.

Defined System Privileges

In Oracle6, three system privileges are available. More than 80 system privileges are available in Oracle7 and Oracle8. The following is a partial list of the database system privileges. The information comes from the Oracle7 Server Administrator's Guide, an excellent reference that describes the capabilities of each privilege:

 ALTER DATABASE ALTER PROFILE ALTER RESOURCE COST ALTER ROLLBACK SEGMENT ALTER SESSION ALTER SYSTEM ALTER TABLESPACE ALTER USER AUDIT SYSTEM BECOME USER CREATE CLUSTER CREATE DATABASE LINK CREATE PROCEDURE CREATE PROFILE CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM CREATE ROLE CREATE ROLLBACK SEGMENT CREATE SESSION CREATE SEQUENCE CREATE SNAPSHOT 

Previous Table of Contents Next