Friday, 18 August 2017

Script to Add AUTHID CURRENT_USER to package specification

Oracle recommends creating packages, procedures in APPS schema. Many 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

Packages are always executed with the privilege of the creator that is custom schema. 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.

## Please test it before using
ls | while read file_name
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


Using above script you can change all your packages in one go and it saves a lot of development time.

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


Leave a Reply