CREATE USERとは？CREATE USER user@localhostを実行・CREATE USER user@ホスト名を実行
CREATE USERとは？
MySQLにログイン可能なユーザを作成するためのSQL文がCREATE USER文になります。以前のバージョンでは以下のSQL文でユーザの作成と権限の付与が同時に実行されました。
|
1 |
GRANT ALL ON *.* TO user@'ホスト名' IDENTIFIED BY 'password'; |
現行バージョンでは以下のSQL文のようにユーザの作成（CREATE USER）と権限の付与（GRANT）を別々に行います。
|
1 2 |
CREATE USER user@'ホスト名' IDENTIFIED BY 'password'; GRANT ALL ON *.* TO user@'ホスト名' ; |
CREATE USERで作成したユーザを使用して動作を検証していきます。
Server version:8.0.24 MySQL Community Serverを使用して検証しています。
world databaseが存在しない場合はMySQLの公式サイトを参照しましょう。
MySQLのコマンドラインツールの起動方法
MySQLをコマンドから操作するためにWindows PowerShellを起動して以下のように実行します。
|
1 2 3 4 5 |
PS C:\> mysql -u root -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. ～ Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
操作例）userテーブルよりデータを表示します。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT USER, HOST, PLUGIN FROM MYSQL.USER; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ |
CREATE USER user@localhostを実行する
ここからはCREATE USER文でlocalhost用のテストユーザを作成及び権限を付与の後ログインしてテスト用のテーブルにアクセス可能なことを確認します。localhostでPHPにてアクセスします。
ユーザを作成後権限を確認する
CREATE USER文でlocalhost用のテストユーザを作成します。
Windows PowerShellを起動してrootユーザでログイン後以下のように実行します。
|
1 |
mysql> CREATE USER demo@'localhost' IDENTIFIED BY 'demo'; |
world databaseにSELECT権限を付与します。
|
1 |
mysql> GRANT SELECT ON world.* TO demo@'localhost'; |
ユーザ情報を確認します。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT USER, HOST, PLUGIN FROM MYSQL.USER; +------------------+-----------+-----------------------+ | USER | HOST | PLUGIN | +------------------+-----------+-----------------------+ | demo | localhost | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ |
アクセス権を確認します。
|
1 2 3 4 5 6 7 |
mysql> SHOW GRANTS FOR demo@'localhost'; +-------------------------------------------------+ | Grants for demo@localhost | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `demo`@`localhost` | | GRANT SELECT ON `world`.* TO `demo`@`localhost`IDENTIFIED BY PASSWORD | +-------------------------------------------------+ |
作成後のユーザからログインする
Windows PowerShellを起動して以下のように実行します。
|
1 2 3 4 5 |
PS C:\> mysql -u demo -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. ～ Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
データベースの一覧を表示します。
|
1 2 3 4 5 6 7 |
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | world | +--------------------+ |
localhost内でphpを使ってMySQLへ接続する
以下のphpのサンプルコードでlocalhostからMySQLへ接続してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php $dsn = 'mysql:host=localhost;dbname=world'; $user = 'demo'; $passwd = 'demo'; try{ $dbh = new PDO($dsn, $user, $passwd); $stmt = $dbh->query("select count(*) from city"); $users = $stmt->fetchAll(PDO::FETCH_ASSOC); echo "<pre>"; print_r( $users ); echo "</pre>"; }catch (PDOException $e){ print('Error:'.$e->getMessage()); die(); } ?> |
Windows PowershellからCLIで実行します。
|
1 2 3 4 5 6 7 8 9 10 11 |
PS C:\> php -f C:\php8\mysql.php <pre>Array ( [0] => Array ( [count(*)] => 4079 ) ) </pre> |
CREATE USER user@ホスト名を実行する
ここからはCREATE USER文でremotehost用のテストユーザを作成及び権限を付与の後ログインしてテスト用のテーブルにアクセス可能なことを確認します。remotehostからA5:SQL Mk-2やPHPにてアクセスします。
ユーザを作成後権限を確認する
CREATE USER文でremotehost用のテストユーザを作成します。
Windows PowerShellを起動してrootユーザでログイン後以下のように実行します。
|
1 |
mysql> CREATE USER demo@'192.168.%' IDENTIFIED BY 'demo'; |
world databaseにSELECT権限を付与します。
|
1 |
mysql> GRANT SELECT ON world.* TO demo@'192.168.%'; |
ユーザ情報を確認します。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT USER, HOST, PLUGIN FROM MYSQL.USER; +------------------+-----------+-----------------------+ | USER | HOST | PLUGIN | +------------------+-----------+-----------------------+ | demo | 192.168.% | caching_sha2_password | | demo | localhost | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ |
アクセス権を確認します。
|
1 2 3 4 5 6 7 |
mysql> SHOW GRANTS FOR demo@'192.168.%'; +-------------------------------------------------+ | Grants for demo@192.168.% | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `demo`@`192.168.%` | | GRANT SELECT ON `world`.* TO `demo`@`192.168.%` | +-------------------------------------------------+ |
A5:SQL Mk-2 から MySQLにremote接続してみる
remotehostからA5:SQL Mk-2を使用してMySQLに接続します。A5:SQL Mk-2はMySQLのcaching_sha2_passwordプラグインに対応しているため、そのまま接続が可能です。
以下A5:SQL Mk-2で接続後実行した結果です。
|
1 2 3 4 |
SHOW DATABASES 実行結果 information_schema world |
現在ログインしているユーザを表示すると、remotehostからも接続していることがみられます。
|
1 2 3 4 5 6 7 8 9 |
mysql> SHOW PROCESSLIST; +----+-----------------+-----------------+-------+---------+-------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+-------+---------+-------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 24400 | Waiting on empty queue | NULL | | 24 | root | localhost:58175 | NULL | Query | 0 | init | show processlist | | 27 | demo | localhost:58418 | NULL | Sleep | 11631 | | NULL | | 38 | demo | remotehost:50397| world | Sleep | 61 | | NULL | +----+-----------------+-----------------+-------+---------+-------+------------------------+------------------+ |
remotehostからphpを使ってMySQLへ接続する
以下のphpのサンプルコードでremotehostからMySQLへ接続してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php $dsn = 'mysql:host=remotehost;dbname=world'; $user = 'demo'; $passwd = 'demo'; try{ $dbh = new PDO($dsn, $user, $passwd); $stmt = $dbh->query("select count(*) from city"); $users = $stmt->fetchAll(PDO::FETCH_ASSOC); echo "<pre>"; print_r( $users ); echo "</pre>"; }catch (PDOException $e){ print('Error:'.$e->getMessage()); die(); } ?> |
Windows PowershellからCLIで実行します。
|
1 2 |
PS C:\php7> php -f C:\php7\mysql.php Error:SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client |
php7.3系列は暗号方式SHA-2を使った認証方式では接続できません。そのため承認PLUGINをcaching_sha2_passwordからmysql_native_passwordに変更します。
rootユーザでログイン後以下を実行して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> ALTER USER demo@'192.168.%' IDENTIFIED WITH mysql_native_password BY 'demo'; mysql> SELECT USER, HOST, PLUGIN FROM MYSQL.USER; +------------------+-----------+-----------------------+ | USER | HOST | PLUGIN | +------------------+-----------+-----------------------+ | demo | 192.168.% | mysql_native_password | | demo | localhost | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ |
再度Windows PowershellからCLIで実行します。
|
1 2 3 4 5 6 7 8 9 |
PS C:\php7> php -f C:\php7\mysql.php <pre>Array ( [0] => Array ( [count(*)] => 4079 ) ) </> |
CREATE USERでUSERを作成後色々と試してみよう
いかがでしたでしょうか。ここまで CRETATE USERでlocalhost、remotehost用のUSERを作成して権限を付与後ログインやPHPによるアクセスなどを試してきました。
実際のプロジェクトでは目的に応じたUSERと権限を設計する必要もあるでしょう。事前の評価もかねて色々と試してはいかがでしょうか。
