Managing User Roles: System Privileges and Object Privileges

In the previous module, you learned how to create users and grant the CREATE SESSION privilege so they could connect to the database. CREATE SESSION is an example of a system privilege, one of two fundamentally different categories of privilege in Oracle, and this module is about understanding both categories well enough to grant, revoke, and audit them with confidence rather than by habit.

When you have finished this module, you will be able to:

1. Explain the difference between system privileges and object privileges
2. Define the different types of system privileges
3. Grant and revoke privileges using the GRANT and REVOKE commands
4. Grant system privileges to all users
5. Allow other users to manage system privileges
6. Query the data dictionary for information about system privileges

A system privilege authorizes an action that affects the database at large, or that spans multiple schemas: creating a session, creating a table anywhere in the database, creating a user, and hundreds of other database-wide operations each have their own system privilege. An object privilege, by contrast, authorizes a specific action on one specific object owned by someone else: SELECT, INSERT, UPDATE, and DELETE on a particular table are object privileges, scoped narrowly to that one table rather than to the database as a whole.

The practical difference shows up immediately in how each is granted. CREATE SESSION, a system privilege, is granted once and applies everywhere: GRANT CREATE SESSION TO myuser;. SELECT on a specific table, an object privilege, is granted per object: GRANT SELECT ON hr.employees TO myuser; grants access to exactly that table and nothing else. Understanding which category a given privilege belongs to is the first step toward granting access deliberately rather than too broadly or too narrowly.

The DBA role carries every system privilege in the database, unlimited space quotas on every tablespace, and the ability to grant any of that to other users in turn. Worth distinguishing precisely, since the same word does double duty here: a DBA, capitalized casually or not, usually refers to the person doing the work of database administration, while the DBA role refers specifically to this one predefined bundle of privileges that person typically holds. A person can be a working DBA without holding the DBA role directly, and a role holder isn't necessarily the person responsible for the system; keeping the two senses of the word separate avoids real confusion later in this module.

Some privileges bundled into the DBA role exist purely for administrative work and are never needed by an ordinary application user; this module spends little time on those. Others, covered in the lessons ahead, are used regularly by both DBAs and non-DBA users alike, and those are worth understanding in real depth.

Confirmed directly in Oracle's own privilege-tracing tools: the DBA role's grant path includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE, the roles providing the privileges needed for full database export and import respectively. Worth knowing alongside these two: current Oracle releases also provide DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE, the modern, Data Pump-specific counterparts. If your export and import work goes through expdp and impdp rather than the older exp and imp utilities, these Data Pump-specific roles are generally the more relevant pair to reach for.

Later lessons in this module cover GRANT and REVOKE in real depth for both categories of privilege, including a distinction worth previewing now because it genuinely surprises people the first time they encounter it: system privileges and object privileges behave differently when revoked. Revoking a system privilege or role from a user who had passed it along to others using WITH ADMIN OPTION does not cascade; those downstream grants survive and must be revoked separately. Revoking an object privilege that was passed along a WITH GRANT OPTION chain works the opposite way: the revoke does cascade, automatically removing the privilege from everyone further down that specific chain, even users the original revoker never directly granted anything to. Getting this backward in either direction is an easy mistake to make, and a later lesson works through it with worked examples.

That same lesson also covers two object-privilege-specific REVOKE clauses worth knowing exist even before you see them in full: CASCADE CONSTRAINTS, which drops any referential integrity constraints a user built using a REFERENCES privilege you are revoking, and FORCE, which is required to revoke EXECUTE on a user-defined type that other tables or types depend on, and which marks every dependent object invalid until the privilege is granted again.

The lessons ahead also cover granting a system privilege to every user at once via PUBLIC, delegating privilege management to other users safely, and querying the data dictionary views that let you verify exactly what any user or role can actually do, rather than guessing from memory.