Snippet Name: Create_MySQL_User_Privileges

Tags:

Created Date: 2025-06-16 13:12:45
Last Modified Date: 2025-05-31 10:32:17

Dependencies:

DBA Notes:

Sample Output:

Title: Create MySQL User Privileges
Author: user
help me create a new user named "_ rapid7_mysql", that ahs access to select all schemas as well as see the definitions of all stored procedures

Author: ChatGPT
To create a MySQL user named `_rapid7_mysql` that can:

- Select from all tables in all schemas (databases), and  
- View stored procedure **definitions**

???you'll need to grant it the following privileges on **all existing and future** databases:

### ??? Step-by-step MySQL Commands:

```sql
-- 1. Create the user (adjust host as needed)
CREATE USER '_rapid7_mysql'@'%' IDENTIFIED BY 'your_secure_password';

-- 2. Grant SELECT on all databases and tables
GRANT SELECT ON *.* TO '_rapid7_mysql'@'%';

-- 3. Grant privilege to see stored procedure definitions
GRANT SHOW VIEW, EXECUTE ON *.* TO '_rapid7_mysql'@'%';

-- 4. Apply privileges
FLUSH PRIVILEGES;
```

### ???? Notes:
- The `%` host allows access from **any** IP; you can restrict it to a specific host/IP (e.g., `'localhost'` or `'10.0.0.5'`) if needed.
- `SHOW VIEW` allows seeing view definitions, which is useful when exploring schema metadata.
- `EXECUTE` is required to see stored procedure names and definitions in `INFORMATION_SCHEMA.ROUTINES`.

If you want to **see the full source code** of stored procedures (not just metadata), the user needs the `SELECT` privilege on the `mysql.proc` table in **MySQL 5.x**, but this was deprecated in **MySQL 8.0**. In MySQL 8.0+, procedure code is stored in the `mysql.routines` table and managed via the `EXECUTE` and `SHOW ROUTINE` privileges.

Would you like help adapting this for a MySQL 8.0 secure configuration or a restricted host/IP?

Author: user
Similar to SQL server,Can I create role called "ReadOnlyAdmin", and assign those priviledges, adn then assign the role to that user?

Author: ChatGPT
Yes, **MySQL 8.0+** supports **roles**, similar to SQL Server's database roles. You can absolutely:

1. Create a role called `ReadOnlyAdmin`
2. Assign that role all necessary privileges
3. Grant the role to a user like `_rapid7_mysql`
4. Set it as the user's default role

---

### ??? Example: Creating a `ReadOnlyAdmin` Role in MySQL 8.0+

```sql
-- 1. Create the role
CREATE ROLE 'ReadOnlyAdmin';

-- 2. Grant privileges to the role
GRANT SELECT, SHOW VIEW, EXECUTE ON *.* TO 'ReadOnlyAdmin';

-- 3. Create the user
CREATE USER '_rapid7_mysql'@'%' IDENTIFIED BY 'your_secure_password';

-- 4. Grant the role to the user
GRANT 'ReadOnlyAdmin' TO '_rapid7_mysql'@'%';

-- 5. Make the role active by default for the user
SET DEFAULT ROLE 'ReadOnlyAdmin' TO '_rapid7_mysql'@'%';
```

---

### ???? Notes:
- All privileges granted to the role will be inherited by any user it???s assigned to.
- `SHOW VIEW` helps with view definitions.
- `EXECUTE` allows access to procedure metadata and is required for `INFORMATION_SCHEMA.ROUTINES`.
- Use `SET DEFAULT ROLE` to make the role automatically apply on login.

This approach is **cleaner** and **more maintainable** than granting individual privileges to each user.

Would you like an audit query to check which users currently have this role assigned?