授权O编译存储过程权限

授权Oracle用户编译包权限

一、查看用户权限

set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from
 dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and a.grantee='API' and 
 a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from 
 dba_sys_privs,dba_users d where grantee='API' and grantee=d.username  order by username;

二、存储过程包、触发器相关权限

与PROCEDURE相关的权限：
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    (在任何模式中查看存储过程的定义)                



与TRIGGER相关的权限：
select * from session_privs where PRIVILEGE like '%TRIGGER%';

PRIVILEGE                               
----------------------------------------

CREATE TRIGGER                          
CREATE ANY TRIGGER                      
ALTER ANY TRIGGER                       
DROP ANY TRIGGER                        
ADMINISTER DATABASE TRIGGER             

三、授权debug存储过程权限(权限过大)

--编译
grant create ANY PROCEDURE,ALTER ANY PROCEDURE to test;
--debug
grant debug connect session to test;
grant DEBUG ANY PROCEDURE to test;
--执行
grant execute on apps.testpkg to test;

四、授权编译存储过程权限(细化权限)

 授权编译权限(细化权限):
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_PROCEDURES WHERE  OWNER='API';


授权对某个存储过程具有debug权限:
GRANT DEBUG ON PROC_TEST TO API;
grant debug connect session to API;


授权对存储过程的编译权限:
GRANT CREATE PROCEDURE TO API;
GRANT EXECUTE ON API.PROC_NETTEST  TO API;


授权对包的编译权限:
GRANT CREATE PROCEDURE TO API;
GRANT EXECUTE ON API.STSCYQRMYY_ZZDY_JYJCBG TO API;

授权其它用户对包的编译权限:
GRANT EXECUTE ON package_name TO other_user;