5.4 The DBA Role The role that literally has the "keys to the kingdom" is the DBA role. With few exceptions, this role will allow the granted user to do almost anything he wants within SQL*Plus or the Server Manager Utility. Well, almost anything. Unless the user has also been placed in the system group , which enables DBA access and the ability to CONNECT INTERNAL or CONNECT / AS SYSDBA, he will not be able to start up or shut down the database, or even completely destroy it by issuing a CREATE DATABASE statement on an existing database. However, he will be able to do tremendous damage to a database by adding or removing tablespaces or other objects either maliciously or unintentionally. 5.4.1 System Privileges for the DBA Role DBA role system privileges are shown in Table 5.3. There are 77 of these privileges in Oracle7, and 89 in Oracle8. The 12 new privileges in Oracle8 encompass actions with directory, library, and type. | A user who has been granted the DBA role has ADMIN OPTION and can therefore pass any of the associated privileges to other users with or without ADMIN OPTION if he chooses. | | Table 5.3. DBA Role System Privileges Privilege | ALTER ANY CLUSTER | ALTER ANY INDEX | ALTER ANY LIBRARY (new in Oracle8) | ALTER ANY PROCEDURE | ALTER ANY ROLE | ALTER ANY SEQUENCE | ALTER ANY SNAPSHOT | ALTER ANY TABLE | ALTER ANY TRIGGER | ALTER ANY TYPE (new in Oracle8) | ALTER DATABASE | ALTER PROFILE | ALTER RESOURCE COST | ALTER ROLLBACK SEGMENT | ALTER SESSION | ALTER SYSTEM | ALTER TABLESPACE | ALTER USER | ANALYZE ANY | AUDIT ANY | AUDIT SYSTEM | BACKUP ANY TABLE | BECOME USER | COMMENT ANY TABLE | CREATE ANY CLUSTER | CREATE ANY DIRECTORY (new in Oracle8) | CREATE ANY INDEX | CREATE ANY LIBRARY (new in Oracle8) | CREATE ANY PROCEDURE | CREATE ANY SEQUENCE | CREATE ANY SNAPSHOT | CREATE ANY SYNONYM | CREATE ANY TABLE | CREATE ANY TRIGGER | CREATE ANY TYPE (new in Oracle8) | CREATE ANY VIEW | CREATE CLUSTER | CREATE DATABASE LINK | CREATE LIBRARY (new in Oracle8) | CREATE PROCEDURE | CREATE PROFILE | CREATE PUBLIC DATABASE LINK | CREATE PUBLIC SYNONYM | CREATE ROLE | CREATE ROLLBACK SEGMENT | CREATE SEQUENCE | CREATE SESSION | CREATE SNAPSHOT | CREATE SYNONYM | CREATE TABLE | CREATE TABLESPACE | CREATE TRIGGER | CREATE TYPE (new in Oracle8) | CREATE USER | CREATE VIEW | DELETE ANY TABLE | DROP ANY CLUSTER | DROP ANY DIRECTORY (new in Oracle8) | DROP ANY INDEX | DROP ANY LIBRARY (new in Oracle8) | DROP ANY PROCEDURE | DROP ANY ROLE | DROP ANY SEQUENCE | DROP ANY SNAPSHOT | DROP ANY SYNONYM | DROP ANY TABLE | DROP ANY TRIGGER | DROP ANY TYPE (new in Oracle8) | DROP ANY VIEW | DROP PROFILE | DROP PUBLIC DATABASE LINK | DROP PUBLIC SYNONYM | DROP ROLLBACK SEGMENT | DROP TABLESPACE | DROP USER | EXECUTE ANY LIBRARY (new in Oracle8) | EXECUTE ANY PROCEDURE | EXECUTE ANY TYPE (new in Oracle8) | FORCE ANY TRANSACTION | FORCE TRANSACTION | GRANT ANY PRIVILEGE | GRANT ANY ROLE | INSERT ANY TABLE | LOCK ANY TABLE | MANAGE TABLESPACE | RESTRICTED SESSION | SELECT ANY SEQUENCE | SELECT ANY TABLE | UPDATE ANY TABLE | As you can see, the DBA role has many system privileges attached to it, and many of these privileges include the ANY qualifier for example, the CREATE ANY TABLE system privilege. The ANY qualifier allows just that anything. The user with an ANY privilege can create the object under any schema or userid . Most of these privileges are rather self-explanatory, except, perhaps, for BECOME USER. BECOME USER is there to allow a privileged user to perform full and user exports and imports. The user doing so must "become the user being exported or imported," and that is what the BECOME USER allows. The privilege does not allow a user to log in as another user without knowing that user's password. 5.4.2 Who Gets the DBA Role? The DBA role conveys considerable privileges essentially all of them. For that reason, this role (and most of the associated privileges) should never be given to any user other than a person who is to have full authority within the database. Restrictions on granting the DBA role should be strictly enforced. There is never any reason to grant developers the DBA role, even though many of them tend to ask for that privilege. In all cases, instruct those who request the DBA role to list the specific privileges needed for their particular application development, along with an explanation of why each individual privilege is needed. The DBA and appropriate managers can then arrange to meet the request ( assuming that the needs are determined to be appropriate and justified). Often, a developer will demand quite forcibly the DBA role because he has not properly identified what specific privileges are really needed for his application. Why is it so risky to grant the DBA role to a developer? When a developer is granted the DBA role and then moves his application into production, will you want to grant the production account the DBA role? Of course not. The potential for compromise of a production system through the DBA role would be far too great. Therefore, imposing the "no DBA role for non-DBAs" rule in development will force the developer to do the necessary up-front work to ensure that only the truly necessary privileges for the application move to production. All too often, the development application is moved into production without a complete examination of the roles assigned to users. The users who have been created in development, for whatever reason, get carried along in an import and are not removed. | We recommend that you carefully examine every user who exists in your development database, and his or her roles and privileges, before and after that database is migrated to production. | | |