create view , any view 권한

뷰를 생성하기위해서는 create view 권한이 필요하다. 

자신의 스키마가 아닌 다른 스키마에 뷰를 생성하기 위해서는 create any view 권한이 필요하다. 

1. grant create view

 

2. view vs any view

 

 

 

 ===  1. grant create view ====

 

create user USER_A identified by "q1w2e3r4";

grant resource to USER_A;

grant connect to USER_A;

exit

 

 

sqlplus USER_A/q1w2e3r4

 

create table USER_A.test_tab(a int);

 

Table created.

 

 

create or replace view USER_A.vw_a

as

select '1' a

from USER_A.test_tab

;

ORA-01031: insufficient privileges

 

exit

sqlplus / as sysdba

grant create view to USER_A;

 

exit

 

sqlplus USER_A/q1w2e3r4

 

create or replace view USER_A.vw_a

as

select '1' a

from USER_A.test_tab

;

View created.

 

exit

===  2. grant create any view ====

sqlplus / as sysdba

 

create user USER_B identified by "q1w2e3r4";

grant resource to USER_B;

grant connect to USER_B;

grant create view to USER_B;

grant select on USER_A.test_tab to USER_B;

 

exit

 

sqlplus USER_B/q1w2e3r4

 

create or replace view USER_B.vw_a

as

select '1' a

from USER_A.test_tab

;

View created.

exit

 

sqlplus USER_A/q1w2e3r4

 

create or replace view USER_B.vw_a2

as

select '1' a

from USER_A.test_tab

;

 

ORA-01031: insufficient privileges

 

 

exit

sqlplus / as sysdba

 

grant create any view to USER_A;

 

 

create or replace view USER_B.vw_a2

as

select '1' a

from USER_A.test_tab

;

View created.

 

drop user USER_A cascade;

drop user USER_B cascade;

 

===  3. 뷰 권한 확인  ====

select * from dba_sys_privs where grantee like 'USER%';