PL/SQL Programming Joke #2: Don't Use Built-In Packages, Because - Definer Rights

As my 25th winter in Chicago approaches (and after the 2016 elections), I attempt to cheer myself up with jokes. Programmer jokes.

Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. 

Here's my second joke for your enjoyment. And just in case you have any doubts, this is a true story. As in not "based on a true story." True. All the way through.

After I left Oracle Corporation back in 1992, I spent a few years consulting before I wrote the first edition of Oracle PL/SQL Programming (and my life changed big time). For about six months, I commuted to downtown Chicago to work at an insurance company.

I was, as you might expect, part of the development team, writing PL/SQL packages and building user interfaces in Oracle's wonderful SQL*Forms 3.

This particular insurance company had been acquired by another, even larger insurance company. And it turned out that all the DBAs worked in an entirely separate department in the parent company. As you might expect, that amount of physical and organization distance did not contribute to good relations between the developers and DBAs.

Well, I'd been there for a month or two, when I came into work Monday morning to find that chaos reigned. Almost of the code was broken and would not compile successfully. 

The problem?

The DBAs had, over the weekend, revoked privileges on all the built-in packages (DBMS_SQL, UTL_FILE, DBMS_OUTPUT, DBMS_UTILITY, etc.) from our development schemas.

Yes, that would make it hard for program units to compile.

Why would a DBA take such drastic action? They were eager to explain:

When you execute a stored subprogram, it uses the privileges of the defining schema. So when you run a subprogram in a package owned by SYS (like all the built-ins), you are running with SYS authority. That is a major security hole, and we plugged it.

Hmmmm. Well, gee, that sounds kind of reasonable so maybe….wait just a minute. That might "sound" reasonable, but it also sounds like absolute nonsense. There is no way that Oracle would have designed the database, and the built-in packages that are necessary for building our apps, with such a security problem.

And once again, we find a nugget of truth inside this ludicrous action: it is absolutely true that when you compile a program unit using definer rights (which happens when you include the AUTHID DEFINER clause in the program unit header, or when you leave out the AUTHID clause entirely), the privileges of that defining schema (the owner of the program unit) will be used to resolve references to database objects and therefore control what you can do when you call that program.

The AUTHID clause was added to PL/SQL in Oracle8i. We were on Oracle8 at the insurance company. So no AUTHID and what does that mean? That all of the code we (all PL/SQL programmers, all of us) wrote in that version of Oracle were "definer rights" programs. So the DBAs were right?

No, no, no!

OK, it's true that the AUTHID clause was not available to PL/SQL programmers. That has nothing to do, however, with how Oracle itself could set up its built-in packages. And so, of course, packages like DBMS_SQL executed with the privileges of the invoker, not the definer. You could not drop data dictionary views using DBMS_SQL, not in Oracle7, Oracle8 or any other version of the Oracle Database.

Silly DBAs. We got them to reverse their revocation of privileges that day. Developers 1 - DBAs 0.

Hey, but if you'd like to test your DBA, ask them to grant you EXECUTE authority on a package named DBMS_SYS_SQL because you need to write some dynamic SQL method 4. 

DBMS_SYS_SQL is the package that DBMS_SQL itself calls to perform its dynamic SQL operations, and if you use that package, you will execute SQL statements with the privileges of SYS.

So if your DBA says "OK, you can use DBMS_SYS_SQL"....then quick make sure your manager knows that person is in drastic need of training and/or mentoring.

Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, do not use any built-in packages. 'Cause they're owned by SYS and therefore you can....

NO

That was a joke, OK? If you have execute authority on a package owned by SYS, then you can use it.

Originally published in ODTUG's Confessions of a Quick and Dirty Programmer series