Thursday, 22 June 2017

Script to Add AUTHID CURRENT_USER to package specification

Oracle recommends creating package in APPS schema. Many of customer creates these objects in custom schema. Though ideally it should not impact working of package, but if there is call to many standard built-in API, you need to provide grants of all those object to schema owner of the package.

Package is always executed with privilege of the creator that is custom schema. You can use AUTHID CURRENT_USER clause in package specification which will execute the package using privilege of the caller.

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



Leave a Reply

%d bloggers like this: