Oracle 1Z0-071: Control User Access

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Control User Access

User REPORTER already has CREATE SESSION. The user must be able to query only HR.EMPLOYEES and must not be able to query other users’ tables or grant this access to anyone else. Which statement should the DBA execute?

Options:

• A. GRANT SELECT ON hr.employees TO reporter WITH GRANT OPTION

• B. GRANT CREATE SESSION TO reporter

• C. GRANT SELECT ANY TABLE TO reporter

• D. GRANT SELECT ON hr.employees TO reporter

Best answer: D

Explanation: The correct choice is the table-level SELECT object privilege on HR.EMPLOYEES. It gives REPORTER exactly the required access and avoids both broader system-wide access and privilege delegation.

This tests least-privilege privilege management in Oracle SQL. When a user needs to query one specific table, the correct action is to grant an object privilege on that table, not a broader system privilege. GRANT SELECT ON hr.employees TO reporter allows queries against only that table.

SELECT ANY TABLE is a system privilege, so it is much broader than required because it permits querying many tables outside the stated need. Adding WITH GRANT OPTION would also exceed the requirement because it lets REPORTER grant that object privilege to other users. Since the stem says REPORTER already has CREATE SESSION, granting it again does not solve the access requirement.

The key takeaway is to use the narrowest object privilege that satisfies the requirement.

• Too broad: the option using SELECT ANY TABLE grants system-wide query capability beyond one table.
• Delegation added: the option with WITH GRANT OPTION lets the user pass access to others, which the stem forbids.
• Wrong privilege: the option granting CREATE SESSION affects login ability, not access to HR.EMPLOYEES.

Question 2

Topic: Control User Access

APPUSER can query HR.EMPLOYEES through both a role and a direct object grant:

GRANT SELECT ON hr.employees TO read_r;
GRANT read_r TO appuser;
GRANT SELECT ON hr.employees TO appuser;

You must remove only the role-based source of this access for APPUSER, and APPUSER must still be able to query HR.EMPLOYEES. Which statement is best?

Options:

• A. REVOKE SELECT ON hr.employees FROM appuser

• B. REVOKE read_r FROM appuser

• C. ALTER USER appuser DEFAULT ROLE NONE

• D. REVOKE SELECT ON hr.employees FROM read_r

Best answer: B

Explanation: Oracle treats a direct object grant and the same privilege received through a role as separate privilege sources. Removing the role from APPUSER removes only the role-based path, so the direct SELECT grant on HR.EMPLOYEES still allows the query.

In Oracle, privileges can come from more than one source at the same time. Here, APPUSER has SELECT on HR.EMPLOYEES directly and also through the role READ_R. If you revoke the role from the user, only the privileges supplied by that role are removed for that user; the direct object privilege is unaffected.

This makes the role revoke the best choice because it meets both requirements:

• remove the role-based source
• keep table access working through the direct grant

Revoking the object privilege from the user would remove the direct path, and revoking it from the role would change the role itself for every user who has it. The key takeaway is that Oracle evaluates direct grants and role grants independently.

• Direct revoke from user fails because it removes the direct SELECT privilege, which is the access that must remain.
• Revoke from the role changes the role’s privilege itself, affecting all users of that role, not just APPUSER.
• Default role change only disables automatic role enablement at login; it does not revoke the role grant itself.

Question 3

Topic: Control User Access

Given this statement:

GRANT SELECT, UPDATE (salary)
ON hr.employees
TO app_user;

What access does it provide to APP_USER?

Options:

• A. Query all columns and update only salary in HR.EMPLOYEES

• B. Query only salary and update all columns in HR.EMPLOYEES

• C. Update salary in every table owned by HR

• D. Create a view on HR.EMPLOYEES without any other privilege

Best answer: A

Explanation: This is an object privilege grant on one table. SELECT applies to the table, while UPDATE (salary) is a column-level update privilege, so the user can query the table and modify only the salary column.

Oracle distinguishes object privileges from system privileges. In this statement, the privileges are granted on a specific object: HR.EMPLOYEES. The SELECT privilege lets APP_USER query rows from that table. The UPDATE (salary) clause is more specific: it allows updates only to the salary column, not to other columns in the table.

So the access is:

• query data from HR.EMPLOYEES
• update the salary column in HR.EMPLOYEES
• no permission is implied for other HR tables
• no broader update privilege is implied for other columns

A common confusion is mixing object privileges on one table with system privileges that apply across the schema or database.

• Column-limited update matters because UPDATE (salary) does not permit updating every column.
• Table-level SELECT matters because SELECT here is not restricted to only the listed update column.
• Object scope matters because privileges on HR.EMPLOYEES do not automatically apply to other HR tables.
• Different privilege type matters because creating a view requires separate privileges; this statement alone is about table access.

Question 4

Topic: Control User Access

User HR owns table EMPLOYEES. You must allow role PAYROLL_R to read all rows in HR.EMPLOYEES and update only the SALARY and COMMISSION_PCT columns. Which statement correctly grants the required access with least privilege?

Options:

• A. GRANT SELECT ANY TABLE, UPDATE (salary, commission_pct) ON hr.employees TO payroll_r

• B. GRANT SELECT, UPDATE (salary, commission_pct) ON hr.employees TO payroll_r

• C. GRANT SELECT, UPDATE ON hr.employees (salary, commission_pct) TO payroll_r

• D. GRANT SELECT ON hr.employees, UPDATE (salary, commission_pct) TO payroll_r

Best answer: B

Explanation: For a table object privilege, Oracle uses GRANT <object privileges> ON <schema.table> TO <user|role>. A column list can be attached to UPDATE, so the role can read the table but update only SALARY and COMMISSION_PCT.

This tests Oracle table object privileges and grant syntax. To grant access on one specific table, use an object grant in the form GRANT privilege_list ON schema.table TO grantee. For table privileges, Oracle allows column-level restriction with UPDATE(column_list) when you want least privilege.

In this case, the role needs:

• SELECT on HR.EMPLOYEES
• UPDATE only for SALARY and COMMISSION_PCT

So the privilege list must be written before the single ON hr.employees clause, with the column list attached to UPDATE. A system privilege such as SELECT ANY TABLE would be broader than required and is not the right choice for granting access to one table.

The key takeaway is that table-specific access uses object privileges, and column limits belong with privileges like UPDATE, not after the table name.

• The choice placing ON hr.employees after only SELECT breaks the object-grant syntax because the privilege list must come before one ON clause.
• The choice putting (salary, commission_pct) after the table name is invalid because column restrictions belong to UPDATE, not to the object name.
• The choice using SELECT ANY TABLE is a system privilege and grants much broader access than the stated requirement.

Question 5

Topic: Control User Access

Review the current grants:

GRANT SELECT ON hr.employees TO ann;
GRANT SELECT ON hr.departments TO ann;
GRANT SELECT ON hr.employees TO ben;
GRANT SELECT ON hr.departments TO ben;
GRANT SELECT ON hr.employees TO cara;
GRANT SELECT ON hr.departments TO cara;

Two more analysts will need the same access, and the DBA wants simpler ongoing privilege maintenance. What is the cleanest next step?

Options:

• A. Grant SELECT ANY TABLE to each analyst.

• B. Create a profile for the analysts and assign the privileges there.

• C. Create a role for the shared table privileges and grant it to the analysts.

• D. Grant the same table privileges to PUBLIC.

Best answer: C

Explanation: When several users need the same object privileges, a role is the cleanest Oracle mechanism. Grant the table privileges to the role once, then grant or revoke the role for each analyst as team membership changes.

Oracle roles are designed to collect privileges that multiple users share. In the exhibit, each analyst receives the same two SELECT grants, so continuing with direct grants means repeating the same work for every new team member and every future privilege change.

A cleaner approach is:

• Create one role
• Grant SELECT on hr.employees and hr.departments to that role
• Grant the role to each analyst

This centralizes privilege management, makes onboarding easier, and simplifies later revokes or changes. The other choices either grant far more access than required or use a feature that does not control object privileges.

• Granting SELECT ANY TABLE gives access far beyond the two HR tables shown.
• Granting the privileges to PUBLIC exposes those tables to every user, not just the analysts.
• A profile manages password and resource settings, not object privileges on tables.

Question 6

Topic: Control User Access

Which statement correctly distinguishes an Oracle object privilege from a system privilege?

Options:

• A. An object privilege allows administrative actions such as CREATE USER.

• B. A system privilege is the same thing as a role assigned to a user.

• C. An object privilege controls access to a specific object, such as SELECT on HR.EMPLOYEES.

• D. A system privilege controls access only to one named table or view.

Best answer: C

Explanation: Oracle separates privileges by scope. Object privileges apply to specific schema objects like tables, views, or sequences, while system privileges allow broader actions in the database, such as creating objects or users.

The core distinction is scope. An object privilege lets a user perform an action on a specific object owned by a schema, such as SELECT, INSERT, or UPDATE on a table or view. A system privilege grants authority to perform a type of action across the database, such as CREATE TABLE or CREATE USER, subject to Oracle rules.

When a question asks about access to one table, view, or other object, think object privilege. When it asks about administrative capability or the right to create or manage database structures, think system privilege.

A role is different again: it is a container that can hold privileges, not a privilege type itself.

• Single-object scope fits object privileges, not system privileges, because system privileges are broader than one named table or view.
• Administrative capability like CREATE USER is a classic system privilege, not an object privilege.
• Role confusion is common, but a role is a bundle of privileges rather than a privilege category.

Question 7

Topic: Control User Access

read_role already contains the SELECT privilege on HR.EMPLOYEES. Which statement gives user APP_USER that access by a direct privilege grant, not by granting the role?

Options:

• A. GRANT read_role TO app_user

• B. GRANT SELECT ON hr.employees TO app_user

• C. GRANT SELECT ON hr.employees TO read_role

• D. GRANT read_role TO PUBLIC

Best answer: B

Explanation: A direct privilege grant names the privilege and grants it straight to the user. For an object privilege, the Oracle syntax is GRANT privilege ON object TO user, so granting SELECT on HR.EMPLOYEES to APP_USER is the only direct grant here.

The core distinction is between granting a privilege itself and granting a role that happens to contain privileges. A direct grant gives the user the privilege explicitly, while a role grant gives the user whatever privileges are bundled inside that role.

For an object privilege, the direct form is:

• GRANT privilege ON schema.object TO user

In the stem, read_role already has SELECT on HR.EMPLOYEES, but granting read_role to APP_USER would still be indirect access through a role. Granting SELECT on HR.EMPLOYEES to read_role also does not give the privilege directly to APP_USER.

The key takeaway is that GRANT ... TO user is not always a direct privilege grant; it is direct only when the thing being granted is the privilege itself, not a role name.

• Granting the role gives APP_USER access indirectly because the privilege comes from read_role, not from a direct privilege entry.
• Granting to the role adds the privilege to read_role, but APP_USER still would not receive it directly.
• Granting to PUBLIC makes the role available broadly and still does not create a direct privilege grant to APP_USER.

Question 8

Topic: Control User Access

A DBA reviews the current grants below:

SELECT grantee, owner, table_name, privilege
FROM   dba_tab_privs
WHERE  grantee IN ('APP_USER','REPORTING_R')
ORDER  BY grantee, table_name;

GRANTEE     OWNER   TABLE_NAME  PRIVILEGE
----------- ------- ----------- ---------
APP_USER    SALES   ORDERS      INSERT
REPORTING_R SALES   ORDERS      SELECT
REPORTING_R SALES   PRODUCTS    SELECT

APP_USER must be able to query SALES.ORDERS, but must not gain access to any other object or the ability to pass privileges to other users. Which action best meets this requirement?

Options:

• A. GRANT SELECT ON sales.orders TO app_user WITH GRANT OPTION;

• B. GRANT reporting_r TO app_user;

• C. GRANT SELECT ANY TABLE TO app_user;

• D. GRANT SELECT ON sales.orders TO app_user;

Best answer: D

Explanation: The requirement is least privilege: give APP_USER only the exact access needed. A direct SELECT grant on SALES.ORDERS satisfies the requirement without exposing SALES.PRODUCTS or allowing privilege delegation.

This tests the difference between an object privilege, a role, a system privilege, and WITH GRANT OPTION. Because APP_USER needs read access to only one table, the narrowest valid change is a direct object grant on SALES.ORDERS.

A role is broader here because the exhibit shows REPORTING_R already includes SELECT on both SALES.ORDERS and SALES.PRODUCTS. A system privilege such as SELECT ANY TABLE is much broader than required. Adding WITH GRANT OPTION would let APP_USER pass that object privilege to other users, which the requirement explicitly forbids.

In Oracle SQL, when you must change access without granting unnecessary authority, choose the smallest privilege scope that satisfies the stated need.

• Role is too broad because REPORTING_R also includes SELECT on SALES.PRODUCTS.
• System privilege is excessive because SELECT ANY TABLE allows access far beyond one table.
• Grant option overreaches because it lets the user grant the privilege to others.
• Existing privilege is irrelevant because INSERT on SALES.ORDERS does not provide query access.

Question 9

Topic: Control User Access

Which statement grants a system privilege to user APP_USER?

Options:

• A. GRANT INSERT ON departments TO app_user

• B. GRANT CREATE SESSION TO app_user

• C. GRANT UPDATE ON locations TO app_user

• D. GRANT SELECT ON employees TO app_user

Best answer: B

Explanation: System privileges allow actions at the database level, such as connecting or creating objects, and they are granted without an ON clause. CREATE SESSION is one of these privileges, so granting it to APP_USER is a system-privilege grant.

In Oracle SQL security, a system privilege allows a user to perform a type of action in the database, such as CREATE SESSION, CREATE TABLE, or CREATE VIEW. These privileges are granted directly to a user or role and do not name a specific table, view, or other object.

Object privileges apply to a particular object and therefore use ON object_name, such as SELECT ON employees or UPDATE ON locations. In the question, only the statement using CREATE SESSION grants a database-level capability rather than access to a named object. A quick rule is: if the grant names a specific object after ON, it is an object privilege, not a system privilege.

• SELECT ON employees is an object privilege because it grants query access to one table.
• INSERT ON departments is an object privilege because it allows DML on a specific table.
• UPDATE ON locations is an object privilege because it applies only to the named table.

Question 10

Topic: Control User Access

Assume orders is a table and clerk is a user.

GRANT INSERT (order_id, order_date), UPDATE (status)
ON orders
TO clerk;

What access does this statement provide to clerk?

Options:

• A. Delete rows from orders, and update only status

• B. Insert values into order_id and order_date, and update only status

• C. Insert rows into any column, and update any column

• D. Select all rows from orders, but not modify them

Best answer: B

Explanation: This GRANT gives column-level object privileges on a table. clerk may insert values only for order_id and order_date, and may update only the status column; no SELECT or DELETE privilege is granted.

In Oracle, a GRANT on a table can assign object privileges either at the table level or, for some privileges, at the column level. Here, INSERT (order_id, order_date) allows inserts only for those named columns, and UPDATE (status) allows changes only to the status column.

Because the statement does not grant SELECT, DELETE, or unrestricted table-level INSERT or UPDATE, clerk does not receive those abilities. The key is to read each privilege together with its column list, which narrows the access to only the specified columns.

• Any-column access is too broad because the column lists restrict both INSERT and UPDATE.
• Query-only access fails because no SELECT privilege appears in the statement.
• Delete capability is unsupported because DELETE must be granted explicitly.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.