Oracle包权限管理实例

2022-09-22 11:19:55 浏览数 (1)

Oracle包、函数权限文档 https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#i2063861

1 创建（附带查看代码权限）

grant create any procedure to gm1;

  • gm1可以创建包
  • gm1可以查看包代码
  • gm1可以执行自己创建包
  • gm1不可以执行别人创建包
代码语言：javascript复制
drop user gm1 cascade;
create user gm1 identified by gm1;
grant create session to gm1;

drop user gm2 cascade;
create user gm2 identified by gm2;
grant create session to gm2;

-- 给gm1创建权限，没权限不能创建包、函数
grant create any procedure to gm1;

-- 切换到gm1创建包
conn gm1/gm1

-- 创建包
set serveroutput on;
drop package emp_bonus;
CREATE PACKAGE emp_bonus AS
  PROCEDURE show (hiredcnt int);
END emp_bonus;
/
CREATE OR REPLACE PACKAGE BODY emp_bonus AS
  PROCEDURE show (hiredcnt int) IS
  BEGIN
    dbms_output.put_line('hiredcnt: ' || hiredcnt);
  END;
END emp_bonus;
/
call emp_bonus.show(100);

可以创建成功，可以执行自己创建的包；但不能执行别人创建的包。

代码语言：javascript复制
SYS@orcl11g> call emp_bonus.show(100);
hiredcnt: 100

Call completed

2 只有执行权限

grant execute on emp_bonus to gm2;

  • gm2只可以执行
  • gm2不能alter修改
  • gm2不能编译
  • gm2不能创建包
代码语言：javascript复制
drop user gm1 cascade;
create user gm1 identified by gm1;
grant create session to gm1;

drop user gm2 cascade;
create user gm2 identified by gm2;
grant create session to gm2;

-- 给gm1创建权限，没权限不能创建包、函数
grant create any procedure to gm1;

conn gm1/gm1;
set serveroutput on;
drop package emp_bonus;
CREATE PACKAGE emp_bonus AS
  PROCEDURE show (hiredcnt int);
END emp_bonus;
/
CREATE OR REPLACE PACKAGE BODY emp_bonus AS
  PROCEDURE show (hiredcnt int) IS
  BEGIN
    dbms_output.put_line('hiredcnt: ' || hiredcnt);
  END;
END emp_bonus;
/
call emp_bonus.show(100);

-- 给gm2执行权限
grant execute on emp_bonus to gm2;

gm2执行

代码语言：javascript复制
-- 切换到gm2
conn gm2/gm2;
set serveroutput on;
call gm1.emp_bonus.show(100);

-- 执行成功
GM2@orcl11g>set serveroutput on;
GM2@orcl11g>call gm1.emp_bonus.show(100);
hiredcnt: 100

gm2建包失败

代码语言：javascript复制
CREATE PACKAGE emp_bonus AS
  PROCEDURE show (hiredcnt int);
END emp_bonus;
/
ERROR at line 1:
ORA-01031: insufficient privileges

3 只有编译权限

不支持单独给一个包编译权限：grant alter on emp_bonus to gm2; 正确写法：grant alter any procedure to gm2;

  • gm2不能执行。
  • gm2不能alter修改。
  • gm2可以编译。
  • gm2不能创建包。
代码语言：javascript复制
drop user gm1 cascade;
create user gm1 identified by gm1;
grant create session to gm1;

drop user gm2 cascade;
create user gm2 identified by gm2;
grant create session to gm2;

-- 给gm1创建权限，没权限不能创建包、函数
grant create any procedure to gm1;

-- 给gm2编译权限
grant alter any procedure to gm2;

-- 切到gm1
conn gm1/gm1;
set serveroutput on;
drop package emp_bonus;
CREATE PACKAGE emp_bonus AS
  PROCEDURE show (hiredcnt int);
END emp_bonus;
/
CREATE OR REPLACE PACKAGE BODY emp_bonus AS
  PROCEDURE show (hiredcnt int) IS
  BEGIN
    dbms_output.put_line('hiredcnt: ' || hiredcnt);
  END;
END emp_bonus;
/
call emp_bonus.show(100);

-- 给gm2编译权限
-- grant alter on emp_bonus to gm2;
-- ORA-02225: only EXECUTE and DEBUG privileges are valid for procedures

gm2执行失败

代码语言：javascript复制
-- 切换到gm2
conn gm2/gm2;
set serveroutput on;
call gm1.emp_bonus.show(100);

-- 执行失败
GM2@orcl11g>call gm1.emp_bonus.show(100);
ERROR at line 1:
ORA-01031: insufficient privileges

gm2编译成功

代码语言：javascript复制
alter package gm1.emp_bonus compile body;
alter package gm1.emp_bonus compile package;

GM2@orcl11g>alter package gm1.emp_bonus compile body;
Package body altered.

GM2@orcl11g>alter package gm1.emp_bonus compile package;
Package altered.

4 修改权限

给两个权限可以修改包内容。 grant create any procedure to gm2; grant alter any procedure to gm2;

  • 修改包内容需要：create 和 alter权限。
  • 但是不能执行。
代码语言：javascript复制
drop user gm1 cascade;
create user gm1 identified by gm1;
grant create session to gm1;

drop user gm2 cascade;
create user gm2 identified by gm2;
grant create session to gm2;

-- 给gm1/gm2创建权限，没权限不能创建包、函数
grant create any procedure to gm1;
grant create any procedure to gm2;
-- 给gm2编译权限
grant alter any procedure to gm2;

-- 切换到gm1创建包
conn gm1/gm1

-- 创建包
set serveroutput on;
drop package emp_bonus;
CREATE PACKAGE emp_bonus AS
  PROCEDURE show (hiredcnt int);
END emp_bonus;
/
CREATE OR REPLACE PACKAGE BODY emp_bonus AS
  PROCEDURE show (hiredcnt int) IS
  BEGIN
    dbms_output.put_line('hiredcnt: ' || hiredcnt);
  END;
END emp_bonus;
/
call emp_bonus.show(100);

gm2执行失败，但修改成功！

代码语言：javascript复制
GM1@orcl11g>conn gm2/gm2
GM2@orcl11g>call gm1.emp_bonus.show(100);
ORA-01031: insufficient privileges

GM2@orcl11g>
CREATE OR REPLACE PACKAGE BODY gm1.emp_bonus AS
  PROCEDURE show (hiredcnt int) IS
  BEGIN
    dbms_output.put_line('hiredcnt: ' || hiredcnt);
  END;
END emp_bonus;
  7  /
Package body created.

5 调试权限

使用调试工具前需要赋予调试权限：

grant debug any procedure to gm2;

打包 oracle

0 人点赞