定义者权限和调用者权限

定义者权限和调用者权限

存储过程定义者权限和调用者权限使用示例默认定义者权限

创建用户

create user xiaomu1 identified by 123456;
create user xiaomu2 identified by 123456;
create role my_xiaomu1;
create role my_xiaomu2;

权限赋值

grant create session to xiaomu1;
grant connect,resource to xiaomu1;
grant create session to my_xiaomu1;
grant my_xiaomu1 to xiaomu1;

grant create session to xiaomu2;
grant connect,resource to xiaomu2;
grant create session to my_xiaomu2;
grant my_xiaomu2 to xiaomu2;

grant create table to xiaomu1;
grant create table to xiaomu2;
grant create PROCEDURE to xiaomu2;
grant unlimited tablespace to xiaomu1;
grant unlimited tablespace to xiaomu2;

创建存储过程和表
xiaomu1

CREATE TABLE department3
(v_deptno NUMBER, 
v_dname  VARCHAR2(20),
v_mgr    NUMBER,
v_loc    NUMBER);

xiaomu2

CREATE TABLE department3
(v_deptno NUMBER, 
v_dname  VARCHAR2(20),
v_mgr    NUMBER,
v_loc    NUMBER);

CREATE OR REPLACE PROCEDURE create_dept10 (
 v_deptno NUMBER,
 v_dname  VARCHAR2,
 v_mgr    NUMBER,
 v_loc    NUMBER) 
AUTHID CURRENT_USER AS
BEGIN
  INSERT INTO department3 VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
/

sys 给xiaomu1 权限

GRANT SELECT ON xiaomu2.create_dept10  TO xiaomu1;
GRANT EXECUTE ON xiaomu2.create_dept10  TO xiaomu1;

xiaomu1 执行存储过程

CALL xiaomu2.create_dept10(11,'INFO',300,1800);
SELECT *from department3;

xiaomu2 执行存储过程

CALL xiaomu2.create_dept10(11,'INFO',300,1800);
CALL xiaomu2.create_dept10(11,'INFO',300,1800);
CALL xiaomu2.create_dept10(11,'INFO',300,1800);
SELECT *from department3;

重新创建定义者权限存储过程
xiaomu2

    CREATE OR REPLACE PROCEDURE create_dept10 (
       v_deptno NUMBER,
       v_dname  VARCHAR2,
       v_mgr    NUMBER,
       v_loc    NUMBER) 
    AS
    BEGIN
       INSERT INTO department3 VALUES (v_deptno, v_dname, v_mgr, v_loc);
    END;
/

xiaomu2 执行存储过程

CALL xiaomu2.create_dept10(11,'INFO',300,1800);
SELECT *from department3;

xiaomu1 执行存储过程 数据还是1条

CALL xiaomu2.create_dept10(11,'INFO',300,1800);
SELECT *from department3;

进入xiaomu2 查询数据 多了xiaomu1 插入的一条

SELECT *from department3;