Manage roles
You can manage account roles using the default stored procedures provided by KakaoCloud MySQL.
Grant permission to role
Stored procedure for granting permissions to a user-created role.
Procedure name
mysql.mnms_grant_right_role
Procedure parameters
| Name | Data type | Description |
|---|---|---|
| p_role | VARCHAR(20) | Name of role to grant permissions to |
| p_right | VARCHAR(8000) | Permissions to grant to the role |
| p_target_db | VARCHAR(250) | Target database for permission |
| p_target_schema | VARCHAR(250) | Target object in the database |
How to use
CALL mysql.mnms_grant_right_role('UserName_dev_role', 'ALTER, CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, UPDATE', 'DBName', 'Object');
Example
Grant permission to role
GRANT option is excluded from the Admin user account. Therefore, permissions can only be granted using system-provided procedures.
Revoke permission from role
Stored procedure for revoking permissions from a user-created role.
Procedure name
mysql.mnms_revoke_right_role
Procedure parameters
| Name | Data type | Description |
|---|---|---|
| p_role | VARCHAR(20) | Name of role to revoke permissions from |
| p_right | VARCHAR(8000) | Permissions to revoke from the role |
| p_target_db | VARCHAR(250) | Target database for revoking permission |
| p_target_schema | VARCHAR(250) | Target object in the database |
How to use
CALL mysql.mnms_revoke_right_role('UserName_dev_role', 'ALTER, CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, UPDATE', 'DBName', 'Object');
Example
- Revoke role permission
GRANT option is excluded from the Admin user account. Therefore, granting and revoking permissions must be performed using system-provided procedures.
Map user to role
Stored procedure for mapping a user to a user-created role.
Procedure name
mysql.mnms_grant_role_user
Procedure parameters
| Name | Data type | Description |
|---|---|---|
| p_role | VARCHAR(20) | Name of the role |
| p_user | VARCHAR(32) | Name of the user account to map to the role |
| p_host | VARCHAR(20) | Host range allowed to access |
How to use
CALL mysql.mnms_grant_role_user('UserName_dev_role', 'UserName_dev', '%');
Example
Map user to role
GRANT option is excluded from the Admin user account. Therefore, granting and revoking permissions must be performed using system-provided procedures.
Unmap user from role
Stored procedure for unmapping a user from a user-created role.
Procedure name
mysql.mnms_revoke_role_user
Procedure parameters
| Name | Data type | Description |
|---|---|---|
| p_role | VARCHAR(20) | Name of the role |
| p_user | VARCHAR(32) | - Name of the user account to unmap - Only lowercase letters and underscores (_) are allowed; length must be between 1 and 32 characters (same as console) |
| p_host | VARCHAR(20) | Host range allowed to access |
How to use
CALL mysql.mnms_revoke_role_user('UserName_role', 'UserName', '%');
Example
Unmap user from role
GRANT option is excluded from the Admin user account. Therefore, granting and revoking permissions must be performed using system-provided procedures.