select * from scott.emp --执行这句,就是封装体外,间接权限就足够。create view vvv as select * from scott.emp --执行这句,就意味这你要把查询scott的表的权限放到封装体(视图)中,必须要给你的用户一个查询scott.emp的表的权限,而不是用dba这个角色带过来。 declare v_c number(10,0); begin select count(*) into v_c from dictionary; dbms_output.put_line(v_c); end; --这就是封装体外,大概不到2000条。create procedure ppp as v_c number(10,0); begin select count(*) into v_c from dictionary; dbms_output.put_line(v_c); end; begin ppp; end;--这就是封装体内,大概不到600条。--执行grant select any table to <用户> 后,两者的结果才会相等(当然给别的相应的权限也能)
SQL> select * from role_sys_privs where role='CONNECT' ;ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE SESSION NOSQL> select * from role_sys_privs where role='RESOURCE' ;ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE TYPE NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE TABLE NO RESOURCE CREATE INDEXTYPE NO 谁和你说的connect包含create view 权限?
SQL> grant create view to connect ;Grant succeeded.SQL> select * from role_sys_privs where role='CONNECT' ;ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE VIEW NO CONNECT CREATE SESSION NOSQL> create user dex2012 identified by xiaojun default tablespace users ;User created. SQL> alter user dex2012 quota unlimited on users ;User altered.SQL> grant connect to dex2012 ;Grant succeeded.SQL> grant create table to dex2012 ;Grant succeeded.SQL> grant create procedure to dex2012 ;Grant succeeded.SQL> conn dex2012/xiaojunSQL> create table t (x int) ;Table created. --直接创建是可以的 SQL> create view ass as select * from t ;View created. --匿名块也是可以的 SQL> begin 2 execute immediate 'create view ass1 as select * from t ' ; 3 end ; 4 /PL/SQL procedure successfully completed.SQL> create procedure pro_add_view is 2 begin 3 execute immediate 'create view ass2 as select * from t ' ; 4 end ; 5 /Procedure created. --这个时候时候才会报错 SQL> exec pro_add_view ; BEGIN pro_add_view ; END;* ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "DEX2012.PRO_ADD_VIEW", line 3 ORA-06512: at line 1SQL> conn / as sysdba Connected.--需要授权,或者直接修改存储过程 在procedure 名称后面加 authid current_user 即可 SQL> grant create view to dex2012 ;Grant succeeded.SQL> conn dex2012/xiaojun Connected. SQL> exec pro_add_view ;PL/SQL procedure successfully completed.SQL> select view_name from user_views ;VIEW_NAME ------------------------------ ASS ASS1 ASS2
resource角色不包含create view的系统权限了。以下是检查授予角色的系统权限 SQL> select * from role_sys_privs;ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE TYPE NO CONNECT CREATE SESSION NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE TABLE NO RESOURCE CREATE INDEXTYPE NO已选择9行。可以看到RESOURCE中没有‘create view’权限如何获得‘create view’权限呢? 1. 直接给用户授予‘create view’权限: 例如有一个test用户需要创建视图,在system用户下授予权限。 sql> grant create view to test;2. 给角色赋予‘create view’权限: sql> grant create view to RESOURCE;
resource角色不包含create view的系统权限了。以下是检查授予角色的系统权限 SQL> select * from role_sys_privs;ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE TYPE NO CONNECT CREATE SESSION NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE TABLE NO RESOURCE CREATE INDEXTYPE NO已选择9行。可以看到RESOURCE中没有‘create view’权限如何获得‘create view’权限呢? 1. 直接给用户授予‘create view’权限: 例如有一个test用户需要创建视图,在system用户下授予权限。 sql> grant create view to test;2. 给角色赋予‘create view’权限: sql> grant create view to RESOURCE;
select * from scott.emp --执行这句,就是封装体外,间接权限就足够。create view vvv as select * from scott.emp --执行这句,就意味这你要把查询scott的表的权限放到封装体(视图)中,必须要给你的用户一个查询scott.emp的表的权限,而不是用dba这个角色带过来。
declare
v_c number(10,0);
begin
select count(*) into v_c from dictionary;
dbms_output.put_line(v_c);
end;
--这就是封装体外,大概不到2000条。create procedure ppp as
v_c number(10,0);
begin
select count(*) into v_c from dictionary;
dbms_output.put_line(v_c);
end; begin
ppp;
end;--这就是封装体内,大概不到600条。--执行grant select any table to <用户> 后,两者的结果才会相等(当然给别的相应的权限也能)
SQL> select * from role_sys_privs where role='CONNECT' ;ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NOSQL> select * from role_sys_privs where role='RESOURCE' ;ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
谁和你说的connect包含create view 权限?
SQL> grant create view to connect ;Grant succeeded.SQL> select * from role_sys_privs where role='CONNECT' ;ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE VIEW NO
CONNECT CREATE SESSION NOSQL> create user dex2012 identified by xiaojun default tablespace users ;User created.
SQL> alter user dex2012 quota unlimited on users ;User altered.SQL> grant connect to dex2012 ;Grant succeeded.SQL> grant create table to dex2012 ;Grant succeeded.SQL> grant create procedure to dex2012 ;Grant succeeded.SQL> conn dex2012/xiaojunSQL> create table t (x int) ;Table created.
--直接创建是可以的
SQL> create view ass as select * from t ;View created.
--匿名块也是可以的
SQL> begin
2 execute immediate 'create view ass1 as select * from t ' ;
3 end ;
4 /PL/SQL procedure successfully completed.SQL> create procedure pro_add_view is
2 begin
3 execute immediate 'create view ass2 as select * from t ' ;
4 end ;
5 /Procedure created.
--这个时候时候才会报错
SQL> exec pro_add_view ;
BEGIN pro_add_view ; END;*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "DEX2012.PRO_ADD_VIEW", line 3
ORA-06512: at line 1SQL> conn / as sysdba
Connected.--需要授权,或者直接修改存储过程 在procedure 名称后面加 authid current_user 即可
SQL> grant create view to dex2012 ;Grant succeeded.SQL> conn dex2012/xiaojun
Connected.
SQL> exec pro_add_view ;PL/SQL procedure successfully completed.SQL> select view_name from user_views ;VIEW_NAME
------------------------------
ASS
ASS1
ASS2
SQL> select * from role_sys_privs;ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
CONNECT CREATE SESSION NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO已选择9行。可以看到RESOURCE中没有‘create view’权限如何获得‘create view’权限呢?
1. 直接给用户授予‘create view’权限:
例如有一个test用户需要创建视图,在system用户下授予权限。
sql> grant create view to test;2. 给角色赋予‘create view’权限:
sql> grant create view to RESOURCE;
SQL> select * from role_sys_privs;ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
CONNECT CREATE SESSION NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO已选择9行。可以看到RESOURCE中没有‘create view’权限如何获得‘create view’权限呢?
1. 直接给用户授予‘create view’权限:
例如有一个test用户需要创建视图,在system用户下授予权限。
sql> grant create view to test;2. 给角色赋予‘create view’权限:
sql> grant create view to RESOURCE;