ora-01031:insufficient privileges

msroom

我在网上查询提示权限错误，但是自己还是找不出原因，


我单独运行问题，
SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  FROM dba_constraints ta,dba_cons_columns tb
where substr(ta.owner,1,8)= 'DBUSRPUB' and
                 ta.owner       = tb.owner and
       ta.constraint_name = tb.constraint_name and
       ta.table_name  = tb.table_name and
       ta.constraint_type(+) = 'P'


但是我创建视图的时候就出现问题咯
CREATE OR REPLACE VIEW REPTABCOLPK_VIEW AS
SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  FROM dba_constraints ta,dba_cons_columns tb
where substr(ta.owner,1,8)= 'DBUSRPUB' and
                 ta.owner       = tb.owner and
       ta.constraint_name = tb.constraint_name and
       ta.table_name  = tb.table_name and
       ta.constraint_type(+) = 'P'

chensq

没有创建视图的权限？

msroom

create or replace view test as
  select *
    from goods

创建其他视图都能正常创建

hrb_qiuyb

最初由 msroom 发布
[B]我在网上查询提示权限错误，但是自己还是找不出原因，


我单独运行问题，
SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  FROM dba_constraints ta,dba_cons_columns tb
where substr(ta.owner,1,8)= 'DBUSRPUB' and
                 ta.owner       = tb.owner and
       ta.constraint_name = tb.constraint_name and
       ta.table_name  = tb.table_name and
       ta.constraint_type(+) = 'P'


但是我创建视图的时候就出现问题咯
CREATE OR REPLACE VIEW REPTABCOLPK_VIEW AS
SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  FROM dba_constraints ta,dba_cons_columns tb
where substr(ta.owner,1,8)= 'DBUSRPUB' and
                 ta.owner       = tb.owner and
       ta.constraint_name = tb.constraint_name and
       ta.table_name  = tb.table_name and
       ta.constraint_type(+) = 'P' [/B]

你的问题的原因是要对dba_constraints ,dba_cons_columns 这两个object进行直接授权，通过role方式的授权是不可以的，见如下的测试：

[php]
SQL> connect qiuyb/qiuyb@zhjs;
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0
Connected as qiuyb

SQL> SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  2  FROM dba_constraints ta,dba_cons_columns tb
  3  where substr(ta.owner,1,8)= 'DBUSRPUB' and
  4  ta.owner = tb.owner and
  5  ta.constraint_name = tb.constraint_name and
  6  ta.table_name = tb.table_name and
  7  ta.constraint_type(+) = 'P'
  8  /

TABLE_NAME COLUMN_NAME       CONSTRAINT_TYPE
---------- ----------------- ---------------

SQL>
SQL> CREATE OR REPLACE VIEW REPTABCOLPK_VIEW AS
  2  SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  3  FROM dba_constraints ta,dba_cons_columns tb
  4  where substr(ta.owner,1,8)= 'DBUSRPUB' and
  5  ta.owner = tb.owner and
  6  ta.constraint_name = tb.constraint_name and
  7  ta.table_name = tb.table_name and
  8  ta.constraint_type(+) = 'P'
  9  /

ORA-01031: insufficient privileges

--以dba_cons_columns单独建立一个视图也不可以
SQL> create or replace view view1 as select * from dba_cons_columns;

ORA-01031: insufficient privileges

SQL> connect / as sysdba
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0
Connected as SYS

SQL> grant select on
SQL> CREATE OR REPLACE VIEW REPTABCOLPK_VIEW AS
  2  SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  3  FROM dba_constraints ta,dba_cons_columns tb
  4  where substr(ta.owner,1,8)= 'DBUSRPUB' and
  5  ta.owner = tb.owner and
  6  ta.constraint_name = tb.constraint_name and
  7  ta.table_name = tb.table_name and
  8  ta.constraint_type(+) = 'P'
  9  .
10  
SQL>
SQL> grant select on dba_cons_columns to qiuyb;

Grant succeeded

SQL> grant select on dba_constraints to qiuyb;

Grant succeeded

SQL> connect qiuyb/qiuyb@zhjs
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0
Connected as qiuyb

SQL>
SQL> CREATE OR REPLACE VIEW REPTABCOLPK_VIEW AS
  2  SELECT distinct ta.table_name,tb.column_name,ta.constraint_type
  3  FROM dba_constraints ta,dba_cons_columns tb
  4  where substr(ta.owner,1,8)= 'DBUSRPUB' and
  5  ta.owner = tb.owner and
  6  ta.constraint_name = tb.constraint_name and
  7  ta.table_name = tb.table_name and
  8  ta.constraint_type(+) = 'P'
  9  /

View created

SQL>

sssss
[/php]

msroom

问题解决咯，感谢

A8

　　怎么解决的