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.

CREATE DATABASE your_database_name;

2. Create a New User

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

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.

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:

FLUSH PRIVILEGES;

5. Show Grants

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.

mysql -u thirdy -p

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

Example

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