Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role DBA.
The DBA role is used to give an individual user the right to administrator an oracle database. This role is normally only granted to users who have a need to view dictionary level views and administrate other user accounts. NOTE: Great care has to be used when assigning the DBA role, because users who have it can perform almost every action in the database short of actually shutting down the system. The following instructions define how to create a user account, assign the DBA role to that user and lists the privileges granted by DBA role.
1. Logon the you Oracle database as sysdba.
mylinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 4 21:56:18 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Create a permanent tablespace for your new user.
SQL> create tablespace admin datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/admin01.dbf’ size 5m;
Tablespace created.
SQL>
3. Create a temporary tablespace for you new user.
SQL> create temporary tablespace admin_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/O
RADATA/ORCL10G/admin_temp01.dbf’ size 5m;
Tablespace created.
SQL>
4. Create you administrative user.
SQL> Create user admin01 identified by admin
2 Default tablespace admin
3 Quota unlimited on admin
4 Temporary tablespace admin_temp;
User created.
SQL>
5. Grant the role DBA to the user developer01.
SQL> grant dba to admin01;
Grant succeeded.
SQL>
6. Connect to the system as the admin01 user and perform a select from a system view.
SQL> connect admin01/admin
Connected.
SQL> select count(*) from v$session;
COUNT(*)
———-
18
SQL>
7. The PL/SQL block below will list all privileges contained in the role DBA, where the variable V_USER defines the role’s privileges to be displayed.
set serveroutput on
declare
v_ct number;
v_user varchar2(30):=’DBA’;
begin
for role in(select * from dba_role_privs where grantee=v_user)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;
for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;
for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/
34
35 36 37 38 39 40 41 42
grant XDBADMIN to DBA;
grant JAVA_ADMIN to DBA;
grant JAVA_DEPLOY to DBA;
grant WM_ADMIN_ROLE to DBA;
grant SCHEDULER_ADMIN to DBA with admin option;
grant EXP_FULL_DATABASE to DBA;
grant IMP_FULL_DATABASE to DBA;
grant DELETE_CATALOG_ROLE to DBA with admin option;
grant SELECT_CATALOG_ROLE to DBA with admin option;
grant EXECUTE_CATALOG_ROLE to DBA with admin option;
grant GATHER_SYSTEM_STATISTICS to DBA;
grant ADVISOR to DBA with admin option;
grant AUDIT ANY to DBA with admin option;
grant DROP USER to DBA with admin option;
grant RESUMABLE to DBA with admin option;
grant ALTER USER to DBA with admin option;
grant CREATE JOB to DBA with admin option;
grant ANALYZE ANY to DBA with admin option;
grant BECOME USER to DBA with admin option;
grant CREATE ROLE to DBA with admin option;
grant CREATE RULE to DBA with admin option;
grant CREATE TYPE to DBA with admin option;
grant CREATE USER to DBA with admin option;
grant CREATE VIEW to DBA with admin option;
grant ALTER SYSTEM to DBA with admin option;
grant AUDIT SYSTEM to DBA with admin option;
grant CREATE TABLE to DBA with admin option;
grant DROP PROFILE to DBA with admin option;
grant ALTER PROFILE to DBA with admin option;
grant ALTER SESSION to DBA with admin option;
grant DROP ANY ROLE to DBA with admin option;
grant DROP ANY RULE to DBA with admin option;
grant DROP ANY TYPE to DBA with admin option;
grant DROP ANY VIEW to DBA with admin option;
grant QUERY REWRITE to DBA with admin option;
grant ALTER ANY ROLE to DBA with admin option;
grant ALTER ANY RULE to DBA with admin option;
grant ALTER ANY TYPE to DBA with admin option;
grant ALTER DATABASE to DBA with admin option;
grant CREATE ANY JOB to DBA with admin option;
grant CREATE CLUSTER to DBA with admin option;
grant CREATE LIBRARY to DBA with admin option;
grant CREATE PROFILE to DBA with admin option;
grant CREATE SESSION to DBA with admin option;
grant CREATE SYNONYM to DBA with admin option;
grant CREATE TRIGGER to DBA with admin option;
grant DROP ANY INDEX to DBA with admin option;
grant DROP ANY TABLE to DBA with admin option;
grant GRANT ANY ROLE to DBA with admin option;
grant LOCK ANY TABLE to DBA with admin option;
grant MERGE ANY VIEW to DBA with admin option;
grant UNDER ANY TYPE to DBA with admin option;
grant UNDER ANY VIEW to DBA with admin option;
grant ALTER ANY INDEX to DBA with admin option;
grant ALTER ANY TABLE to DBA with admin option;
grant CREATE ANY RULE to DBA with admin option;
grant CREATE ANY TYPE to DBA with admin option;
grant CREATE ANY VIEW to DBA with admin option;
grant CREATE OPERATOR to DBA with admin option;
grant CREATE RULE SET to DBA with admin option;
grant CREATE SEQUENCE to DBA with admin option;
grant DROP TABLESPACE to DBA with admin option;
grant UNDER ANY TABLE to DBA with admin option;
grant ALTER TABLESPACE to DBA with admin option;
grant BACKUP ANY TABLE to DBA with admin option;
grant CREATE ANY INDEX to DBA with admin option;
grant CREATE ANY TABLE to DBA with admin option;
grant CREATE DIMENSION to DBA with admin option;
grant CREATE INDEXTYPE to DBA with admin option;
grant CREATE PROCEDURE to DBA with admin option;
grant DELETE ANY TABLE to DBA with admin option;
grant DROP ANY CLUSTER to DBA with admin option;
grant DROP ANY CONTEXT to DBA with admin option;
grant DROP ANY LIBRARY to DBA with admin option;
grant DROP ANY OUTLINE to DBA with admin option;
grant DROP ANY SYNONYM to DBA with admin option;
grant DROP ANY TRIGGER to DBA with admin option;
grant EXECUTE ANY RULE to DBA with admin option;
grant EXECUTE ANY TYPE to DBA with admin option;
grant INSERT ANY TABLE to DBA with admin option;
grant MANAGE ANY QUEUE to DBA with admin option;
grant MANAGE SCHEDULER to DBA with admin option;
grant SELECT ANY TABLE to DBA with admin option;
grant UPDATE ANY TABLE to DBA with admin option;
grant ALTER ANY CLUSTER to DBA with admin option;
grant ALTER ANY LIBRARY to DBA with admin option;
grant ALTER ANY OUTLINE to DBA with admin option;
grant ALTER ANY TRIGGER to DBA with admin option;
grant COMMENT ANY TABLE to DBA with admin option;
grant CREATE TABLESPACE to DBA with admin option;
grant DEQUEUE ANY QUEUE to DBA with admin option;
grant DROP ANY OPERATOR to DBA with admin option;
grant DROP ANY RULE SET to DBA with admin option;
grant DROP ANY SEQUENCE to DBA with admin option;
grant ENQUEUE ANY QUEUE to DBA with admin option;
grant EXECUTE ANY CLASS to DBA with admin option;
grant FORCE TRANSACTION to DBA with admin option;
grant MANAGE FILE GROUP to DBA with admin option;
grant MANAGE TABLESPACE to DBA with admin option;
grant ON COMMIT REFRESH to DBA with admin option;
grant ALTER ANY RULE SET to DBA with admin option;
grant ALTER ANY SEQUENCE to DBA with admin option;
grant CREATE ANY CLUSTER to DBA with admin option;
grant CREATE ANY CONTEXT to DBA with admin option;
grant CREATE ANY LIBRARY to DBA with admin option;
grant CREATE ANY OUTLINE to DBA with admin option;
grant CREATE ANY SYNONYM to DBA with admin option;
grant CREATE ANY TRIGGER to DBA with admin option;
grant DROP ANY DIMENSION to DBA with admin option;
grant DROP ANY DIRECTORY to DBA with admin option;
grant DROP ANY INDEXTYPE to DBA with admin option;
grant DROP ANY PROCEDURE to DBA with admin option;
grant RESTRICTED SESSION to DBA with admin option;
grant ALTER ANY DIMENSION to DBA with admin option;
grant ALTER ANY INDEXTYPE to DBA with admin option;
grant ALTER ANY PROCEDURE to DBA with admin option;
grant ALTER RESOURCE COST to DBA with admin option;
grant CHANGE NOTIFICATION to DBA with admin option;
grant CREATE ANY OPERATOR to DBA with admin option;
grant CREATE ANY RULE SET to DBA with admin option;
grant CREATE ANY SEQUENCE to DBA with admin option;
grant CREATE EXTERNAL JOB to DBA with admin option;
grant DEBUG ANY PROCEDURE to DBA with admin option;
grant DROP PUBLIC SYNONYM to DBA with admin option;
grant EXECUTE ANY LIBRARY to DBA with admin option;
grant EXECUTE ANY PROGRAM to DBA with admin option;
grant FLASHBACK ANY TABLE to DBA with admin option;
grant GRANT ANY PRIVILEGE to DBA with admin option;
grant READ ANY FILE GROUP to DBA with admin option;
grant SELECT ANY SEQUENCE to DBA with admin option;
grant CREATE ANY DIMENSION to DBA with admin option;
grant CREATE ANY DIRECTORY to DBA with admin option;
grant CREATE ANY INDEXTYPE to DBA with admin option;
grant CREATE ANY PROCEDURE to DBA with admin option;
grant CREATE DATABASE LINK to DBA with admin option;
grant DROP ANY SQL PROFILE to DBA with admin option;
grant EXECUTE ANY OPERATOR to DBA with admin option;
grant EXECUTE ANY RULE SET to DBA with admin option;
grant EXPORT FULL DATABASE to DBA with admin option;
grant GLOBAL QUERY REWRITE to DBA with admin option;
grant IMPORT FULL DATABASE to DBA with admin option;
grant ALTER ANY SQL PROFILE to DBA with admin option;
grant CREATE PUBLIC SYNONYM to DBA with admin option;
grant DEBUG CONNECT SESSION to DBA with admin option;
grant DROP ROLLBACK SEGMENT to DBA with admin option;
grant EXECUTE ANY INDEXTYPE to DBA with admin option;
grant EXECUTE ANY PROCEDURE to DBA with admin option;
grant FORCE ANY TRANSACTION to DBA with admin option;
grant MANAGE ANY FILE GROUP to DBA with admin option;
grant SELECT ANY DICTIONARY to DBA with admin option;
grant ALTER ROLLBACK SEGMENT to DBA with admin option;
grant ANALYZE ANY DICTIONARY to DBA with admin option;
grant CREATE ANY SQL PROFILE to DBA with admin option;
grant SELECT ANY TRANSACTION to DBA with admin option;
grant CREATE ROLLBACK SEGMENT to DBA with admin option;
grant CREATE MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER SQL TUNING SET to DBA with admin option;
grant CREATE EVALUATION CONTEXT to DBA with admin option;
grant DROP PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY MATERIALIZED VIEW to DBA with admin option;
grant GRANT ANY OBJECT PRIVILEGE to DBA with admin option;
grant ADMINISTER DATABASE TRIGGER to DBA with admin option;
grant ADMINISTER RESOURCE MANAGER to DBA with admin option;
grant ALTER ANY MATERIALIZED VIEW to DBA with admin option;
grant CREATE PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER ANY EVALUATION CONTEXT to DBA with admin option;
grant CREATE ANY MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER ANY SQL TUNING SET to DBA with admin option;
grant CREATE ANY EVALUATION CONTEXT to DBA with admin option;
grant EXECUTE ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER on SYS.MAP_OBJECT to DBA;
grant DELETE on SYS.MAP_OBJECT to DBA;
grant INSERT on SYS.MAP_OBJECT to DBA;
grant SELECT on SYS.MAP_OBJECT to DBA;
grant UPDATE on SYS.MAP_OBJECT to DBA;
grant ON COMMIT REFRESH on SYS.MAP_OBJECT to DBA;
grant QUERY REWRITE on SYS.MAP_OBJECT to DBA;
grant DEBUG on SYS.MAP_OBJECT to DBA;
grant FLASHBACK on SYS.MAP_OBJECT to DBA;
grant EXECUTE on SYS.DBMS_FLASHBACK to DBA;
grant EXECUTE on SYS.OUTLN_PKG to DBA;
grant EXECUTE on SYS.OUTLN_EDIT_PKG to DBA;
grant EXECUTE on SYS.DBMS_RESUMABLE to DBA;
grant EXECUTE on SYS.DBMS_DEFER_QUERY to DBA;
grant EXECUTE on SYS.DBMS_DEFER_SYS to DBA;
grant EXECUTE on SYS.DBMS_STORAGE_MAP to DBA;
grant UPDATE on WKSYS.WK$SYS_CONFIG to DBA;
grant EXECUTE on PORTAL.WWV_EXPORT to DBA;
grant EXECUTE on SYS.DBMS_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_INTERNAL_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_SERVER_ALERT to DBA;
grant EXECUTE on SYS.DBMS_TDB to DBA;
grant ALTER on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AW$ to DBA;
grant DEBUG on SYS.AW$ to DBA;
grant SELECT on SYS.PS$ to DBA;
grant DEBUG on SYS.PS$ to DBA;
grant SELECT on SYS.AW_PROP$ to DBA;
grant DEBUG on SYS.AW_PROP$ to DBA;
grant SELECT on SYS.AW_OBJ$ to DBA;
grant DEBUG on SYS.AW_OBJ$ to DBA;
grant EXECUTE on SYS.DBMS_SERVER_TRACE to DBA;
grant EXECUTE on SYS.DBMS_SERVICE to DBA;
grant EXECUTE on SYS.DBMS_MONITOR to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;
grant EXECUTE on SYS.DBMS_UADV_ARR to DBA;
grant EXECUTE on SYS.DBMS_UNDO_ADV to DBA;
grant EXECUTE on SYS.LOAD_UNDO_STAT to DBA;
grant EXECUTE on SYS.RESET_UNDO_STAT to DBA;
grant EXECUTE on SYS.DBMS_FEATURE_USAGE_REPORT to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_CAPTURE to DBA;
PL/SQL procedure successfully completed.
SQL>
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com