week 8

8.3 SQL Data Control Language (DCL)

MySQL uses GRANT and REVOKE to manage user privileges:

 

-- Create a database user for a bank teller

CREATE USER 'teller_user'@'localhost' IDENTIFIED BY 'SecureP@ss123';

 

-- Grant only SELECT on customer table (tellers can view but not modify)

GRANT SELECT ON bank_db.CUSTOMER TO 'teller_user'@'localhost';

 

-- Grant SELECT and INSERT on TRANSACTION table

GRANT SELECT, INSERT ON bank_db.TRANSACTION TO 'teller_user'@'localhost';

 

-- Grant full privileges to the DBA

GRANT ALL PRIVILEGES ON bank_db.* TO 'dba_user'@'localhost' WITH GRANT OPTION;

 

-- Revoke UPDATE on CUSTOMER from teller (they had it mistakenly)

REVOKE UPDATE ON bank_db.CUSTOMER FROM 'teller_user'@'localhost';

 

-- Show what privileges a user has

SHOW GRANTS FOR 'teller_user'@'localhost';

 

PRIVILEGE TYPES in MySQL:

Privilege	What It Allows
SELECT	Read data from tables
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
CREATE	Create new tables or databases
DROP	Delete tables or databases
INDEX	Create/drop indexes
ALL PRIVILEGES	All of the above