8.1 How do I…Grant system and object privileges?Problem
I want to grant system and object privileges to a user account. The system privileges will be used to enable users to create objects and other user accounts, for the capability to export the database, and for other privileges. The object privileges will be used to enable users to INSERT, UPDATE, DELETE, SELECT, drop, and manipulate objects such as tables, sequences, and indexes. How do I grant system and object privileges?
Technique
Use the GRANT command to grant both system and object privileges to a user account. Privileges can also be granted to roles. The syntax to grant privileges is
GRANT privilege TO [user, role, PUBLIC] [WITH ADMIN OPTION]
Around 100 system privileges exist in Oracle8, which can be listed by querying the SYSTEM_PRIVILEGE_MAP data dictionary view. A sample of frequently used system privileges is shown in Table 8.1. Many of the DROP privileges have corresponding ALTER and CREATE privileges, such as CREATE ANY INDEX.
System Privilege Capability to… ANALYZE ANY Analyze any table of any user account BECOME USER Connect to the database as another user without knowing their password DELETE ANY TABLE Delete records from any table of any user account DROP ANY CLUSTER Drop any cluster from any user account DROP ANY INDEX Drop any index from any user account DROP ANY PROCEDURE Drop any procedure of any user account DROP ANY ROLE Drop any role from the database (be careful not to drop default roles) DROP ANY SEQUENCE Drop any sequence from any user account DROP ANY SNAPSHOT Drop any snapshot (and snapshot log) from any user account DROP ANY SYNONYM Drop any synonym from any user account DROP ANY TABLE Drop any table from any user account (be careful not to drop data dictionary tables) DROP ANY TRIGGER Drop any trigger from any user account DROP ANY TYPE Drop any object type from any user account DROP ANY VIEW Drop any view from any user account (be careful not to drop data dictionary views) DROP PROFILE Drop any profile DROP PUBLIC DATABASE LINK Drop any public database link DROP PUBLIC SYNONYM Drop any public synonym DROP ROLLBACK SEGMENT Drop any rollback segment DROP TABLESPACE Drop any tablespace (be VERY careful when giving this privilege out) DROP USER Drop any user EXECUTE ANY PROCEDURE Execute any procedure of any user account EXECUTE ANY TYPE Execute any object type GRANT ANY PRIVILEGE Grant any system privilege to any user, including DBA GRANT ANY ROLE Grant any role to any user account INSERT ANY TABLE Insert records into any table of any user account LOCK ANY TABLE Explicitly lock any table of any user account (having more users that have this privilege increases the chances of lock contention) MANAGE TABLESPACE Take tablespaces online/offline, and do tablespace hot backups SELECT ANY SEQUENCE Increment any sequence of any user account, or see the current value SELECT ANY TABLE Select data from any table of any user account SYSDBA Have full DBA privileges, which includes all system privileges previously listed SYSOPER Have instance capabilities such as database startup and shutdown Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP8_1.SQL, shown in Figure 8.1, creates the sample user accounts and roles used throughout this How-To.
The first statement creates the TERRI user account with a CREATE USER statement. The second statement creates the ROOFER role and the third statement creates the DEPT8 table. Run the file to create the sample objects.
SQL> START CHP8_1.sql
User created.
Role created.
Table created.
2. Run CHP8_2.SQL to grant privileges to the TERRI user account, as shown in Figure 8.2.
The first GRANT statement grants all object privileges on the DEPT8 table to the TERRI user account. This includes SELECT, INSERT, UPDATE, and DELETE. The second GRANT statement grants the CONNECT and RESOURCE default roles, along with the ROOFER role, to the TERRI user account. CONNECT is really the combination of eight system privileges: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW. RESOURCE is the combination of five system privileges: CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, and CREATE TRIGGER. Granting the CONNECT and RESOURCE roles is a simpler method than granting five or eight separate privileges.
3. System and object privileges can be granted to individual user accounts or to roles. Another option is to grant the privilege to PUBLIC. By doing so, every user account in the database has that privilege. Run the CHP8_3.SQL script, as shown in Figure 8.3, which grants object privileges for the DEPT8 table to PUBLIC.
CHP8_3.SQL grants the SELECT, INSERT, UPDATE, and DELETE object privileges on the DEPT8 table to PUBLIC. All user accounts will be able to modify data, regardless of any other role or privilege they might have been granted. Unless a PUBLIC synonym is created for the DEPT8 table, all users will have to reference it as WAITE.DEPT8.
4. By using the WITH ADMIN OPTION clause of the GRANT command, you pass along the capability for other user accounts to grant the privilege. Run CHP8_4.SQL, shown in Figure 8.4, which grants the capability to drop any synonym with the admin option to TERRI.
The GRANT in CHP8_4.SQL empowers the TERRI user account to issue a similar grant to any other user account in the database. By default, the GRANT command does not pass along the capability for other users to grant privileges.
How It Works
Step 1 creates the user account, role, and table used throughout this How-To. Step 2 issues two GRANT statements to give the TERRI user object and system privileges. Step 3 uses the GRANT command to create privileges to PUBLIC, for which all user accounts in the database are affected. Step 4 demonstrates the WITH ADMIN OPTION clause, which enables other user accounts to propagate the privilege to other user accounts and roles.
Comments
DBA_TAB_PRIVS contains the object privileges granted to the connected user account. DBA_ROLE_PRIVS contains roles granted to the user account and DBA_SYS_PRIVS contains system privileges granted to a user account. Also, SYSTEM_PRIVILEGE_MAP shows all available system privileges. See How-To’s 8.2, 8.3, and 8.4 for additional information on roles, which make privilege management easier.