Oracle privilege

Decide what a user can do via privileges. The database has two types of privileges.

System privilieges control what a user can do in the database. For example, can they create tables, create users, drop tablespaces? These privileges apply mainly to adding or changing structures in the database.

Object privileges control how a user can access the actual data in the database. For example, what data can they see, change, or delete? These privileges apply primariliy to rows in a table or view.

System privileges

The CREATE SESSION privilege gives users access to the database.

WITH ADMIN OPTION is another feature with system privileges. You can grant this system privilege as part of a system privilege to allow the user to grant the privilege to someone else.

If WITH ADMIN OPTION is revoked, all users given that privilege by that person will retain the privileges.

Object privileges

Object privileges control data access and modification.

You can grant only eight object privileges:

SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, EXECUTE

Keep these privilege tidbits in mind:

When you own an object, you automatically have all the privileges on that object. In other words, you don't have to be granted SELECT on you own table.

Object privileges cannot be revoked from the owner of an object.

Whatever schema owns the object ultimately controls that object's privileges.

Without express permission, no one else can manage the object privileges of said object — well, no one except a user who might have the system privilege GRANT ANY OBJECT (usually reserved for DBAs).

Object privilege cannot be revoked by anyone but the person who granted it except for someone with the GRANT ANY OBJECT privilege. Not even the owner can revoke a privilege on her own object unless she was the grantor.

cascading revoke

Role

You can group privileges with database roles for ease of management.