UNIX script to add AUTHID CURRENT_USER to package specification

Oracle recommends and creates packages, procedures and views in APPS schema. Some customers create these objects in the custom schema like xxont, xxap, xxinv, etc.

It does not impact working of package or procedure. If there are calls to many standard built-in API which are under apps schema, you need to provide grants of all those objects to schema owner of the custom package.

grant execute on standard_api to xxont

It is fine if the grant is only for one or two objects, what if there are many?

AUTHID CURRENT_USER

Packages are always executed with the privilege of the creator. You can use AUTHID CURRENT_USER clause in package specification which will execute the package using the privilege of the current_user.

Use below script to do it in bulk. Copy all specification in one of UNIX directory and execute it. It will add AUTHID CURRENT_USER for all package specification.

This script is helpful during Oracle Apps Upgrade from 11.5.10 to 12.1.3 if you have not used AUTHID.

## Please test it before using
ls | while read file_name
do
echo "$file_name" 
abc=`grep -ic AUTHID $file_name`
if [ $abc -eq 0 ]; then
sed -e '1,/AS/s/\<AS\>/AUTHID CURRENT_USER AS/' $file_name > temp
mv temp $file_name
fi 
done

This is the way it works,

  1. Start
  2. List and read each file in current directory
  3. Check if AUTHID is present
  4. If no use SED Unix command to add it.
  5. End

Using above script you can add AUTHID CURRENT_USER to all package specification in bulk. It saves a lot of development time.

Please do let me know if you find this helpful in the comments section below.

Reference:-

 

Related Post