You're writing up some application logic as a sequence of SQL operations. You profile it and find - hmmm - better recode this as a proc. Write up the proc, toss it in a package - you're done, right?
Well, wrong. Some very nasty things can happen to a perfectly charming, unassuming PL/SQL package between the time you write it and the time it goes 'live.' The main culprits? Ownership and grants. By default your proc will be AUTHID DEFINER - so whoever uses it assumes the privileges of the owning schema. The result?
GRANT EXECUTE ON SCHEMA.UTIL_PACKAGE TO USERS_ROLE
has an effect like
GRANT ALL ON SCHEMA.% TO USERS_ROLE
Now some will point out that the users don't really have the ability to run arbitrary code because they can only run what is in the package. Er.. well, not quite. Are you sure you can know, by inspection that this is OK?
Of course, the biggest danger is when the package has dynamic SQL or DDL. This is when you REALLY don't want definer's rights. It's easy to slip up with some 'elegant' logic. Maybe you have the package provision its own permissions. Maybe you reindex a temporary table on the fly. All very reasonable, until you ask yourself
In a nutshell, any authorized user can trigger the side effects of an AUTHID DEFINER package. This is really not what you wanted when you issued your GRANT, is it?
OK great. So you've made the package AUTHID CURRENT_USER. Now you compile it, great!
You run it from SYSTEM, it works perfectly.
You run it from a user granted by a role - whoa! Why did it compile? Why did it execute and then fail? You didn't grant SELECT on tables A and B or UPDATE on table C, etc. Huh? Well, when you compiled the package, you were the table owner and it compiled fine. When you parsed the SQL to run the proc as an end user, it compiled fine because all the parser sees is the granted package. Then when it starts actually running the proc, the permissions are checked dynamically and they may not fit the bill. Now you get the delight of doing grants and roles for every user. Whoo hoo!
So I encourage you to consider the following:
- Create a new schema to hold your package.
- Create a new role with the grants to the underlying objects needed to run the package
- Grant the new role to the package-owner schema and to all the intended end users
- Create the package AUTHID CURRENT_USER.
In this way you know you are compiling the package with exactly the limited specific rights that your end-user accounts will employ.