Providing Security using GRANT and REVOKE
AIM: To learn GRANT and REVOKE commands to restrict privileges.
(1) GRANT Statement
Grant privileges to a user (or to a user role)
Syntax:
Syntax:
Grant System-wide Privs:
GRANT system_priv(s) TO grantee
[IDENTIFIED BY password] [WITH ADMIN OPTION]
GRANT role TO grantee
[IDENTIFIED BY password] [WITH ADMIN OPTION]
GRANT ALL PRIVILEGES TO grantee
[IDENTIFIED BY password] [WITH ADMIN OPTION]
Grant privs on specific objects:
GRANT object_priv [(column, column,...)]
ON [schema.]object
TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
GRANT ALL PRIVILEGES [(column, column,...)]
ON [schema.]object
TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
GRANT object_priv [(column, column,...)]
ON DIRECTORY directory_name
TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
GRANT object_priv [(column, column,...)]
ON JAVA [RE]SOURCE [schema.]object
TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
grantee:
user
role
PUBLIC
system_privs:
CREATE SESSION - Allows user to connect to the database
UNLIMITED TABLESPACE - Use an unlimited amount of any tablespace.
SELECT ANY TABLE - Query tables, views, or mviews in any schema
UPDATE ANY TABLE - Update rows in tables and views in any schema
INSERT ANY TABLE - Insert rows into tables and views in any schema
Also System Admin rights to CREATE, ALTER or DROP:
cluster, context, database, link, dimension, directory, index,
materialized view, operator, outline, procedure, profile, role,
rollback segment, sequence, session, synonym, table, tablespace,
trigger, type, user, view. (full list of system privs)
object_privs:
SELECT, UPDATE, INSERT, DELETE, ALTER, DEBUG, EXECUTE, INDEX, REFERENCES
roles:
SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE
SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE
AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - advanced queuing
SNMPAGENT - Enterprise Manager/Intelligent Agent.
RECOVERY_CATALOG_OWNER - rman
HS_ADMIN_ROLE - heterogeneous services
plus any user defined roles you have available
Notes:
Several Object_Privs can be assigned in a single GRANT statement
e.g.
GRANT SELECT (empno), UPDATE (sal) ON scott.emp TO emma
e.g.
GRANT SELECT (empno), UPDATE (sal) ON scott.emp TO emma
WITH HIERARCHY OPTION will grant the object privilege on all subobjects, including any created after the GRANT statement is issued.
WITH GRANT OPTION will enable the grantee to grant those object privileges to other users and roles.
"GRANT ALL PRIVILEGES..." may also be written as "GRANT ALL..."
"GRANT ALL PRIVILEGES..." may also be written as "GRANT ALL..."
(ii) REVOKE Statement
Revoke privileges from users or roles.
Syntax:
Syntax:
Roles:
REVOKE role FROM {user, | role, |PUBLIC}
System Privs:
REVOKE system_priv(s) FROM {user, | role, |PUBLIC}
REVOKE ALL FROM {user, | role, |PUBLIC}
Object Privs:
REVOKE object_priv [(column1, column2..)] ON [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON DIRECTORY directory_name
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON JAVA [RE]SOURCE [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
key:
object_privs
ALTER, DELETE, EXECUTE, INDEX, INSERT,
REFERENCES, SELECT, UPDATE, ALL PRIVILEGES
system_privs
ALTER ANY INDEX, BECOME USER, CREATE TABLE, DROP ANY VIEW
RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE
plus too many others to list here
roles
Standard Oracle roles -
SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE
plus any user defined roles you have available
FORCE, will revoke all privileges from a user-defined-type and mark it's dependent objects INVALID.
The roles CONNECT, RESOURCE and DBA are now deprecated (supported only for backwards compatibility) unless you are still running Oracle 6.0
Error ORA-01927 "cannot REVOKE privileges you did not grant" - This usually means you tried revoking permission from the table owner, e.g.
Oracle will not allow REVOKE select on USER1.Table1 from USER1 Owners of objects ALWAYS have full permissions on those objects. This is one reason it makes sense to place tables in one schema and the packaged prodecures used to access those tables in a separate schema.
Oracle will not allow REVOKE select on USER1.Table1 from USER1 Owners of objects ALWAYS have full permissions on those objects. This is one reason it makes sense to place tables in one schema and the packaged prodecures used to access those tables in a separate schema.