看到太多客户，使用业务用户的权限都是DBA，这样设置是最简单的，也是最危险的，这里给大家介绍一种设置权限的方法。
测试环境为Oracle 12c
1.connect 角色具有的系统权限
SQL> select * from role_sys_privs where role='CONNECT';
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
CONNECT SET CONTAINERNO 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
------------------------------ ---------------------------------------- --- ---
DBACREATE PLUGGABLE DATABASE NO YES
DBAUSE ANY SQL TRANSLATION PROFILE NO YES
DBADROP ANY CUBE BUILD PROCESS NO YES
DBACREATE CUBE NO YES
DBAALTER ANY CUBE DIMENSION NO YES
DBAALTER ANY MINING MODEL NO YES
DBADROP ANY MINING MODEL NO YES
DBADROP ANY EDITION NO YES
DBACHANGE NOTIFICATION NO YES
DBAADMINISTER ANY SQL TUNING SET NO YES
DBAALTER ANY SQL PROFILE NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE RULE NO YES
DBAEXPORT FULL DATABASE NO YES
DBAEXECUTE ANY EVALUATION CONTEXT NO YES
DBADEQUEUE ANY QUEUE NO YES
DBADROP ANY INDEXTYPE NO YES
DBAALTER ANY INDEXTYPE NO YES
DBAEXECUTE ANY LIBRARY NO YES
DBACREATE ANY LIBRARY NO YES
DBACREATE ANY DIRECTORY NO YES
DBAALTER PROFILENO YES
DBAEXECUTE ANY PROCEDURE NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE ROLE NO YES
DBASELECT ANY SEQUENCE NO YES
DBADROP ANY INDEX NO YES
DBAUPDATE ANY TABLE NO YES
DBAINSERT ANY TABLE NO YES
DBASELECT ANY TABLE NO YES
DBADROP ROLLBACK SEGMENT NO YES
DBABECOME USER NO YES
DBADROP TABLESPACE NO YES
DBAALTER SESSIONNO YES
DBACREATE SESSION NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBADROP ANY MEASURE FOLDER NO YES
DBASELECT ANY CUBE NO YES
DBAALTER ANY CUBE NO YES
DBACREATE ANY ASSEMBLY NO YES
DBAALTER ANY EDITION NO YES
DBAANALYZE ANY DICTIONARY NO YES
DBAALTER ANY RULE SET NO YES
DBACREATE RULE SET NO YES
DBADEBUG ANY PROCEDURE NO YES
DBACREATE DIMENSION NO YES
DBAALTER ANY LIBRARY NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAUNDER ANY TYPE NO YES
DBADROP ANY MATERIALIZED VIEW NO YES
DBADROP ANY TRIGGER NO YES
DBAALTER ANY PROCEDURE NO YES
DBAFORCE ANY TRANSACTION NO YES
DBAALTER DATABASE NO YES
DBADELETE ANY TABLE NO YES
DBAALTER ROLLBACK SEGMENT NO YES
DBAALTER ANY MEASURE FOLDER NO YES
DBASET CONTAINERNO YES
DBAEM EXPRESS CONNECT NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAUPDATE ANY CUBE DIMENSION NO YES
DBACREATE ANY CUBE BUILD PROCESS NO YES
DBACREATE CUBE DIMENSION NO YES
DBAALTER ANY ASSEMBLY NO YES
DBACREATE ASSEMBLY NO YES
DBACREATE ANY EDITION NO YES
DBAEXECUTE ANY PROGRAM NO YES
DBAEXECUTE ANY RULE NO YES
DBAIMPORT FULL DATABASE NO YES
DBAEXECUTE ANY RULE SET NO YES
DBACREATE ANY RULE SET NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAFLASHBACK ANY TABLE NO YES
DBARESUMABLE NO YES
DBAADMINISTER DATABASE TRIGGER NO YES
DBACREATE ANY OUTLINE NO YES
DBAALTER ANY DIMENSION NO YES
DBACREATE ANY DIMENSION NO YES
DBAEXECUTE ANY OPERATOR NO YES
DBACREATE TYPE NO YES
DBACREATE TRIGGER NO YES
DBAGRANT ANY ROLE NO YES
DBADROP ANY VIEWNO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE VIEW NO YES
DBALOCK ANY TABLE NO YES
DBAALTER USER NO YES
DBACREATE USER NO YES
DBAALTER TABLESPACE NO YES
DBACREATE TABLESPACE NO YES
DBARESTRICTED SESSION NO YES
DBAREAD ANY TABLE NO YES
DBAEXEMPT DML REDACTION POLICY NO YES
DBAUPDATE ANY CUBE BUILD PROCESS NO YES
DBADROP ANY CUBENO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAINSERT ANY CUBE DIMENSION NO YES
DBACREATE MINING MODEL NO YES
DBACREATE ANY JOB NO YES
DBACREATE JOB NO YES
DBACREATE ANY RULE NO YES
DBADROP ANY EVALUATION CONTEXT NO YES
DBACREATE ANY EVALUATION CONTEXT NO YES
DBACREATE EVALUATION CONTEXT NO YES
DBAGRANT ANY OBJECT PRIVILEGE NO YES
DBASELECT ANY DICTIONARY NO YES
DBADROP ANY DIMENSION NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAUNDER ANY TABLE NO YES
DBACREATE INDEXTYPE NO YES
DBACREATE ANY OPERATOR NO YES
DBADROP ANY LIBRARY NO YES
DBAANALYZE ANY NO YES
DBAALTER ANY ROLE NO YES
DBACREATE ANY SEQUENCE NO YES
DBACREATE ANY INDEX NO YES
DBACREATE ANY TABLE NO YES
DBAALTER ANY CUBE BUILD PROCESS NO YES
DBASELECT ANY CUBE BUILD PROCESS NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBASELECT ANY MEASURE FOLDER NO YES
DBAEXEMPT DDL REDACTION POLICY NO YES
DBACREATE ANY CREDENTIAL NO YES
DBACREATE ANY SQL TRANSLATION PROFILE NO YES
DBADELETE ANY MEASURE FOLDER NO YES
DBACREATE ANY MEASURE FOLDER NO YES
DBASELECT ANY MINING MODEL NO YES
DBACREATE ANY MINING MODEL NO YES
DBAMANAGE FILE GROUP NO YES
DBAMANAGE SCHEDULER NO YES
DBAADMINISTER RESOURCE MANAGER NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAALTER ANY OUTLINE NO YES
DBADROP ANY CONTEXT NO YES
DBAEXECUTE ANY INDEXTYPE NO YES
DBAUNDER ANY VIEW NO YES
DBADROP ANY TYPENO YES
DBAALTER ANY TYPE NO YES
DBAALTER ANY MATERIALIZED VIEW NO YES
DBACREATE PROFILE NO YES
DBADROP PUBLIC DATABASE LINK NO YES
DBAALTER ANY INDEX NO YES
DBACREATE CLUSTER NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAREDEFINE ANY TABLE NO YES
DBACOMMENT ANY TABLE NO YES
DBADROP ANY TABLE NO YES
DBACREATE ROLLBACK SEGMENT NO YES
DBAAUDIT SYSTEM NO YES
DBAALTER SYSTEM NO YES
DBACREATE CREDENTIAL NO YES
DBADROP ANY SQL TRANSLATION PROFILE NO YES
DBASELECT ANY CUBE DIMENSION NO YES
DBADELETE ANY CUBE DIMENSION NO YES
DBACREATE ANY CUBE DIMENSION NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACOMMENT ANY MINING MODEL NO YES
DBAEXECUTE ASSEMBLY NO YES
DBAEXECUTE ANY ASSEMBLY NO YES
DBAMANAGE ANY FILE GROUP NO YES
DBAEXECUTE ANY CLASS NO YES
DBADROP ANY RULE SET NO YES
DBADEBUG CONNECT SESSION NO YES
DBAON COMMIT REFRESH NO YES
DBAENQUEUE ANY QUEUE NO YES
DBACREATE ANY INDEXTYPE NO YES
DBAALTER ANY OPERATOR NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE ANY TYPE NO YES
DBADROP ANY DIRECTORY NO YES
DBAALTER RESOURCE COST NO YES
DBACREATE ANY PROCEDURE NO YES
DBACREATE PROCEDURE NO YES
DBAFORCE TRANSACTION NO YES
DBAALTER ANY SEQUENCE NO YES
DBACREATE SEQUENCE NO YES
DBACREATE ANY VIEW NO YES
DBADROP PUBLIC SYNONYM NO YES
DBADROP ANY SYNONYM NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE ANY CLUSTER NO YES
DBABACKUP ANY TABLE NO YES
DBACREATE TABLE NO YES
DBALOGMINING NO YES
DBACREATE SQL TRANSLATION PROFILE NO YES
DBAADMINISTER SQL MANAGEMENT OBJECT NO YES
DBAINSERT ANY MEASURE FOLDER NO YES
DBAUPDATE ANY CUBE NO YES
DBAADMINISTER SQL TUNING SET NO YES
DBAMERGE ANY VIEW NO YES
DBADROP ANY OUTLINE NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE OPERATOR NO YES
DBACREATE LIBRARY NO YES
DBAGRANT ANY PRIVILEGE NO YES
DBADROP PROFILE NO YES
DBAALTER ANY TRIGGER NO YES
DBACREATE ANY TRIGGER NO YES
DBADROP ANY PROCEDURE NO YES
DBAAUDIT ANY NO YES
DBADROP ANY ROLENO YES
DBADROP ANY SEQUENCE NO YES
DBACREATE PUBLIC SYNONYM NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBACREATE SYNONYM NO YES
DBADROP ANY CLUSTER NO YES
DBAALTER ANY TABLE NO YES
DBAFLASHBACK ARCHIVE ADMINISTER NO YES
DBAALTER ANY SQL TRANSLATION PROFILE NO YES
DBACREATE CUBE BUILD PROCESS NO YES
DBACREATE MEASURE FOLDER NO YES
DBACREATE ANY CUBE NO YES
DBADROP ANY CUBE DIMENSION NO YES
DBADROP ANY ASSEMBLY NO YES
DBACREATE EXTERNAL JOB NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAREAD ANY FILE GROUP NO YES
DBACREATE ANY SQL PROFILE NO YES
DBADROP ANY SQL PROFILE NO YES
DBASELECT ANY TRANSACTION NO YES
DBAADVISOR NO YES
DBADROP ANY RULENO YES
DBAALTER ANY RULE NO YES
DBAALTER ANY EVALUATION CONTEXT NO YES
DBACREATE ANY CONTEXT NO YES
DBAMANAGE ANY QUEUE NO YES
DBAGLOBAL QUERY REWRITE NO YES
ROLE PRIVILEGE ADM COM
------------------------------ ---------------------------------------- --- ---
DBAQUERY REWRITENO YES
DBADROP ANY OPERATOR NO YES
DBAEXECUTE ANY TYPE NO YES
DBACREATE ANY MATERIALIZED VIEW NO YES
DBACREATE MATERIALIZED VIEW NO YES
DBACREATE PUBLIC DATABASE LINK NO YES
DBACREATE DATABASE LINK NO YES
DBACREATE ANY SYNONYM NO YES
DBAALTER ANY CLUSTER NO YES
DBADROP USER NO YES
DBAMANAGE 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;
