DCL- (Data Control Language)

 1)   GRANT

This command is used to assign a privilege to the user. You can assign SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER and INDEX privilege to user.

Syntax

GRANT PRIVILEGE ON OBJECT TO USER

Example

To grant single privilege

GRANT SELECT ON EMPLOYEES TO HR

To grant multiple privilege

GRANT SELECT, INSERT, DELETE ON EMPLOYEES TO HR

To grant all privilege

GRANT ALL ON EMPLOYEES TO HR

To grant all privilege to public ( all users )

GRANT ALL ON EMPLOYEES TO PUBLIC

You can give privilege on functions & procedures

Syntax

To grant privilege on functions or procedures

GRANT EXECUTE ON OBJECT TO USER

2)   REVOKE

This command is used to revoke the privileges assigned to the user.

Syntax

REVOKE PRIVILEGE ON OBJECT FROM USER

Example

To revoke single privilege

REVOKE SELECT ON T1 FROM HR

To revoke multiple privilege

REVOKE SELECT, INSERT, DELETE ON T1 FROM HR

To revoke all privilege

REVOKE ALL ON T1 FROM HR

To revoke all privilege to public ( all users )

REVOKE ALL ON EMPLOYEES FROM PUBLIC

Syntax

To revoke privilege on functions or procedures

REVOKE EXECUTE ON OBJECT FROM USER