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.
