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复习笔记
相关内容
热门资讯
“台独”顽固分子沈伯洋参选台北...
【环球时报特约记者 陈立非】台湾今年年底举行“九合一”选举，国民党很早就确定由现任台北市市长蒋万安争...
伊拉克和巴基斯坦据称已分别同伊...
总台记者当地时间5月12日获悉，伊拉克和巴基斯坦已分别同伊朗签订协议，以从海湾地区运输石油和液化天然...
京沪高铁“涨价”，调价背后有何...
昨天（11日），京沪高铁发布公告称，决定对京沪高速线、合蚌高速线动车组列车公布票价进行优化调整，时速...
第三方样品A厂家生产支架样品测...
第三方样品A厂家生产支架样品测试实验报告 一、检测范围 本次检测对象为A厂家生产的XX型医用金属...
伊朗德黑兰地区发生4.6级地震
总台记者获悉，当地时间5月12日23时47分左右，伊朗德黑兰地区发生4.6级地震。震中位于德黑兰省和...
被科威特指控“武装渗透”布比延...
新华社科威特城/德黑兰5月12日电（记者尹炣 陈霄）科威特政府12日指认，伊朗伊斯兰革命卫队多名武装...
酒吧办护士制服派对被指低俗，当...
据媒体报道，5月12日国际护士节，浙江衢州有网友发帖称，当地APK·ELITE CLUB酒吧举办所谓...
珠海冠宇获得发明专利授权：“一...
证券之星消息，根据天眼查APP数据显示珠海冠宇（688772）新获得一项发明专利授权，专利名为“一种...
大华申请数据写入方法专利，提高...
国家知识产权局信息显示，浙江大华技术股份有限公司申请一项名为“数据写入方法、电子设备及计算机可读存储...
现在，赖清德更焦虑了
执笔/月半刀&宝刀刀&胡一刀5月13日，美国总统特朗普将开启访华行程。外界高度关注此次会晤中双方将如...