Oracle包权限管理实例

mingjie

发布于 2022-09-22 11:19:55

8700

文章被收录于专栏：Postgresql源码分析Postgresql源码分析

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不可以执行别人创建包

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);

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

SYS@orcl11g> call emp_bonus.show(100);
hiredcnt: 100

Call completed

2 只有执行权限

grant execute on emp_bonus to gm2;

gm2只可以执行
gm2不能alter修改
gm2不能编译
gm2不能创建包

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执行

-- 切换到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建包失败

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不能创建包。

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执行失败

-- 切换到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编译成功

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权限。
但是不能执行。

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执行失败，但修改成功！

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;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-08-15，如有侵权请联系 cloudcommunity@tencent.com 删除

打包

oracle

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

打包

oracle

登录后参与评论

0 条评论

热度

Oracle包权限管理实例

Oracle包权限管理实例

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

2 只有执行权限

3 只有编译权限

4 修改权限

5 调试权限

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐