12c禁用DBA權(quán)限，你怎么給業(yè)務(wù)用戶授權(quán)
看到太多客戶，使用業(yè)務(wù)用戶的權(quán)限都是DBA，這樣設(shè)置是最簡單的，也是最危險的，這里給大家介紹一種設(shè)置權(quán)限的方法。
測試環(huán)境為Oracle 12c
1.connect 角色具有的系統(tǒng)權(quán)限
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 角色具有的系統(tǒng)權(quán)限
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角色都是做哪些操作
創(chuàng)新互聯(lián)自2013年創(chuàng)立以來，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站制作、成都網(wǎng)站設(shè)計網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元德江做網(wǎng)站,已為上家服務(wù),為德江各地企業(yè)和個人服務(wù),聯(lián)系電話:13518219792
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為什么不能給業(yè)務(wù)用戶DBA權(quán)限??！
1.從安全層面考慮
2.從管理上考慮
DBA 角色所具有的權(quán)限
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.業(yè)務(wù)用戶權(quán)限設(shè)置
要點：
-- 權(quán)限要足夠的小
-- 設(shè)計業(yè)務(wù)自己獨有的角色
--應(yīng)付安全檢查
--創(chuàng)建一個業(yè)務(wù)用戶角色
SQL> create role app;
Role created.
--默認(rèn)給connect,resource 角色授予app角色
SQL> grant connect,resource to app;
Grant succeeded.
--app角色可能權(quán)限不夠，再單獨給需要的系統(tǒng)權(quán)限，比如給insert any table
grant insert ANY TABLE to app;
--把a(bǔ)pp角色給roidba用戶
grant app to roidba;三個重要的視圖，可以查看用戶權(quán)限
dba_role_privs
dba_sys_privs
role_role_privs
當(dāng)前題目：12c禁用DBA權(quán)限，你怎么給業(yè)務(wù)用戶授權(quán)
當(dāng)前路徑：http://bm7419.com/article26/isgicg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營銷、動態(tài)網(wǎng)站、網(wǎng)頁設(shè)計公司、全網(wǎng)營銷推廣、建站公司、做網(wǎng)站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 手機(jī)網(wǎng)站建設(shè)有哪些步驟? 2016-11-13
- 企業(yè)手機(jī)網(wǎng)站建設(shè)需要注意哪些問題 2015-06-24
- 網(wǎng)站SEO優(yōu)化如何抓取手機(jī)網(wǎng)站圖片 2016-10-13
- 手機(jī)網(wǎng)站和小程序建設(shè)的建設(shè)方案和推廣方案 2013-06-24
- 分享手機(jī)網(wǎng)站細(xì)節(jié)設(shè)計的知識 2016-12-13
- 企業(yè)手機(jī)網(wǎng)站建設(shè)開發(fā)都有哪些方式? 2023-04-04
- 【成都seo優(yōu)化】手機(jī)站優(yōu)化五大要素 2016-11-13
- 成都制作手機(jī)網(wǎng)站制作建設(shè)需要注意哪些事項呢？ 2017-01-01
- 關(guān)于移動端手機(jī)網(wǎng)站優(yōu)化解決方案 2016-02-20
- 北京SEO優(yōu)化：手機(jī)網(wǎng)站如何做好優(yōu)化？ 2015-07-08
- 手機(jī)網(wǎng)站建設(shè)時需要注意的事項有哪些？ 2022-05-13
- 成都手機(jī)網(wǎng)站建設(shè)需要注意事項 2018-02-24