Shulou(Shulou.com)06/01 Report--

Today, I encountered a small problem in the course of testing, which made me wonder: why is it OK when you execute alone, but you don't have permission when you create a view?

SEIANG@seiang11g > select value from v$mystat, v$statname 2 where v$mystat.statistic# = v$statname.statistic# 3 and v$statname.name = 'redo size'; VALUE- 29152SEIANG@seiang11g > create or replace view redo_size1 2 as 3 select value from v$mystat, v$statname 4 where v$mystat.statistic# = v$statname.statistic# 5 and v$statname.name =' redo size';select value from v$mystat, v$statname * ERROR at line 3:ORA-01031: insufficient privileges

At first, I thought I didn't have create view permissions, so I queried the seiang user's de-permissions view:

SEIANG@seiang11g > select * from role_sys_privs where PRIVILEGE = 'CREATE VIEW'

ROLE PRIVILEGE ADM

-

DBA CREATE VIEW YES

Problem solving:

The first time:

SYS@seiang11g > GRANT SELECT ANY DICTIONARY to seiang

Grant succeeded.

SEIANG@seiang11g > create or replace view redo_size 2 as 3 select value from v$mystat, v$statname 4 where v$mystat.statistic# = v$statname.statistic# 5 and v$statname.name = 'redo size'; View created. To reclaim permissions, continue to try SYS@seiang11g > revoke SELECT ANY DICTIONARY from seiang; Revoke succeeded. SEIANG@seiang11g > create or replace view redo_size1 2 as 3 select value from v$mystat, v$statname 4 where v$mystat.statistic# = v$statname.statistic# 5 and v$statname.name = 'redo size';select value from v$mystat, v$statname * ERROR at line 3:ORA-01031: insufficient privileges second time: SYS@seiang11g > grant select on v$statname to seiang Grant select on v$statname to seiang * ERROR at line 1:ORA-02030: can only select from fixed tables/views SYS@seiang11g > select * from dba_synonyms t where t.synonym_name = 'Venture STATNAME' OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC V$STATNAME SYS V_$STATNAME

Analysis: for views that start with v$, views that cannot start directly with grant,v$ are synonymous with vested $

The third time: SYS@seiang11g > grant select on v_$statname to seiang; Grant succeeded. SEIANG@seiang11g > create or replace view redo_size1 2 as 3 select value from v$mystat, v$statname 4 where v$mystat.statistic# = v$statname.statistic# 5 and v$statname.name = 'redo size';select value from v$mystat, v$statname * ERROR at line 3:ORA-01031: insufficient privileges SYS@seiang11g > grant select on v_$mystat to seiang; Grant succeeded. SEIANG@seiang11g > create or replace view redo_size3 2 as 3 select value from v$mystat, v$statname 4 where v$mystat.statistic# = v$statname.statistic# 5 and v$statname.name = 'redo size'; View created.

The following is an explanation of the Oracle 11g official documentation:

*

To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATEANY VIEW system privilege.

To create a view in your own schema, you must have CREATE VIEW system access. To create a view in another user's schema, you must have CREATE ANY VIEW system permissions.

To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.

To create a child view, you must have UNDER ANY VIEW system permissions or UNDER object permissions for that super view.

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

The owner of the view schema must have the necessary permissions to select, insert, update, or delete rows from the view (all tables or views on which it is based). The owner must grant these permissions directly, not through roles.

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.