Thứ Hai, 30 tháng 11, 2020

Quản lý Quyền trong Oracle Database

1. Check
select * from dictionary ORDER BY TABLE_NAME;

---SYSTEM PRIVILEGES
-- liệt kê quyền tương ứng cua DBA
select * from DBA_SYS_PRIVS where grantee like 'DBA';

-- User co quyen DBA
select * from DBA_ROLE_PRIVS where granted_role='DBA'; 

---OBJECT PRIVILEGES : Quyền trên object: table, view, package.. 
select * from dba_tab_privs where gratee like 'SYSTEM'; 

--Quyền trên SESSION

SELECT * FROM SESSION_PRIVS;   // chi tiết quyền của session hiện tại

select * from SESSION_ROLES;

2. Gán quyền

NHÓM QUYỀN SYSTEM PRIVILEGES

Grant Select any table to <user>         
Truy vấn trên bất cứ table nào         

Grant insert any table to <user>        
Thêm mới trên bất cứ table nào         

Grant update any table to <user>         
Cập nhật trên bất cứ table nào

Grant delete any table to <user>      
Xóa trên bất cứ table nào         


FLASHBACK
grant flashback on test_owner.emp  to binhtv
select required clause timestamp (select /*flashback*/* from test_owner.tab1  as of timestamp(to_timestamp('2014/09/209 10:05:00','YYYY/MM/DD HH24:MI'))  where config_id is null)
---PROCEDURES, FUNTIONS
GRANT CREATE ANY PROCEDURE, ALTER ANY PROCEDURE, EXECUTE ANY PROCEDURE, DROP ANY PROCEDURE  TO USER1         
GRANT EXECUTE on LF_PORTAL.<function_name||procedure_name> TO USER1;

--Gán quyền cho tạo procedure trên schema của user1 - PACKAGES
GRANT CREATE PROCEDURE, ALTER PROCEDURE, EXECUTE PROCEDURE, DROP PROCEDURE  TO USER1

GRANT ALTER, EXECUTE ON TEST_OWNER.PCK__TEST TO TEST_OWNER

GRANT CREATE TABLE, UNLIMITED TABLESPACE TO BINHTV

-Gán FULL quyền trên package TEST_OWNER.PCK_TEST cho user TEST_OWNER

GRANT CREATE session, CREATE table, CREATE view, GRANT ALL ON TEST_OWNER.PCK_TEST TO TEST_OWNER
      
CREATE procedure,CREATE synonym,
      ALTER table, ALTER view, ALTER procedure,ALTER synonym,


GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
    CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
    AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

- Quyền view function, procedure, package
grant SELECT on DBA_SOURCE to binhtv;     // phai co quyen SYSDBA moi gan duoc
GRANT DEBUG ON A.PACKAGE_NAME TO binhtv

- Quyen view moi package:
        
        grant SELECT ANY DICTIONARY to test_owner
        grant debug any procedure to test_owner

--Sequence
GRANT CREATE SEQUENCE, ALTER ANY SEQUENCE, SELECT ANY SEQUENCE TO test_owner

GRANT  SELECT ANY SEQUENCE TO test_owner

NHÓM QUYỀN OBJECT PRIVILEGES

GRANT SELECT, INSERT, UPDATE, DELETE ON TEST_OWNER.TAB1 TO test_owner

- Trên cột
   GRANT INSERT (ename, job) ON emp TO swilliams, jward;
grant update (ename),insert (empno, ename)  on emp to sami;

ROLE 
/* User chỉ có quyền trên schema hiện tại cần Role: Connect, Resource, ko tạo được object trên schma khác */

GRANT CONNECT, RESOURCE TO BINHTV
GRANT SYSDBA TO BINHTV
        
- Không được gán quyền ROLE cho role khác

3. REVOKE

-- System privileges
REVOKE DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE FROM USER1
REVOKE ALL ON dept FROM human_resources;

-- Object privileges
REVOKE SELECT, INSERT, UPDATE insert ON HR.emp FROM jfee, tsmith;

-- Script thu hồi toàn bộ quyền hệ thống của user user1
select 'revoke ' || privilege || ' from  ' ||GRANTEE||';' 
from DBA_SYS_PRIVS where grantee like 'USER1';

-- Script thu hồi toàn bộ quyền table của user user1
select 'revoke ' || privilege || ' on ' || owner || '.' || table_name ||' from ' ||GRANTEE||';' 
from dba_tab_privs where gratee like 'USER1'; 

4. Others
Chú ý ADMIN OPTION, GRANT OPTION
Danh sách quyền hệ thống:
PRIVILEGE                              
----------------------------------------
ADMINISTER ANY SQL TUNING SET           
ADMINISTER DATABASE TRIGGER             
ADMINISTER RESOURCE MANAGER             
ADMINISTER SQL MANAGEMENT OBJECT        
ADMINISTER SQL TUNING SET               
ADVISOR                                 
ALTER ANY ANALYTIC VIEW                 
ALTER ANY ASSEMBLY                      
ALTER ANY ATTRIBUTE DIMENSION           
ALTER ANY CLUSTER                       
ALTER ANY CUBE                          

PRIVILEGE                              
----------------------------------------
ALTER ANY CUBE BUILD PROCESS            
ALTER ANY CUBE DIMENSION                
ALTER ANY DIMENSION                     
ALTER ANY EDITION                       
ALTER ANY EVALUATION CONTEXT            
ALTER ANY HIERARCHY                     
ALTER ANY INDEX                         
ALTER ANY INDEXTYPE                     
ALTER ANY LIBRARY                       
ALTER ANY MATERIALIZED VIEW             
ALTER ANY MEASURE FOLDER                

PRIVILEGE                              
----------------------------------------
ALTER ANY MINING MODEL                  
ALTER ANY OPERATOR                      
ALTER ANY OUTLINE                       
ALTER ANY PROCEDURE                     
ALTER ANY ROLE                          
ALTER ANY RULE                          
ALTER ANY RULE SET                      
ALTER ANY SEQUENCE                      
ALTER ANY SQL PROFILE                   
ALTER ANY SQL TRANSLATION PROFILE       
ALTER ANY TABLE                         

PRIVILEGE                              
----------------------------------------
ALTER ANY TRIGGER                       
ALTER ANY TYPE                          
ALTER DATABASE                          
ALTER LOCKDOWN PROFILE                  
ALTER PROFILE                           
ALTER RESOURCE COST                     
ALTER ROLLBACK SEGMENT                  
ALTER SESSION                           
ALTER SYSTEM                            
ALTER TABLESPACE                        
ALTER USER                              

PRIVILEGE                              
----------------------------------------
ANALYZE ANY                             
AUDIT ANY                               
AUDIT SYSTEM                            
BACKUP ANY TABLE                        
BECOME USER                             
CHANGE NOTIFICATION                     
COMMENT ANY MINING MODEL                
COMMENT ANY TABLE                       
CREATE ANALYTIC VIEW                    
CREATE ANY ANALYTIC VIEW                
CREATE ANY ASSEMBLY                     

PRIVILEGE                              
----------------------------------------
CREATE ANY ATTRIBUTE DIMENSION          
CREATE ANY CLUSTER                      
CREATE ANY CONTEXT                      
CREATE ANY CREDENTIAL                   
CREATE ANY CUBE                         
CREATE ANY CUBE BUILD PROCESS           
CREATE ANY CUBE DIMENSION               
CREATE ANY DIMENSION                    
CREATE ANY DIRECTORY                    
CREATE ANY EDITION                      
CREATE ANY EVALUATION CONTEXT           

PRIVILEGE                              
----------------------------------------
CREATE ANY HIERARCHY                    
CREATE ANY INDEX                        
CREATE ANY INDEXTYPE                    
CREATE ANY JOB                          
CREATE ANY LIBRARY                      
CREATE ANY MATERIALIZED VIEW            
CREATE ANY MEASURE FOLDER               
CREATE ANY MINING MODEL                 
CREATE ANY OPERATOR                     
CREATE ANY OUTLINE                      
CREATE ANY PROCEDURE                    

PRIVILEGE                              
----------------------------------------
CREATE ANY RULE                         
CREATE ANY RULE SET                     
CREATE ANY SEQUENCE                     
CREATE ANY SQL PROFILE                  
CREATE ANY SQL TRANSLATION PROFILE      
CREATE ANY SYNONYM                      
CREATE ANY TABLE                        
CREATE ANY TRIGGER                      
CREATE ANY TYPE                         
CREATE ANY VIEW                         
CREATE ASSEMBLY                         

PRIVILEGE                              
----------------------------------------
CREATE ATTRIBUTE DIMENSION              
CREATE CLUSTER                          
CREATE CREDENTIAL                       
CREATE CUBE                             
CREATE CUBE BUILD PROCESS               
CREATE CUBE DIMENSION                   
CREATE DATABASE LINK                    
CREATE DIMENSION                        
CREATE EVALUATION CONTEXT               
CREATE EXTERNAL JOB                     
CREATE HIERARCHY                        

PRIVILEGE                              
----------------------------------------
CREATE INDEXTYPE                        
CREATE JOB                              
CREATE LIBRARY                          
CREATE LOCKDOWN PROFILE                 
CREATE MATERIALIZED VIEW                
CREATE MEASURE FOLDER                   
CREATE MINING MODEL                     
CREATE OPERATOR                         
CREATE PLUGGABLE DATABASE               
CREATE PROCEDURE                        
CREATE PROFILE                          

PRIVILEGE                              
----------------------------------------
CREATE PUBLIC DATABASE LINK             
CREATE PUBLIC SYNONYM                   
CREATE ROLE                             
CREATE ROLLBACK SEGMENT                 
CREATE RULE                             
CREATE RULE SET                         
CREATE SEQUENCE                         
CREATE SESSION                          
CREATE SQL TRANSLATION PROFILE          
CREATE SYNONYM                          
CREATE TABLE                            

PRIVILEGE                              
----------------------------------------
CREATE TABLESPACE                       
CREATE TRIGGER                          
CREATE TYPE                             
CREATE USER                             
CREATE VIEW                             
DEBUG ANY PROCEDURE                     
DEBUG CONNECT ANY                       
DEBUG CONNECT SESSION                   
DELETE ANY CUBE DIMENSION               
DELETE ANY MEASURE FOLDER               
DELETE ANY TABLE                        

PRIVILEGE                              
----------------------------------------
DEQUEUE ANY QUEUE                       
DROP ANY ANALYTIC VIEW                  
DROP ANY ASSEMBLY                       
DROP ANY ATTRIBUTE DIMENSION            
DROP ANY CLUSTER                        
DROP ANY CONTEXT                        
DROP ANY CUBE                           
DROP ANY CUBE BUILD PROCESS             
DROP ANY CUBE DIMENSION                 
DROP ANY DIMENSION                      
DROP ANY DIRECTORY                      

PRIVILEGE                              
----------------------------------------
DROP ANY EDITION                        
DROP ANY EVALUATION CONTEXT             
DROP ANY HIERARCHY                      
DROP ANY INDEX                          
DROP ANY INDEXTYPE                      
DROP ANY LIBRARY                        
DROP ANY MATERIALIZED VIEW              
DROP ANY MEASURE FOLDER                 
DROP ANY MINING MODEL                   
DROP ANY OPERATOR                       
DROP ANY OUTLINE                        

PRIVILEGE                              
----------------------------------------
DROP ANY PROCEDURE                      
DROP ANY ROLE                           
DROP ANY RULE                           
DROP ANY RULE SET                       
DROP ANY SEQUENCE                       
DROP ANY SQL PROFILE                    
DROP ANY SQL TRANSLATION PROFILE        
DROP ANY SYNONYM                        
DROP ANY TABLE                          
DROP ANY TRIGGER                        
DROP ANY TYPE                           

PRIVILEGE                              
----------------------------------------
DROP ANY VIEW                           
DROP LOCKDOWN PROFILE                   
DROP PROFILE                            
DROP PUBLIC DATABASE LINK               
DROP PUBLIC SYNONYM                     
DROP ROLLBACK SEGMENT                   
DROP TABLESPACE                         
DROP USER                               
EM EXPRESS CONNECT                      
ENQUEUE ANY QUEUE                       
EXECUTE ANY ASSEMBLY                    

PRIVILEGE                              
----------------------------------------
EXECUTE ANY CLASS                       
EXECUTE ANY EVALUATION CONTEXT          
EXECUTE ANY INDEXTYPE                   
EXECUTE ANY LIBRARY                     
EXECUTE ANY OPERATOR                    
EXECUTE ANY PROCEDURE                   
EXECUTE ANY PROGRAM                     
EXECUTE ANY RULE                        
EXECUTE ANY RULE SET                    
EXECUTE ANY TYPE                        
EXECUTE ASSEMBLY                        

PRIVILEGE                              
----------------------------------------
EXEMPT DDL REDACTION POLICY             
EXEMPT DML REDACTION POLICY             
EXPORT FULL DATABASE                    
FLASHBACK ANY TABLE                     
FLASHBACK ARCHIVE ADMINISTER            
FORCE ANY TRANSACTION                   
FORCE TRANSACTION                       
GLOBAL QUERY REWRITE                    
GRANT ANY OBJECT PRIVILEGE              
GRANT ANY PRIVILEGE                     
GRANT ANY ROLE                          

PRIVILEGE                              
----------------------------------------
IMPORT FULL DATABASE                    
INSERT ANY CUBE DIMENSION               
INSERT ANY MEASURE FOLDER               
INSERT ANY TABLE                        
LOCK ANY TABLE                          
LOGMINING                               
MANAGE ANY FILE GROUP                   
MANAGE ANY QUEUE                        
MANAGE FILE GROUP                       
MANAGE SCHEDULER                        
MANAGE TABLESPACE                       

PRIVILEGE                              
----------------------------------------
MERGE ANY VIEW                          
ON COMMIT REFRESH                       
QUERY REWRITE                           
READ ANY FILE GROUP                     
READ ANY TABLE                          
REDEFINE ANY TABLE                      
RESTRICTED SESSION                      
RESUMABLE                               
SELECT ANY CUBE                         
SELECT ANY CUBE BUILD PROCESS           
SELECT ANY CUBE DIMENSION               

PRIVILEGE                              
----------------------------------------
SELECT ANY MEASURE FOLDER               
SELECT ANY MINING MODEL                 
SELECT ANY SEQUENCE                     
SELECT ANY TABLE                        
SELECT ANY TRANSACTION                  
SET CONTAINER                           
UNDER ANY TABLE                         
UNDER ANY TYPE                          
UNDER ANY VIEW                          
UNLIMITED TABLESPACE                    
UPDATE ANY CUBE                         

PRIVILEGE                              
----------------------------------------
UPDATE ANY CUBE BUILD PROCESS          
UPDATE ANY CUBE DIMENSION               
UPDATE ANY TABLE                        
USE ANY JOB RESOURCE                    
USE ANY SQL TRANSLATION PROFILE
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master