Managing Schema Objects and Access practice questions

Worked examples for Managing Schema Objects and Access

1. Question 1

   The owner of HR.DEPARTMENTS granted the REFERENCES privilege on it to user SALES, who then created a foreign key on SALES.ORDERS pointing at HR.DEPARTMENTS(dept_id). HR now runs `REVOKE REFERENCES ON departments FROM sales CASCADE CONSTRAINTS;`. What is the result?

   1. A. The REVOKE succeeds and the privilege is removed, but the existing foreign-key constraint on SALES.ORDERS remains fully enforced.

      This assumes the dependent constraint survives. The foreign key exists only because of the REFERENCES privilege; revoking that privilege with CASCADE CONSTRAINTS drops the constraint rather than leaving it in force.

   2. B. The REVOKE fails; a REFERENCES privilege with a dependent foreign key cannot be revoked, even with CASCADE CONSTRAINTS.

      This is the 'REFERENCES with dependents is un-revokable' misconception. Omitting CASCADE CONSTRAINTS would raise an error, but supplying it is precisely what authorizes the revoke by dropping the dependent constraints.

   3. C. It was SELECT, not REFERENCES, that was required to define the foreign key, so revoking REFERENCES has no effect on the constraint.

      This is the 'SELECT enables a foreign key' misconception. Creating a foreign key that points at another user's table requires the REFERENCES privilege on that table, not SELECT, so revoking REFERENCES directly affects the constraint.

   4. D. The foreign-key constraint on SALES.ORDERS is dropped along with the revoked REFERENCES privilege.Correct answer

      The foreign key depends on the REFERENCES privilege that made it possible. REVOKE ... REFERENCES ... CASCADE CONSTRAINTS removes the privilege and drops every FK constraint that relied on it, so the constraint on SALES.ORDERS is dropped.

   Explanation

   Defining a foreign key against another user's table requires the REFERENCES object privilege on that table, and any such constraint depends on the privilege continuing to exist. Revoking REFERENCES therefore cannot leave the dependent foreign key standing: CASCADE CONSTRAINTS must be supplied so the revoke can drop those constraints, and without it the revoke would error. The constraint is removed as a consequence of the revoke, not preserved.

2. Question 2

   User DEV holds the CREATE VIEW system privilege. DEV needs to read HR.EMPLOYEES, and the only SELECT privilege DEV has on that table comes from the role RPT_ROLE, which was granted `SELECT ON hr.employees` and then granted to DEV. In a session where RPT_ROLE is enabled, DEV runs `CREATE VIEW dev.emp_v AS SELECT employee_id, salary FROM hr.employees;`. What happens?

   1. A. The view is created successfully, because an enabled role confers its object privileges for every operation exactly as a direct grant would.

      This is the 'a role is equivalent to a direct grant everywhere' misconception. An enabled role does authorize ad-hoc DML such as a plain SELECT, but Oracle deliberately ignores role-supplied object privileges when it compiles a stored object like a view, so the create does not succeed.

   2. B. The statement fails, but only because referencing HR's table from DEV's own schema requires the CREATE ANY VIEW system privilege rather than CREATE VIEW.

      This is the 'a cross-schema view needs CREATE ANY VIEW' misconception. CREATE VIEW is sufficient to build a view in one's own schema even when it references another schema's tables; CREATE ANY VIEW is required only to create the view inside another user's schema. The real blocker is the role-supplied privilege, not the system privilege.

   3. C. The statement fails with ORA-01031 (insufficient privileges), because the SELECT privilege on the base table must be granted to the view owner directly, not through a role.Correct answer

      The CREATE VIEW prerequisites require the owner to hold the necessary object privileges on every base table by a direct grant; privileges available only through a role are not counted. With SELECT on HR.EMPLOYEES reaching DEV solely via RPT_ROLE, the CREATE VIEW raises ORA-01031.

   4. D. The view is created successfully; the rule that privileges must be held directly rather than through a role governs only PL/SQL stored procedures, not views.

      This wrongly narrows the direct-grant rule to PL/SQL. The CREATE VIEW prerequisites state the base-table object privileges must be granted directly and not through a role, so the rule applies to views just as it does to stored procedures.

   Explanation

   Oracle does not count privileges obtained through a role when it compiles a stored object such as a view; the owner must hold the object privilege on every base table by a direct grant. A SELECT that reaches the user only via an enabled role therefore satisfies an interactive query but not a CREATE VIEW, which fails with an insufficient-privileges error. Building a view in one's own schema needs only CREATE VIEW even when it reads another schema's tables, so the system privilege is not the obstacle here.

3. Question 3

   The CUSTOMERS table has no index on its EMAIL column. A DBA runs `ALTER TABLE customers ADD CONSTRAINT cust_email_uk UNIQUE (email);`. Which statement best describes the effect on indexing?

   1. A. Nothing is indexed; a UNIQUE constraint only checks values on INSERT and never creates an index.

      This is the 'constraints do not build indexes' misconception. Enforcing uniqueness efficiently requires an index, so Oracle automatically creates one on the constrained column when no usable index already exists.

   2. B. A non-unique bitmap index is created automatically to support the constraint.

      This misidentifies the index type. To back a UNIQUE (or PRIMARY KEY) constraint when none suitable exists, Oracle creates a unique B-tree index, not a bitmap index.

   3. C. Oracle automatically creates a unique index on EMAIL to enforce the constraint, and dropping the constraint may also drop that index.Correct answer

      A UNIQUE or PRIMARY KEY constraint is enforced through an index; when no usable index exists, Oracle creates a unique index on the constrained columns, and that implicitly created index is dropped when the constraint is dropped.

   4. D. The ALTER TABLE fails unless you first create the unique index on EMAIL yourself.

      This assumes the index is a prerequisite. Oracle creates the enforcing index automatically when one does not exist; a pre-existing index (even non-unique) can be reused, but its absence does not cause the statement to fail.

   Explanation

   A UNIQUE or PRIMARY KEY constraint is enforced through an index, so when you add one and no suitable index already exists, Oracle silently creates a unique index on the constrained columns. If a usable index is already present it is reused instead of building a new one. Because the index was created to support the constraint, dropping the constraint can drop the index along with it.

4. Question 4

   A view is defined as `CREATE VIEW emp_dept AS SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id;`. Each department has many employees. A user runs `UPDATE emp_dept SET dept_name = 'Sales' WHERE emp_id = 100;`. What happens?

   1. A. It succeeds and updates the matching DEPARTMENTS row, because any column projected by a view is updatable.

      This is the 'every projected column is updatable' misconception. In a join view, only columns that map to a key-preserved table may be modified; DEPT_NAME maps to DEPARTMENTS, which is not key-preserved here, so Oracle rejects the update with ORA-01779 rather than changing the department row.

   2. B. It fails with ORA-01779, because dept_name maps to DEPARTMENTS, which is not a key-preserved table in this join view.Correct answer

      A table in a join view is key-preserved only if its key stays unique across the view's result. One department maps to many employee rows, so DEPARTMENTS is not key-preserved, and a column mapping to it cannot be modified — Oracle raises ORA-01779 ('cannot modify a column which maps to a non key-preserved table').

   3. C. It fails with ORA-01732, because DML of any kind is illegal against a view that contains a join.

      This is the 'join views are never updatable' misconception. A join view is updatable through columns of its key-preserved table — updating EMP_NAME here would succeed. ORA-01732 applies to views with GROUP BY, DISTINCT, aggregates, or set operators, not to this modification.

   4. D. It succeeds against the view only; the base tables are untouched because a view keeps its own copy of the data.

      This is the 'a view stores its own data' misconception. A view stores no data — it is a stored query, and DML through it acts on the base tables. Here the update never applies at all; it is rejected with ORA-01779.

   Explanation

   A view built on a join can still be updated, but only through columns that map to a key-preserved table — one whose key remains unique in the view's result set. A column belonging to a table that is not key-preserved cannot be modified, because one base row could be reached through many view rows. Modifying a column of the key-preserved side of the same view would have been permitted.

5. Question 5

   User HR runs `GRANT SELECT ON employees TO mgr WITH GRANT OPTION;`. MGR then runs `GRANT SELECT ON hr.employees TO analyst;`. Later HR runs `REVOKE SELECT ON employees FROM mgr;`. What can ANALYST do with HR.EMPLOYEES afterward?

   1. A. ANALYST keeps SELECT, because revoking a privilege from one user never affects a privilege another user already holds.

      This is the 'object-privilege revoke does not cascade' misconception. Because MGR received SELECT WITH GRANT OPTION and re-granted it, revoking it from MGR cascades and removes the privilege from ANALYST as well.

   2. B. The REVOKE fails; HR must first revoke MGR's downstream grant to ANALYST before the privilege can be revoked from MGR.

      This assumes downstream grants must be unwound manually first. Oracle imposes no such ordering — the REVOKE succeeds immediately and automatically cascades to the grants MGR made from that privilege.

   3. C. ANALYST also loses SELECT, because revoking an object privilege that was granted WITH GRANT OPTION cascades to everyone the grantee re-granted it to.Correct answer

      Revoking an object privilege granted WITH GRANT OPTION revokes it from the grantee and, in a cascading fashion, from every user to whom the grantee had in turn granted it. When HR revokes from MGR, ANALYST's derived SELECT disappears too.

   4. D. ANALYST keeps SELECT, because an object privilege granted WITH GRANT OPTION behaves like a system privilege granted WITH ADMIN OPTION, whose revoke does not cascade.

      This conflates the object-privilege rule with the system-privilege rule, which is exactly where they differ. A system privilege revoke does not cascade, but an object privilege revoke does — so ANALYST loses the SELECT it derived from MGR.

   Explanation

   An object privilege passed on via WITH GRANT OPTION creates a dependency chain, and revoking the privilege from the intermediate grantee tears down that chain: everyone who received the privilege from that grantee loses it too. This cascading behaviour is specific to object privileges and is precisely where they diverge from system privileges, whose revoke stops at the named user. No downstream cleanup is required first — the cascade is automatic.

6. Question 6

   A unique index is created with `CREATE UNIQUE INDEX emp_email_uidx ON employees(email);` and it is the object enforcing EMAIL uniqueness. While tuning a query, a DBA runs `ALTER INDEX emp_email_uidx INVISIBLE;`, leaving the instance parameter OPTIMIZER_USE_INVISIBLE_INDEXES at its default. A user then inserts a row whose EMAIL duplicates an existing value. What happens?

   1. A. The INSERT succeeds; making the index invisible stops it from being maintained, saving DML overhead, so duplicates are permitted until it is made visible again.

      This confuses INVISIBLE with UNUSABLE. An UNUSABLE index is no longer maintained, but an INVISIBLE index continues to be maintained on every DML and continues to enforce its uniqueness; only its consideration during query optimization is suppressed.

   2. B. The INSERT fails with a duplicate-value error; an invisible index is still maintained by DML and still enforces uniqueness — invisibility only hides it from the optimizer when it chooses access paths.Correct answer

      ALTER INDEX ... INVISIBLE changes only whether the cost-based optimizer will consider the index for query access paths (governed by OPTIMIZER_USE_INVISIBLE_INDEXES, default FALSE). The index keeps being updated on every DML and keeps policing uniqueness, so a duplicate EMAIL raises ORA-00001.

   3. C. The ALTER INDEX statement itself fails, because an index that backs a uniqueness rule cannot be made invisible.

      This is the 'a constraint- or uniqueness-backing index cannot be invisible' misconception. Oracle lets any index — including one enforcing a unique or primary-key constraint — be marked invisible; visibility and enforcement are independent properties of the index.

   4. D. The INSERT fails, but only because the optimizer transparently falls back to the invisible index for the uniqueness check; had OPTIMIZER_USE_INVISIBLE_INDEXES been TRUE the duplicate would have been allowed.

      This is the 'uniqueness enforcement depends on optimizer visibility' misconception. Enforcement is not an optimizer decision and never consults OPTIMIZER_USE_INVISIBLE_INDEXES; that parameter governs only query access-path selection, so uniqueness holds identically whether it is TRUE or FALSE.

   Explanation

   An index plays two independent roles: it can enforce a uniqueness rule and it can offer the optimizer a fast access path. Marking an index invisible suppresses only the second role — the optimizer stops considering it unless explicitly told otherwise — while the engine keeps maintaining the index on every DML and keeps enforcing any uniqueness it backs. Invisibility is a plan-testing tool, not a way to disable or soft-delete an index the way UNUSABLE does.

7. Question 7

   User SCOTT has been granted SELECT on HR.EMPLOYEES but does not own it. SCOTT wants one data-dictionary view that lists every table he can query — including HR.EMPLOYEES — and shows which schema each table belongs to. Which view should he query, and why?

   1. A. DBA_TABLES, because it is the only view that can show tables in another schema and it is available to every user by default.

      This is the 'DBA_ views are open to everyone' misconception. DBA_TABLES does list tables across schemas but requires a DBA-level privilege, and it is not the least-privileged view that meets SCOTT's need.

   2. B. ALL_TABLES, because it lists every table the current user can access — his own plus those granted to him — and includes an OWNER column.Correct answer

      ALL_TABLES shows all tables the current user has access to, spanning multiple schemas, and carries an OWNER column to identify each table's schema. That exactly covers SCOTT's own tables plus HR.EMPLOYEES, which was granted to him.

   3. C. USER_TABLES, because it lists every table the current user can access and has an OWNER column to distinguish schemas.

      This misstates USER_TABLES on two counts. It lists only tables the current user owns — not HR.EMPLOYEES — and it has no OWNER column, since every row already belongs to the current user.

   4. D. USER_TAB_PRIVS, because table visibility is determined solely by the privilege grants that this view records.

      This confuses a privilege-listing view with a table-listing view. USER_TAB_PRIVS records grants, not table definitions, and querying it would not enumerate the tables themselves the way ALL_TABLES does.

   Explanation

   The dictionary view families widen in scope: USER_* covers only objects the current user owns, ALL_* covers everything the current user can access (own plus granted), and DBA_* covers the whole database but needs a DBA-level privilege. Because SCOTT wants his own tables together with a table merely granted to him, and wants each table's owning schema, the accessible-scope view with an OWNER column is the right and least-privileged choice. USER_* views omit the OWNER column entirely, since every row is implicitly the current user's.

8. Question 8

   You want HR_CLERK to be able to change only the SALARY column of the EMPLOYEES table, and you are considering restricting other object privileges to specific columns as well. Which statement about column-level object privileges in Oracle is correct?

   1. A. `GRANT UPDATE (salary) ON employees TO hr_clerk;` is valid; column-level grants are supported for UPDATE, INSERT, and REFERENCES.Correct answer

      Oracle permits a column list only on the INSERT, UPDATE, and REFERENCES object privileges. `GRANT UPDATE (salary) ON employees TO hr_clerk;` restricts the grantee to updating just that column, which is exactly the supported column-level form.

   2. B. `GRANT SELECT (salary) ON employees TO hr_clerk;` is valid; SELECT can be limited to specific columns just like UPDATE.

      This is the 'column-level SELECT' misconception. SELECT is a table-wide object privilege and cannot take a column list; a column-restricted SELECT must instead be achieved with a view, not with a column-level grant.

   3. C. Column-level privileges are not supported at all; every object privilege necessarily applies to the whole table.

      This denies column-level grants entirely. Oracle does support them for INSERT, UPDATE, and REFERENCES, so a grant naming a single column such as SALARY is legal for those privileges.

   4. D. `GRANT DELETE (salary) ON employees TO hr_clerk;` limits the grantee to deleting values in that one column.

      This is the 'column-level DELETE' misconception. DELETE removes whole rows and cannot be restricted to a column; naming a column on a DELETE grant is not permitted, and a column list is valid only for INSERT, UPDATE, and REFERENCES.

   Explanation

   Only three object privileges accept a column list — INSERT, UPDATE, and REFERENCES — because each can meaningfully act on individual columns. SELECT and DELETE always operate table-wide, so they cannot be narrowed with a column list; column-restricted reading is done with a view instead. Recognizing which privileges are column-grantable is the crux of the item.