Oracle 权限管理 访问其他用户资源的权限

有时候不希望用户拥有DBA权限，但是需要访问其他用户资源的权限，但是Oralce没有直接将一个用户权限授给另一个用户的直接办法，这种情况下可以采用下列办法。

-- 1、创建用于管理权限的角色
create role role_test;
-- 2、将角色 role_test授权给用户 user_test
grant role_test to user_test;
-- 3、授权给角色 role_test（以下权限已剔除了等级较高的DBA权限，只是一些数据层面的权限）;
grant CONNECT                                 to role_test  ;
grant RESOURCE                                to role_test  ;
grant ALTER ANY ANALYTIC VIEW                 to role_test  ;
grant ALTER ANY DIMENSION                     to role_test  ;
grant ALTER ANY EDITION                       to role_test  ;
grant ALTER ANY EVALUATION CONTEXT            to role_test  ;
grant ALTER ANY HIERARCHY                     to role_test  ;
grant ALTER ANY INDEX                         to role_test  ;
grant ALTER ANY INDEXTYPE                     to role_test  ;
grant ALTER ANY LIBRARY                       to role_test  ;
grant ALTER ANY MATERIALIZED VIEW             to role_test  ;
grant ALTER ANY MEASURE FOLDER                to role_test  ;
grant ALTER ANY MINING MODEL                  to role_test  ;
grant ALTER ANY OPERATOR                      to role_test  ;
grant ALTER ANY OUTLINE                       to role_test  ;
grant ALTER ANY PROCEDURE                     to role_test  ;
grant ALTER ANY SEQUENCE                      to role_test  ;
grant ALTER ANY SQL PROFILE                   to role_test  ;
grant ALTER ANY SQL TRANSLATION PROFILE       to role_test  ;
grant ALTER ANY TABLE                         to role_test  ;
grant ALTER ANY TRIGGER                       to role_test  ;
grant ALTER ANY TYPE                          to role_test  ;
grant ALTER PROFILE                           to role_test  ;
grant ALTER RESOURCE COST                     to role_test  ;
grant ALTER SESSION                           to role_test  ;
grant ANALYZE ANY                             to role_test  ;
grant ANALYZE ANY DICTIONARY                  to role_test  ;
grant BACKUP ANY TABLE                        to role_test  ;
grant COMMENT ANY MINING MODEL                to role_test  ;
grant COMMENT ANY TABLE                       to role_test  ;
grant CREATE ANALYTIC VIEW                    to role_test  ;
grant CREATE ANY ANALYTIC VIEW                to role_test  ;
grant CREATE ANY ASSEMBLY                     to role_test  ;
grant CREATE ANY ATTRIBUTE DIMENSION          to role_test  ;
grant CREATE ANY CONTEXT                      to role_test  ;
grant CREATE ANY CREDENTIAL                   to role_test  ;
grant CREATE ANY DIMENSION                    to role_test  ;
grant CREATE ANY DIRECTORY                    to role_test  ;
grant CREATE ANY EDITION                      to role_test  ;
grant CREATE ANY EVALUATION CONTEXT           to role_test  ;
grant CREATE ANY HIERARCHY                    to role_test  ;
grant CREATE ANY INDEX                        to role_test  ;
grant CREATE INDEXTYPE                        to role_test  ;
grant CREATE ANY JOB                          to role_test  ;
grant CREATE ANY LIBRARY                      to role_test  ;
grant CREATE ANY MATERIALIZED VIEW            to role_test  ;
grant CREATE ANY MEASURE FOLDER               to role_test  ;
grant CREATE ANY MINING MODEL                 to role_test  ;
grant CREATE OPERATOR                         to role_test  ;
grant CREATE ANY OUTLINE                      to role_test  ;
grant CREATE ANY PROCEDURE                    to role_test  ;
grant CREATE ANY RULE                         to role_test  ;
grant CREATE ANY RULE SET                     to role_test  ;
grant CREATE ANY SEQUENCE                     to role_test  ;
grant CREATE ANY SQL PROFILE                  to role_test  ;
grant CREATE ANY SQL TRANSLATION PROFILE      to role_test  ;
grant CREATE ANY SYNONYM                      to role_test  ;
grant CREATE ANY TABLE                        to role_test  ;
grant CREATE ANY TRIGGER                      to role_test  ;
grant CREATE ANY TYPE                         to role_test  ;
grant CREATE ANY VIEW                         to role_test  ;
grant CREATE ASSEMBLY                         to role_test  ;
grant CREATE ATTRIBUTE DIMENSION              to role_test  ;
grant CREATE CREDENTIAL                       to role_test  ;
grant CREATE DATABASE LINK                    to role_test  ;
grant CREATE DIMENSION                        to role_test  ;
grant CREATE EVALUATION CONTEXT               to role_test  ;
grant CREATE EXTERNAL JOB                     to role_test  ;
grant CREATE HIERARCHY                        to role_test  ;
grant CREATE JOB                              to role_test  ;
grant CREATE LIBRARY                          to role_test  ;
grant CREATE MATERIALIZED VIEW                to role_test  ;
grant CREATE MEASURE FOLDER                   to role_test  ;
grant CREATE MINING MODEL                     to role_test  ;
grant CREATE PROFILE                          to role_test  ;
grant CREATE PUBLIC DATABASE LINK             to role_test  ;
grant CREATE ROLLBACK SEGMENT                 to role_test  ;
grant CREATE RULE                             to role_test  ;
grant CREATE RULE SET                         to role_test  ;
grant CREATE SQL TRANSLATION PROFILE          to role_test  ;
grant CREATE SYNONYM                          to role_test  ;
grant CREATE VIEW                             to role_test  ;
grant DEBUG ANY PROCEDURE                     to role_test  ;
grant DEBUG CONNECT ANY                       to role_test  ;
grant DEBUG CONNECT SESSION                   to role_test  ;
grant DELETE ANY MEASURE FOLDER               to role_test  ;
grant DELETE ANY TABLE                        to role_test  ;
grant DEQUEUE ANY QUEUE                       to role_test  ;
grant DROP ANY ANALYTIC VIEW                  to role_test  ;
grant DROP ANY CONTEXT                        to role_test  ;
grant DROP ANY DIMENSION                      to role_test  ;
grant DROP ANY DIRECTORY                      to role_test  ;
grant DROP ANY EDITION                        to role_test  ;
grant DROP ANY EVALUATION CONTEXT             to role_test  ;
grant DROP ANY HIERARCHY                      to role_test  ;
grant DROP ANY INDEX                          to role_test  ;
grant DROP ANY INDEXTYPE                      to role_test  ;
grant DROP ANY LIBRARY                        to role_test  ;
grant DROP ANY MATERIALIZED VIEW              to role_test  ;
grant DROP ANY MEASURE FOLDER                 to role_test  ;
grant DROP ANY MINING MODEL                   to role_test  ;
grant DROP ANY OPERATOR                       to role_test  ;
grant DROP ANY SEQUENCE                       to role_test  ;
grant DROP ANY SQL PROFILE                    to role_test  ;
grant DROP ANY SQL TRANSLATION PROFILE        to role_test  ;
grant DROP ANY SYNONYM                        to role_test  ;
grant DROP ANY TABLE                          to role_test  ;
grant DROP ANY TRIGGER                        to role_test  ;
grant DROP ANY TYPE                           to role_test  ;
grant ENQUEUE ANY QUEUE                       to role_test  ;
grant EXECUTE ANY ASSEMBLY                    to role_test  ;
grant EXECUTE ANY CLASS                       to role_test  ;
grant EXECUTE ANY EVALUATION CONTEXT          to role_test  ;
grant EXECUTE ANY INDEXTYPE                   to role_test  ;
grant EXECUTE ANY LIBRARY                     to role_test  ;
grant EXECUTE ANY OPERATOR                    to role_test  ;
grant EXECUTE ANY PROCEDURE                   to role_test  ;
grant EXECUTE ANY PROGRAM                     to role_test  ;
grant EXECUTE ANY RULE                        to role_test  ;
grant EXECUTE ANY RULE SET                    to role_test  ;
grant EXECUTE ANY TYPE                        to role_test  ;
grant EXECUTE ASSEMBLY                        to role_test  ;
grant EXEMPT DDL REDACTION POLICY             to role_test  ;
grant EXEMPT DML REDACTION POLICY             to role_test  ;
grant EXPORT FULL DATABASE                    to role_test  ;
grant FLASHBACK ANY TABLE                     to role_test  ;
grant FLASHBACK ARCHIVE ADMINISTER            to role_test  ;
grant FORCE ANY TRANSACTION                   to role_test  ;
grant FORCE TRANSACTION                       to role_test  ;
grant GLOBAL QUERY REWRITE                    to role_test  ;
grant IMPORT FULL DATABASE                    to role_test  ;
grant INSERT ANY CUBE DIMENSION               to role_test  ;
grant INSERT ANY MEASURE FOLDER               to role_test  ;
grant INSERT ANY TABLE                        to role_test  ;
grant LOGMINING                               to role_test  ;
grant MANAGE SCHEDULER                        to role_test  ;
grant ON COMMIT REFRESH                       to role_test  ;
grant QUERY REWRITE                           to role_test  ;
grant RESTRICTED SESSION                      to role_test  ;
grant RESUMABLE                               to role_test  ;
grant SELECT ANY DICTIONARY                   to role_test  ;
grant SELECT ANY MEASURE FOLDER               to role_test  ;
grant SELECT ANY MINING MODEL                 to role_test  ;
grant SELECT ANY SEQUENCE                     to role_test  ;
grant SELECT ANY TABLE                        to role_test  ;
grant SELECT ANY TRANSACTION                  to role_test  ;
grant UPDATE ANY TABLE                        to role_test  ;
grant USE ANY JOB RESOURCE                    to role_test  ;
grant USE ANY SQL TRANSLATION PROFILE         to role_test  ;

-- 4、回收user_test用户的DBA权限
revoke DBA from user_test;