There is a need for an access control mechanism implemented in a multi-user environment. You have to be able to control who can access what. Thus Oracle implemented privileges to control the access and roles to encapsulate privileges.
Granting direct access to several users will become a bit clumsy after a while not to mention keeping them aligned. Thus Oracle introduced roles. With roles you can easily manage all privileges – by naming the set of privileges, keeping them organized, …
There are three main categories of privileges:
- System privileges
- Object privileges
- the “Public” role
System privileges
System privileges allow a user to perform an action on objects or sets of objects. Only the user with ADMIN option (or instance admin) can grant this privilege! There 47 system privileges to be used – see the table below for more details.
| Privilege | Description |
|---|---|
| ADMIN | Allows user to do any admin's tasks (bakcups, migration, user creation / deletion, ...) |
| ALTER / CREATE / DROP / FLUSH / LOAD / REFRESH / UNLOAD ANY CACHE GROUP | Allows user to ALTER / CREATE / DROP / FLUSH / LOAD / REFRESH / UNLOAD any cache group |
| ALTER / CREATE / DROP ANY INDEX | Allows a user to ALTER / CREATE / DROP any index |
| ALTER / CREATE / DROP ANY MATERIALIZED VIEW | Allows a user to ALTER / CREATE / DROP any materialized view |
| ALTER / CREATE / DROP / EXECUTE ANY PROCEDURE | Allows a user to ALTER / CREATE / DROP / EXECUTE any procedure, function or package |
| ALTER / CREATE / DROP / SELECT ANY SEQUENCE | Allows a user to ALTER / CREATE / DROP / SELECT any sequence |
| ALTER / CREATE / DELETE / DROP / INSERT / SELECT / UPDATE ANY TABLE | Allows a user to ALTER / CREATE / DELETE / DROP / INSERT / SELECT / UPDATE any table |
| ALTER / CREATE / DROP ANY VIEW | Allows a user to ALTER / CREATE / DROP any view |
| CACHE_MANAGER | Allows a user to do any operation with cache groups |
| CREATE / DROP PUBLIC SYNONYM | Allows a user to CREATE / DROP a public synonym only |
| CREATE SESSION | Allow a user to connect to a database |
| CREATE / DROP ANY SYNONYM | Allows a user to CREATE / DROP any synonym |
| XLA | Allows a user to connect as an XLA reader |
Object privileges
Object privileges allow a user to perform a particular action to a specific object or define access to a particular object. In Oracle, there is always an “object owner” which has the full right (all privileges) to an object created by him. The object owner can grant any privilege to another user (except few that are restricted to owners only; i.e. TRUNCATE). A user with ADMIN option can do the same.
| Privilege | Description |
|---|---|
| DELETE | Applies to: - tables Allows to delete data from a table |
| EXECUTE | Applies to: - package, procedure, function Allows to execute a package, procedure or function |
| FLUSH | Applies to: - cache group Allows to flush a cache group |
| INDEX | Applies to: - tables - views Allows to create an index on a view or table |
| INSERT | Applies to: - tables - synonyms Allows to insert data into a table or synonym |
| LOAD | Applies to: - cache group Allows to load a cache group |
| REFERENCES | Applies to: - tables - materialized views Allows to create a foreign key on a table or materialized view. It implicitly grants SELECT to a parent table! |
| REFRESH | Applies to: - cache group Allows to refresh a cache group |
| SELECT | Applies to: - tables - sequence - view - materialized view - synonym Allows to select data from a table, sequence, view, materialized view or synonym |
| UNLOAD | Applies to: - cache group Allows to unload a cache group |
| UPDATE | Applies to: - tables Allows to update data in a table |
PUBLIC
All users have by default PUBLIC role which allows them to access publicly available objects. This is very handy if you want to grant a privilegeĀ or role to everyone, you do not need to list down all users or roles and grant it to all of them you can grant it to PUBLIC and that’s it š