SQL> select * from session_privs where PRIVILEGE like '%PROCEDURE%';PRIVILEGE ---------------------------------------- CREATE PROCEDURE CREATE ANY PROCEDURE ALTER ANY PROCEDURE DROP ANY PROCEDURE EXECUTE ANY PROCEDURE DEBUG ANY PROCEDURE -------授予CREATE ANY PROCEDURE ,EXECUTE ANY PROCEDURE
1、如果只想授予一个用户对一个包的执行权限,但不能查看包体: grant execute on package_name to user_name; 2、如果想授予一个用户所有包的执行权限,并且能查看包体,但不能修改编译: grant create any procedure,select any table,execute any procedure to user_name;
grant execute on pkgname to usrname;grant select_catalog_role to usrname;
scott@ORCL> create or replace package pck_fsum 2 as 3 function fun_sumsal(c_deptno emp.deptno%type) return number; 4 end; 5 /程序包已创建。scott@ORCL> ed 已写入 file afiedt.buf 1 create or replace package body pck_fsum 2 as 3 function fun_sumsal(c_deptno emp.deptno%type) return number 4 as 5 c_sal number; 6 begin 7 select sum(sal) into c_sal from emp where deptno=c_deptno; 8 return c_sal; 9 end; 10* end; scott@ORCL> /程序包体已创建。 1* create user test1 identified by sys scott@ORCL> /用户已创建。scott@ORCL> grant connect,resource to test1 2 /授权成功。 scott@ORCL> ed 已写入 file afiedt.buf 1* grant execute on pck_fsum to test1 scott@ORCL> /授权成功。scott@ORCL> conn test1/sys 已连接。 test1@ORCL> ed 已写入 file afiedt.buf 1* select scott.pck_fsum.fun_sumsal(10) from dual test1@ORCL> /SCOTT.PCK_FSUM.FUN_SUMSAL(10) ----------------------------- 8750 ----查看包头包体的定义test1@ORCL> conn scott/sys 已连接。 scott@ORCL> grant select on all_source to test1 2 /授权成功。scott@ORCL> grant debug on pck_fsum to test1 ---关键这里 2 /授权成功。scott@ORCL> set long 1000 scott@ORCL> conn test1/sys 已连接。test1@ORCL> set pagesize 200 test1@ORCL> select text from all_source a where a.type in('PACKAGE','PACKAGE BODY') 2 and a.name=upper('pck_fsum') 3 /TEXT -------------------------------------------------------------------------------- package pck_fsum as function fun_sumsal(c_deptno emp.deptno%type) return number; end; package body pck_fsum as function fun_sumsal(c_deptno emp.deptno%type) return number as c_sal number; begin select sum(sal) into c_sal from emp where deptno=c_deptno; return c_sal; end; end;已选择14行。test1@ORCL>
SQL> select * from session_privs where PRIVILEGE like '%PROCEDURE%';PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
DEBUG ANY PROCEDURE -------授予CREATE ANY PROCEDURE ,EXECUTE ANY PROCEDURE
我是在USER_A用户里创建里存储过程,想将这个存储过程的执行和查看权限授权给USER_B用户,
grant execute on package_name to user_name;
2、如果想授予一个用户所有包的执行权限,并且能查看包体,但不能修改编译:
grant create any procedure,select any table,execute any procedure to user_name;
详细请参考这里:
Oracle 用户、对象权限、系统权限
Oracle 角色、配置文件
2 as
3 function fun_sumsal(c_deptno emp.deptno%type) return number;
4 end;
5 /程序包已创建。scott@ORCL> ed
已写入 file afiedt.buf 1 create or replace package body pck_fsum
2 as
3 function fun_sumsal(c_deptno emp.deptno%type) return number
4 as
5 c_sal number;
6 begin
7 select sum(sal) into c_sal from emp where deptno=c_deptno;
8 return c_sal;
9 end;
10* end;
scott@ORCL> /程序包体已创建。
1* create user test1 identified by sys
scott@ORCL> /用户已创建。scott@ORCL> grant connect,resource to test1
2 /授权成功。
scott@ORCL> ed
已写入 file afiedt.buf 1* grant execute on pck_fsum to test1
scott@ORCL> /授权成功。scott@ORCL> conn test1/sys
已连接。
test1@ORCL> ed
已写入 file afiedt.buf 1* select scott.pck_fsum.fun_sumsal(10) from dual
test1@ORCL> /SCOTT.PCK_FSUM.FUN_SUMSAL(10)
-----------------------------
8750
----查看包头包体的定义test1@ORCL> conn scott/sys
已连接。
scott@ORCL> grant select on all_source to test1
2 /授权成功。scott@ORCL> grant debug on pck_fsum to test1 ---关键这里
2 /授权成功。scott@ORCL> set long 1000
scott@ORCL> conn test1/sys
已连接。test1@ORCL> set pagesize 200
test1@ORCL> select text from all_source a where a.type in('PACKAGE','PACKAGE BODY')
2 and a.name=upper('pck_fsum')
3 /TEXT
--------------------------------------------------------------------------------
package pck_fsum
as
function fun_sumsal(c_deptno emp.deptno%type) return number;
end;
package body pck_fsum
as
function fun_sumsal(c_deptno emp.deptno%type) return number
as
c_sal number;
begin
select sum(sal) into c_sal from emp where deptno=c_deptno;
return c_sal;
end;
end;已选择14行。test1@ORCL>
user_name 要换成自己的用户名,楼主咱就不会思考,只能完全照搬??