I've been having a discussion with some PL/SQL Challenge reviewers regarding the use of the SYS. prefix in calls to DBMS_OUTPUT.PUT_LINE in the quizzes. We don't always include the prefix (mostly because I only recently started following this best practice - more on that below - in recent quizzes), but you will see it more and more.
One reviewer pointed out that a guideline for writing a good quiz is "Don't include unnecessary information." and this seems to be unnecessary.
Well, maybe, but also a little bit less secure. I have started prefixing SYS. in front of built-in package calls after reading Bryn Llewellyn's excellent white paper, "Doing SQL from PL/SQL: Best and Worst Practices", available here. One of his principles is:
Principle_4: References to objects that Oracle Corporation ships with Oracle Database should be dot-qualified with the Owner. (This is frequently, but not always, Sys.) This preserves the intended meaning even if a local object, whose name collides with that of the intended object, is created in the schema which will be current when name resolution is done.
Of course, you are unlikely to create a local package named DBMS_OUTPUT. But what if someone "breaks into" your database and injects a package with that name? It will effectively hijack all calls to the subprograms in the package.
So that's the rationale for putting SYS. in front of calls to DBMS_OUTPUT.PUT_LINE.
Your thoughts?
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
My thoughts?
ReplyDeleteI think it is too much info. The first time I saw it, it was something special and it doesn't add to the quiz.
If someone is able to hijack the DBMS_OUTPUT after hacking, I'more afraid about other damage and access options to the database. A normal PL/SQL program should not have DBMS_OUTPUT calls. There is no interface where this data should be visible. The PUT_LINE is only for developers and logging info who noone reads.....
Also in the quizes there are a lot of assumptions, in where it is posed that many settings are default. This is one of them.
Absolute addressing. Could be useful, but being schizophrenic is a bad idea. In this way I can never trust any piece of code anymore....
If I decide to override default behaviour (by subclassing, it is an OO principle) and some programmer thinks that it is smart to use this type of programming, it breaks the purpose of changing the behaviour.
Conclusion: don't use it. I read the article on this later, but I think it won't change my mind.
> Of course, you are unlikely to create a local package named DBMS_OUTPUT. But what if someone "breaks into" your database and injects a package with that name? It will effectively hijack all calls to the subprograms in the package.
ReplyDeleteI hope if someone does these things it will be reflected by "Assumptions" section changes. :)
What if someone creates the table DUAL or view V$SESSION? What if not? What if someone revokes grant on package DBMS_OUTPUT from user? What if he recreates an important synonym? IMHO, all of this is possible. And while answering a quiz I prefer to look on the Assumptions and to be guided by the good sense rather than by the professional paranoia.
I see no reason for including the schema prefix for SYS objects in quizzes. May be (and even should be) in production code.
Good luck,
Alex
PS: Such hijackers are headache for DBA, system administrator and security service guys. If a hijacker can access the database, a developer has nothing and none to believe...
I see no problem with it. The first time I saw it I thought it was a bit odd. Particularly since I hadn't seen it any Feuerstein code previously. But, the inclusion reinforces the normal assumptions that the only objects that exist are those of a default install and any explicit ddl given in the question itself.
ReplyDeleteSo, it's not really additional information. It's just being explicit about something we normally assume.
Even if it surprises a reader to see it, what possible misinformation could be introduced by the inclusion of it?
Redundant? yes
Dangerous? no
But, while I think it's fine to include it, doing so as a means of protecting against an intruder that has somehow gotten "create procedure" privileges on my schema seems weak. The intruder could simply alter my procedure and remove the SYS reference. If my goal is to protect my code from a hacker that already has access to my database, the only method would be to not use stored procedures. :)
For the purposes of the quiz as a competition, I see neither harm nor advantage to fully qualifying database object names. The only place where I can foresee a problem is if a quiz intentionally includes an incorrect schema in order to invalidate an otherwise correct answer.
ReplyDeleteAs a learning experience I do see advantage to following best practices in the PL/SQL code that is presented. Novices tend to emulate the examples they are given and thus are less likely to develop poor coding practices if they are predominately exposed to code that exhibits good practices. Experienced software developers, too, benefit from repeated exposure to solid coding practices.
Using fully qualified names does not protect one’s code from an attacker who has successfully compromised the database; however, explicitly referencing the schema does defend against attempts to use synonyms to intercept actions intended for the original object. Such concerns may not be very important for the DBMS_OUTPUT package, but other packages and tables may be more sensitive. For example, intercepting calls to the DBMS_CRYPTO package could allow an intruder to gain access to what was intended to be secured information.
Thanks for your feedback. I have decided to back away from including "SYS." Theoretically it is the right thing to do, but as a best practice it is a marginal one, and if it takes away from the quiz experience, it's not worth it.
ReplyDeleteThe issue isn't using schema notation or not for Oracle builtins. That's a red herring. The issue is that any database source code written by developers that depends on a public synonym is poor practice.
ReplyDeleteHello All,
ReplyDeleteI don't completely agree with the last statement
that using public synonyms is poor practice.
While using public synonyms does require an additional step in name resolution, it makes application maintenance much easier, for example when we want to change ownership of objects or even have to locate objects in the same or different databases.
When it comes to security issues, once an intruder has access to our code, it has access to everything, whether we use synonyms or not.
Developers can and should be "educated" not to create objects of their own that override SYS objects, but if Oracle itself wanted to enforce this as a "general must" then they would probably have prevented it at all at the databse level.
Using or not the prefixing in the quiz code
is a matter of personal taste of the author.
Best Regards,
Iudith
We the assumptions that the challenge has the use of prefix is not needed. As it was said before pl/sql challenge is not about best pratices, it's about the language and too much information makes quizes harder.
ReplyDelete