Free the Oracle DBA (rights)!



  • So, Oracle lets you index DUAL (aka the one-row dummy table Oracle provides because it insists on having a FROM in every SELECT even when it doesn't make sense to do so) for some reason. Odd, but harmless, right? Not if you have function-based indexes turned on, and users can create indexes and functions...

    Apparently, Oracle runs user-defined functions on indexes as a privileged user.

    :headdesk:

    So, all you need to do is CREATE FUNCTION a function that grants DBA rights to your user, and use it in an index on DUAL. Now you have DBA rights!


  • sockdevs

    :headdesk::headdesk::headdesk::headdesk::headdesk:     :headdesk::headdesk::headdesk::headdesk::headdesk:
    :headdesk::headdesk::headdesk::headdesk::headdesk:     :headdesk::headdesk::headdesk::headdesk::headdesk:

    :headdesk::headdesk::headdesk::headdesk::headdesk:     :headdesk::headdesk::headdesk::headdesk::headdesk:
    :headdesk::headdesk::headdesk::headdesk::headdesk:     :headdesk::headdesk::headdesk::headdesk::headdesk:

    :headdesk::headdesk::headdesk::headdesk::headdesk:     :headdesk::headdesk::headdesk::headdesk::headdesk:
    :headdesk::headdesk::headdesk::headdesk::headdesk:     :headdesk::headdesk::headdesk::headdesk::headdesk:

    that was enough fail i had to get the whole office involved to get enough headdesks



  • For people who are not insane, DUAL is explained in the article:

    In this case, the table in question is called "DUAL". This table has only one column, and one value: "X". It's main function is to allow for syntactically correct SQL commands if values are selected that do not correspond to a particular table. For example, to write a SQL statement to retrieve the current date, you may write: SELECT sysdate FROM DUAL; "sysdate" isn't an actual column, but by using the DUAL table we can make this look like a normal SQL query.

    So DUAL is only shipped in the first place due to pedantic dickweeds? This is good software engineering, Oracle. (Also great naming. DUAL? Why DUAL?)

    :headdesk: <- I thought this was some kind of robot scotty-dog. It's supposed to be a headdesk? Another WTF.



  • @blakeyrat said:

    So DUAL is only shipped in the first place due to pedantic dickweeds?

    IIRC the pedantic dickweed here is Oracle itself, which doesn’t allow SELECT statements without a FROM.



  • @VinDuv said:

    IIRC the pedantic dickweed here is Oracle itself, which doesn’t allow SELECT statements without a FROM.

    HAHAHAHAHA why???


  • sockdevs

    @blakeyrat said:

    :headdesk: <- I thought this was some kind of robot scotty-dog

    K-9?



  • Yeah something like that. To be honest, I had no clue what that was supposed to be until blabber mouth pointed it out. I guess the tail/antenna is a ... monitor? And the whiskers are... uh... I still don't know? Even knowing it's supposed to be a headdesk, I still can't visually parse it that way.


  • sockdevs

    I think the 'whiskers' you're seeing are the chair legs. And the person's legs too, obviously :smile:


  • sockdevs

    @blakeyrat said:

    blabber mouth pointed it out.

    i think that's the nicest name i've ever been called! THANKS!

    semi-relatedly, you do know that all emoji have their name available as a title attribute so you can hover over them and see what they are?


  • sockdevs

    Plus, when you quote the post, the :headdesk: is in the editor. Though, depending on the size of the quote, it may be hidden.



  • @tarunik said:

    Not if you have function-based indexes turned on...

    And the user has access to create a function. And to create indexes.

    It's no less of a WTF, mind.


  • BINNED

    Right click -> open image in new tab

    http://what.thedailywtf.com/uploads/default/_emoji/headdesk.png?v=0

    Still looks like a cat throwing up to me



  • @blakeyrat said:

    HAHAHAHAHA why???

    I didn’t now either, so I checked [Wikipedia][1], and it looks like the hilarity doesn’t stop there:

    The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.

    :wtf:

    Beginning with 10g Release 1 Oracle Database have been optimized and the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists.

    :facepalm:
    [1]: http://en.wikipedia.org/wiki/DUAL_table


  • area_deu

    I wonder which silly name this vulnerability will get.
    Double Vision?


  • Discourse touched me in a no-no place

    @aliceif said:

    I wonder which silly name this vulnerability will get.
    Double Vision?

    DUAL Vision?


  • sockdevs

    Functional DUALity?

    I'll get my coat…



  • @Jaloopa said:

    Right click -> open image in new tab

    http://what.thedailywtf.com/uploads/default/_emoji/headdesk.png?v=0

    Did a baby draw that? In fucking Word?! Christ.



  • @VinDuv said:

    IIRC the pedantic dickweed here is Oracle itself, which doesn’t allow SELECT statements without a FROM.

    No kidding. Just tried a FROMless select with a sane DB (SQLite3, being called from Python):

    >>> conn.execute('SELECT sqlite_version();').fetchone()
    (u'3.6.21',)


  • @loopback0 said:

    And the user has access to create a function. And to create indexes.

    It's no less of a WTF, mind.

    Yeah, our environment's bacon gets saved by ordinary users not having CREATE FUNCTION and CREATE INDEX rights out here...


  • BINNED

    I don't quite understand the WTF in this.Probably because I don't know what function based indexes are


  • sockdevs

    @Jaloopa said:

    I don't quite understand the WTF in this.Probably because I don't know what function based indexes are

    The closest MSSQL has is indexing a computed column; I imagine function-based indexing serves the same purpose.



  • @tarunik said:

    Yeah, our environment's bacon gets saved by ordinary users not having CREATE FUNCTION and CREATE INDEX rights out here.

    Right. I mean...they talk about "attackers," but you already have to be a valid user with some hefty permissions to begin with. Granted, combined with other stuff it could be really problematic.



  • @Jaloopa said:

    Probably because I don't know what function based indexes are

    The places where I've used them is to, e.g., create a case insensitive index or index a timestamp by the day (i.e., upper( some_column ) or trunc( some_timestamp_column )). I'm sure other people have come up with more complicated / sophisticated uses.

    @RaceProUK said:

    The closest MSSQL has is indexing a computed column

    The words sound like it's about the same thing.


  • BINNED

    so an index can apply to a UDF instead of a bare column collection, and when the indexer runs that function it does it as DBO?

    Yeah, that's pretty silly



  • I forget, does the scott/tiger account have access to CREATE FUNCTION by default?

    Granted, any decent Oracle DBA would have disabled that account...



  • @powerlord said:

    scott/tiger

    Didn't they finally get rid of it in 11?



  • Removed from 9i onwards IIRC.

    A quick Google suggests that people ask how to recreate them because.... :wtf:


  • sockdevs

    @loopback0 said:

    A quick Google suggests that people ask how to recreate them because....

    here's my answer to that particular query:



  • Welp, just shared this with the Office :/


  • BINNED

    @VinDuv said:

    >The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.

    Why the hell didn't it get renamed to DUAL2 and old DUAL left in for backwards compatibility then?

    Jeez Oracle, get some standards.


  • sockdevs

    apologies to @tarunik for the title edit, i've been resisting making that for the last two days and could no longer resist.....

    :laughing:



  • Unless you really screw up your security in an Oracle database, creating an index on dual is only possible as user SYS, which is basically the user that owns the dictionary and can do anything.
    Even a user with DBA rights cannot create an index on the dual table (unless explicitly granted by SYS[1]).

    Functions run by default under the priviliges of the owner of the function.
    So if you want to put a grant in there, the user creating the function must be able to grant the privileges anyway.
    If you do not have DBA rights, you cannot create a function that grants those rights to anyone. That is, you can create it, but it will throw a runtime exception.

    Oracle has lots of things to hate, but this doesn't seem to be one of them.

    [1] I think, but I have never tried it, or seen some other idiot try this.



  • @nerd4sale said:

    [1] I think, but I have never tried it, or seen some other idiot try this.

    I was tempted, but it seemed best not to try it in the long run.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.