12 c DBA権限を無効にして、業務ユーザーにどのように授権しますか?


多くのお客様を見て、業務利用者の権限はDBAです。このように設定するのが一番簡単で、最も危険です。ここでは権限を設定する方法を紹介します。
テスト環境はOracle 12 cです。
1.connect          
SQL>  select * from role_sys_privs where role='CONNECT';

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
CONNECT                        SET CONTAINER                            NO  YES
CONNECT                        CREATE SESSION                           NO  YES

2.resource          
SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
RESOURCE                       CREATE SEQUENCE                          NO  YES
RESOURCE                       CREATE TRIGGER                           NO  YES
RESOURCE                       CREATE CLUSTER                           NO  YES
RESOURCE                       CREATE PROCEDURE                         NO  YES
RESOURCE                       CREATE TYPE                              NO  YES
RESOURCE                       CREATE OPERATOR                          NO  YES
RESOURCE                       CREATE TABLE                             NO  YES
RESOURCE                       CREATE INDEXTYPE                         NO  YES
connectを見てみて、resourceの役はすべてどれらの操作をしますか?
SQL> create user roi identified by roi;

User created.

SQL> conn / as sysdba
Connected.
SQL> grant create session to roi;

Grant succeeded.

SQL> conn roi/roi
Connected.
SQL> 
SQL> select sysdate from dual;

SYSDATE
-----------------------
22-DEC-2017 09:06:48

SQL> create table tt(id int);

Table created.

SQL> create index idx_tt on tt(id);

Index created.

SQL> insert into tt values(11);
insert into tt values(11)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL> conn / as sysdba
Connected.
SQL> alter user roi quota unlimited on users;

User altered.

SQL> 

SQL> conn roi/roi
Connected.
SQL> insert into tt values(11);

1 row created.

SQL> commit;

Commit complete.

SQL> update tt set id=111 where id=11;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from tt;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> select * from tt;

        ID
----------
       111
なぜビジネスユーザーにDBA権限を与えられないのですか?1.安全面から考える2.管理面から考える
DBAキャラが持つ権限
SQL> conn / as sysdba
Connected.
SQL> select * from role_sys_privs where role='DBA';

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE PLUGGABLE DATABASE                NO  YES
DBA                            USE ANY SQL TRANSLATION PROFILE          NO  YES
DBA                            DROP ANY CUBE BUILD PROCESS              NO  YES
DBA                            CREATE CUBE                              NO  YES
DBA                            ALTER ANY CUBE DIMENSION                 NO  YES
DBA                            ALTER ANY MINING MODEL                   NO  YES
DBA                            DROP ANY MINING MODEL                    NO  YES
DBA                            DROP ANY EDITION                         NO  YES
DBA                            CHANGE NOTIFICATION                      NO  YES
DBA                            ADMINISTER ANY SQL TUNING SET            NO  YES
DBA                            ALTER ANY SQL PROFILE                    NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE RULE                              NO  YES
DBA                            EXPORT FULL DATABASE                     NO  YES
DBA                            EXECUTE ANY EVALUATION CONTEXT           NO  YES
DBA                            DEQUEUE ANY QUEUE                        NO  YES
DBA                            DROP ANY INDEXTYPE                       NO  YES
DBA                            ALTER ANY INDEXTYPE                      NO  YES
DBA                            EXECUTE ANY LIBRARY                      NO  YES
DBA                            CREATE ANY LIBRARY                       NO  YES
DBA                            CREATE ANY DIRECTORY                     NO  YES
DBA                            ALTER PROFILE                            NO  YES
DBA                            EXECUTE ANY PROCEDURE                    NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE ROLE                              NO  YES
DBA                            SELECT ANY SEQUENCE                      NO  YES
DBA                            DROP ANY INDEX                           NO  YES
DBA                            UPDATE ANY TABLE                         NO  YES
DBA                            INSERT ANY TABLE                         NO  YES
DBA                            SELECT ANY TABLE                         NO  YES
DBA                            DROP ROLLBACK SEGMENT                    NO  YES
DBA                            BECOME USER                              NO  YES
DBA                            DROP TABLESPACE                          NO  YES
DBA                            ALTER SESSION                            NO  YES
DBA                            CREATE SESSION                           NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            DROP ANY MEASURE FOLDER                  NO  YES
DBA                            SELECT ANY CUBE                          NO  YES
DBA                            ALTER ANY CUBE                           NO  YES
DBA                            CREATE ANY ASSEMBLY                      NO  YES
DBA                            ALTER ANY EDITION                        NO  YES
DBA                            ANALYZE ANY DICTIONARY                   NO  YES
DBA                            ALTER ANY RULE SET                       NO  YES
DBA                            CREATE RULE SET                          NO  YES
DBA                            DEBUG ANY PROCEDURE                      NO  YES
DBA                            CREATE DIMENSION                         NO  YES
DBA                            ALTER ANY LIBRARY                        NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            UNDER ANY TYPE                           NO  YES
DBA                            DROP ANY MATERIALIZED VIEW               NO  YES
DBA                            DROP ANY TRIGGER                         NO  YES
DBA                            ALTER ANY PROCEDURE                      NO  YES
DBA                            FORCE ANY TRANSACTION                    NO  YES
DBA                            ALTER DATABASE                           NO  YES
DBA                            DELETE ANY TABLE                         NO  YES
DBA                            ALTER ROLLBACK SEGMENT                   NO  YES
DBA                            ALTER ANY MEASURE FOLDER                 NO  YES
DBA                            SET CONTAINER                            NO  YES
DBA                            EM EXPRESS CONNECT                       NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            UPDATE ANY CUBE DIMENSION                NO  YES
DBA                            CREATE ANY CUBE BUILD PROCESS            NO  YES
DBA                            CREATE CUBE DIMENSION                    NO  YES
DBA                            ALTER ANY ASSEMBLY                       NO  YES
DBA                            CREATE ASSEMBLY                          NO  YES
DBA                            CREATE ANY EDITION                       NO  YES
DBA                            EXECUTE ANY PROGRAM                      NO  YES
DBA                            EXECUTE ANY RULE                         NO  YES
DBA                            IMPORT FULL DATABASE                     NO  YES
DBA                            EXECUTE ANY RULE SET                     NO  YES
DBA                            CREATE ANY RULE SET                      NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            FLASHBACK ANY TABLE                      NO  YES
DBA                            RESUMABLE                                NO  YES
DBA                            ADMINISTER DATABASE TRIGGER              NO  YES
DBA                            CREATE ANY OUTLINE                       NO  YES
DBA                            ALTER ANY DIMENSION                      NO  YES
DBA                            CREATE ANY DIMENSION                     NO  YES
DBA                            EXECUTE ANY OPERATOR                     NO  YES
DBA                            CREATE TYPE                              NO  YES
DBA                            CREATE TRIGGER                           NO  YES
DBA                            GRANT ANY ROLE                           NO  YES
DBA                            DROP ANY VIEW                            NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE VIEW                              NO  YES
DBA                            LOCK ANY TABLE                           NO  YES
DBA                            ALTER USER                               NO  YES
DBA                            CREATE USER                              NO  YES
DBA                            ALTER TABLESPACE                         NO  YES
DBA                            CREATE TABLESPACE                        NO  YES
DBA                            RESTRICTED SESSION                       NO  YES
DBA                            READ ANY TABLE                           NO  YES
DBA                            EXEMPT DML REDACTION POLICY              NO  YES
DBA                            UPDATE ANY CUBE BUILD PROCESS            NO  YES
DBA                            DROP ANY CUBE                            NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            INSERT ANY CUBE DIMENSION                NO  YES
DBA                            CREATE MINING MODEL                      NO  YES
DBA                            CREATE ANY JOB                           NO  YES
DBA                            CREATE JOB                               NO  YES
DBA                            CREATE ANY RULE                          NO  YES
DBA                            DROP ANY EVALUATION CONTEXT              NO  YES
DBA                            CREATE ANY EVALUATION CONTEXT            NO  YES
DBA                            CREATE EVALUATION CONTEXT                NO  YES
DBA                            GRANT ANY OBJECT PRIVILEGE               NO  YES
DBA                            SELECT ANY DICTIONARY                    NO  YES
DBA                            DROP ANY DIMENSION                       NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            UNDER ANY TABLE                          NO  YES
DBA                            CREATE INDEXTYPE                         NO  YES
DBA                            CREATE ANY OPERATOR                      NO  YES
DBA                            DROP ANY LIBRARY                         NO  YES
DBA                            ANALYZE ANY                              NO  YES
DBA                            ALTER ANY ROLE                           NO  YES
DBA                            CREATE ANY SEQUENCE                      NO  YES
DBA                            CREATE ANY INDEX                         NO  YES
DBA                            CREATE ANY TABLE                         NO  YES
DBA                            ALTER ANY CUBE BUILD PROCESS             NO  YES
DBA                            SELECT ANY CUBE BUILD PROCESS            NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            SELECT ANY MEASURE FOLDER                NO  YES
DBA                            EXEMPT DDL REDACTION POLICY              NO  YES
DBA                            CREATE ANY CREDENTIAL                    NO  YES
DBA                            CREATE ANY SQL TRANSLATION PROFILE       NO  YES
DBA                            DELETE ANY MEASURE FOLDER                NO  YES
DBA                            CREATE ANY MEASURE FOLDER                NO  YES
DBA                            SELECT ANY MINING MODEL                  NO  YES
DBA                            CREATE ANY MINING MODEL                  NO  YES
DBA                            MANAGE FILE GROUP                        NO  YES
DBA                            MANAGE SCHEDULER                         NO  YES
DBA                            ADMINISTER RESOURCE MANAGER              NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            ALTER ANY OUTLINE                        NO  YES
DBA                            DROP ANY CONTEXT                         NO  YES
DBA                            EXECUTE ANY INDEXTYPE                    NO  YES
DBA                            UNDER ANY VIEW                           NO  YES
DBA                            DROP ANY TYPE                            NO  YES
DBA                            ALTER ANY TYPE                           NO  YES
DBA                            ALTER ANY MATERIALIZED VIEW              NO  YES
DBA                            CREATE PROFILE                           NO  YES
DBA                            DROP PUBLIC DATABASE LINK                NO  YES
DBA                            ALTER ANY INDEX                          NO  YES
DBA                            CREATE CLUSTER                           NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            REDEFINE ANY TABLE                       NO  YES
DBA                            COMMENT ANY TABLE                        NO  YES
DBA                            DROP ANY TABLE                           NO  YES
DBA                            CREATE ROLLBACK SEGMENT                  NO  YES
DBA                            AUDIT SYSTEM                             NO  YES
DBA                            ALTER SYSTEM                             NO  YES
DBA                            CREATE CREDENTIAL                        NO  YES
DBA                            DROP ANY SQL TRANSLATION PROFILE         NO  YES
DBA                            SELECT ANY CUBE DIMENSION                NO  YES
DBA                            DELETE ANY CUBE DIMENSION                NO  YES
DBA                            CREATE ANY CUBE DIMENSION                NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            COMMENT ANY MINING MODEL                 NO  YES
DBA                            EXECUTE ASSEMBLY                         NO  YES
DBA                            EXECUTE ANY ASSEMBLY                     NO  YES
DBA                            MANAGE ANY FILE GROUP                    NO  YES
DBA                            EXECUTE ANY CLASS                        NO  YES
DBA                            DROP ANY RULE SET                        NO  YES
DBA                            DEBUG CONNECT SESSION                    NO  YES
DBA                            ON COMMIT REFRESH                        NO  YES
DBA                            ENQUEUE ANY QUEUE                        NO  YES
DBA                            CREATE ANY INDEXTYPE                     NO  YES
DBA                            ALTER ANY OPERATOR                       NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE ANY TYPE                          NO  YES
DBA                            DROP ANY DIRECTORY                       NO  YES
DBA                            ALTER RESOURCE COST                      NO  YES
DBA                            CREATE ANY PROCEDURE                     NO  YES
DBA                            CREATE PROCEDURE                         NO  YES
DBA                            FORCE TRANSACTION                        NO  YES
DBA                            ALTER ANY SEQUENCE                       NO  YES
DBA                            CREATE SEQUENCE                          NO  YES
DBA                            CREATE ANY VIEW                          NO  YES
DBA                            DROP PUBLIC SYNONYM                      NO  YES
DBA                            DROP ANY SYNONYM                         NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE ANY CLUSTER                       NO  YES
DBA                            BACKUP ANY TABLE                         NO  YES
DBA                            CREATE TABLE                             NO  YES
DBA                            LOGMINING                                NO  YES
DBA                            CREATE SQL TRANSLATION PROFILE           NO  YES
DBA                            ADMINISTER SQL MANAGEMENT OBJECT         NO  YES
DBA                            INSERT ANY MEASURE FOLDER                NO  YES
DBA                            UPDATE ANY CUBE                          NO  YES
DBA                            ADMINISTER SQL TUNING SET                NO  YES
DBA                            MERGE ANY VIEW                           NO  YES
DBA                            DROP ANY OUTLINE                         NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE OPERATOR                          NO  YES
DBA                            CREATE LIBRARY                           NO  YES
DBA                            GRANT ANY PRIVILEGE                      NO  YES
DBA                            DROP PROFILE                             NO  YES
DBA                            ALTER ANY TRIGGER                        NO  YES
DBA                            CREATE ANY TRIGGER                       NO  YES
DBA                            DROP ANY PROCEDURE                       NO  YES
DBA                            AUDIT ANY                                NO  YES
DBA                            DROP ANY ROLE                            NO  YES
DBA                            DROP ANY SEQUENCE                        NO  YES
DBA                            CREATE PUBLIC SYNONYM                    NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            CREATE SYNONYM                           NO  YES
DBA                            DROP ANY CLUSTER                         NO  YES
DBA                            ALTER ANY TABLE                          NO  YES
DBA                            FLASHBACK ARCHIVE ADMINISTER             NO  YES
DBA                            ALTER ANY SQL TRANSLATION PROFILE        NO  YES
DBA                            CREATE CUBE BUILD PROCESS                NO  YES
DBA                            CREATE MEASURE FOLDER                    NO  YES
DBA                            CREATE ANY CUBE                          NO  YES
DBA                            DROP ANY CUBE DIMENSION                  NO  YES
DBA                            DROP ANY ASSEMBLY                        NO  YES
DBA                            CREATE EXTERNAL JOB                      NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            READ ANY FILE GROUP                      NO  YES
DBA                            CREATE ANY SQL PROFILE                   NO  YES
DBA                            DROP ANY SQL PROFILE                     NO  YES
DBA                            SELECT ANY TRANSACTION                   NO  YES
DBA                            ADVISOR                                  NO  YES
DBA                            DROP ANY RULE                            NO  YES
DBA                            ALTER ANY RULE                           NO  YES
DBA                            ALTER ANY EVALUATION CONTEXT             NO  YES
DBA                            CREATE ANY CONTEXT                       NO  YES
DBA                            MANAGE ANY QUEUE                         NO  YES
DBA                            GLOBAL QUERY REWRITE                     NO  YES

ROLE                           PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
DBA                            QUERY REWRITE                            NO  YES
DBA                            DROP ANY OPERATOR                        NO  YES
DBA                            EXECUTE ANY TYPE                         NO  YES
DBA                            CREATE ANY MATERIALIZED VIEW             NO  YES
DBA                            CREATE MATERIALIZED VIEW                 NO  YES
DBA                            CREATE PUBLIC DATABASE LINK              NO  YES
DBA                            CREATE DATABASE LINK                     NO  YES
DBA                            CREATE ANY SYNONYM                       NO  YES
DBA                            ALTER ANY CLUSTER                        NO  YES
DBA                            DROP USER                                NO  YES
DBA                            MANAGE TABLESPACE                        NO  YES

220 rows selected.
3.        
  :
--         
--            
--      

--          
SQL> create role app;    

Role created.

--   connect,resource     app  
SQL> grant connect,resource to app;

Grant succeeded.

--app        ,           ,   insert any table
grant insert ANY TABLE to app;

-- app   roidba  
grant app to roidba;
3つの重要なビューは、ユーザー権限dbaを確認することができます。role_privs
dba_sys.privs
role_role_privs