LU09.S02 - SQL- DCL: CREATE USER

Create the User: Create a user named restrictedUser with the password SafePassword123 using the mysql_native_password plugin.

CREATE USER 'restrictedUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SafePassword123';

Overview of the current privileges: Display all users.

SELECT user, host, plugin FROM mysql.user;

The result set should look like:

Grant Privileges Without Table Management: Grant the user restrictedUser the ability to read and write data but not to create, alter, or drop tables. Use the following commands to give only the required privileges.

GRANT SELECT, INSERT, UPDATE, DELETE ON myDatabase.* TO 'restrictedUser'@'localhost';

Revoke Privileges: To be certain that nothing unintended can happen revoke the CREATE, ALTER, and DROP privileges explicitly.

REVOKE CREATE, ALTER, DROP ON myDatabase.* FROM 'restrictedUser'@'localhost';

View the User’s Privileges: Check the privileges to ensure that the user cannot manage tables.

SHOW GRANTS FOR 'restrictedUser'@'localhost';

Test the User’s Access: Establish a new console connection to the database by using restrictedUser + password.

Finally, try to perform a CREATE TABLE or DROP TABLE operation. The attempt should result in a permission error als displayed in the image below.

Delete the User (optional): After testing, you can delete the user if they are no longer needed.

Volkan Demir