Data dictionary quiz question
This is a totally unfair quiz question (for anyone who isn’t intimately acquainted with the Oracle data dictionary views). There, I’ve warned you. I would have got this one wrong, myself.
Which of the following queries (if any) will run without error?
SELECT * FROM dba_tab_privs WHERE owner = 'SCOTT'; SELECT * FROM all_tab_privs WHERE owner = 'SCOTT'; SELECT * FROM user_tab_privs WHERE owner = 'SCOTT';
Now, don’t go and try running these in your database until after you’ve written down what you think the answers are. That would be cheating 🙂
.
.
.
EDIT
Ok, now to break the suspense for all the other readers (both of you), who couldn’t be bothered testing it out for yourself.
Statement 1. The query on DBA_TAB_PRIVS will succeed – assuming you have the necessary privileges on the DBA* views. This view does include the column OWNER.
Statement 3. The query on USER_TAB_PRIVS, unlike what I and several others might assume, will succeed. Many data dictionary views, such as USER_TABLES, omit the OWNER column – which makes sense, since it is expected that it will be simply the currently-logged-in user. But for user_tab_privs, this column is provided, for good reason – because the table you have a privilege on may very well be owned by another schema.
Statement 2. The query on ALL_TAB_PRIVS, in order to be consistent with DBA_TAB_PRIVS and USER_TAB_PRIVS, should have the OWNER column, by rights. But, just to make things interesting, the column is called TABLE_SCHEMA instead. So, my query would fail.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Mette Stephansen
3 March 2011 - 2:47 pm
1 & 2 will do OK
Jeffrey Kemp
3 March 2011 - 3:18 pm
Nope – try again 🙂
Niall Litchfield
3 March 2011 - 4:38 pm
I’m pretty certain that the column isn’t OWNER in those views. probably is TABLE_OWNER.. I’ll have to go look now.
Niall Litchfield
3 March 2011 - 4:43 pm
Ah Ha! Wouldn’t have expected that. You should take up a job writing OCP questions 🙂
poelger
3 March 2011 - 5:04 pm
Didn’t know it and tried out the statements.
Nasty.
John Flack
3 March 2011 - 9:47 pm
I was totally wrong. Had to do a DESC to find out why. Thanks for reminding me that I don’t know Oracle as well as I think I do.
Jeffrey Kemp
3 March 2011 - 9:48 pm
My condolences 🙂
gary
4 March 2011 - 6:09 am
Didn’t get that. Its about time they actually deprecated some of of those views and moved to an INFORMATION_SCHEMA model and made it all a bit more consistent.
Oh, and your assumptions should specify that the user has select access to DBA_TAB_PRIVS 🙂
Jeffrey Kemp
4 March 2011 - 9:17 am
🙂 well, this ain’t the PL/SQL Challenge, so I get to make up the rules: Rule #1: assume nothing. Rule #2: Assume. Absolutely. Nothing. 🙂