3.8 Privileges The DBA can create a user account but, until the CREATE SESSION system privilege is granted, that user cannot even log on or connect to the database. Once a minimum privilege of CREATE SESSION is granted, the user can connect, but cannot access any objects other than those granted explicitly to him or to the public user. The public user is a special entry in the data dictionary. Privileges granted to public are automatically available to every user in the system who has been granted the CREATE SESSION privilege. Granting public access is commonly done with tables containing data that must be made generally available, or for objects where access by any user will not compromise anything within the database. A typical example of an area of information which can be made available to the public to view is a States lookup or reference table which contains state names and their respective abbreviations. There is no reason to prevent anyone from seeing the values in the States lookup table, although there would be good business reasons to prevent the general public from having INSERT, UPDATE, or DELETE privileges on this table. 3.8.1 About System and Object Privileges Privileges fall into two general categories: system privileges and object privileges. System privileges allow the user to log on to the system and create or manipulate objects. Object privileges allow the user some sort of access to the data within an object, or allow the user to execute a stored program. 3.8.1.1 System privileges Some examples of system privileges are: - CREATE SESSION
- CREATE TABLE
- ALTER SESSION
- CREATE ANY VIEW
There are more than 80 system privileges available to Oracle users. The number of privileges will vary depending on the Oracle version being used. A complete list can be found in the data dictionary table called system_privilege_map. In the above list, the CREATE SESSION privilege is the only privilege required for a user to connect to a database. In a secure system, this might be the only privilege granted to a user. In general, when considered at the lowest level, the system privileges do not convey any rights for data access. The lowest-level system privileges are the ones like the first three items in the preceding list. However, note the word ANY in the fourth item. The CREATE ANY VIEW privilege allows exactly that. Any user given this privilege may create a view within any other user's area. For example, if user ralph has the CREATE ANY VIEW privilege, he can issue the following command: SQL> CREATE VIEW mary.emp_dept 2> AS SELECT employee_name, department_num 3> FROM mary.emplyee; A system privilege only conveys a specific right. In the example, ralph has created a view that is now owned by user mary , but ralph himself may not be able to select from that view. Only the right to create was given to ralph , not the right to use. 3.8.1.2 Object privileges We've already looked at the most common object privileges: - SELECT
- INSERT
- UPDATE
- DELETE
There are only 16 object privileges in an Oracle version 8.0.4 database. A complete list of object privileges can be found in the data dictionary table, table_privilege_map. Object privileges allow a user the right to manipulate the data within the object or, in the case of programs, to execute the program. These privileges are usually referred to as grants and are object-specific. A user granted INSERT on mary 's employee table does not automatically have any privileges on any other table owned by mary or anyone else. Following are the Oracle object privileges: - ALTER
-
The structure of the table or sequence may be altered . For example, columns may be added or column data types may be changed. - AUDIT
-
Auditing may be enabled or disabled on an object. For example, auditing could be enabled to capture each time data was inserted into a table. - COMMENT
-
For each table in a database, an informational message about the table can be entered. - DELETE
-
Allows rows to be removed from the table. - GRANT
-
Allows the user receiving the grant option to grant privileges to other users. - INDEX
-
Allows the user to create an index only on the table. - INSERT
-
Allows new rows to be created in the table. - LOCK
-
Allows the user to lock an object. - RENAME
-
The table can be renamed for example, RENAME TABLE emp AS old_emp;. - SELECT
-
All data may be selected for viewing from the table. - UPDATE
-
The data in columns may be modified; this is the only privilege that can be restricted to specific columns. - REFERENCES
-
Allows the user to define foreign key integrity constraints only on the table. - EXECUTE
-
Applies only to stored procedures and functions. - CREATE
-
Allows the user to create a table. - READ
-
Allows the user to read from a directory. - WRITE
-
Allows the user to write to a directory. As we've said, the most-used object privileges are SELECT, INSERT, UPDATE, and DELETE. These apply to tables and, in some instances, views. You will note that ALL is not included in the list. If you grant ALL on a table to a user, that user is granted all of the table privileges shown in the above list. The only privilege that can be granted on a stored program is EXECUTE. If the SELECT privilege is granted, the user will be allowed to look at all of the data in the specific table. The object privileges are "all or nothing" grants. They cannot be restricted to specific rows or records. The UPDATE privilege is somewhat different, though, in that this privilege can be granted on specific columns. When you examine the GRANT command used to give system and object privileges, you should notice the striking absence of a limiting clause. You cannot grant SELECT where some condition is true. For example, you cannot attempt to grant access to the employee table WHERE manager_name = `SMITHY'. The statement will fail. The object privileges SELECT, INSERT, UPDATE, and DELETE, specifically , are whole-table privileges. The distinction between a system privilege and an object privilege becomes a little blurred in the case of the INDEX privilege. This privilege allows the user to create indexes on a specific table owned by another user. Compare this to the privilege conveyed by the CREATE ANY INDEX system privilege. With the system privilege, the user can create indexes on any table in the database. With the command: GRANT INDEX ON employee TO mary mary can only create indexes on the employee table.  | 
