12c 禁用DBA权限，你怎么给业务用户授权
admin
2023-04-24 02:21:17
0次
看到太多客户，使用业务用户的权限都是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
上一篇：SQL复习笔记
相关内容
热门资讯
烈焰熊熊，堪比“原子弹”，欧洲...
据美国有线电视新闻网（CNN）7月29日报道，席卷法国西南部的大片野火燃烧得如此猛烈，以至于形成了一...
外交部：对日本熊本县地震遇难者...
据凤凰卫视报道，针对日本熊本县发生强震造成人员伤亡，中国外交部发言人毛宁7月29日在例行记者会上表示...
高铁喧哗被劝阻后叫嚣全家体制内...
“女子高铁喧哗被劝阻后叫嚣全家体制内”一事迎来官方通报。据新京报报道，7月29日，辽宁北票市公安局发...
广东专业蓝牙耳机定制厂家推荐，...
在珠三角腹地，广东早已成为全球蓝牙耳机产业链的“心脏地带”。从深圳华强北的声学元件批发，到东莞、惠州...
格雷厄姆葬礼变地缘竞价场！两大...
有些葬礼，送别的是逝者；有些葬礼，演的却是活人最现实的政治博弈。当地时间7月28日，美国老牌鹰派参议...
一道小学数学题难住3名医学博士...
3名不同学校的博士同做一道数学题，谁最厉害？近日，一陕西网友发布的视频引发热议。29日，视频发布者“...
华为全新“小艺任务”亮相：整合...
7月29日，在华为鸿蒙电脑新品技术沟通会上，终端云服务小艺业务部总裁贾永利宣布，小艺将迎来“从对话到...
广西日报特稿《王的猜想》火了，...
澎湃新闻记者 蒋子文 实习生 汤煜炜为应对外界购买纸质版特稿《王的猜想》的热情，《广西日报》宣布紧急...
月租20元内！2026年平价云...
不少手游玩家、账号运营从业者挑选云手机时，首要诉求就是低价够用，想要控制每月支出在20元以内，同时兼...
诺奖得主如何看待王虹境遇？“我...
中村修二（2014年诺贝尔物理学奖得主）【导读】近日王虹与邓煜双双获得2026年菲尔兹奖，成为了首批...