Developing an API with Oracle PL/SQL

Developing an API with PL/SQL can be a fun experience.  At Blizzard, I have this project to decentralize control of a database’s privilege management, so that each team’s designated “manager” will use this API to self service the granting and revoking of access to the data that they own.  This database is one where different sources of data converges before ETL extracts it to a data warehouse, so such delegation of responsibilities makes sense.  This article focuses on a few fundamental issues involving using PL/SQL to develop an API.

Definer Rights vs. Invoker Rights

When it comes to making an API with PL/SQL, the very first consideration has to be the security context in which the code will execute. This leads to the Definer Rights(DR) vs. Invoker Rights(IR) discussion.  This decision will ultimately determine the structure of your code.  Let’s compare DR and IR in the following 5 aspects:

Execution rights granularity

Under DR, if you grant execute privilege of a PL/SQL package to a user, the user has full execution rights to any procedure or functions in that package.  If you don’t want to mix code of different scope together, you can break them up into different packages and grant them to different classes of users.  IR, however, gives you more granularity in that even though you granted the execution privilege to a user, it is the user’s privileges that determine if the user can run the SQL statements or not.  This way, you can organize the code into packages in a way that makes the best sense to you.  Besides, it is the SQL statements that you should concern the most.  IR allows the natural enforcement of privileges against SQL statements, whereas DR just lets users run the code no question asked.

Dynamic SQL

If you use plan to use a lot of dynamic SQL and particularly if the SQL statements are composed from the input parameters of the code, then you shouldn’t use DR because you’d be simply allowing the user to exploit the definer’s privileges.  IR works with dynamic SQL better, because no matter what SQL statements are being generated dynamically, the underlying privileges and roles ensure that the invoker can only execute what it has privileges for.

Frame of Reference

With DR, the entire frame of references is relative to the definer, which is the schema where you place your PL/SQL objects.  Therefore, you don’t need to fully qualify the path to your objects.  For example, in your PL/SQL code, you can reference a table in the same schema w/o qualifying it with a schema name.  With IR, however, all references must be fully qualified since the security context is relative to the invoker, which is not fixed.

Error handling

Error handling is simpler under DR, since the definer’s privileges are well-known at compile time.  This way, you can more easily predict what kind of errors would arise when your code runs.  For IR, however, you need to anticipate that users with different privileges would be running your code.  Your error handling would need to have more depth.

Security

Both IR and DR have pitfalls in security.  With IR, a more privileged user can execute the code owned by a less privileged user, allowing the code to gain access to more resources.  For this same reason, 12c added a new privilege requirement for IR.  You’ll find more details down below.  Under DR, however, the privileges of the definer is well-known, but once granted the user can run anything in the code in the identity of the definer.

	Definer Rights	Invoker Rights
Execution rights granularity	Low	High
Dynamic sql	Risky	Safer
Frame of reference	Relative to definer	Fully qualified
Error handling	Very simple	Complicated
Security risks	Invoker can run anything in package.	A more privileged invoker's privileges can be exposed to a less privileged user's API.

There is not an absolute right or wrong choice between the two.  But for my project, I chose IR simply for its granularity in terms of execution rights.  Since I write a lot of dynamic SQL, it is important to me that Oracle evaluates the privileges of the invoker before running the dynamically generated SQL statements.

Grant with admin option

So with IR and dynamic SQL in mind, the way you would enforce privileges on the invokers are definitely through roles.  This way, you won’t make a mess of privileges.  Encapsulate the privileges into roles that make sense to you.  In this following illustration, I have individuals designated as team managers.  They are granted a specific manager role, which is granted the team’s reader and writer roles WITH ADMIN OPTION.  This allows the managers to be able to grant these reader/writer roles onward to other people.  Each team manager cannot grant the reader/writer roles belonging to another team, simply because he/she is not granted those roles with admin option.  This enforces team isolation.  But if a particular team manager does need to grant across team, it is possible by granting those reader/writer roles to the manager role with admin option.

prefix_ in the name is only to differentiate the role from any out of the package from Oracle.  This is good practice for any other objects.

12c: INHERIT ANY PRIVILEGE privilege

Starting with 12c, Invoker Rights requires making one additional grant before IR would work.  This is based on the idea that a higher privileged user can call the API and expose its resources to a less privileged user’s API.  The following grant would need to be made.  To me, this is nothing more than just an acknowledgement that there is a potential scope breach.

grant inherit any privileges to {user};