How to Grant Object Privileges in Oracle

No user can touch another user's tables without a privilege. Object privileges allow specific actions on specific objects: SELECT on a table, UPDATE of one column, EXECUTE on a procedure. The owner grants them with GRANT and takes them back with REVOKE, which is how an application user gets exactly the access it needs and nothing more.

Code for This Guide

The main examples are in the examples/security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

grant {privilege [(column, ...)] [, ...] | all} on object
  to {user | role | public} [with grant option]

revoke {privilege [, ...] | all} on object from {user | role | public}

Object privileges include SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, INDEX, and ALTER. UPDATE, INSERT, and REFERENCES can be limited to columns. The examples grant privileges to NIMBUS_APP, a user created as in how to create users with profiles in Oracle.

Grant Read Access and One Updatable Column

The owner, NIMBUS, lets the application read FLIGHTS and ROUTES and update only the status of a flight.

Example:

grant select on flights to nimbus_app;
grant select on routes to nimbus_app;
grant update (status) on flights to nimbus_app;

select grantee, table_name, privilege from user_tab_privs_made
where  grantee = 'NIMBUS_APP' order by table_name, privilege;

select grantee, table_name, column_name, privilege from user_col_privs_made
where  grantee = 'NIMBUS_APP';

Output:

Grant succeeded.

Grant succeeded.

Grant succeeded.

GRANTEE       TABLE_NAME    PRIVILEGE
_____________ _____________ ____________
NIMBUS_APP    FLIGHTS       SELECT
NIMBUS_APP    ROUTES        SELECT

GRANTEE       TABLE_NAME    COLUMN_NAME    PRIVILEGE
_____________ _____________ ______________ ____________
NIMBUS_APP    FLIGHTS       STATUS         UPDATE

USER_TAB_PRIVS_MADE lists table privileges granted, and USER_COL_PRIVS_MADE column privileges.

What the Application User Sees

Connected as NIMBUS_APP with SQLcl's CONNECT command, the user can read flights and change a status, but cannot see CUSTOMERS at all.

Example:

connect nimbus_app/"Fly#Nimbus2026"@localhost:1521/FREEPDB1

select count(*) as visible_flights from nimbus.flights;
update nimbus.flights set status = status where flight_id = 1;
rollback;
select count(*) from nimbus.customers;

Output:

   VISIBLE_FLIGHTS
__________________
              2856

1 row updated.

Rollback complete.

Error starting at line : 6
In command -
select count(*) from nimbus.customers
Error at Command Line : 6 Column : 29
Error report -
SQL Error: ORA-00942: table or view "NIMBUS"."CUSTOMERS" does not exist

For a table it has no privilege on, Oracle reports ORA-00942, that the table does not exist, rather than insufficient privileges, so as not to reveal that it is there.

Revoke Privileges

Example:

revoke update on flights from nimbus_app;
revoke select on routes from nimbus_app;

select table_name, privilege from user_tab_privs_made where grantee = 'NIMBUS_APP';

Output:

Revoke succeeded.

Revoke succeeded.

TABLE_NAME    PRIVILEGE
_____________ ____________
FLIGHTS       SELECT

After the revokes, only SELECT on FLIGHTS remains. Revoking UPDATE removes the column-level grant too.

Things to Know

• WITH GRANT OPTION lets the grantee pass the privilege on; revoking from the grantee also revokes from those it granted to.
• Grant to roles rather than to many users one by one, so a change reaches all of them at once.
• Applications should refer to the owner's objects with the schema name, or through synonyms.

Related Guides

• How to Create Users with Profiles in Oracle
• How to Use WITH CHECK OPTION on Oracle Views

Conclusion

GRANT gives a user or role specific privileges on specific objects, down to single columns, and REVOKE takes them back. Grant applications only what they need; Oracle hides objects a user cannot access behind ORA-00942.