在做imp的时候，发现log中有以下报错：
|
1 2 3 4 5 6 7 8 |
…… IMP-00041: Warning: object created with compilation warnings "CREATE FORCE VIEW "MYUESR"."V_VIEW01" ("ICPCOD" "E") AS " "select user02.IcpCode" "from user01.icp icp,user01.custcontact custcontact" "where user02.id = custcontact.id(+)" …… |
通过直接在数据库查询，发现报错没有权限，但是select却是能够操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SQL> select * from V_VIEW01 where rownum<10; select * from V_VIEW01 where rownum<10 * ERROR at line 1: ORA-04063: view "MYUESR.V_VIEW01" has errors SQL> show error view V_VIEW01 Errors for VIEW V_VIEW01: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-01031: insufficient privileges SQL> SQL> select count(*) from user01.icp; COUNT(*) ---------- 976 SQL> select count(*) from user01.custcontact; COUNT(*) ---------- 1310 |
奇怪的是，MYUESR已经有dba权限，难道还不能建视图：
于是进行以下测试：
先创建2个用户，并给予connect和resource权限
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> create user user1 identified by user1; User created. SQL> create user user2 identified by user2; User created. SQL> grant connect,resource to user1,user2; Grant succeeded. SQL> SQL> |
用user2用户建表和视图，其中表和视图是引用到user1的表：user1.main，发现就算给user2 dba权限，建视图还是会报权限不够。但是建表没问题。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
SQL> conn user1/user1 Connected. SQL> show user USER is "user1" SQL> create table user1.main(i int primary key,a varchar2(10)); Table created. SQL> SQL> conn user2/user2 Connected. SQL> show user USER is "user2" SQL> create view user2.ts_view as select * from user1.main; create view user2.ts_view as select * from user1.main * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table user2.ts_tab as select * from user1.main; create table user2.ts_tab as select * from user1.main * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> ---grant dba to user2 SQL> conn / as sysdba Connected. SQL> grant dba to user2; Grant succeeded. SQL> conn user2/user2 Connected. SQL> SQL> create view user2.ts_view as select * from user1.main; create view user2.ts_view as select * from user1.main * ERROR at line 1: ORA-01031: insufficient privileges SQL> create table user2.ts_tab as select * from user1.main; Table created. SQL> |
必须进行显式授权，才能建立视图：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> --grant select any to user2 SQL> conn / as sysdba Connected. SQL> grant select any table to user2; Grant succeeded. SQL> conn user2/user2 Connected. SQL> create view user2.ts_view as select * from user1.main; View created. SQL> --creaet view successful SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> |
另外，在有dba权限和select any table的权限下，尝试在user2下建表，表有外键约束，参照user1用户下的main表，发现也是报错的：
|
1 2 3 4 5 6 7 8 9 |
SQL> -- try to create table using reference to user1.main.i SQL> create table user2.child(id references user1.main(i),a varchar2(10)); create table user2.child(id references user1.main(i),a varchar2(10)) * ERROR at line 1: ORA-01031: insufficient privileges SQL> |
必须也进行显式授权：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> --grant references to user2 SQL> conn / as sysdba Connected. SQL> grant references on user1.main to user2 SQL> / Grant succeeded. SQL> conn user2/user2 Connected. SQL> create table user2.child(id references user1.main(i),a varchar2(10)); Table created. |
结论：
1.建view的时候如果需要select别的schema的表，必须显式授权（授予dba 的role角色没用）
2.建table的时候如果需要reference别的schema的表，也必须显示授权（授予dba 的role角色没用）