# Mysql Database User Role

o create a **database** and a **role** in MySQL where the role can view and edit the database but **cannot delete** any data, follow these steps:

### 1\. **Create a New Database**

First, log in to MySQL with an admin user (like `root`) and create the new database.

```python
CREATE DATABASE your_database_name;
```

### 2\. **Create a New User**

Next, create a new user (role). This user will have specific permissions later.

```python
CREATE USER 'thirdy'@'localhost' IDENTIFIED BY 'Test@123456';
```

* Replace `'your_username'` with the desired username.
    
* Replace `'your_password'` with a strong password.
    

### 3\. **Grant Permissions to the User**

Now, give the user permission to **view and edit** the database, but restrict **delete** permissions.

```python
GRANT SELECT, CREATE, INSERT, UPDATE, REFERENCES ON your_database_name.* TO 'thirdy'@'localhost';
```

This grants:

* **SELECT**: The ability to view data.
    
* **INSERT**: The ability to add data.
    
* **UPDATE**: The ability to modify existing data.
    

The user **cannot delete** any data because the `DELETE` privilege is not given.

### 4\. **Apply Changes**

After granting the permissions, run the following command to apply the changes:

```python
FLUSH PRIVILEGES;
```

### 5\. **Show Grants**

```python
SHOW GRANTS FOR 'thirdy'@'localhost';
```

### 5\. **Test the User Permissions**

You can log in as the new user and verify that they have the appropriate permissions.

```python
mysql -u thirdy -p
```

Once logged in, try running `SELECT`, `INSERT`, and `UPDATE` queries. The user should not be able to delete records.

### Example

```python
SELECT * FROM your_table; -- Should work

INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2'); -- Should work

UPDATE your_table SET column1 = 'new_value' WHERE id = 1; -- Should work

DELETE FROM your_table WHERE id = 1; -- Should NOT work
```