What Permissions Do I Need To Compile Invalid Objects In Another Schema?

Did you know that Compile Any Object permission doesn’t exist? I looked for it, and couldn’t find it. As a matter of fact, if you check SYSTEM_PRIVILEGE_MAP dictionary view, you’ll get confirmation that this privilege doesn’t exist.

Oracle, please add this permission in a future release…

Assuming I don’t have the DBA role, or DBA permissions, and granting it is out of the question, what permissions do I need to compile invalid objects in another schema?

I came around this problem, when I had to setup a user with full access to a schema, and permissions to compile that schema’s objects.

I’ll share with you my findings:

1. What permissions do I need to compile invalid objects (and not only) in another schema?
2. Why would someone require permissions to compile objects in another schema?
3. When would objects become invalid?
4. How do I compile invalid objects?
5. Script to compile invalid objects

For objects that you own, it is easy. You can compile any objects in your own schema.
For objects that you do not own, you can’t just compile, unless you have permission to do so.

DML permissions on a view will not grant you permission to compile the view.
Execute permission on a procedure, will not give you permission to compile the procedure.
Select on a synonym will not allow you to compile the synonym.

1. What permissions do I need to compile invalid objects in another schema?

Oracle’s documentation has the answer, but is not very intuitive.

In order to compile a procedure, function, package in another schema you must have ALTER ANY PROCEDURE system privilege.
In order to compile a view in another schema, you must have ALTER ANY TABLE system privilege.
In order to compile a materialized view in another schema, you must have ALTER ANY MATERIALIZED VIEW system privilege.
In order to compile a private synonym in another schema, you must have CREATE ANY SYNONYM and DROP ANY SYNONYM system privileges.
In order to compile a public synonym, you must have CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM system privileges.

The above rules make sense, as the statement you invoke to compile an object, starts like “ALTER … COMPILE”, thus the alter any … privilege requirement.

A cool hack for synonyms:

In order to compile a synonym, it is suffice to describe the synonym, which probably doesn’t require the create any synonym/drop any synonym permissions.

SQL> desc synonym_name

If Oracle can resolve the synonym, it will validate it. No need to issue “alter synonym synonym_name compile;” statement.

2. Why would someone require permissions to compile objects in another schema?

There are situations when users in support roles are in charge of certain schemas in the database, and they do not know the password to those schemas. Connecting as the schema owner is out of question. In order to compile invalid objects for those schemas, they need permissions to do so.
It is debatable what is posing more risk, having the password to the schema, or having the ALTER ANY … permissions. From my point of view, the latter is higher risk.
There is an even better solution to this problem (connecting through proxy), but that will be a future subject to discus, in another post.

3. When would objects become invalid?

Some types of objects, (views, procedures, synonyms…), reference other objects in the database. For example a procedure references a table, a view references a table and another view.

Oracle classifies these objects into 2 categories:
–dependent object (references another object)
–referenced object

An object becomes invalid if:

– the reference between the dependent object and the referenced object cannot be solved by the compiler (the dependent object is marked invalid).
– the referenced object changes, the dependent object is marked invalid.

Example: performing DDL on a referenced table, can invalidate a view, or procedure; dropping the underlying table of a synonym, marks the synonym invalid.

4. How to compile invalid objects?

There are multiple ways to compile invalid objects in the database.

– Oracle provides a script, called utlrp.sql (${ORACLE_HOME}/rdbms/admin/utlrp.sql). In order to use this script, you need to be connected as sys.
– there is an Oracle packages utl_recomp, that can be used to compile invalid objects.
– manually compile invalid objects (this is by far my favorite one)

To manually compile invalid objects, I use a script to generate the compile commands, and run the generated sql.

5. Script to compile invalid objects

This script uses dba_objects view, which can easily be replaced with user_objects or all_objects. To run the script:

SQL>@gen_compile_inv_obj.sql

/*
Script name: gen_compile_inv_obj.sql
 
Example:     @gen_compile_inv_obj.sql
 
Created By   Diana Robete
Copyright:   @2016 dbaparadise.com
 
Comments:    If you don't have access to dba views, these can be replaced by all_ or user_ views
*/
 
set pagesize 1000
set linesize 200
 
select 'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;'
from dba_objects where object_type in ('PACKAGE','VIEW','PROCEDURE','TRIGGER','MATERIALIZED VIEW') and status='INVALID'
union
select 'desc ' || owner || '.' || object_name
from dba_objects where object_type='SYNONYM' and status='INVALID' and owner <> 'PUBLIC'
union
select 'desc ' || object_name
from dba_objects where object_type='SYNONYM' and status='INVALID' and owner='PUBLIC'
union
select 'alter package ' ||  owner || '.' || object_name || ' compile body;'
from dba_objects where object_type='PACKAGE BODY' and status='INVALID'
order by 1;
 
Prompt ***Run the generated sql statements to compile the invalid objects***

-Diana