Managing Users and Roles in MySQL

In MySQL, you can easily create users, define roles, and assign privileges to control access to specific databases.

Here's a guide on how to create a user, set up a role, and grant permissions in MySQL, for example with the role student_api and the user thirdy, with access restricted to localhost.

1. Creating a User

To add a new user in MySQL, use the CREATE USER command. In your scenario, to create the user thirdy who can only connect from localhost, you would use the following SQL command:

CREATE USER 'thirdy'@'localhost' IDENTIFIED BY 'hard@password123';

This command creates a user named thirdy who can only connect from the local machine, with a specified password for security.

2. Creating a Role

MySQL allows the creation of roles, which are collections of privileges. Roles make managing permissions easier, especially when several users need similar access levels. To create a role named student_api, you would use this SQL statement:

CREATE ROLE 'student_api';

This command defines a new role that you can later assign to users and give specific privileges.

3. Granting Privileges to the Role

Next, assign the necessary privileges to the student_api role. In your case, you want this role to have SELECT, INSERT, and UPDATE privileges on a specific database. Here's how you can grant these permissions:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'student_api';

Replace database_name with the actual name of your database. This grants the role permission to perform the specified actions on all tables in the database.

4. Assigning the Role to a User

Once the role has been created and the privileges assigned, you can grant the student_api role to the thirdy user with the following command:

GRANT 'student_api' TO 'thirdy'@'localhost';

This assigns the student_api role to the user thirdy, meaning they now have the SELECT, INSERT, and UPDATE privileges on the specified database.

5. Enabling the Role

Finally, to ensure that the student_api role is enabled for the thirdy user by default, you can run this command:

SET DEFAULT ROLE 'student_api' FOR 'thirdy'@'localhost';

This ensures that whenever thirdy logs in, the role student_api is automatically active, giving the user access to the necessary privileges.

5. Flushing Privileges

To ensure that the changes take effect immediately, you should run the following command:

FLUSH PRIVILEGES;

References:

1. How to Grant Permissions in MySQL - Apono.io

2. Managing MySQL Roles - Prisma.io

3. Granting Privileges to Users in MySQL - MySQL Documentation

4. MySQL CREATE USER and GRANT Commands - DigitalOcean

5. Using Roles in MySQL - MySQL Tutorial