情形:创建一个oracle用户testuser,并赋予v$session视图的查询权限。
SQL> conn / as sysdba
Connected.
SQL> create user testuser identified by testuser;
User created.
SQL> grant connect,resource to testuser;
Grant succeeded.
SQL> grant select on v$session to testuser;
grant select on v$session to testuser
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL>
报错了!感觉不可能啊。查了下oracle官方文档:
没看懂什么意思,在网上查了好多资料,终于明白官方文档的意思了。
下面是我解决该错误,所涉及的知识点。
1、Static Data Dictionary Views
They are called static, because they change only when a change is made to the data dictionary (for example, when a new table is created or when a user is granted new privileges).
Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY
.
Many data dictionary tables have three corresponding views:
(1) USER_XXX : displays all the information from the schema of the current user. No special privileges are required to query these views.
(2) ALL_XXX : displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
(3) DBA_XXX : displays all relevant information in the entire database.
DBA_
views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE
privilege. This privilege is assigned to the DBA
role when the system is initially installed.
2、Dynamic Performance Views
Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS
. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.
Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.The catalog.sql
script contains definitions of the views and public synonyms for the dynamic performance views. You must run catalog.sql
to create these views and synonyms. After installation, only user SYS
or anyone with SYSDBA
role has access to the dynamic performance tables.
V$ Views
The actual dynamic performance views are identified by the prefix V_$
. Public synonyms for these views have the prefix V$
. Database administrators and other users should access only the V$
objects, not the V_$
objects.
The dynamic performance views are used by Oracle Enterprise Manager, which is the primary interface for accessing information about system performance. After an instance is started, the V$
views that read from memory are accessible. Views that read data from disk require that the database be mounted, and some require that the database be open.
大家看到这里,可能还是不够理解上面的赋权限语句为什么错误?
下面我就用大白话的形式说一下我的理解哇。
ORA-02030: can only select from fixed tables/views
这里的意思是: 对于fixed tables/views 只能查询,不能做其他操作,例如GRANT SELECT ON V$SESSION TO testuser;
利用V$FIXED_TABLE来查询数据库都有哪些fixed tables/views.
V$FIXED_TABLE : This view displays all dynamic performance tables, views, and derived tables in the database. Some V$
tables (for example, V$ROLLNAME
) refer to real tables and are therefore not listed.
可以利用V$FIXED_VIEW_DEFINITION来查询某一个fixed table/view的定义。
V$FIXED_VIEW_DEFINITION : This view contains the definitions of all the fixed views
3、fixed tables/views只可以执行select或desc
4、fixed tables/views只在v$fixed_table存在
5、非sys用户如何访问fixed tables/views (这里以v$session为例)
方法一:granting SELECT privilege to testuser on V_$SESSION
SQL> grant select on v$session to testuser;
grant select on v$session to testuser
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL> grant select on V_$SESSION to testuser;
Grant succeeded.
SQL> conn testuser/testuser
Connected.
SQL> select count(*) from v$session;
COUNT(*)
----------
30
方法二:creating a view based on V$SESSION and granting SELECT privilege to testuser on the view that was created
SQL> conn / as sysdba
Connected.
SQL> create view db_session as select * from v$session;
View created.
SQL> grant select on db_session to testuser;
Grant succeeded.
SQL> conn testuser/testuser
Connected.
SQL> select count(*) from v$session;
COUNT(*)
----------
30
SQL>