Stepwise-Lab

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?

  1. ORA-00942: table or view does not exist
  2. ORA-01031: insufficient privileges
  3. An empty result
  4. 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?

  1. ORA-01031: insufficient privileges
  2. ORA-00942: table or view does not exist
  3. 1 row updated
  4. 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);
  1. 0
  2. The number of system privileges received through roles
  3. The number of system privileges granted directly
  4. 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?

  1. B and C both lose SELECT on A.T.
  2. B loses SELECT; C keeps it.
  3. The REVOKE fails because B has granted the privilege to another user.
  4. 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.