GRANT <role>

以前に作成したロールを既存のユーザーに割り当てます。次に、ステートメントSET ROLE <rolename>を使用してロールの権限を引き受けるか、 SET ROLE ALLを使用して、割り当てられているすべてのロールを引き受けることができます。

あらすじ

GrantRoleStmt
GRANTRolenameListTOUsernameList
RolenameList
Rolename,
UsernameList
Username,

分析チーム用の新しいロールと、 jenniferという名前の新しいユーザーを作成します。

$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE ROLE analyticsteam; Query OK, 0 rows affected (0.02 sec) mysql> GRANT SELECT ON test.* TO analyticsteam; Query OK, 0 rows affected (0.02 sec) mysql> CREATE USER jennifer; Query OK, 0 rows affected (0.01 sec) mysql> GRANT analyticsteam TO jennifer; Query OK, 0 rows affected (0.01 sec)

ロールに関連付けられた権限を使用できるようにするには、デフォルトでjenniferSET ROLE analyticsteamが必要であることに注意してください。

$ mysql -ujennifer Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW TABLES in test; ERROR 1044 (42000): Access denied for user 'jennifer'@'%' to database 'test' mysql> SET ROLE analyticsteam; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT Select ON test.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW TABLES IN test; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

ステートメントSET DEFAULT ROLEを使用してロールをjenniferに関連付けることができるため、ロールに関連付けられた権限を引き受けるためにステートメントSET ROLEを実行する必要はありません。

$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET DEFAULT ROLE analyticsteam TO jennifer; Query OK, 0 rows affected (0.02 sec)
$ mysql -ujennifer Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT Select ON test.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW TABLES IN test; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

MySQL の互換性

このステートメントは、MySQL 8.0 の機能であるロールと完全に互換性があると理解されています。互換性の違いは、GitHub で問題を介して報告されたにする必要があります。

こちらもご覧ください

このページは役に立ちましたか?

Playground
登録なしで TiDB の機能をワンストップでインタラクティブに体験できます。
製品
TiDB Dedicated
TiDB Serverless
TiDB
価格
PoC お問い合わせ
エコシステム
TiKV
TiFlash
OSS Insight
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.