|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Users, roles and privileges in Oracle
A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges. A privileges can be assigned to a user or a role
The set of privileges is fixed, that is, there is no SQL statement like create privilege xyz...
There are quite a few system privileges: in Oracle 9.2, we count 157 of them, and 10g has even 173. Those can be displayed with
select name from system_privilege_map
Executing this statement, we find privileges like create session, drop user, alter database, see system privileges.
System privileges can be audited.
Arguably, the most important system privileges are:
privileges can be assigned to the following types of database objects:
For a user to be able to access an object in another user's schema, he needs the according object privilege.
If a privilege is granted to the special role public, this privilege can be executed by all other users. However, sysdba cannot be granted to public.
to be finished ...
Along with the installation, more exactly with the creation of an oracle database, Oracle creates predefined roles. These are:
Assigning privileges to users and roles
A privilege can be assigned to a user with the grant sql statment. On the other hand, revoke allows to take away such privileges from users and roles.
Oracle stores the granted privileges in its data dictionary.
Displaying the relationship between users, roles and privileges
Use this script to recursively list users, granted roles and privileges.
Thanks to Josette Hammer for notifying me of a typo and to Muhammad Imran for correcting an error on this page.