| 标题 | Oracle数据库存储过程与权限 |
| 内容 | 在执行存储过程时，我们可能会遇到权限问题 ● 定义者权限存储过程 ● 调用者权限存储过程 在数据库中创建存储过程时，定义者权限是缺省模式 当指定AUTHID CURRENT_USER关键字后，便是调用者权限存储过程 他俩之间最根本的差异在于role能否在存储过程中生效 ㈠ 定义者权限存储过程问题 定义者权限存储过程role无效，必须要有显式授权 即便是拥有dba role，还是不能访问不同用户的表 > 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 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。