Data Control Language

 Data Control Language (DCL)



🔹 Step 1: Login as Admin (root)

Open MySQL command line and login as root (or another admin account):

mysql -u root -p

Enter your root password.


🔹 Step 2: Create Database and Table

CREATE DATABASE UniversityDB;
USE UniversityDB;

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Marks INT
);

INSERT INTO Students VALUES (1, 'Amit', 85),
                            (2, 'Raj', 70),
                            (3, 'Neha', 92),
                            (4, 'Sneha', 60);

🔹 Step 3: Create Users
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'pass2';
CREATE USER 'user3'@'localhost' IDENTIFIED BY 'pass3';


'localhost' means these users can log in only from the same computer where MySQL is running.

If you want them to connect from anywhere, use '%' instead of 'localhost'.


🔹 Step 4: Grant Privileges
  • user1 → read-only
GRANT SELECT ON UniversityDB.Students TO 'user1'@'localhost';
  • user2 → insert-only
GRANT INSERT ON UniversityDB.Students TO 'user2'@'localhost';

  • user3 → full access
GRANT SELECT, INSERT, UPDATE, DELETE ON UniversityDB.Students TO 'user3'@'localhost';
  • If you want user3 to also be able to grant privileges to others:
GRANT SELECT ON UniversityDB.Students TO 'user3'@'localhost' WITH GRANT OPTION;

🔹 Step 5: Apply Privileges
FLUSH PRIVILEGES;

🔹 Step 6: Test Users
Exit root session:
EXIT;
Now log in as each user:
Login as user1:
mysql -u user1 -p
Password: pass1
Try:
USE UniversityDB;
SELECT * FROM Students;   --  Works
INSERT INTO Students VALUES (5,'Kiran',77);  --  Error


Login as user2:
mysql -u user2 -p
Password: pass2
Try:
USE UniversityDB;
INSERT INTO Students VALUES (6,'Meena',88);  --  Works
SELECT * FROM Students;  --  Error



Login as user3:
mysql -u user3 -p
Password: pass3
Try:
USE UniversityDB;
UPDATE Students SET Marks=95 WHERE StudentID=2;  --  Works
DELETE FROM Students WHERE StudentID=1;          --  Works (unless revoked)




🔹 Step 7: Revoke Example
If you want to take away insert permission from user2:
REVOKE INSERT ON UniversityDB.Students FROM 'user2'@'localhost';
FLUSH PRIVILEGES;
Now user2 cannot insert anymore.





Comments

Post a Comment

Popular posts from this blog

Checkpoint

Image
  What is a Checkpoint in DBMS? A checkpoint in a Database Management System (DBMS) is a mechanism used to minimize the amount of work needed for recovery after a system crash. It essentially acts like a “save point” — the system writes all modified data (dirty pages) from the buffer (RAM) to the disk and records a checkpoint in the log file . Purpose of Checkpoint Reduce recovery time — only redo or undo transactions after the last checkpoint. Ensure consistency between database and log files. Flush dirty pages (modified data not yet written to disk). Mark a safe state from which the system can recover. How It Works  When a checkpoint occurs: All committed transactions before the checkpoint are written to disk . The checkpoint record is written to the log file . Transactions after the checkpoint may still be in progress (these are handled by recovery if crash occurs). During recovery: Start from the last checkpoint , not from the b...
Read more

SUB Queries

 SUB Queries in SQL Table 1:  Students student_id name age department 1 Asha 19 Computer 2 Ravi 20 Mechanical 3 Priya 21 Computer 4 Suresh 20 Civil 5 Meena 19 Computer Table 2: Marks mark_id student_id subject marks 1 1 DBMS 85 2 1 Networks 78 3 2 DBMS 69 4 2 Networks 75 5 3 DBMS 92 6 3 Networks 88 7 4 DBMS 55 8 4 Networks 60 9 5 DBMS 70 10 5 Networks 72 CREATE TABLE Students (     student_id INT PRIMARY KEY,     name VARCHAR(50) NOT NULL,     age INT,     department VARCHAR(50) ); CREATE TABLE Marks (     mark_id INT PRIMARY KEY,     student_id INT,     subject VARCHAR(50),     marks INT,     FOREIGN KEY (student_id) REFERENCES Students(student_id) ); INSERT INTO Students (student_id, name, age, department) VALUES (1, 'Asha', 19, 'Computer'), (2, 'Ravi', 20, 'Mechanical'), (3, 'P...
Read more