SQL> create view test_view as select *from customer;
create view test_view as select *from customer
*
ERROR at line 1:
ORA-01031: insufficient privileges
第一反应是这个schema没有create view的权限。
查询发现有。
SQL> select *from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE ROLE
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
----------------------------------------
CREATE MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
SELECT ANY DICTIONARY
16 rows selected.
而且表的访问也是没有问题的，权限通过role来grant,而且建立了synonym。
SQL> select *from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TRUDB1 TRUDBO1_ALL NO YES NO
SQL> select count(*)from customer;
COUNT(*)
----------
0
最后发现如果创建视图，通过role来授予权限，是有这种情况的，得赋予object privilege
用table owner登录。
SQL> grant select on customer to trudb1;
Grant succeeded.
然后用目标用户登录，创建视图
SQL> create view test_view as select *from customer;
View created.
SQL> drop view test_view;
View dropped.
SQL> select count(*)from customer;
COUNT(*)
----------
0