看到太多客户，使用业务用户的权限都是DBA，这样设置是最简单的，也是最危险的，这里给大家介绍一种设置权限的方法。
测试环境为Oracle 12c
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;三个重要的视图，可以查看用户权限
dba_role_privs
dba_sys_privs
role_role_privs
本文转自 roidba 51CTO博客，原文链接：http://blog.51cto.com/roidba/2053826，如需转载请自行联系原作者