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:
How to Grant Permissions in MySQL - Apono.io
Managing MySQL Roles - Prisma.io
Granting Privileges to Users in MySQL - MySQL Documentation
MySQL CREATE USER and GRANT Commands - DigitalOcean
Using Roles in MySQL - MySQL Tutorial
