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

Comments

  1. 10,000 years ago (in Oracle time) I was a DBA of a V6 database. Back then using SYS for maintenance and such of the database was pretty common (We didn't have the internet back then so hacking into a system was nearly impossible, we didn't even have dial in to this system back then.) The SA of the system (who knew nothing about Oracle), learned that SYS was like ROOT and felt it necessary to change the password. He had some elaborate system to derive a cryptic password. Of course he didn't tell me what he was doing. So when a job failed I was completely taken by surprise.

    Finally I figured out what was happening. When I asked him about it he stuck to his guns, we need this level of security! (He was a bit vague on why, since it wasn't a financial system or even remotely like that.) Rather then make a fuss I just did a connect internal form then on and he could change the password all he wanted, I never needed to use it. Of course I never told him I was bypassing his security scheme.

    ReplyDelete
    Replies
    1. You do what you gotta do. :-) Thanks for sharing!

      Delete
  2. Ah, yes ...
    I suddenly remembered a problem, many, many years ago,
    still in Oracle7, when I was very proud of having implemented
    some dynamic queries in a package, using DBMS_SQL,
    with the purpose to call afterwards some of those functions
    from simple SQL statements.

    And then, I just discovered that I could not add the required
    PRAGMA RESTRICT_REFERENCES to my packaged functions,
    because DBMS_SQL itself did NOT have that PRAGMA defined :(:(

    The good, old times .... since then, the PRAGMA was added,
    and even documented, though today it is not strictly needed
    any more ...

    And, yes, our so many similarities :):)

    I also worked for many years on an Insurance application ...
    and also developed a lot in SQL*FORMS V3 and also earlier, as well as later versions ...
    By the way, thanks a lot for calling SQL*FORMS wonderful :):)
    I hope this is NOT a joke ... Oracle Forms, with its various versions is one of the Oracle products that I loved most along
    the years :):):)

    And, the last similarity:

    Our DBA-s also have not ever heard about DBMS_SYS_SQL ...
    and also not about Method 4 ... and, no, it surely doesn't occur to any of them that they are missing anything and might need to learn
    or be mentored ...

    The "joke" is that they are still there, and I am not ...

    Cheers & Best Regards,
    Iudith



    ReplyDelete
    Replies
    1. Certainly no joke about SQL*Forms. I cut my PL/SQL teeth of V3, I loved that product. I build an interactive debugger for SQL*Forms in SQL*Forms. That was a lot of fun! It was a genuine RAD tool that perfectly meshed with user expectations of UIs back then.

      Delete
  3. Hello Steven,

    If already at this point of "memories of old people" ...
    then let me just add that I also worked a lot in older versions of SQL*FORMS (V2.1, V2.3) using a simple text editor ...
    I learned the "tricky working in editor" from even older people ...
    since the times when forms were intially created using a command-line interactive utility called FASTFORM, and then customized manually, for example by adding triggers
    (then yet BEFORE the PL/SQL era at all !) ...
    In reward, I taught those oldies to use the first GUI developer, born in V2.1 ...

    But all this manual work helped me A LOT later, when I created some amazing utilities for various upgrades of Forms ...

    I especially remember one of our upgrades, from Hebrew 7bit to Hebrew 8bit on the old good VMS systems ... still in V2.3 ...

    After a software was bought from another company to do this upgrade, the result was a total mess ... and the resulting forms were completely unusable ...
    So, we had to cancel this upgrade after a day + a night of work ... and then, on my way home in the middle of the night,
    I elaborated a completely different strategy for this upgrade ... and the next day it took me a few hours to complete it
    and afterwards the upgrade was done fast and successfully :):)

    And now the joke part of it: That utility that I created became so "famous", that ultimately I was friendly asked "to lend it" to other companies ... even by the same person (a good and serious DBA by the way) who initially sold us
    the first upgrade method that failed ...

    I have a very long and very beautiful history with ALL the versions of SQL*FORMS :):)

    And also helped others a lot by answering their questions in various forums, which was always very pleasant for me to do :):)

    Cheers & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. That is a great story, Iudith.

      "on my way home in the middle of the night,
      I elaborated a completely different strategy for this upgrade ... and the next day it took me a few hours to complete it"

      Aren't those moments wonderful?

      Delete
  4. Yes, they are wonderful indeed :):)

    Those moments are "one's finest hour", if I'm allowed to use a famous phrase of Winston Churchill :):)

    And I did have many ones like this one, simply because each time when we faced an upgrade, there was a "central issue" involved that I knew before head that NO ONE would help me in any way
    if I am not able to find the solution by myself.

    The only problem is that in business companies everything is measured in terms of "time and money" only ...
    the beauty of a solution DOES NOT matter to anyone ... surely not to those "managers" who had never ever in their lives "made their own hands wet" by trying to solve a problem by themselves ...

    Even now I hear things like "Oh, what a great product is SAP !",
    because, when doing an upgrade, there is "only" need of a whole "armada" (yes, tens and hundreds) of implementers, and tens of meetings for reporting backward compatibility bugs ...
    in comparison with Oracle Forms for example ... where each upgrade was so complicated because in each version they completely modified the software ...

    I have put aside for my own memory all these "little jewels", that I was so proud of at their time :):)

    If I had had the chance of working in a software-oriented environment/company, I guess that all these nice solutions could have gained a lot more recognition ...


    I am still very sorry up to this day that no one in my late company has ever become fully aware of how great a product Oracle Forms was indeed ... they just "carried it over" from version to version,
    secretly awaiting the moment when they could get rid of it completely ... just as they prepare for getting rid of Oracle as a database.

    In the ugly business-oriented world of today, one should always expect that ALL his efforts and achievements be cancelled from one moment to the next ... because there will always be software managers ready to put the carriage in front of the horses and convince the business management that throwing away the entire software and replacing it with something else is what will bring them the desired results ( aka the desired money ) ...
    just like the search for the "philosopher's stone" in the Middle Age ...

    Cheers & Best Regards,
    Iudith

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts