Best practice for a DBA to login to a database

Question

I've read that a DBA never should login to a database as system user. That makes me wondering... how should a DBA login to a database? Should he/she create a DBA account and use it for login purposes? But I think that would be the same as login as system. Or should he login to each schema as the owner of that schema ?

Answer 1

Log in as sys, create an account for yourself and grant that account just the privileges you require. Give yourself a reasonable password. "Password" is not a good password.

Here are the top ten most used passwords and even if it is your intention that only you log on as DBA these are still not good passwords:

• password
• 123456
• 12345678
• 1234
• qwerty
• 12345
• dragon
• pussy
• baseball
• football

Here is a script I have used on Oracle 9i and 11g that grants more than I've ever needed. Further pruning of the permissions on a production system would be a good exercise in security.

Edit @ miracle173 asks what is the difference between the script below and just granting DBA privilege? The most important difference is that by granting individual privileges you can remove what is unnecessary. If you grant a DBA role then you cannot pick and choose unless you edit the role which is not advisable. The requirements change from when you are developing a database where you could need everything to a database that has been deployed to production where you want to have the least privileges necessary. Best practice is exactly what Leigh Riffel answered: do what you need to do with the least privileges.

Edit @miracle173 correctly points out that SYSTEM tablespace is not recommended for a user. I have changed it to USERS in the script and on my primary development database.

Edit @miracle173 also makes some good points on the distinction in privileges between a DBA who maintains and a developer. I do DBA and application development for the databases I work on so you could break down the script according to what you are doing. Practices and standards vary by industry, organization and habit so what you need to work with can change from instance to instance. It's common in my shop for the development database to have more privileges for admins and developers than production.

CREATE USER ADMIN
  IDENTIFIED BY <choose a good password>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

A DBA might need these depending on what Oracle options are in use:

  GRANT ADMINISTER DATABASE TRIGGER TO ADMIN;
  ALTER USER ADMIN DEFAULT ROLE ALL;
  GRANT ALTER ANY CLUSTER TO ADMIN;
  GRANT ALTER ANY DIMENSION TO ADMIN;
  GRANT ALTER ANY INDEX TO ADMIN;
  GRANT ALTER ANY INDEXTYPE TO ADMIN;
  GRANT ALTER ANY LIBRARY TO ADMIN;
  GRANT ALTER ANY MATERIALIZED VIEW TO ADMIN;
  GRANT ALTER ANY OUTLINE TO ADMIN;
  GRANT ALTER ANY PROCEDURE TO ADMIN;
  GRANT ALTER ANY ROLE TO ADMIN;
  GRANT ALTER ANY SEQUENCE TO ADMIN;
  GRANT ALTER ANY TABLE TO ADMIN WITH ADMIN OPTION;
  GRANT ALTER ANY TRIGGER TO ADMIN;
  GRANT ALTER ANY TYPE TO ADMIN;
  GRANT ALTER DATABASE TO ADMIN;
  GRANT ALTER PROFILE TO ADMIN;
  GRANT ALTER RESOURCE COST TO ADMIN;
  GRANT ALTER ROLLBACK SEGMENT TO ADMIN;
  GRANT ALTER SESSION TO ADMIN;
  GRANT ALTER SYSTEM TO ADMIN;
  GRANT ALTER TABLESPACE TO ADMIN;
  GRANT ALTER USER TO ADMIN;
  GRANT ANALYZE ANY TO ADMIN;
  GRANT AUDIT ANY TO ADMIN;
  GRANT AUDIT SYSTEM TO ADMIN;
  GRANT AUTHENTICATEDUSER TO ADMIN WITH ADMIN OPTION;
  GRANT BACKUP ANY TABLE TO ADMIN;
  GRANT BECOME USER TO ADMIN;
  GRANT COMMENT ANY TABLE TO ADMIN;
  GRANT CREATE ANY CLUSTER TO ADMIN;
  GRANT CREATE ANY CONTEXT TO ADMIN;
  GRANT CREATE ANY DIMENSION TO ADMIN;
  GRANT CREATE ANY DIRECTORY TO ADMIN;
  GRANT CREATE ANY INDEX TO ADMIN;
  GRANT CREATE ANY INDEXTYPE TO ADMIN;
  GRANT CREATE ANY LIBRARY TO ADMIN;
  GRANT CREATE ANY MATERIALIZED VIEW TO ADMIN;
  GRANT CREATE ANY OPERATOR TO ADMIN;
  GRANT CREATE ANY OUTLINE TO ADMIN;
  GRANT CREATE ANY PROCEDURE TO ADMIN;
  GRANT CREATE ANY SEQUENCE TO ADMIN;
  GRANT CREATE ANY SYNONYM TO ADMIN;
  GRANT CREATE ANY TABLE TO ADMIN;
  GRANT CREATE ANY TRIGGER TO ADMIN;
  GRANT CREATE ANY TYPE TO ADMIN;
  GRANT CREATE ANY VIEW TO ADMIN;
  GRANT CREATE CLUSTER TO ADMIN;
  GRANT CREATE DATABASE LINK TO ADMIN;
  GRANT CREATE DIMENSION TO ADMIN;
  GRANT CREATE INDEXTYPE TO ADMIN;
  GRANT CREATE LIBRARY TO ADMIN;
  GRANT CREATE MATERIALIZED VIEW TO ADMIN;
  GRANT CREATE OPERATOR TO ADMIN;
  GRANT CREATE PROCEDURE TO ADMIN;
  GRANT CREATE PROFILE TO ADMIN;
  GRANT CREATE PUBLIC DATABASE LINK TO ADMIN;
  GRANT CREATE PUBLIC SYNONYM TO ADMIN;
  GRANT CREATE ROLE TO ADMIN;
  GRANT CREATE ROLLBACK SEGMENT TO ADMIN;
  GRANT CREATE SEQUENCE TO ADMIN;
  GRANT CREATE SESSION TO ADMIN;
  GRANT CREATE SYNONYM TO ADMIN;
  GRANT CREATE TABLE TO ADMIN;
  GRANT CREATE TABLESPACE TO ADMIN;
  GRANT CREATE TRIGGER TO ADMIN;
  GRANT CREATE TYPE TO ADMIN;
  GRANT CREATE USER TO ADMIN;
  GRANT CREATE VIEW TO ADMIN;
  GRANT DBA TO ADMIN WITH ADMIN OPTION;
  GRANT DEBUG ANY PROCEDURE TO ADMIN;
  GRANT DEBUG CONNECT SESSION TO ADMIN;
  GRANT DELETE ANY TABLE TO ADMIN;
  GRANT DELETE, SELECT ON SYSTEM.MVIEW_FILTERINSTANCE TO ADMIN WITH GRANT OPTION;
  GRANT DELETE_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT DROP ANY CLUSTER TO ADMIN;
  GRANT DROP ANY CONTEXT TO ADMIN;
  GRANT DROP ANY DIMENSION TO ADMIN;
  GRANT DROP ANY DIRECTORY TO ADMIN;
  GRANT DROP ANY INDEX TO ADMIN;
  GRANT DROP ANY INDEXTYPE TO ADMIN;
  GRANT DROP ANY LIBRARY TO ADMIN;
  GRANT DROP ANY MATERIALIZED VIEW TO ADMIN;
  GRANT DROP ANY OPERATOR TO ADMIN;
  GRANT DROP ANY OUTLINE TO ADMIN;
  GRANT DROP ANY PROCEDURE TO ADMIN;
  GRANT DROP ANY ROLE TO ADMIN;
  GRANT DROP ANY SEQUENCE TO ADMIN;
  GRANT DROP ANY SYNONYM TO ADMIN;
  GRANT DROP ANY TABLE TO ADMIN;
  GRANT DROP ANY TRIGGER TO ADMIN;
  GRANT DROP ANY TYPE TO ADMIN;
  GRANT DROP ANY VIEW TO ADMIN;
  GRANT DROP PROFILE TO ADMIN;
  GRANT DROP PUBLIC DATABASE LINK TO ADMIN;
  GRANT DROP PUBLIC SYNONYM TO ADMIN;
  GRANT DROP ROLLBACK SEGMENT TO ADMIN;
  GRANT DROP TABLESPACE TO ADMIN;
  GRANT DROP USER TO ADMIN;
  GRANT EXECUTE ANY INDEXTYPE TO ADMIN;
  GRANT EXECUTE ANY LIBRARY TO ADMIN;
  GRANT EXECUTE ANY OPERATOR TO ADMIN;
  GRANT EXECUTE ANY PROCEDURE TO ADMIN;
  GRANT EXECUTE ANY TYPE TO ADMIN;
  GRANT EXECUTE_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT EXP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT FLASHBACK ANY TABLE TO ADMIN;
  GRANT FORCE ANY TRANSACTION TO ADMIN;
  GRANT FORCE TRANSACTION TO ADMIN;
  GRANT GATHER_SYSTEM_STATISTICS TO ADMIN WITH ADMIN OPTION;
  GRANT GLOBAL QUERY REWRITE TO ADMIN;
  GRANT GRANT ANY OBJECT PRIVILEGE TO ADMIN;
  GRANT GRANT ANY PRIVILEGE TO ADMIN;
  GRANT GRANT ANY ROLE TO ADMIN;
  GRANT HS_ADMIN_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT IMP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT INSERT ANY TABLE TO ADMIN;
  GRANT LOCK ANY TABLE TO ADMIN;
  GRANT LOGSTDBY_ADMINISTRATOR TO ADMIN WITH ADMIN OPTION;
  GRANT MANAGE TABLESPACE TO ADMIN;
  GRANT OEM_MONITOR TO ADMIN WITH ADMIN OPTION;
  GRANT OLAP_USER TO ADMIN WITH ADMIN OPTION;
  GRANT ON COMMIT REFRESH TO ADMIN;
  GRANT QUERY REWRITE TO ADMIN;
  GRANT QUEUE_USER_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT RECOVERY_CATALOG_OWNER TO ADMIN WITH ADMIN OPTION;
  GRANT RESOURCE TO ADMIN WITH ADMIN OPTION;
  GRANT RESTRICTED SESSION TO ADMIN;
  GRANT RESUMABLE TO ADMIN;
  GRANT SELECT ANY DICTIONARY TO ADMIN;
  GRANT SELECT ANY SEQUENCE TO ADMIN;
  GRANT SELECT ANY TABLE TO ADMIN WITH ADMIN OPTION;
  GRANT SELECT ON SYSTEM.DEF$_AQCALL TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_CALLDEST TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_DESTINATION TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_ERROR TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_LOB TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.REPCAT$_REPPROP TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.REPCAT$_REPSCHEMA TO ADMIN WITH GRANT OPTION;
  GRANT SELECT_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT UNDER ANY TABLE TO ADMIN;
  GRANT UNDER ANY TYPE TO ADMIN;
  GRANT UNDER ANY VIEW TO ADMIN;
  GRANT UNLIMITED TABLESPACE TO ADMIN WITH ADMIN OPTION;
  GRANT UPDATE ANY TABLE TO ADMIN;
 BEGIN
 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
  (GRANTEE_NAME   => 'ADMIN', 
   PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
   ADMIN_OPTION   => FALSE);
END;
/

A developer might need these privileges depending on what you are working with

 GRANT OLAP_DBA TO ADMIN WITH ADMIN OPTION;
  GRANT AUTHENTICATEDUSER TO ADMIN WITH ADMIN OPTION;
  GRANT RESOURCE TO ADMIN WITH ADMIN OPTION;
  //java privileges
  GRANT EJBCLIENT TO ADMIN WITH ADMIN OPTION;
  GRANT JAVAUSERPRIV TO ADMIN WITH ADMIN OPTION;
  GRANT JAVA_DEPLOY TO ADMIN WITH ADMIN OPTION;
  GRANT JAVADEBUGPRIV TO ADMIN WITH ADMIN OPTION;
  GRANT JAVAIDPRIV TO ADMIN WITH ADMIN OPTION;

      -- 4 Java Privileges for ADMIN 
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'createClassLoader'
        ,permission_action => ''
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.io.FilePermission'
        ,permission_name   => '<<ALL FILES>>'
        ,permission_action => 'execute'
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'writeFileDescriptor'
        ,permission_action => '*'
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'readFileDescriptor'
        ,permission_action => '*'
        ,key               => KEYNUM
        );
    END;
    /
  --advanced queue
  GRANT AQ_ADMINISTRATOR_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT AQ_USER_ROLE TO ADMIN WITH ADMIN OPTION;
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'MANAGE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'ENQUEUE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'DEQUEUE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
  //moving data in and out
  GRANT EXP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT IMP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;

Answer 2

In theory you should login as the user with as few privileges as possible that can still accomplish the task. In practice you can maintain a better password, save time, and have more useful auditing/logging information if you login as yourself. Here is the guideline I follow:

Only login using an account other than your own when…

1. Your own account doesn’t have sufficient privileges.
2. You are trying to track down an issue that cannot be reproduced using your own account.

Answer 3

The reason that I do not use the system user is that there is the danger that a lot of trash could be generated in the system schema: auxiliary objects that you create to use for some analysis or achieve some other dba tasks. Also it is possible that you blow up the system tablespace if you generate a lot of data in you auxiliary table. It is almost not possible to reoganize the system tablespace. An individual dba user for each database administrator avoids sharing the password with anyone and the audit trail information generated by database users can be assigned to a persons. I have a user that has granted the dba role and the connect role and a default tablespace user. For the things that cannot be done with this user I login to the host machine and connect as sys user using / as sysdba without password. I think this approach conforms to the principle of least privilege. I think this principle does not demand to have a user for each dba task.

I is possible to have different administrator types and create different database roles for this administrator types. Oracle also has such different roles. Currently we do not have such different administrator types.

Using the schema user allows you to do some specific tasks but not all dba tasks. Also the user is not assigned to a specific person. I is not necessary that a dba has the password of a schema user.