数据库中如何实现普通用户服务特殊管理权限

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章主要介绍数据库中如何实现普通用户服务特殊管理权限，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

普通用户服务生成AWR报告权限：
grant select any dictionary to user;
grant execute on DBMS_WORKLOAD_REPOSITORY to user;
测试通过过了。

普通用户赋予查看数据字典权限：
直接赋予
select_catalog_role角色给普通用户就可以了。

select_catalog_role角色包含了如下的权限。

SQL> select * from ROLE_ROLE_PRIVS where role='SELECT_CATALOG_ROLE';

ROLE GRANTED_ROLE ADMIN_OPT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ---------
SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE NO

该角色拥有的权限大体是如下列出的，由于权限太多，并未全部列出。
grant select on SYS.DBA_HIST_LATCH_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_LATCH_PARENT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_LIBRARYCACHE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_LOG to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_MEMORY_RESIZE_OPS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_MEMORY_TARGET_ADVICE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_MEM_DYNAMIC_COMP to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_METRIC_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_MTTR_TARGET_ADVICE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_MUTEX_SLEEP to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_MVPARAMETER to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_OPTIMIZER_ENV to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_OSSTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_OSSTAT_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PARAMETER to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PARAMETER_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PERSISTENT_QMN_CACHE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PERSISTENT_QUEUES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PERSISTENT_SUBS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PGASTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PGA_TARGET_ADVICE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PLAN_OPERATION_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PLAN_OPTION_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_PROCESS_MEM_SUMMARY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_RESOURCE_LIMIT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_ROWCACHE_SUMMARY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_Rclass="lazy" data-src_CONSUMER_GROUP to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_Rclass="lazy" data-src_PLAN to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_RULE_SET to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SEG_STAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SEG_STAT_OBJ to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SERVICE_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SERVICE_STAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SERVICE_WAIT_CLASS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SESSMETRIC_HISTORY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SESS_TIME_STATS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SGA to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SGASTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SGA_TARGET_ADVICE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SHARED_POOL_ADVICE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SHARED_SERVER_SUMMARY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SNAPSHOT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SNAP_ERROR to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQLBIND to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQLCOMMAND_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQLSTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQLTEXT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQL_BIND_METADATA to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQL_PLAN to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQL_SUMMARY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SQL_WORKAREA_HSTGRM to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_STAT_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_STREAMS_APPLY_SUM to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_STREAMS_CAPTURE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_STREAMS_POOL_ADVICE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SYSMETRIC_HISTORY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SYSMETRIC_SUMMARY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SYSSTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SYSTEM_EVENT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_SYS_TIME_MODEL to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_TABLESPACE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_TABLESPACE_STAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_TBSPC_SPACE_USAGE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_TEMPFILE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_TEMPSTATXS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_THREAD to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_TOPLEVELCALL_NAME to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_UNDOSTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_WAITCLASSMET_HISTORY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_WAITSTAT to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_HIST_WR_CONTROL to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_CONSTRAINT_EXP to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_GEN_STMTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_GEN_STMTS_EXP to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_OBJECTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_OBJECTS_BASE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_OBJECTS_EXP to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_POSTGEN_STMTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_PREGEN_STMTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_SITES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IAS_TEMPLATES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IDENTIFIERS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INDEXES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INDEXTYPES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INDEXTYPE_ARRAYTYPES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INDEXTYPE_COMMENTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INDEXTYPE_OPERATORS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IND_COLUMNS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IND_EXPRESSIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IND_PARTITIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IND_STATISTICS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_IND_SUBPARTITIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INTERNAL_TRIGGERS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_INVALID_OBJECTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_ARGUMENTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_CLASSES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_COMPILER_OPTIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_DERIVATIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_FIELDS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_IMPLEMENTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_INNERS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_LAYOUTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_METHODS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_NCOMPS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_POLICY to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_RESOLVERS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JAVA_THROWS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JOBS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JOBS_RUNNING to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_JOIN_IND_COLUMNS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_KGLLOCK to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LIBRARIES to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LMT_FREE_SPACE to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LMT_USED_EXTENTS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LOBS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LOB_PARTITIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LOB_SUBPARTITIONS to SELECT_CATALOG_ROLE;
grant select on SYS.DBA_LOB_TEMPLATES to SELECT_CATALOG_ROLE;