HiveBrain v1.2.0
Get Started
← Back to all entries
principleMinor

Best practice for a DBA to login to a database

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dbaoraclebest-practicesstackoverflow
practicelogindatabasefordbabest

Problem

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 ?

Solution

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 
  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 A

Code Snippets

CREATE USER ADMIN
  IDENTIFIED BY <choose a good password>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
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 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;

Context

StackExchange Database Administrators Q#28194, answer score: 5

Revisions (0)

No revisions yet.