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 🙂
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.