Oracle包权限管理实例

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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

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

Call completed
1
2
3
4

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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
1
2
3
4
5
6
7
8
9

gm2建包失败

CREATE PACKAGE emp_bonus AS
  PROCEDURE show (hiredcnt int);
END emp_bonus;
/
ERROR at line 1:
ORA-01031: insufficient privileges
1
2
3
4
5
6

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

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
1
2
3
4
5
6
7
8
9

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.
1
2
3
4
5
6
7
8

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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.
1
2
3
4
5
6
7
8
9
10
11
12
13

5 调试权限

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

grant debug any procedure to gm2;