LU09.S01 - SQL- DCL: Preparatory Work - Under Construction

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';

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 myAppDB.* TO 'restrictedUser'@'localhost';

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

REVOKE CREATE, ALTER, DROP ON myAppDB.* 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: Connect as restrictedUser and try to perform a CREATE TABLE or DROP TABLE operation. The attempt should result in a permission error.

1. Go to Webstorm and try to connect to the database
2. Then display the systemdate from mysql (select is allowed)
3. Finally, try to create a table (it should fail, because this user has no privileges to create tables)

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

Lösung

Volkan Demir