Activity 16: user role in MySQL database
What is a Role in MySQL?
A role is a set of privileges (permissions) that can be assigned to one or more users.
It simplifies user management by grouping privileges into roles, rather than assigning them individually to users.
Steps to Set a User Role in MySQL Database
Step 1: Connect to the MySQL Server
Use the MySQL client or any MySQL management tool (like phpMyAdmin or MySQL Workbench) to log in as a user with administrative privileges (e.g., root).
bashCopy codemysql -u root -p
Enter your root password when prompted.
Step 2: Create a Role
Use the CREATE ROLE command to create a role. A role itself does not have any privileges initially.
SQL Example:
sqlCopy codeCREATE ROLE 'read_only';
CREATE ROLE 'data_entry';
Step 3: Grant Privileges to the Role
Assign specific permissions to the role using the GRANT statement.
Grant Read-Only Privileges:
sqlCopy codeGRANT SELECT ON your_database.* TO 'read_only';
Grant Data Entry Privileges:
sqlCopy codeGRANT SELECT, INSERT, UPDATE ON your_database.* TO 'data_entry';
Step 4: Create a User
Create a user to whom you will assign one or more roles.
SQL Example:
sqlCopy codeCREATE USER 'john_doe'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'jane_doe'@'localhost' IDENTIFIED BY 'password456';
Step 5: Assign Role to a User
Use the GRANT statement to assign a role to a user.
Assign Read-Only Role to User:
sqlCopy codeGRANT 'read_only' TO 'john_doe'@'localhost';
Assign Data Entry Role to User:
sqlCopy codeGRANT 'data_entry' TO 'jane_doe'@'localhost';
Step 6: Activate Role for a User (Optional)
To use the role during a session, the user may need to activate the role explicitly (unless the role is marked as default).
SQL Example:
sqlCopy codeSET ROLE 'read_only';
Step 7: Verify Role and Privileges
You can verify the privileges and roles granted to a user with the following commands:
List Roles Assigned to a User:
sqlCopy codeSHOW GRANTS FOR 'Victor'@'localhost';
List All Roles in the Database:
sqlCopy codeSELECT * FROM mysql.roles_mapping;