GRANT/REVOKE examples

check own privileges:
SQL> SELECT * FROM SESSION_PRIVS;

syntax of system privileges:
GRANT [system_privileges | roles] {ANY} <object_type>
TO [user | role | PUBLIC] {WITH GRANT OPTION}

syntax of object privileges:
GRANT [ALL {object_privileges}] ON object 
TO [user | role | PUBLIC] {WITH GRANT OPTION}  

system privileges options:
ALTER, CREATE, DROP, EXECUTE, SELECT

object privileges options:
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL

object types:
CONTEXT, DATABASE LINK, DICTIONARY, INDEX, MATERIALIZED VIEW, PACKAGES, PROCEDURE, PROFILE, ROLE, SESSION, SEQUENCE, SYNONYM, USERS, VIEW, TYPE, TABLE, TABLESPACE, TRIGGER

some of system roles:
CONNECT, RESOURCE, DBA

examples of system privileges:
e.g.
SQL> grant dba to newhr

e.g.
SQL> grant create type to hrnew;

e.g. using ANY syntax
SQL> grant create any table to hrnew;
-> hrnew user is able to create table for any schema/user.

e.g. revoke ANY grant
SQL> revoke create any table from hrnew;

e.g. grant for refreshing materialized view
SQL> grant alter any materialized view to hrnew; 
-> hrnew is able to refresh all materialized views on whole database schemas

examples of object privileges:
e.g.
SQL> grant select on hr.employees to hrnew;

e.g. 
SQL> grant select, update, delete on hr.employees to hrnew;

e.g. 
SQL> grant all on hr.employees to hrnew;

e.g.
SQL> grant all on hr.employees to public;

e.g.
SQL> revoke select on hr.employees from hrnew;

e.g.
SQL> revoke select, update, delete on hr.employees from hrnew;

e.g.
SQL> revoke all on hr.employees from public;

e.g. for "references" option
SQL> conn hr/hr
Connected.
SQL> grant references on employees to hrnew;
Grant succeeded.
SQL> conn hrnew/hrnew
Connected.
SQL> alter table newemployees add foreign key (employee_id) references employees(employee_id);
Table altered.