12c 禁用DBA权限，你怎么给业务用户授权

看到太多客户，使用业务用户的权限都是DBA，这样设置是最简单的，也是最危险的，这里给大家介绍一种设置权限的方法。

测试环境为Oracle 12c

1.connect 角色具有的系统权限SQL>  select * from role_sys_privs where role='CONNECT';ROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---CONNECT                        SET CONTAINER                            NO  YESCONNECT                        CREATE SESSION                           NO  YES2.resource 角色具有的系统权限SQL> select * from role_sys_privs where role='RESOURCE';ROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---RESOURCE                       CREATE SEQUENCE                          NO  YESRESOURCE                       CREATE TRIGGER                           NO  YESRESOURCE                       CREATE CLUSTER                           NO  YESRESOURCE                       CREATE PROCEDURE                         NO  YESRESOURCE                       CREATE TYPE                              NO  YESRESOURCE                       CREATE OPERATOR                          NO  YESRESOURCE                       CREATE TABLE                             NO  YESRESOURCE                       CREATE INDEXTYPE                         NO  YES

看看connect,resource角色都是做哪些操作

SQL> create user roi identified by roi;User created.SQL> conn / as sysdbaConnected.SQL> grant create session to roi;Grant succeeded.SQL> conn roi/roiConnected.SQL> SQL> select sysdate from dual;SYSDATE-----------------------22-DEC-2017 09:06:48SQL> 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 sysdbaConnected.SQL> alter user roi quota unlimited on users;User altered.SQL> SQL> conn roi/roiConnected.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 sysdbaConnected.SQL> select * from role_sys_privs where role='DBA';ROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE PLUGGABLE DATABASE                NO  YESDBA                            USE ANY SQL TRANSLATION PROFILE          NO  YESDBA                            DROP ANY CUBE BUILD PROCESS              NO  YESDBA                            CREATE CUBE                              NO  YESDBA                            ALTER ANY CUBE DIMENSION                 NO  YESDBA                            ALTER ANY MINING MODEL                   NO  YESDBA                            DROP ANY MINING MODEL                    NO  YESDBA                            DROP ANY EDITION                         NO  YESDBA                            CHANGE NOTIFICATION                      NO  YESDBA                            ADMINISTER ANY SQL TUNING SET            NO  YESDBA                            ALTER ANY SQL PROFILE                    NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE RULE                              NO  YESDBA                            EXPORT FULL DATABASE                     NO  YESDBA                            EXECUTE ANY EVALUATION CONTEXT           NO  YESDBA                            DEQUEUE ANY QUEUE                        NO  YESDBA                            DROP ANY INDEXTYPE                       NO  YESDBA                            ALTER ANY INDEXTYPE                      NO  YESDBA                            EXECUTE ANY LIBRARY                      NO  YESDBA                            CREATE ANY LIBRARY                       NO  YESDBA                            CREATE ANY DIRECTORY                     NO  YESDBA                            ALTER PROFILE                            NO  YESDBA                            EXECUTE ANY PROCEDURE                    NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE ROLE                              NO  YESDBA                            SELECT ANY SEQUENCE                      NO  YESDBA                            DROP ANY INDEX                           NO  YESDBA                            UPDATE ANY TABLE                         NO  YESDBA                            INSERT ANY TABLE                         NO  YESDBA                            SELECT ANY TABLE                         NO  YESDBA                            DROP ROLLBACK SEGMENT                    NO  YESDBA                            BECOME USER                              NO  YESDBA                            DROP TABLESPACE                          NO  YESDBA                            ALTER SESSION                            NO  YESDBA                            CREATE SESSION                           NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            DROP ANY MEASURE FOLDER                  NO  YESDBA                            SELECT ANY CUBE                          NO  YESDBA                            ALTER ANY CUBE                           NO  YESDBA                            CREATE ANY ASSEMBLY                      NO  YESDBA                            ALTER ANY EDITION                        NO  YESDBA                            ANALYZE ANY DICTIONARY                   NO  YESDBA                            ALTER ANY RULE SET                       NO  YESDBA                            CREATE RULE SET                          NO  YESDBA                            DEBUG ANY PROCEDURE                      NO  YESDBA                            CREATE DIMENSION                         NO  YESDBA                            ALTER ANY LIBRARY                        NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            UNDER ANY TYPE                           NO  YESDBA                            DROP ANY MATERIALIZED VIEW               NO  YESDBA                            DROP ANY TRIGGER                         NO  YESDBA                            ALTER ANY PROCEDURE                      NO  YESDBA                            FORCE ANY TRANSACTION                    NO  YESDBA                            ALTER DATABASE                           NO  YESDBA                            DELETE ANY TABLE                         NO  YESDBA                            ALTER ROLLBACK SEGMENT                   NO  YESDBA                            ALTER ANY MEASURE FOLDER                 NO  YESDBA                            SET CONTAINER                            NO  YESDBA                            EM EXPRESS CONNECT                       NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            UPDATE ANY CUBE DIMENSION                NO  YESDBA                            CREATE ANY CUBE BUILD PROCESS            NO  YESDBA                            CREATE CUBE DIMENSION                    NO  YESDBA                            ALTER ANY ASSEMBLY                       NO  YESDBA                            CREATE ASSEMBLY                          NO  YESDBA                            CREATE ANY EDITION                       NO  YESDBA                            EXECUTE ANY PROGRAM                      NO  YESDBA                            EXECUTE ANY RULE                         NO  YESDBA                            IMPORT FULL DATABASE                     NO  YESDBA                            EXECUTE ANY RULE SET                     NO  YESDBA                            CREATE ANY RULE SET                      NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            FLASHBACK ANY TABLE                      NO  YESDBA                            RESUMABLE                                NO  YESDBA                            ADMINISTER DATABASE TRIGGER              NO  YESDBA                            CREATE ANY OUTLINE                       NO  YESDBA                            ALTER ANY DIMENSION                      NO  YESDBA                            CREATE ANY DIMENSION                     NO  YESDBA                            EXECUTE ANY OPERATOR                     NO  YESDBA                            CREATE TYPE                              NO  YESDBA                            CREATE TRIGGER                           NO  YESDBA                            GRANT ANY ROLE                           NO  YESDBA                            DROP ANY VIEW                            NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE VIEW                              NO  YESDBA                            LOCK ANY TABLE                           NO  YESDBA                            ALTER USER                               NO  YESDBA                            CREATE USER                              NO  YESDBA                            ALTER TABLESPACE                         NO  YESDBA                            CREATE TABLESPACE                        NO  YESDBA                            RESTRICTED SESSION                       NO  YESDBA                            READ ANY TABLE                           NO  YESDBA                            EXEMPT DML REDACTION POLICY              NO  YESDBA                            UPDATE ANY CUBE BUILD PROCESS            NO  YESDBA                            DROP ANY CUBE                            NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            INSERT ANY CUBE DIMENSION                NO  YESDBA                            CREATE MINING MODEL                      NO  YESDBA                            CREATE ANY JOB                           NO  YESDBA                            CREATE JOB                               NO  YESDBA                            CREATE ANY RULE                          NO  YESDBA                            DROP ANY EVALUATION CONTEXT              NO  YESDBA                            CREATE ANY EVALUATION CONTEXT            NO  YESDBA                            CREATE EVALUATION CONTEXT                NO  YESDBA                            GRANT ANY OBJECT PRIVILEGE               NO  YESDBA                            SELECT ANY DICTIONARY                    NO  YESDBA                            DROP ANY DIMENSION                       NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            UNDER ANY TABLE                          NO  YESDBA                            CREATE INDEXTYPE                         NO  YESDBA                            CREATE ANY OPERATOR                      NO  YESDBA                            DROP ANY LIBRARY                         NO  YESDBA                            ANALYZE ANY                              NO  YESDBA                            ALTER ANY ROLE                           NO  YESDBA                            CREATE ANY SEQUENCE                      NO  YESDBA                            CREATE ANY INDEX                         NO  YESDBA                            CREATE ANY TABLE                         NO  YESDBA                            ALTER ANY CUBE BUILD PROCESS             NO  YESDBA                            SELECT ANY CUBE BUILD PROCESS            NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            SELECT ANY MEASURE FOLDER                NO  YESDBA                            EXEMPT DDL REDACTION POLICY              NO  YESDBA                            CREATE ANY CREDENTIAL                    NO  YESDBA                            CREATE ANY SQL TRANSLATION PROFILE       NO  YESDBA                            DELETE ANY MEASURE FOLDER                NO  YESDBA                            CREATE ANY MEASURE FOLDER                NO  YESDBA                            SELECT ANY MINING MODEL                  NO  YESDBA                            CREATE ANY MINING MODEL                  NO  YESDBA                            MANAGE FILE GROUP                        NO  YESDBA                            MANAGE SCHEDULER                         NO  YESDBA                            ADMINISTER RESOURCE MANAGER              NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            ALTER ANY OUTLINE                        NO  YESDBA                            DROP ANY CONTEXT                         NO  YESDBA                            EXECUTE ANY INDEXTYPE                    NO  YESDBA                            UNDER ANY VIEW                           NO  YESDBA                            DROP ANY TYPE                            NO  YESDBA                            ALTER ANY TYPE                           NO  YESDBA                            ALTER ANY MATERIALIZED VIEW              NO  YESDBA                            CREATE PROFILE                           NO  YESDBA                            DROP PUBLIC DATABASE LINK                NO  YESDBA                            ALTER ANY INDEX                          NO  YESDBA                            CREATE CLUSTER                           NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            REDEFINE ANY TABLE                       NO  YESDBA                            COMMENT ANY TABLE                        NO  YESDBA                            DROP ANY TABLE                           NO  YESDBA                            CREATE ROLLBACK SEGMENT                  NO  YESDBA                            AUDIT SYSTEM                             NO  YESDBA                            ALTER SYSTEM                             NO  YESDBA                            CREATE CREDENTIAL                        NO  YESDBA                            DROP ANY SQL TRANSLATION PROFILE         NO  YESDBA                            SELECT ANY CUBE DIMENSION                NO  YESDBA                            DELETE ANY CUBE DIMENSION                NO  YESDBA                            CREATE ANY CUBE DIMENSION                NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            COMMENT ANY MINING MODEL                 NO  YESDBA                            EXECUTE ASSEMBLY                         NO  YESDBA                            EXECUTE ANY ASSEMBLY                     NO  YESDBA                            MANAGE ANY FILE GROUP                    NO  YESDBA                            EXECUTE ANY CLASS                        NO  YESDBA                            DROP ANY RULE SET                        NO  YESDBA                            DEBUG CONNECT SESSION                    NO  YESDBA                            ON COMMIT REFRESH                        NO  YESDBA                            ENQUEUE ANY QUEUE                        NO  YESDBA                            CREATE ANY INDEXTYPE                     NO  YESDBA                            ALTER ANY OPERATOR                       NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE ANY TYPE                          NO  YESDBA                            DROP ANY DIRECTORY                       NO  YESDBA                            ALTER RESOURCE COST                      NO  YESDBA                            CREATE ANY PROCEDURE                     NO  YESDBA                            CREATE PROCEDURE                         NO  YESDBA                            FORCE TRANSACTION                        NO  YESDBA                            ALTER ANY SEQUENCE                       NO  YESDBA                            CREATE SEQUENCE                          NO  YESDBA                            CREATE ANY VIEW                          NO  YESDBA                            DROP PUBLIC SYNONYM                      NO  YESDBA                            DROP ANY SYNONYM                         NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE ANY CLUSTER                       NO  YESDBA                            BACKUP ANY TABLE                         NO  YESDBA                            CREATE TABLE                             NO  YESDBA                            LOGMINING                                NO  YESDBA                            CREATE SQL TRANSLATION PROFILE           NO  YESDBA                            ADMINISTER SQL MANAGEMENT OBJECT         NO  YESDBA                            INSERT ANY MEASURE FOLDER                NO  YESDBA                            UPDATE ANY CUBE                          NO  YESDBA                            ADMINISTER SQL TUNING SET                NO  YESDBA                            MERGE ANY VIEW                           NO  YESDBA                            DROP ANY OUTLINE                         NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE OPERATOR                          NO  YESDBA                            CREATE LIBRARY                           NO  YESDBA                            GRANT ANY PRIVILEGE                      NO  YESDBA                            DROP PROFILE                             NO  YESDBA                            ALTER ANY TRIGGER                        NO  YESDBA                            CREATE ANY TRIGGER                       NO  YESDBA                            DROP ANY PROCEDURE                       NO  YESDBA                            AUDIT ANY                                NO  YESDBA                            DROP ANY ROLE                            NO  YESDBA                            DROP ANY SEQUENCE                        NO  YESDBA                            CREATE PUBLIC SYNONYM                    NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            CREATE SYNONYM                           NO  YESDBA                            DROP ANY CLUSTER                         NO  YESDBA                            ALTER ANY TABLE                          NO  YESDBA                            FLASHBACK ARCHIVE ADMINISTER             NO  YESDBA                            ALTER ANY SQL TRANSLATION PROFILE        NO  YESDBA                            CREATE CUBE BUILD PROCESS                NO  YESDBA                            CREATE MEASURE FOLDER                    NO  YESDBA                            CREATE ANY CUBE                          NO  YESDBA                            DROP ANY CUBE DIMENSION                  NO  YESDBA                            DROP ANY ASSEMBLY                        NO  YESDBA                            CREATE EXTERNAL JOB                      NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            READ ANY FILE GROUP                      NO  YESDBA                            CREATE ANY SQL PROFILE                   NO  YESDBA                            DROP ANY SQL PROFILE                     NO  YESDBA                            SELECT ANY TRANSACTION                   NO  YESDBA                            ADVISOR                                  NO  YESDBA                            DROP ANY RULE                            NO  YESDBA                            ALTER ANY RULE                           NO  YESDBA                            ALTER ANY EVALUATION CONTEXT             NO  YESDBA                            CREATE ANY CONTEXT                       NO  YESDBA                            MANAGE ANY QUEUE                         NO  YESDBA                            GLOBAL QUERY REWRITE                     NO  YESROLE                           PRIVILEGE                                ADM COM------------------------------ ---------------------------------------- --- ---DBA                            QUERY REWRITE                            NO  YESDBA                            DROP ANY OPERATOR                        NO  YESDBA                            EXECUTE ANY TYPE                         NO  YESDBA                            CREATE ANY MATERIALIZED VIEW             NO  YESDBA                            CREATE MATERIALIZED VIEW                 NO  YESDBA                            CREATE PUBLIC DATABASE LINK              NO  YESDBA                            CREATE DATABASE LINK                     NO  YESDBA                            CREATE ANY SYNONYM                       NO  YESDBA                            ALTER ANY CLUSTER                        NO  YESDBA                            DROP USER                                NO  YESDBA                            MANAGE TABLESPACE                        NO  YES220 rows selected.

3.业务用户权限设置要点：--  权限要足够的小-- 设计业务自己独有的角色--应付安全检查--创建一个业务用户角色SQL> create role app;    Role created.--默认给connect,resource 角色授予app角色SQL> grant connect,resource to app;Grant succeeded.--app角色可能权限不够，再单独给需要的系统权限，比如给insert any tablegrant insert ANY TABLE to app;--把app角色给roidba用户grant app to roidba;

三个重要的视图，可以查看用户权限
dba_role_privs

dba_sys_privs

role_role_privs