oracle 权限表视图,创建视图时引用表的权限问题

在做imp的时候，发现log中有以下报错：

……

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(+)"

……

1

2

3

4

5

6

7

8

……

IMP-00041:Warning:objectcreatedwithcompilationwarnings

"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却是能够操作：

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

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 *fromV_VIEW01whererownum<10;

select *fromV_VIEW01whererownum<10

*

ERRORatline1:

ORA-04063:view"MYUESR.V_VIEW01"haserrors

SQL>showerrorviewV_VIEW01

ErrorsforVIEWV_VIEW01:

LINE/COLERROR

-------------------------------------------------------------------------

0/0ORA-01031:insufficientprivileges

SQL>

SQL>selectcount(*)fromuser01.icp;

COUNT(*)

----------

976

SQL>selectcount(*)fromuser01.custcontact;

COUNT(*)

----------

1310

奇怪的是，MYUESR已经有dba权限，难道还不能建视图：

于是进行以下测试：

先创建2个用户，并给予connect和resource权限

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>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SQL>createuseruser1identifiedbyuser1;

Usercreated.

SQL>createuseruser2identifiedbyuser2;

Usercreated.

SQL>grantconnect,resourcetouser1,user2;

Grantsucceeded.

SQL>

SQL>

用user2用户建表和视图，其中表和视图是引用到user1的表：user1.main，发现就算给user2 dba权限，建视图还是会报权限不够。但是建表没问题。

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

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>connuser1/user1

Connected.

SQL>showuser

USERis"user1"

SQL>createtableuser1.main(iintprimarykey,avarchar2(10));

Tablecreated.

SQL>

SQL>connuser2/user2

Connected.

SQL>showuser

USERis"user2"

SQL>createviewuser2.ts_viewasselect *fromuser1.main;

createviewuser2.ts_viewasselect *fromuser1.main

*

ERRORatline1:

ORA-00942:tableorviewdoesnotexist

SQL>createtableuser2.ts_tabasselect *fromuser1.main;

createtableuser2.ts_tabasselect *fromuser1.main

*

ERRORatline1:

ORA-00942:tableorviewdoesnotexist

SQL>

SQL>---grantdbatouser2

SQL>conn/assysdba

Connected.

SQL>grantdbatouser2;

Grantsucceeded.

SQL>connuser2/user2

Connected.

SQL>

SQL>createviewuser2.ts_viewasselect *fromuser1.main;

createviewuser2.ts_viewasselect *fromuser1.main

*

ERRORatline1:

ORA-01031:insufficientprivileges

SQL>createtableuser2.ts_tabasselect *fromuser1.main;

Tablecreated.

SQL>

必须进行显式授权，才能建立视图：

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>

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>--grantselectanytouser2

SQL>conn/assysdba

Connected.

SQL>grantselectanytabletouser2;

Grantsucceeded.

SQL>connuser2/user2

Connected.

SQL>createviewuser2.ts_viewasselect *fromuser1.main;

Viewcreated.

SQL>--creaetviewsuccessful

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

另外，在有dba权限和select any table的权限下，尝试在user2下建表，表有外键约束，参照user1用户下的main表，发现也是报错的：

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

SQL>--trytocreatetableusingreferencetouser1.main.i

SQL>createtableuser2.child(idreferencesuser1.main(i),avarchar2(10));

createtableuser2.child(idreferencesuser1.main(i),avarchar2(10))

*

ERRORatline1:

ORA-01031:insufficientprivileges

SQL>

必须也进行显式授权：

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

2

3

4

5

6

7

8

9

10

11

12

13

SQL>--grantreferencestouser2

SQL>conn/assysdba

Connected.

SQL>grantreferencesonuser1.maintouser2

SQL>/

Grantsucceeded.

SQL>connuser2/user2

Connected.

SQL>createtableuser2.child(idreferencesuser1.main(i),avarchar2(10));

Tablecreated.

结论：

1.建view的时候如果需要select别的schema的表，必须显式授权(授予dba 的role角色没用)

2.建table的时候如果需要reference别的schema的表，也必须显示授权(授予dba 的role角色没用)