Alpana A. Borse

Welcome to Database Laboratory

3. Data Control Language (DCL)

1. Grant Privileges to a User

GRANT SELECT ON Employee TO user_name;

-- Output: "Grant succeeded."

GRANT INSERT, UPDATE ON Department TO user_name;

-- Output: "Grant succeeded."

GRANT DELETE ON Employee TO user_name;

-- Output: "Grant succeeded."

2. Grant All Privileges to a User on Specific Tables

GRANT ALL PRIVILEGES ON Employee TO user_name;

-- Output: "Grant succeeded."

GRANT ALL PRIVILEGES ON Department TO user_name;

-- Output: "Grant succeeded."

3. Grant Privileges to a Role and Assign Role to a User

CREATE ROLE manager_role;

-- Output: "Role created."

GRANT SELECT, INSERT, UPDATE ON Employee TO manager_role;

-- Output: "Grant succeeded."

GRANT SELECT ON Department TO manager_role;

-- Output: "Grant succeeded."

GRANT manager_role TO user_name;

-- Output: "Grant succeeded."

4. Revoke Specific Privileges from a User

REVOKE INSERT ON Employee FROM user_name;

-- Output: "Revoke succeeded."

REVOKE UPDATE ON Department FROM user_name;

-- Output: "Revoke succeeded."

5. Revoke All Privileges from a User on Specific Tables

REVOKE ALL PRIVILEGES ON Employee FROM user_name;

-- Output: "Revoke succeeded."

REVOKE ALL PRIVILEGES ON Department FROM user_name;

-- Output: "Revoke succeeded."

6. Grant Privileges to Multiple Users

GRANT SELECT ON Employee TO user1, user2;

-- Output: "Grant succeeded."

GRANT INSERT, UPDATE ON Department TO user1, user2;

-- Output: "Grant succeeded."

7. Revoke Privileges from Multiple Users

REVOKE DELETE ON Employee FROM user1, user2;

-- Output: "Revoke succeeded."

REVOKE SELECT ON Department FROM user1, user2;

-- Output: "Revoke succeeded."

8. Grant Privileges with the Option to Grant to Others

GRANT SELECT ON Employee TO user_name WITH GRANT OPTION;

-- Output: "Grant succeeded."

9. Revoke All Privileges Including the Ability to Grant to Others

REVOKE ALL PRIVILEGES ON Employee FROM user_name CASCADE;

-- Output: "Revoke succeeded."

Example Users:

user1 could be something like john_doe.
user2 could be something like jane_smith.

Steps to Select or Create Users:

Check Existing Users: You can view existing users in the database using a query or command specific to your DBMS. For example:

SELECT User FROM mysql.user;

Create New Users: If user1 and user2 don’t exist, you can create them. Here are examples for different DBMS:

CREATE USER 'john_doe'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'jane_smith'@'localhost' IDENTIFIED BY 'password';

3. Use Users in DCL Commands: Once the users are selected or created, you can replace user1 and user2 with the actual usernames in your DCL commands.

GRANT SELECT ON Employee TO john_doe;

GRANT INSERT, UPDATE ON Department TO jane_smith;

Example Using Specific Users:

GRANT SELECT ON Employee TO john_doe;

GRANT INSERT, UPDATE ON Department TO jane_smith;

Page updated

Google Sites

Report abuse

3. Data Control Language (DCL)

1. Grant Privileges to a User

2. Grant All Privileges to a User on Specific Tables

3. Grant Privileges to a Role and Assign Role to a User

4. Revoke Specific Privileges from a User

5. Revoke All Privileges from a User on Specific Tables

6. Grant Privileges to Multiple Users

7. Revoke Privileges from Multiple Users

8. Grant Privileges with the Option to Grant to Others

9. Revoke All Privileges Including the Ability to Grant to Others

Example Users:

Steps to Select or Create Users:

SELECT User FROM mysql.user;

Create New Users: If user1 and user2 don’t exist, you can create them. Here are examples for different DBMS:

Example Using Specific Users: