1. What is DCL?
- DCL (Data Control Language): Commands used to manage permissions and access control for database users.
- Key Commands:
GRANT
REVOKE
2. GRANT Command
- Purpose: Provides permissions to users to perform specific operations on database objects.
- Syntax:
GRANT privilege1, privilege2, ... ON object_name TO user1, user2, ... [WITH GRANT OPTION];
- Examples:
- Grant SELECT permission on the
Employees
table to a user:GRANT SELECT ON Employees TO User1;
- Grant multiple permissions:
GRANT SELECT, INSERT ON Employees TO User2;
- Grant permission with the ability to further delegate:
GRANT SELECT ON Employees TO User3 WITH GRANT OPTION;
- Grant SELECT permission on the
- Key Points:
- Privileges include:
SELECT
,INSERT
,UPDATE
,DELETE
,EXECUTE
, etc. - WITH GRANT OPTION: Allows the recipient to grant the same permissions to other users.
- Privileges include:
3. REVOKE Command
- Purpose: Removes permissions from users.
- Syntax:
REVOKE privilege1, privilege2, ... ON object_name FROM user1, user2, ...;
- Examples:
- Revoke SELECT permission from a user:
REVOKE SELECT ON Employees FROM User1;
- Revoke all permissions on a table:
REVOKE ALL PRIVILEGES ON Employees FROM User2;
- Revoke SELECT permission from a user:
- Key Points:
- Removes previously granted permissions.
- Does not affect the data or structure of the database objects.
Comparison Table
Command | Purpose | Effect |
---|---|---|
GRANT | Provides permissions to users | Allows users to perform specific operations. |
REVOKE | Removes permissions from users | Denies previously granted operations. |
Common Privileges
Privilege | Description |
---|---|
SELECT | Allows reading data from a table/view. |
INSERT | Allows inserting new rows into a table. |
UPDATE | Allows modifying existing data in a table. |
DELETE | Allows removing rows from a table. |
EXECUTE | Allows running stored procedures or functions. |
Exam Tips
- Memorize the syntax for
GRANT
andREVOKE
commands. - Understand the effect of
WITH GRANT OPTION
inGRANT
. - Know the privileges (
SELECT
,INSERT
,UPDATE
, etc.) and when they are used. - Remember:
GRANT
adds permissions.REVOKE
removes permissions.