System Privileges Compared to Object Privileges

Oracle has two fundamental categories of privilege, plus a third that sits between them, introduced more recently and covered at the end of this lesson. Object privileges control access to a specific table, view, sequence, or other named object. System privileges control the right to perform a class of action across the database, or across any schema, rather than on one named object. If you want a user to be able to select from one specific table, you grant an object privilege on that table. If you want a user to be able to create tables in the first place, anywhere they have a schema to create them in, you grant the CREATE TABLE system privilege instead.

The distinction goes deeper than just scope. Each category is granted with different syntax, delegated with a different clause, and recorded in different data dictionary views:

One asymmetry worth knowing before you rely on either delegation clause: revoking a system privilege from a user who passed it along WITH ADMIN OPTION does not cascade to whoever they granted it to; those downstream grants survive and need revoking separately. Revoking an object privilege that traveled along a WITH GRANT OPTION chain works the opposite way and does cascade, automatically removing it from everyone further down that chain. A later lesson in this module covers GRANT and REVOKE in full depth, including worked examples of both directions.

An object privilege gives the grantee permission to use a schema object^[1] owned by another user in a particular way. Several types of object privilege exist, and some apply only to certain kinds of schema object; INDEX applies only to tables, while SELECT applies to tables, views, and sequences. Object privileges can be granted individually, grouped in a list, or granted all at once with the keyword ALL to implicitly grant every available privilege for that object.

Warning: be careful using ALL, since it may grant more than you actually intend to.

Object privileges can also be scoped down to a single column rather than an entire table, which matters for UPDATE and REFERENCES specifically: GRANT UPDATE (salary) ON hr.employees TO payroll_clerk; permits updating only the salary column, not the rest of the row.

Not every schema object has object privileges to grant at all. Indexes, triggers, clusters, and database links have none; controlling who can act on them is done entirely through system privileges instead, such as ALTER ANY INDEX. If you find yourself looking for a GRANT statement to control access to one of these object types specifically, that's a sign you want a system privilege, not an object privilege.

Oracle AI Database 26ai provides several object privileges for tables, giving the table owner considerable flexibility in controlling how their objects are used and by whom.

The following privileges are commonly granted, and you should know them well:

1. SELECT: the most commonly used privilege for tables. With this privilege, the table owner permits the grantee to query the specified table with a SELECT statement.
2. INSERT: permits the grantee to create new rows in the specified table with an INSERT statement.
3. UPDATE: permits the grantee to modify existing rows in the specified table with an UPDATE statement.
4. DELETE: permits the grantee to remove rows from the specified table with a DELETE statement.

Privileges obtained through a role generally work the same as privileges granted directly, with one category of exception worth remembering: certain DDL operations, such as creating a view that depends on another user's table, require the privilege to have been granted directly rather than inherited through a role. If a statement that should work fails with a privilege error even though the connected user's role clearly includes the needed privilege, this is one of the first things worth checking.

Current Oracle releases add a third category sitting between the other two: a schema privilege behaves like a system privilege, but is boxed into a single named schema instead of applying database-wide.

GRANT SELECT ANY TABLE ON SCHEMA hr TO bob;

This grants bob SELECT on every current and future table and view in the HR schema specifically, and nothing outside it. That fills a real gap between two less convenient alternatives: granting SELECT on every table individually, which needs updating every time a new table appears, and granting SELECT ANY TABLE database-wide, which reaches far past the one schema bob actually needs. Granting a schema privilege on a schema you do not own requires GRANT ANY SCHEMA PRIVILEGE, or the broader GRANT ANY PRIVILEGE. Delegation uses WITH ADMIN OPTION, the same clause used for ordinary system privileges, not WITH GRANT OPTION. Schema-level grants are recorded in DBA_SCHEMA_PRIVS, which uses SCHEMA as its schema-identifying column name rather than OWNER.

[1]Schema objects: Schema objects are logical data storage structures. Schema objects do not have a one-to-one correspondence to physical files on disk that store their information.