MySQL UserRole

CREATE USER

• https://dev.mysql.com/doc/refman/8.0/en/create-user.html
• https://dev.mysql.com/doc/refman/8.0/en/account-names.html

CREATE USER <user>[@<host>] [<authOption>]

• <authOption>
  • IDENTIFIED BY <password>

CREATE ROLE

• https://dev.mysql.com/doc/refman/8.0/en/create-role.html
• https://dev.mysql.com/doc/refman/8.0/en/role-names.html

CREATE ROLE <role>

GRANT

• https://dev.mysql.com/doc/refman/8.0/en/grant.html

GRANT
    <privilegeType> [(<column> [, <column>])]
    , [<privilegeType> [(<column> [, <column>])]]
    ON [TABLE | FUNCTION | PROCEDURE] <privilegeLevel>
    TO <user[@<host>] | role>
    [WITH GRANT OPTION]

• <privilegeType>
  • https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
  • ALL
• <privilegeLevel>
  • <table>
  • <database>.<table>
  • <database>.<routine>
  • 이름 대신 *을 사용할 수 있습니수
• WITH GRANT OPTION: 다른 사용자에게 자신이 부여받은 권한을 부여 할 수 있는 권한을 부여합니다

GRANT <role>, [,<role>]
    TO <user[@<host>] | role>, [,<user[@<host>] | role>]
    [WITH ADMIN OPTION]

FLUSH PRIVILEGES

SHOW GRANTS FOR <user[@<host>] | role>

REVOKE

권한 관리 예시

<database>_admin

GRANT ALL ON <database>.* TO <database>_admin

<database>_writer

GRANT INSERT, UPDATE, DELETE ON <database>.* TO <database>_writer

<database>_reader

GRANT SELECT ON <database>.* TO <database>_reader