sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle权限管理 授权用户访问Oracle数据字典三种方式

时间:2016-07-23 22:22   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇关于Oracle数据库权限管理文章,对授权用户访问Oracle数据字典三种方式进行总结

在很多时候,希望给用户最小的权限,让其访问系统数据字典,检查数据库的运行状态。这种事情在乙方的工作中非常常见。下面介绍三种方法处理这个问题

1.select any table权限

这里说明select any table不能直接访问数据字典

SQL> conn / as sysdba
Connected.
SQL> create user xff_any identified by oracleplus;

User created.

SQL> grant connect,select any table to xff_any;

Grant succeeded.

SQL> conn xff_any/oracleplus
Connected.
SQL>  select count(*) from dba_users;
 select count(*) from dba_users
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from chf.t_xff;

  COUNT(*)
----------
      2770

select any table默认情况下,只能访问业务的表,但是不能访问数据字典的数据。所以单纯的这个属性不能满足需求。

2.SELECT ANY DICTIONARY权限

SQL> conn / as sysdba 
Connected.
SQL> create user xff_DICTIONARY identified by oracleplus;

User created.

SQL> grant connect to xff_DICTIONARY;

Grant succeeded.

SQL> conn xff_DICTIONARY/oracleplus
Connected.
SQL> select count(*) from dba_users;
select count(*) from dba_users
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> conn / as sysdba
Connected.
SQL> grant SELECT ANY DICTIONARY to xff_DICTIONARY;

Grant succeeded.

SQL> conn xff_DICTIONARY/oracleplus
Connected.
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        32

SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

这里可以看出SELECT ANY DICTIONARY权限只能访问数据字典,不能访问业务的表,访问业务的表需要另外授权

3.SELECT_CATALOG_ROLE角色

SQL> conn / as sysdba
Connected.
SQL> create user xff_CATALOG identified by oracleplus;

User created.

SQL> grant connect,SELECT_CATALOG_ROLE to xff_CATALOG;

Grant succeeded.

SQL> conn xff_CATALOG/oracleplus
Connected.
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        33

SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

这里可以看出SELECT_CATALOG_ROLE权限只能访问数据字典,不能访问业务的表,访问业务的表需要另外授权

4.O7_DICTIONARY_ACCESSIBILITY参数

SQL> conn / as sysdba
Connected.
SQL> create user xff_O7 identified by oracleplus;

User created.

SQL> grant connect to xff_o7;

Grant succeeded.

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true;
alter system set O7_DICTIONARY_ACCESSIBILITY=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> conn xff_o7/oracleplus
Connected.
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        34

这里通过O7_DICTIONARY_ACCESSIBILITY和SELECT ANY TABLE权限,实现访问业务数据和数据字典


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle权限管理 授权用户访问Oracle数据字典三种方式

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/857.html

Oracle研究中心

关键词:

Oracle权限管理

授权用户访问Oracle数据字典三种方式