oracle存储过程的定义者权限与调用者权限 |
您所在的位置：网站首页 › oracle数据库储存过程 › oracle存储过程的定义者权限与调用者权限 |
|
oracle存储过程的定义者权限与调用者权限
当某个用户定义的存储过程由另一个用户调用时，需要指定存储过程的Definer（定义者权限 )与Invoker（调用者权限）。 一、存储过程的定义者权限存储过程默认为该权限，也可以在定义存储过程的is|as之前添加authid definer参数，格式如下： create [or replace] procedure 名称 [(参数1 in | out | in out 类型, 参数2 in | out | in out 类型, ...)] authid definer is|as --变量声明区间 begin --业务逻辑区间 exception --异常处理 end; /说明：如果一个存储过程的执行权限为定义者权限，则当另一个用户调用该存储过程时，和存储过程的所有者调用该存储过程是一样的（就相当于是存储过程的所有者调用了该存储过程）。 测试效果： 1、在scott用户下创建一张表t1，并输入数据 create table t1( id number(6) primary key, name varchar2(20), age number(2), phone varchar2(20) ); SQL> select * from t1; ID NAME AGE PHONE ---------- -------------------- ---------- -------------------- 101 JACK 25 13603735588 102 JERRY 21 13503736645 103 TOM 32 13037350422 104 ROSE 28 13903732266 105 MARK 19 15903736633 106 JIMMI 20 15037355066 6 rows selected. 2、在scott用户下创建一个存储过程，使用默认的definer权限，查看某个用户的姓名和电话 SQL> create or replace procedure sp_get_t1 (v_id number, v_name out varchar2, v_phone out varchar2) authid definer is begin select name,phone into v_name,v_phone from t1 where id=v_id; exception when no_data_found then dbms_output.put_line('没有该用户信息！'); end; 15 / Procedure created. 3、查看该存储过程的信息该存储过程的执行权限为DEFINER（定义者权限）。 SQL> select object_name,procedure_name,authid from user_procedures where object_name='SP_GET_T1'; OBJECT_NAME PROCEDURE_NAME AUTHID ------------------------------ -------------------- ------------ SP_GET_T1 DEFINER 4、调用该存储过程 SQL> declare v_id t1.id%type; v_name t1.name%type; v_phone t1.phone%type; begin v_id:=&t1_id; sp_get_t1(v_id,v_name,v_phone); dbms_output.put_line( v_id||'用户的姓名：'||v_name|| ',电话：'||v_phone); end; 12 / Enter value for t1_id: 103 old 6: v_id:=&t1_id; new 6: v_id:=103; 103用户的姓名：TOM,电话：13037350422 PL/SQL procedure successfully completed. 5、创建一个用户JACK，在该用户下创建表t1，并输入数据 （1）创建用户JACK并授权 SQL> show user; USER is "SYS" SQL> create user JACK identified by JACK; User created. Elapsed: 00:00:00.09 Elapsed: 00:00:00.03 SQL> grant resource,connect to JACK; Grant succeeded. （2）以JACK用户身份登录，创建表t1并输入数据 SQL> show user; USER is "JACK" SQL> create table t1( id number(6) primary key, name varchar2(20), age number(2), phone varchar2(20) 6 ); Table created. SQL> select * from t1; ID NAME AGE PHONE ---------- -------------------- ---------- -------------------- 101 张飞 25 100801 102 赵云 21 200258 103 关羽 32 400125 6、以scott用户身份登录，把存储过程sp_get_t1的执行权限授予用户JACK SQL> show user; USER is "SCOTT" SQL> grant execute on sp_get_t1 to JACK; Grant succeeded. 7、以用户JACK身份登录，调用存储过程sp_get_t1并查看结果 SQL> show user; USER is "JACK" SQL> declare v_id t1.id%type; v_name t1.name%type; v_phone t1.phone%type; begin v_id:=&t1_id; sp_get_t1(v_id,v_name,v_phone); dbms_output.put_line( v_id||'用户的姓名：'||v_name|| ',电话：'||v_phone); end; 11 12 / Enter value for t1_id: 103 old 6: v_id:=&t1_id; new 6: v_id:=103; 103用户的姓名：TOM,电话：13037350422 PL/SQL procedure successfully completed. 此时发现，用户JACK调用存储过程sp_get_t1的输出结果和存储过程的定义者scott调用该存储过程的结果是完全相同的。 二、存储过程的调用者权限可以在定义存储过程的is|as之前添加authid current_user参数，格式如下： create [or replace] procedure 名称 [(参数1 in | out | in out 类型, 参数2 in | out | in out 类型, ...)] authid current_user is|as --变量声明区间 begin --业务逻辑区间 exception --异常处理 end; /说明：如果一个存储过程的执行权限为调用者权限，则当另一个用户调用该存储过程时，就相当于该用户自己创建了一个完全相同的存储过程。 测试效果： 1、在scott用户下创建一个存储过程，使用current_user权限，查看某个用户的姓名和电话 SQL> show user; USER is "SCOTT" SQL> create or replace procedure sp_get_t1_02 (v_id number, v_name out varchar2, v_phone out varchar2) authid current_user is begin select name,phone into v_name,v_phone from t1 where id=v_id; exception when no_data_found then dbms_output.put_line('没有该用户信息！'); end; 15 / Procedure created. 2、查看该存储过程的信息该存储过程的执行权限为CURRENT_USER（调用者权限）。 SQL> select object_name,procedure_name,authid from user_procedures where object_name='SP_GET_T1_02'; OBJECT_NAME PROCEDURE_NAME AUTHID ------------------------------ -------------------- ------------ SP_GET_T1_02 CURRENT_USER 3、scott用户调用该存储过程 SQL> show user; USER is "SCOTT" SQL> declare v_id t1.id%type; v_name t1.name%type; v_phone t1.phone%type; begin v_id:=&t1_id; sp_get_t1_02(v_id,v_name,v_phone); dbms_output.put_line( v_id||'用户的姓名：'||v_name|| ',电话：'||v_phone); end; 12 / Enter value for t1_id: 103 old 6: v_id:=&t1_id; new 6: v_id:=103; 103用户的姓名：TOM,电话：13037350422 PL/SQL procedure successfully completed. 4、以scott用户身份登录，把存储过程sp_get_t1_02的执行权限授予用户JACK SQL> show user; USER is "SCOTT" SQL> SQL> grant execute on sp_get_t1_02 to JACK; Grant succeeded. 5、以用户JACK身份登录，调用存储过程sp_get_t1_02并查看结果 SQL> show user; USER is "JACK" SQL> declare v_id t1.id%type; v_name t1.name%type; v_phone t1.phone%type; begin v_id:=&t1_id; scott.sp_get_t1_02(v_id,v_name,v_phone); dbms_output.put_line( v_id||'用户的姓名：'||v_name|| ',电话：'||v_phone); end; 12 / Enter value for t1_id: 103 old 6: v_id:=&t1_id; new 6: v_id:=103; 103用户的姓名：关羽,电话：400125 PL/SQL procedure successfully completed. 此时发现，用户JACK调用存储过程sp_get_t1_02，就像自己编写了一个存储过程，调用的是自己的t1表。 四、查看存储过程信息 1、查看当前用户所定义的所有存储过程的名称 SQL> select object_name,procedure_name,object_type,deterministic,authid from user_procedures; OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE DET AUTHID ------------------------------ -------------------- ------------------- --- ------------ SP_EMP_INSERT PROCEDURE NO DEFINER SP_SET_EMP_SAL PROCEDURE NO DEFINER SP_DISP PROCEDURE NO DEFINER SP_CIRCLE_AREA PROCEDURE NO DEFINER SP_UPDATE_EMP_SAL PROCEDURE NO DEFINER SP_GET_EMP PROCEDURE NO DEFINER SP_UPDATE_EMP_SAL_01 PROCEDURE NO DEFINER SP_INSERT_T01_FORALL PROCEDURE NO DEFINER SP_SCORE_GRADE PROCEDURE NO DEFINER SP_DELETE_T1_FORALL PROCEDURE NO DEFINER SP_INSERT_T01_FORALL02 PROCEDURE NO DEFINER SP_UPDATE_T1_FORALL PROCEDURE NO DEFINER SP_INSERT_T01 PROCEDURE NO DEFINER SP_UPDATE_EMP_BULK PROCEDURE NO DEFINER SP_UPDATE_T01_FORALL PROCEDURE NO DEFINER SP_EMP_ENAME_DEPT PROCEDURE NO DEFINER SP_EMP_DEPTNO11 PROCEDURE NO DEFINER SP_EMP_DEPTNO PROCEDURE NO DEFINER SP_FETCH_EMP PROCEDURE NO DEFINER PS_ADD PROCEDURE NO DEFINER SP_ADD PROCEDURE NO DEFINER SET_SAL PROCEDURE NO DEFINER GET_EMP PROCEDURE NO DEFINER TEST_CREATE PROCEDURE NO DEFINER UPDATE_SAL PROCEDURE NO DEFINER ADD_SAL PROCEDURE NO DEFINER 26 rows selected. 2、查看存储过程的参数信息 SQL> desc get_emp; PROCEDURE get_emp Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_EMPNO NUMBER IN V_ENAME VARCHAR2 OUT V_SAL NUMBER OUT 3、查看某个存储过程的代码 SQL> select text from user_source where name='SP_UPDATE_EMP_SAL'; TEXT -------------------------------------------------------------------------------- procedure sp_update_emp_sal as type t_table_emp is table of emp%rowtype index by binary_integer; v_table_emp t_table_emp; begin update emp set sal=sal*1.1 where deptno=10; select * bulk collect into v_table_emp from emp where deptno=10; for i in v_table_emp.first..v_table_emp.last loop dbms_output.put_line('雇员编号：'||v_table_emp(i).empno|| ', 姓名：'||v_table_emp(i).ename|| ', 工资：'||v_table_emp(i).sal); end loop; end; 14 rows selected. 五、存储过程应用举例 1、无参数存储过程把emp表10部分所有人的工资增加10%，并且显示员工编号、姓名及工资信息。 SQL> create or replace procedure sp_update_emp_sal as type t_table_emp is table of emp%rowtype index by binary_integer; v_table_emp t_table_emp; begin update emp set sal=sal*1.1 where deptno=10; select * bulk collect into v_table_emp from emp where deptno=10; for i in v_table_emp.first..v_table_emp.last loop dbms_output.put_line('雇员编号：'||v_table_emp(i).empno|| ', 姓名：'||v_table_emp(i).ename|| ', 工资：'||v_table_emp(i).sal); end loop; end; / Procedure created.运行存储过程，结果如下： SQL> call sp_update_emp_sal(); 雇员编号：7934, 姓名：MILLER, 工资：1574.21 雇员编号：7782, 姓名：CLARK, 工资：2965.71 雇员编号：7839, 姓名：KING, 工资：6051.21 Call completed. 注意：无参数存储过程调用时要加一对空括号。 2、带输入参数的存储过程对上例的存储过程进行修改，通过指定一个输入参数，用户在调用时指定某个部门，则把该部门员工的工资增加10%，并显示该部门员工的工号、姓名和工资。代码如下： SQL> create or replace procedure sp_update_emp_sal_01 (v_deptno number) as type t_table_emp is table of emp%rowtype index by binary_integer; v_table_emp t_table_emp; begin update emp set sal=sal*1.1 where deptno=v_deptno; select * bulk collect into v_table_emp from emp where deptno=v_deptno; for i in v_table_emp.first..v_table_emp.last loop dbms_output.put_line('雇员编号：'||v_table_emp(i).empno|| ', 姓名：'||v_table_emp(i).ename|| ', 工资：'||v_table_emp(i).sal); end loop; end; / Procedure created.调用时指定20部门作为参数，查询结果如下： SQL> call sp_update_emp_sal_01(20); 雇员编号：7788, 姓名：SCOTT, 工资：2423.63 雇员编号：7876, 姓名：ADAMS, 工资：1334.63 雇员编号：7566, 姓名：JONES, 工资：3603.38 雇员编号：7902, 姓名：FORD, 工资：3633.63 雇员编号：7369, 姓名：SMITH, 工资：971.63 Call completed. 3、指定输出参数输入一个圆的半径，求圆的面积，代码如下： SQL> create or replace procedure sp_circle_area (v_radius in number,area out number) as begin area:=3.1415926*power(v_radius,2); end; 7 / Procedure created.在代码块中调用该该存储过程，结果如下： SQL> declare v_area number(15,8); begin sp_circle_area(4,v_area); dbms_output.put_line('圆的面积为：'||v_area); end; 7 / 圆的面积为：50.2654816 PL/SQL procedure successfully completed. 4、综合练习为emp插入一条新记录： （1）创建一个序列seq_emp，从9001开始编号 SQL> create sequence seq_emp start with 9001; Sequence created. （2）创建一个存储过程sp_emp_insert create or replace procedure sp_emp_insert (v_ename varchar2, v_job varchar2 default 'SALESMAN', v_mgr number default 7698, v_hiredate date default sysdate, v_sal number default 2000, v_comm number default 0, v_deptno number default 40) as begin insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(seq_emp.nextval, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno); end; /执行存储过程： SQL> call sp_emp_insert('Jerry'); Call completed. Elapsed: 00:00:00.02 SQL> call sp_emp_insert('John',v_sal=>1800); Call completed. Elapsed: 00:00:00.00 SQL> call sp_emp_insert('Merry',v_hiredate=>sysdate-10,v_sal=>2100); Call completed. Elapsed: 00:00:00.01 SQL> select * from emp where empno>9000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ------ 9001 Jerry SALESMAN 7698 2020-02-17 01:01:22 2000 0 40 9002 John SALESMAN 7698 2020-02-17 01:02:39 1800 0 40 9003 Merry SALESMAN 7698 2020-02-07 01:03:40 2100 0 40 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |