Revoke oracle public privleges

1. Introduction of Oracle privilege
A privilege is a right to execute an SQL statement or to access another user's object. Privilege mentioned in this bug is related with package objects.

When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:

Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

           grant execute on object to user;

2. What is the default privilege of UTL_FILE, DBMS_OBFUSCATION_TOOLKIT, DBMS_LOB?
UTL_FILE, DBMS_OBFUSCATION_TOOLKIT, DBMS_LOB are Oracle installed packages. By default, they are granted to PUBLIC, which means that all users can use them.

3. How to revoke the public privilege?
Use revoke. Take package UTL_FILE for example:
a. Connect to the database as SYS. 
   SQL> connect / AS SYSDBA
b. Revoke execute privileges on the utl_file package from PUBLIC. 
   SQL> revoke execute on utl_file from public;

4. What would be the impact if we revoke the privilege?
Oracle highly recommends keeping DBMS_SQL, DBMS_JOB, DBMS_LOB granted to PUBLIC just to keep user’s database running. Otherwise lots of things break including some of the utilities like Import/Export, Datapump expdp/impdp, SQL*Loader, etc.

If you still want to revoke privileges from PUBLIC make sure that you re-grant privileges to user that require them and recompile possible invalidated objects:
a. Re-grant permissions only to users that require them. 
   Example:
   SQL> grant execute on utl_file to sadmin;
b. Run any suitable tests to ensure that the application runs properly. 
   One way of checking is to see if any objects become invalid after removing the grants:
   select owner, object_name, object_type from dba_objects
   where status <>'VALID' ;
c. Recompile the invalidated objects.

5. Applying a oracle patch may cause the privileges on these packages are reset to public.
Solution: Put all revoke statements that are non-default in a script that can be run again. You may also want to put the grants you need for individual users in this script, then everytime you  apply a patchset, or you do a mayor upgrade, you can run this custom script again to make sure all grants are as you see fit.

6. Reference in oracle support:
 Note:247093.1 Be Cautious When Revoking Privileges Granted to PUBLIC

 Note:390225.1 Execute Privileges Are Reset For Public After Applying Patchset

转载本站文章请注明作者和出处:http://blog.youkuaiyun.com/crazy_samba ，请勿用于任何商业用途