Controlling User Access practice questions

Worked examples for Controlling User Access

1. Question 1

   The role `HR_READER` already exists. You must let that role query every column of `EMPLOYEES` and update only its `SALARY` column, using a single statement. Which statement does this?

   1. A. GRANT SELECT (emp_id, salary), UPDATE (salary) ON employees TO hr_reader;

      Assumes a column list may follow any object privilege. GRANT permits a column list only with INSERT, REFERENCES, and UPDATE — never with SELECT (column-level read is done with a view), so the SELECT (emp_id, salary) clause makes the statement fail to parse.

   2. B. GRANT SELECT, UPDATE ON employees (salary) TO hr_reader;

      Attaches the column list to the object instead of to the privilege. In the on_object_clause the table name stands alone; the column list belongs to the privilege that restricts it, so this is invalid syntax and would in any case say nothing about which of the two privileges is column-limited.

   3. C. GRANT SELECT, UPDATE (salary) ON employees TO hr_reader;Correct answer

      Correct. Several object privileges may be listed in one GRANT, and a column list binds to the privilege it immediately follows — so SELECT is granted on the whole table while UPDATE is restricted to SALARY. The grantee_clause names the role directly (Oracle GRANT, object privileges).

   4. D. GRANT SELECT, UPDATE (salary) ON employees TO ROLE hr_reader;

      Invents a ROLE keyword in the grantee clause. GRANT's grantee_clause accepts bare user names, role names, or PUBLIC — Oracle resolves the name itself — so the extra keyword is a syntax error.

   Explanation

   Object privileges may be listed together in one GRANT, and a parenthesised column list applies to the privilege it directly follows, not to the table named in the ON clause. Only INSERT, REFERENCES, and UPDATE accept a column list; a column-limited SELECT is expressed with a view instead. The grantee is written as a plain name, with no keyword distinguishing a role from a user.

2. Question 2

   You are connected as the owner of the EMPLOYEES table and you want PUBLIC to be able to create tables. You issue the following statement. What is the result? ```sql GRANT CREATE TABLE ON employees TO PUBLIC; ```

   1. A. The statement succeeds: PUBLIC gains the CREATE TABLE privilege, restricted to the EMPLOYEES table.

      This is the core system-vs-object confusion: it treats a system privilege as something that can be narrowed to a named object by an ON clause. CREATE TABLE authorizes a kind of action inside a schema, not an action on one table, so it can never appear in the object-grant form of GRANT; there is no such thing as a table-scoped CREATE TABLE privilege.

   2. B. ORA-01031: insufficient privileges

      Reads the failure as an authorization problem — that the grantor lacks the right to pass CREATE TABLE on. The statement never reaches an authorization check: it is rejected while the privilege list is validated against the object-grant form, and the owner's own privilege set is irrelevant to that rejection.

   3. C. ORA-01919: role 'CREATE TABLE' does not exist

      Assumes an unrecognized name in the privilege list falls through to being resolved as a role. Role resolution happens only in the system-privilege/role branch of GRANT (the branch with no ON clause); because ON employees selects the object-privilege branch, CREATE TABLE is validated as an object privilege and rejected as one.

   4. D. ORA-00990: missing or invalid privilegeCorrect answer

      The ON clause puts the statement in the object-privilege branch of GRANT, where only object privileges (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES, READ, ALL PRIVILEGES) are legal. CREATE TABLE is a system privilege, so it is not a valid privilege for an object grant and Oracle raises ORA-00990: missing or invalid privilege.

   Explanation

   GRANT has two distinct forms. The system-privilege form names no object — the privilege authorizes a kind of action (CREATE TABLE, CREATE SESSION, SELECT ANY TABLE) within the grantee's own schema or database-wide. The object-privilege form uses ON to name a specific object, and only privileges defined on objects may appear there. Putting a system privilege in front of an ON clause therefore fails validation rather than producing a narrower, table-scoped version of that privilege.

3. Question 3

   You are connected as the owner of `DEPARTMENTS`. No privilege on `DEPARTMENTS` has ever been granted to PUBLIC by you or by anyone else. You now run the following statement. What is the result? ```sql REVOKE SELECT ON departments FROM PUBLIC ```

   1. A. ORA-01952: system privileges not granted to 'PUBLIC'

      Applies the system-privilege/role revoke diagnostic to an object privilege. ORA-01952 is raised by `REVOKE <system privilege or role> FROM grantee` when that grantee never held it; an ON-qualified revoke of an object privilege takes the object-privilege path instead.

   2. B. The statement completes successfully; revoking a privilege that was never granted is treated as a no-op.

      Assumes REVOKE is idempotent. Oracle validates that the current user actually made the grant being withdrawn; when there is no matching grant the statement is rejected outright rather than silently succeeding with nothing to remove.

   3. C. ORA-00942: table or view does not exist

      Assumes the object in a REVOKE is resolved through the grantee's namespace, so PUBLIC's lack of the privilege would hide the table. The object is resolved in the grantor's schema, where DEPARTMENTS exists; the failure is about the missing grant, not the missing table.

   4. D. ORA-01927: cannot REVOKE privileges you did not grantCorrect answer

      A grantor may withdraw only the object privileges it actually granted. Since this user never granted SELECT on DEPARTMENTS to PUBLIC, there is no grant to revoke and Oracle raises ORA-01927.

   Explanation

   REVOKE of an object privilege is scoped to grants the current user made: the database looks for a matching grantor/grantee/object/privilege row, and when none exists it rejects the statement instead of succeeding vacuously. Note the contrast in how a successful revoke behaves — withdrawing an object privilege that was passed on WITH GRANT OPTION cascades and removes the downstream grants too, whereas revoking a system privilege granted WITH ADMIN OPTION leaves the downstream grants in place.

4. Question 4

   You are connected as the owner of the `EMPLOYEES` and `DEPARTMENTS` tables. Which statement executes successfully and gives every current and future database user the ability to both query and modify rows in `EMPLOYEES`?

   1. A. GRANT CREATE SESSION ON employees TO PUBLIC

      Treats a system privilege as though it could be object-scoped. CREATE SESSION is a system privilege and is granted with `GRANT CREATE SESSION TO grantee` — it can never appear in a grant that carries an ON clause, so the parser rejects the statement (ORA-00990, missing or invalid privilege).

   2. B. GRANT SELECT ON employees, departments TO PUBLIC

      Assumes the ON clause accepts a list of objects the way the privilege clause accepts a list of privileges. The grant_object_privileges syntax is `ON [schema.]object` — exactly one object per statement — so the comma after employees is a syntax error; two GRANT statements are required.

   3. C. GRANT SELECT, UPDATE ON employees TO PUBLICCorrect answer

      Correct object grant: several object privileges may be listed in one statement, the ON clause names a single table, and PUBLIC is the keyword grantee that covers every current and future user. This is the only statement here that Oracle executes without error.

   4. D. GRANT ALL PRIVILEGES ON employees TO ALL USERS

      Treats `ALL USERS` as a way to name everybody. The only keyword grantee meaning 'all users' is PUBLIC, which the GRANT grammar special-cases; `ALL` is an Oracle reserved word with no such provision and cannot stand as an unquoted grantee, and two bare tokens can never form one identifier anyway — so `TO ALL USERS` fails to parse before Oracle ever checks whether such a grantee exists.

   Explanation

   An object grant has a fixed shape: one or more object privileges, then `ON` a single schema object, then `TO` a grantee list. System privileges such as CREATE SESSION have no ON clause and cannot be mixed into that form, and the ON clause never accepts more than one object. PUBLIC is the reserved grantee that extends a privilege to every current and future user; no other keyword such as ALL USERS has that meaning.

5. Question 5

   You own the EMPLOYEES table. Every database user — including users created after today — must be able to query EMPLOYEES and to pass that query privilege on to other users, but no user may gain any privilege that modifies the table. Which statement achieves exactly this?

   1. A. GRANT SELECT ON employees TO ALL USERS WITH GRANT OPTION;

      Assumes ALL USERS is a grantee keyword. The grantee clause accepts users, roles, and the single keyword PUBLIC; ALL USERS is parsed as an identifier, so this fails rather than granting anything.

   2. B. GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTION;Correct answer

      PUBLIC is the grantee group that implicitly contains every current and future database user, and WITH GRANT OPTION is the object-privilege clause allowing the grantee to re-grant; it is permitted when granting to a user or to PUBLIC. Only SELECT is named, so no modifying privilege is conferred.

   3. C. GRANT SELECT ON employees TO PUBLIC WITH ADMIN OPTION;

      Applies WITH ADMIN OPTION to an object privilege. ADMIN OPTION belongs to the system-privilege and role forms of GRANT; the object-privilege form takes WITH GRANT OPTION, so this statement is not valid syntax for a grant on a table.

   4. D. GRANT ALL ON employees TO PUBLIC WITH GRANT OPTION;

      Reads ALL as covering all grantees or as a harmless synonym for read access. In an object grant, ALL (equivalently ALL PRIVILEGES) means every object privilege the grantor can grant on that object — including INSERT, UPDATE, and DELETE — which violates the read-only requirement.

   Explanation

   Granting to PUBLIC is how a privilege is made available to every user of the database, present and future, without naming them; it is a grantee keyword, not a role you create or a phrase like ALL USERS. The clause that lets a grantee pass an object privilege on is WITH GRANT OPTION — WITH ADMIN OPTION is its counterpart for system privileges and roles and is not accepted in an object grant. Finally, the privilege list controls what is conferred: naming SELECT confers only query access, whereas ALL in an object grant confers every object privilege on the table, including the DML privileges.

6. Question 6

   You are connected as the owner of `EMPLOYEES`. Which statement executes successfully and lets every database user both query `EMPLOYEES` and pass that query privilege on to other users and roles?

   1. A. GRANT SELECT ON employees TO PUBLIC WITH ADMIN OPTION

      Confuses WITH ADMIN OPTION with WITH GRANT OPTION. WITH ADMIN OPTION is the re-grant clause for system privileges and roles only; on an object grant the parser expects the keyword GRANT after WITH and raises ORA-00993 (missing GRANT keyword).

   2. B. GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTIONCorrect answer

      The object-privilege re-grant clause is WITH GRANT OPTION, written after the TO clause, and it may be specified when the grantee is a user or PUBLIC. This is the only statement here that Oracle executes without error, and it is exactly what enables the grantee to pass SELECT on.

   3. C. GRANT SELECT ON employees WITH GRANT OPTION TO PUBLIC

      Assumes the clauses of GRANT can be reordered. WITH GRANT OPTION is a trailing clause that must follow the grantee list; placing it between the object and TO breaks the grant_object_privileges grammar and the statement fails to parse.

   4. D. GRANT SELECT, GRANT OPTION ON employees TO PUBLIC

      Treats the re-grant capability as an object privilege that can be listed alongside SELECT. GRANT OPTION is not a member of the object_privilege list (which holds ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, and so on); it is only ever expressed as the trailing WITH GRANT OPTION clause.

   Explanation

   Object privileges are made re-grantable with the trailing WITH GRANT OPTION clause, which sits after the TO grantee list and may be used when granting to a user or to PUBLIC. WITH ADMIN OPTION is the parallel clause for system privileges and roles and is not accepted on an object grant. The capability is not itself an object privilege, so it cannot be listed next to SELECT, nor can the clause be moved ahead of the TO clause.

7. Question 7

   You are connected as the owner of `EMPLOYEES` and want to give PUBLIC a privilege restricted to the `SALARY` column only. Which statement executes successfully?

   1. A. GRANT UPDATE (salary) ON employees TO PUBLICCorrect answer

      Column lists are permitted on an object grant only for INSERT, REFERENCES, and UPDATE, and the list follows the privilege name and precedes the ON clause. UPDATE (salary) satisfies both rules, so this grant succeeds and confines the privilege to one column.

   2. B. GRANT SELECT (salary) ON employees TO PUBLIC

      Assumes any object privilege can be narrowed to a column. SELECT is not one of the column-qualifiable privileges — only INSERT, REFERENCES, and UPDATE are — so Oracle rejects the column list; column-level reads must be exposed through a view instead.

   3. C. GRANT DELETE (salary) ON employees TO PUBLIC

      Treats DELETE as column-scopable. DELETE removes whole rows, so a column list is meaningless for it and is not in the set of column-qualifiable privileges (INSERT, REFERENCES, UPDATE); the statement is rejected.

   4. D. GRANT UPDATE ON employees.salary TO PUBLIC

      Puts the column in the ON clause. The ON clause takes `[schema.]object`, so employees.salary is read as object SALARY in schema EMPLOYEES rather than as a column, and the statement fails; the column belongs in a parenthesised list after the privilege name.

   Explanation

   Oracle allows an object privilege on a table to be narrowed to specific columns, but only for INSERT, REFERENCES, and UPDATE — privileges that write or reference individual columns. The column list is written in parentheses immediately after the privilege name, while the ON clause still names the table alone, because a dotted name there is interpreted as schema.object. Restricting reads to certain columns is done by granting SELECT on a view that projects only those columns.

8. Question 8

   You are connected as the owner of the EMPLOYEES table and you execute the following statement. What is the result? ```sql GRANT SELECT, UPDATE ON employees TO PUBLIC WITH ADMIN OPTION; ```

   1. A. ORA-00990: missing or invalid privilege

      Assumes the defect is in the privilege list — that SELECT and UPDATE cannot be granted in one statement, or that UPDATE is not a valid object privilege. Both are valid object privileges and a single GRANT may name several of them; the parser objects to the WITH clause, not to the privilege list.

   2. B. ORA-00994: missing OPTION keyword

      Assumes Oracle accepts WITH ADMIN as the start of a valid clause and only checks the trailing keyword. ORA-00994 is raised when the OPTION keyword itself is left off (WITH GRANT); here OPTION is present and it is ADMIN that is illegal after WITH in an object grant.

   3. C. ORA-00993: missing GRANT keywordCorrect answer

      In a GRANT of object privileges the only legal propagation clause is WITH GRANT OPTION, so after WITH the parser requires the keyword GRANT. Finding ADMIN instead, it raises ORA-00993 — whose documented cause includes a WITH clause not followed by GRANT OPTION.

   4. D. The statement succeeds, and any user may now pass SELECT and UPDATE on EMPLOYEES on to other users.

      The classic swap of the two propagation clauses: it treats WITH ADMIN OPTION as a general 'let the grantee re-grant this' modifier. WITH ADMIN OPTION applies only to system privileges and roles; object privileges are re-grantable only via WITH GRANT OPTION, so the statement never runs.

   Explanation

   System privileges and object privileges are propagated by different clauses. A system privilege or role is passed on with WITH ADMIN OPTION, while a privilege on a named object is passed on with WITH GRANT OPTION; the two are not interchangeable and the grammar for an object grant accepts only the latter. Because the mismatch is caught while parsing the WITH clause, the statement fails outright rather than granting anything, and the error names the keyword the parser expected there.