User access control: 1Z0-071 practice questions
Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- System privileges versus object privileges
- Granting privileges on tables
- Granting privileges versus granting roles
Practice questions
1. User B has no privileges on table A.SECRET. What does B get from SELECT * FROM a.secret?
- ORA-00942: table or view does not exist
- ORA-01031: insufficient privileges
- An empty result
- The column names but no rows
Show the answer
Answer: A
Without any privilege on an object, Oracle reports ORA-00942 so that users cannot discover which objects exist. ORA-01031 appears when you can see the object but lack the privilege for the specific operation, for example UPDATE when you only have SELECT. Try it: your user gets ORA-00942 for the SYS-owned table OBJ$.
2. Your user can query SYS.DUAL, because SELECT on it is granted to PUBLIC. What does UPDATE sys.dual SET dummy = 'Y' return?
- ORA-01031: insufficient privileges
- ORA-00942: table or view does not exist
- 1 row updated
- ORA-02030: can only select from fixed tables/views
Show the answer
Answer: A
Your user can see DUAL through its SELECT privilege, but holds no UPDATE privilege, so Oracle reports ORA-01031 rather than hiding the table with ORA-00942 (which is what you get with no privilege at all). Oracle 26ai reports the more precise ORA-41900: missing UPDATE privilege on "SYS"."DUAL". ORA-02030 is raised for DML on fixed views such as SESSION_PRIVS.
3. What does this query return for any user connected normally?
SELECT COUNT(*)
FROM (SELECT privilege FROM user_sys_privs
MINUS
SELECT privilege FROM session_privs);- 0
- The number of system privileges received through roles
- The number of system privileges granted directly
- NULL
Show the answer
Answer: A
System privileges granted directly are always active, so every row of USER_SYS_PRIVS is also in SESSION_PRIVS and the difference is empty. The other direction can be non-empty: a user who holds an enabled role, such as CONNECT, can have more privileges in SESSION_PRIVS than in USER_SYS_PRIVS. Your practice schema holds its privileges directly and has no roles, so both directions give 0 for you. COUNT(*) never returns NULL.
4. A owns T and runs GRANT SELECT ON t TO b WITH GRANT OPTION. B runs GRANT SELECT ON a.t TO c. Then A runs REVOKE SELECT ON t FROM b. What is true?
- B and C both lose SELECT on A.T.
- B loses SELECT; C keeps it.
- The REVOKE fails because B has granted the privilege to another user.
- B keeps SELECT because it was granted WITH GRANT OPTION.
Show the answer
Answer: A
Revoking an object privilege cascades: Oracle records who granted each object privilege, so the grant B made to C disappears together with B's own. The grant option never protects B from the revoke, and the revoke does not fail because B passed the privilege on. System privileges granted WITH ADMIN OPTION are the opposite case: their revoke does not cascade.