Role Management
You can manage account roles using the default stored procedures provided in KakaoCloud MySQL.
Role grant procedure
This stored procedure is used to grant permissions to a user-defined role.
Procedure name
mysql.mnms_grant_right_role
Procedure parameters
| Name | Data Type | Description |
|---|---|---|
| p_role | VARCHAR(20) | Name of the ROLE to grant permissions to. |
| p_right | VARCHAR(8000) | Permissions to be granted to the ROLE. |
| p_target_db | VARCHAR(250) | Target DB where the permissions will be granted. |
| p_target_schema | VARCHAR(250) | Object in the DB where the permissions will be granted. |
How to use
CALL mysql.mnms_grant_right_role('UserName_dev_role', 'ALTER, CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, UPDATE', 'DBName', 'Object');
Example
Grant role permissions
As the GRANT option is excluded from the Admin user account, permissions can only be granted using the system-provided procedure.
Role revoke procedure
This stored procedure is used to revoke permissions granted to a user-defined Role.
Procedure name
mysql.mnms_revoke_right_role
Procedure parameters
| Name | Data Type | Description |
|---|---|---|
| p_role | VARCHAR(20) | Name of the ROLE from which the permissions will be revoked. |
| p_right | VARCHAR(8000) | Permissions to be revoked from the ROLE. |
| p_target_db | VARCHAR(250) | Target DB where the permissions will be revoked. |
| p_target_schema | VARCHAR(250) | Object in the DB where the permissions will be revoked. |
How to use
CALL mysql.mnms_revoke_right_role('UserName_dev_role', 'ALTER, CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, UPDATE', 'DBName', 'Object');
Example
- Revoking Role Permissions
As the GRANT option is excluded from the Admin user account, permissions can only be granted or revoked using the system-provided procedure.
Role to user mapping procedure
This stored procedure is used to map users to a user-defined 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) | Username to be mapped to the ROLE. |
| p_host | VARCHAR(20) | Host range from which access is allowed. |
How to use
CALL mysql.mnms_grant_role_user('UserName_dev_role', 'UserName_dev', '%');
Example
Mapping role to user
As the GRANT option is excluded from the Admin user account, permissions can only be granted or revoked using the system-provided procedure.
Role to user unmapping procedure
This stored procedure is used to unmap users from a user-defined 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) | Username to be unmapped from the ROLE. |
| p_host | VARCHAR(20) | Host range from which access is allowed. |
How to use
CALL mysql.mnms_revoke_role_user('UserName_role', 'UserName', '%');
Example
Unmapping role from user
As the GRANT option is excluded from the Admin user account, permissions can only be granted or revoked using the system-provided procedure.