oracle数据库存储过程与权限
Oracle数据库存储过程与权限 在执行存储过程时，我们可能会遇到权限问
题
快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题
? 定义者权限存储过程
? 调用者权限存储过程
在数据库中创建存储过程时，定义者权限是缺省模式
当指定AUTHID CURRENT_USER关键字后，便是调用者权限存储过程 他俩之间最根本的差异在于role能否在存储过程中生效
? 定义者权限存储过程问题
定义者权限存储过程role无效，必须要有显式授权
即便是拥有dba role，还是不能访问不同用户的
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
grant connect,resource to u1 identified by u1;
Grant succeeded.
grant dba to u2 identified by u2; Grant succeeded.
conn u1/u1
Connected.
create table t as select * from user_objects;
Table created.
conn u2/u2
Connected.
create or replace procedure p_test 2 as
3 begin
4 delete from u1.t;
5 commit;
6 end;
7 /
Warning: Procedure created with compilation errors.
show error;
Errors for PROCEDURE P_TEST: LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored 4/18 PL/SQL: ORA-00942: table or view does not exist
conn u1/u1
Connected.
grant all on t to u2;
Grant succeeded.
conn u2/u2
Connected.
create or replace procedure p_test 2 as
3 begin
4 delete from u1.t;
5 commit;
6 end;
7 /
Procedure created.
? 调用者权限存储过程问题
调用者权限存储过程role编译不可见，但运行时可见 用动态SQL避免直接授权，而将权限的检查延后至运行时
conn u1/u1
Connected.
revoke all on t from u2; Revoke succeeded.
conn u2/u2
Connected.
create or replace procedure p_test
2 authid current_user
3 as
4 begin
5 delete from u1.t;
6 commit;
7 end;
8 /
Warning: Procedure created with compilation errors.
show error;
Errors for PROCEDURE P_TEST: LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3 PL/SQL: SQL Statement ignored 5/18 PL/SQL: ORA-00942: table or view does not exist
create or replace procedure p_test
2 authid current_user
3 as
4 begin
5 execute immediate
6 'delete from u1.t';
7 commit;
8 end;
9 /
Procedure created.
exec p_test;
PL/SQL procedure successfully completed.
select count(*) from u1.t; COUNT(*)
----------
0
浙公网安备 33021202002483