# 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:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
FLUSH PRIVILEGES;
```

#### **References:**

1. [How to Grant Permissions in MySQL](https://www.apono.io) - Apono.io
    
2. [Managing MySQL Roles](https://www.prisma.io) - Prisma.io
    
3. [Granting Privileges to Users in MySQL](https://dev.mysql.com) - MySQL Documentation
    
4. [MySQL CREATE USER and GRANT Commands](https://www.digitalocean.com) - DigitalOcean
    
5. [Using Roles in MySQL](https://www.mysqltutorial.org) - MySQL Tutorial