MySQL データベース接続ユーザーの作成

MySQL データベース接続ユーザーの作成

MySQLでデータベース接続ユーザーの作成に関するSQLについて、以下に示します。

※ここではMySQLのバージョン5.6を使用して、動作確認を行っています。

※データベースの作成については、当サイトのMySQL データベースの作成のページをご覧ください。

※テーブルの作成については、当サイトのMySQL テーブルの作成のページをご覧ください。

データベース接続ユーザーの作成

データベース接続ユーザーを作成するには、以下のようなSQLを実行します。

mysql>, GRANT ALL ON *.* TO 'user01'@'%' IDENTIFIED BY 'xxxxxxxxxx' WITH GRANT OPTION,
  • GRANT文はユーザーに権限を付与するものになりますが、指定されたユーザーがまだ作成されていない場合は、ユーザーの作成も行われます。

    ただし、MySQLの設定ファイル(Linux環境では「/etc/my.cnf」)で、「sql_mode」に「NO_AUTO_CREATE_USER」が設定されている場合は、GRANT文でユーザーの作成も行う際に「IDENTIFIED BY」でのパスワードの指定を省略することはできなくなります。

    下記に示すCREATE USER文でユーザーを作成する際は、「sql_mode」に「NO_AUTO_CREATE_USER」が設定されていても「IDENTIFIED BY」でのパスワードの指定を省略ができます。

  • 「GRANT」に続けて、付与する権限を指定します。

    「ALL」を指定すると、全ての権限が付与されるようになります。

    ただし、「ALL」には「GRANT OPTION」権限は含まれません。

    「SELECT, INSERT, UPDATE, DELETE」のように、付与したい権限の種類をカンマで区切って指定することもできます。

  • 「ON」に続けて、権限を付与するデータベースとテーブルを「データベース名.テーブル名」の形で指定します。

    「*.*」と指定すると、全てのデータベースのテーブルに権限が付与されるようになります。

    「db01.*」と指定すると、データベース「db01」の全てのテーブルに権限が付与されるようになります。

    「db01.tbl01」と指定すると、データベース「db01」のテーブル「tbl01」に権限が付与されるようになります。

  • 「TO」に続けて、権限を付与するユーザーを「’ユーザー名’@’ホスト名’」の形で指定します。

    「’user01’@’%’」と指定すると、全てのホストから接続可能なユーザー「user01」に権限が付与されるようになります。

    「’user01’@’localhost’」と指定すると、ローカルホストから接続可能なユーザー「user01」に権限が付与されるようになります。

    「’user01’@’192.168.1.101’」と指定すると、IPアドレス「192.168.1.101」を持つホストから接続可能なユーザー「user01」に権限が付与されるようになります。

    「’user01’@’192.168.1.0/2555.255.255.0’」または「’user01’@’192.168.1.%’」と指定すると、IPアドレス「192.168.1.0/2555.255.255.0」を持つホストから接続可能なユーザー「user01」に権限が付与されるようになります。

    以降で説明するSQLを含め、ユーザーの指定を「user01」と、ホスト名を省略した場合は、「’user01’@’%’」と指定したのと同様になります。

    ※MySQLへの外部からのアクセスを許可するには、ファイアウォールでもアクセスが許可されている必要があります。CentOSでの設定については、当サイトのCentOS7 64bit firewalldによるアクセス制御のページCentOS6 64bit iptablesによるアクセス制御のページをご覧ください。

  • 「IDENTIFIED BY」に続けて、ユーザーのパスワードを指定します。

  • 「WITH」に続けて「GRANT OPTION」を指定すると、「GRANT」に続けて指定した「ALL」には含まれなかった「GRANT OPTION」権限が付与されるようになります。

ユーザーの作成と権限の付与を別々に行う場合は、以下のようなSQLを実行します。

mysql>, CREATE USER 'user01'@'%' IDENTIFIED BY 'xxxxxxxxxx',
mysql>, GRANT ALL ON *.* TO 'user01'@'%' WITH GRANT OPTION,
  • 「CREATE USER」に続けて、作成するユーザーを「’ユーザー名’@’ホスト名’」の形で指定します。

    ユーザーの指定方法は、上記のGRANT文で説明したものと同様です。

  • 「IDENTIFIED BY」に続けて、ユーザーのパスワードを指定します。

  • GRANT文については、「IDENTIFIED BY」の指定がない点を除いて、上記で説明したものと同様です。

データベース接続ユーザーの一覧の確認

データベース接続ユーザーの一覧を確認するには、以下のようなSQLを実行します。

mysql>, SELECT host,user,password FROM mysql.user, +-----------------------+--------+-------------------------------------------+ | host | user | password | +-----------------------+--------+-------------------------------------------+ | localhost | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | | localhost.localdomain | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | | 127.0.0.1 | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | | ::1 | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | | % | user01 | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | +-----------------------+--------+-------------------------------------------+
  • ここではデフォルトで作成されているユーザー「root」に、ユーザー「user01」を追加で作成した際の結果になっています。

既存のデータベース接続ユーザーのGRANT文の確認

既存のデータベース接続ユーザーのGRANT文を確認するには、以下のようなSQLを実行します。

mysql>, SHOW GRANTS FOR 'user01'@'%', +----------------------------------------------------------------------------------------------------------------------------------+ | Grants for user01@% | +----------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'user01'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------+
  • 「SHOW GRANTS FOR」に続けて、GRANT文を確認するデータベース接続ユーザーを指定します。

    ここではユーザー「’user01’@’%’」のGRANT文を確認するようにしています。

既存のデータベース接続ユーザーのパスワードの変更

既存のデータベース接続ユーザーのパスワードを変更するには、以下のようなSQLを実行します。

mysql>, SET PASSWORD FOR 'user01'@'%' = PASSWORD('xxxxxxxxxx'),
  • 「SET PASSWORD FOR」に続けて、パスワードを変更するデータベース接続ユーザーを指定します。

    ここではユーザー「’user01’@’%’」のパスワードを変更するようにしています。

  • 「=」に続けて、PASSWORD関数を使用して変更するパスワードを指定します。

データベース接続ユーザーの削除

データベース接続ユーザーを削除するには、以下のようなSQLを実行します。

mysql>, DROP USER 'user01'@'%',
  • 「DROP USER」に続けて、削除するデータベース接続ユーザーを指定します。

    ここではユーザー「’user01’@’%’」を削除するようにしています。

MySQL データベース接続ユーザーの作成については、以上です。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。