song,user1,user2 都有dba的角色
一, 执行sys下的 procedure时 ,必须显示赋权 grant execute on procedure_name to user_name;
二, 在sys下, 不需要被显示赋于对像的权限。 别的用户需要
三, 执行别的用户(不包括sys)下的procedure时, 不需要执行用户的显示的赋权
证明:
SQL> conn /as sysdba
Connected.
SQL> create or replace procedure t_pro as
2 begin
3 null;
4 insert into user1.t_t values ('a');
5 end;
6 /
Procedure created. --sys用户 不需要 被显示给user1.t_t的权限
SQL> grant dba to song;
Grant succeeded.
SQL> conn song/song
Connected.
SQL> exec sys.t_pro;
BEGIN sys.t_pro; END; --song执行sys的, 执行不了,但是报的错不是没有权限
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.T_PRO' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn /as sysdba
Connected.
SQL> exec sys.t_pro;
PL/SQL procedure successfully completed.
SQL> show user
USER is "SYS"
SQL> grant execute any procedure to song; --给执行任何procedure的权限
Grant succeeded.
SQL> conn song/song
Connected.
SQL> exec sys.t_pro;
BEGIN sys.t_pro; END; --还是执行不了,(可以认为给的any procedure不包含sys下的对象)
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.T_PRO' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn /as sysdba
Connected.
SQL> grant execute on t_pro to song; --显示赋于执行权限
Grant succeeded.
SQL> conn song/song
Connected.
SQL> exec sys.t_pro; --成功
PL/SQL procedure successfully completed.
SQL> conn song/song
Connected.
SQL> create or replace procedure t_pro_song as
2 begin
3 null;
4 insert into user1.t_t values ('b');
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE T_PRO_SONG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/19 PL/SQL: ORA-01031: insufficient privileges --有dba权限也用不了user1的t_t表
SQL> conn user1/user1
Connected.
SQL> grant insert on t_t to song;
Grant succeeded.
SQL> conn song/song
Connected.
SQL> create or replace procedure t_pro_song as
2 begin
3 null;
4 insert into user1.t_t values ('b');
5 end;
6 /
Procedure created. --显示给权限之后,可以
SQL> conn user2/user2
Connected.
SQL> exec song.t_pro_song; --user2 虽没有显示赋权,但是可以执行
PL/SQL procedure successfully completed.
SQL> select * from user1.t_t;
NAME
----------
user1
a
a
b
SQL> conn song/song
Connected.
SQL> create or replace procedure t_pro_song_a AUTHID CURRENT_USER as --以AUTHID CURRENT_USER 定义,下面还是可以执行
2 begin
3 null;
4 insert into user1.t_t values ('b');
5 end;
6 /
Procedure created.
SQL> conn user2/user2
Connected.
SQL> exec song.t_pro_song_a;
PL/SQL procedure successfully completed.
SQL> select * from user1.t_t;
NAME
----------
user1
a
a
b
b
by song
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-772367/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25099483/viewspace-772367/