Experiment no 5

✅ Additional Software Required

Database: MySQL Community Server
SQL Client Tool: MySQL Workbench
Operating System: Compatible with Windows, Linux, macOS
Optional Tools: VirtualBox, Docker, or network tools for multi-user simulation (if needed)

✅ IX. Additional Resources Used

Official MySQL Documentation: https://dev.mysql.com/doc
Instructor's class notes and materials
Online SQL resources: W3Schools, GeeksforGeeks, TutorialsPoint
MySQL Workbench Help and built-in documentation
Community forums like Stack Overflow

✅ X. Result (Output of the Procedure)

The DCL (Data Control Language) commands were successfully executed.
New users (Jay, John) were created using CREATE USER.
Privileges were granted using GRANT and revoked using REVOKE.
Roles were used to simplify permission management.
Proper access control was established as expected.

✅ XI. Practical Related Questions – Answers

1. What is the use of WITH GRANT OPTION in the GRANT command?

The WITH GRANT OPTION allows a user not only to use the privileges they receive but also to pass those privileges to other users. This is useful for delegating access management.

2. Output/Explanation of SQL Commands

-- Create user Jay
CREATE USER 'Jay'@'localhost' IDENTIFIED BY 'jay123';

-- Grant system privileges
GRANT CREATE ON *.* TO 'Jay'@'localhost';
GRANT CREATE VIEW ON *.* TO 'Jay'@'localhost';

-- Grant object privileges
GRANT SELECT, INSERT, UPDATE ON practice_db.EMP TO 'Jay'@'localhost';
GRANT SELECT, UPDATE (deptno, empno) ON practice_db.DEPT TO 'Jay'@'localhost';

-- Change user password
ALTER USER 'Jay'@'localhost' IDENTIFIED BY 'admin123';

-- Revoke system privileges
REVOKE CREATE, CREATE VIEW ON *.* FROM 'Jay'@'localhost';

-- Revoke object privileges
REVOKE SELECT, INSERT, UPDATE ON practice_db.EMP FROM 'Jay'@'localhost';

-- Create role
CREATE ROLE 'emp_pvr';

-- Assign system privileges to role
GRANT CREATE, CREATE VIEW ON *.* TO 'emp_pvr';

-- Grant role to users
GRANT 'emp_pvr' TO 'Jay'@'localhost', 'John'@'localhost';

✅ Final Summary:

Users were created and granted privileges.
Some privileges were revoked to simulate real-world access control.
A role was created (emp_pvr) and assigned to users.
Roles helped in grouping privileges and simplifying assignments.

✅ XII. Exercises (With MySQL-Compatible Queries)

1. Create the user Jay:

CREATE USER 'Jay'@'localhost' IDENTIFIED BY 'jay123';

2. Grant SELECT, INSERT, DELETE privileges:

GRANT SELECT, INSERT, DELETE ON practice_db.EMP TO 'Jay'@'localhost';
GRANT SELECT, INSERT, DELETE ON practice_db.DEPT TO 'Jay'@'localhost';

3. Grant UPDATE privilege on specific columns of EMP:

GRANT UPDATE (empno, salary) ON practice_db.EMP TO 'Jay'@'localhost';

4. Revoke all above privileges:

REVOKE SELECT, INSERT, DELETE, UPDATE ON practice_db.EMP FROM 'Jay'@'localhost';
REVOKE SELECT, INSERT, DELETE ON practice_db.DEPT FROM 'Jay'@'localhost';

5. Create role dept_pvr:

CREATE ROLE 'dept_pvr';

6. Assign system privileges to the role:

GRANT CREATE, CREATE VIEW ON *.* TO 'dept_pvr';

7. Assign the role to users:

GRANT 'dept_pvr' TO 'Jay'@'localhost';
GRANT 'dept_pvr' TO 'John'@'localhost';

8. Grant object privileges to the role:

GRANT SELECT, INSERT, DELETE ON practice_db.EMP TO 'dept_pvr';
GRANT SELECT, INSERT, DELETE ON practice_db.DEPT TO 'dept_pvr';

9. Assign object privileges to users via role:

No extra command is needed once the role is granted. Just ensure the user has the role activated.

✅ Optional for session-level role activation (if needed):

SET ROLE 'dept_pvr';

✅ Schema for DCL Practical (User Privileges, Roles, Grant/Revoke)

1. Tables to be created:

-- Create EMP table
CREATE TABLE EMP (
    empno INT PRIMARY KEY,
    empname VARCHAR(50),
    salary DECIMAL(10, 2),
    phno VARCHAR(15)
);

-- Insert sample data into EMP
INSERT INTO EMP (empno, empname, salary, phno) VALUES (101, 'Rahul Sharma', 45000.00, '9876543210');
INSERT INTO EMP (empno, empname, salary, phno) VALUES (102, 'Neha Gupta', 52000.00, '9123456789');
INSERT INTO EMP (empno, empname, salary, phno) VALUES (103, 'Amit Verma', 47000.00, '9988776655');
INSERT INTO EMP (empno, empname, salary, phno) VALUES (104, 'Priya Singh', 55000.00, '9012345678');
INSERT INTO EMP (empno, empname, salary, phno) VALUES (105, 'Sanjay Kumar', 48000.00, '9876501234');

-- Create DEPT table with foreign key referencing EMP
CREATE TABLE DEPT (
    deptno INT PRIMARY KEY,
    empno INT,
    deptname VARCHAR(50),
    location VARCHAR(50),
    jobtype VARCHAR(30),
    FOREIGN KEY (empno) REFERENCES EMP(empno)
);

-- Insert sample data into DEPT
INSERT INTO DEPT (deptno, empno, deptname, location, jobtype) VALUES (201, 101, 'Finance', 'Mumbai', 'Analyst');
INSERT INTO DEPT (deptno, empno, deptname, location, jobtype) VALUES (202, 102, 'HR', 'Delhi', 'Manager');
INSERT INTO DEPT (deptno, empno, deptname, location, jobtype) VALUES (203, 103, 'IT', 'Bangalore', 'Developer');
INSERT INTO DEPT (deptno, empno, deptname, location, jobtype) VALUES (204, 104, 'Marketing', 'Chennai', 'Executive');
INSERT INTO DEPT (deptno, empno, deptname, location, jobtype) VALUES (205, 105, 'Operations', 'Hyderabad', 'Supervisor');

2. Users to be created (by DBA/Admin):

DROP USER IF EXISTS 'Jay'@'localhost';
CREATE USER 'Jay'@'localhost' IDENTIFIED BY 'jay123';

Note: Adjust 'localhost' to your actual host or % for any host.

3. Role to be created:

DROP ROLE IF EXISTS 'dept_pvr'; CREATE ROLE 'dept_pvr';

4. System privileges for the role:

-- Grant basic CREATE privilege (includes creating tables) GRANT CREATE ON . TO 'dept_pvr'; -- Grant specific privilege for creating views GRANT CREATE VIEW ON . TO 'dept_pvr';

5. Assign role to users:

GRANT 'dept_pvr' TO 'Jay'@'localhost', 'John'@'localhost';

6. Object privileges on tables

Granting directly to user Jay:

GRANT SELECT, INSERT, DELETE ON expno5.emp TO 'Jay'@'localhost';
GRANT SELECT, INSERT, DELETE ON expno5.dept TO 'Jay'@'localhost';

-- MySQL does not support column-level UPDATE privileges,
-- so grant UPDATE on the entire EMP table:

GRANT UPDATE ON expno5.emp TO 'Jay'@'localhost';

Granting to role `dept_pvr`:

GRANT SELECT, INSERT, DELETE ON expno5.emp TO 'dept_pvr';
GRANT SELECT, INSERT, DELETE ON expno5.dept TO 'dept_pvr';

-- Grant UPDATE privilege on entire EMP table to role:
GRANT UPDATE ON expno5.dept TO 'dept_pvr';

Replace your_database with your actual database name.

7. Revoking privileges from user Jay:

REVOKE SELECT, INSERT, DELETE, UPDATE ON expno5.emp FROM 'Jay'@'localhost';
REVOKE SELECT, INSERT, DELETE ON expno5.dept FROM 'Jay'@'localhost';

Summary Notes:

MySQL does not support column-level privileges (UPDATE(empno, salary)), so grant UPDATE on the full table.
Always specify the database name before table (e.g., your_database.EMP).
Replace 'localhost' with appropriate host or % if users connect remotely.
Make sure you have appropriate privileges to create users, roles, and grant privileges.

💡 Note for Practical Execution

Make sure you're logged in as a user with DBA rights (like admin) to perform CREATE USER, GRANT, and REVOKE operations.
You can check privileges using:
- Oracle: SELECT * FROM USER_TAB_PRIVS;
- MySQL: SHOW GRANTS FOR 'Jay'@'localhost';

📌 Summary of Schema

Component	Name	Description
Table	EMP	Employee details
Table	DEPT	Department details linked to EMP
Users	Jay, John	Target users for GRANT/REVOKE testing
Role	dept_pvr	Role to manage shared privileges

Search This Blog

Technology and Innovation by Atul Kalukhe

Experiment no 5

✅ Additional Software Required

✅ IX. Additional Resources Used

✅ X. Result (Output of the Procedure)

✅ XI. Practical Related Questions – Answers

✅ Final Summary:

✅ XII. Exercises (With MySQL-Compatible Queries)

✅ Schema for DCL Practical (User Privileges, Roles, Grant/Revoke)

1. Tables to be created:

2. Users to be created (by DBA/Admin):

3. Role to be created:

DROP ROLE IF EXISTS 'dept_pvr'; CREATE ROLE 'dept_pvr';

4. System privileges for the role:

-- Grant basic CREATE privilege (includes creating tables) GRANT CREATE ON . TO 'dept_pvr'; -- Grant specific privilege for creating views GRANT CREATE VIEW ON . TO 'dept_pvr';

5. Assign role to users:

6. Object privileges on tables

Granting directly to user Jay:

Granting to role `dept_pvr`:

7. Revoking privileges from user Jay:

💡 Note for Practical Execution

📌 Summary of Schema

Popular posts from this blog

Technology and Innovation by Atul Kalukhe

Experiment no 5

✅ Additional Software Required

✅ IX. Additional Resources Used

✅ X. Result (Output of the Procedure)

✅ XI. Practical Related Questions – Answers

✅ Final Summary:

✅ XII. Exercises (With MySQL-Compatible Queries)

✅ Schema for DCL Practical (User Privileges, Roles, Grant/Revoke)

1. Tables to be created:

2. Users to be created (by DBA/Admin):

3. Role to be created:

DROP ROLE IF EXISTS 'dept_pvr'; CREATE ROLE 'dept_pvr';

4. System privileges for the role:

-- Grant basic CREATE privilege (includes creating tables) GRANT CREATE ON *.* TO 'dept_pvr'; -- Grant specific privilege for creating views GRANT CREATE VIEW ON *.* TO 'dept_pvr';

5. Assign role to users:

6. Object privileges on tables

Granting directly to user Jay:

Granting to role dept_pvr:

7. Revoking privileges from user Jay:

💡 Note for Practical Execution

📌 Summary of Schema

Popular posts from this blog

Technology and Innovation by Atul Kalukhe

-- Grant basic CREATE privilege (includes creating tables) GRANT CREATE ON . TO 'dept_pvr'; -- Grant specific privilege for creating views GRANT CREATE VIEW ON . TO 'dept_pvr';

Granting to role `dept_pvr`: