GratanでMySQLのアカウントと権限設定をコード管理

概要

MySQLの権限設定を行う場合、
DB担当者がDBサーバーにログインしてGRANT文を発行します。
Gratanを使うとこの権限設定をRuby構文でコードとして記述することができます。

SQL文を直接発行するのではなくコードで管理できるので
開発者からのPull RequestでDB担当者がMargeということが出来ます。

環境

CentOS 7.1.1503
MySQL 5.6
Ruby 2.2.0
Gratan 0.2.7


インストール

gem install gratan


事前準備

まず初めにMySQL内の権限設定をGrantfileにエクスポートします。

gratan -e --with-identifier -o Grantfile


表示例

[root@test ~]# gratan -e --with-identifier -o Grantfile
Export Grants to `Grantfile`


Grantfileの中身(初期状態)

エクスポートされたGrantfileは以下のようになっています。

user "root", "127.0.0.1" do
  on "*.*", :with=>"GRANT OPTION" do
    grant "ALL PRIVILEGES"
  end
end

user "root", "::1" do
  on "*.*", :with=>"GRANT OPTION" do
    grant "ALL PRIVILEGES"
  end
end

user "", "localhost" do
  on "*.*" do
    grant "USAGE"
  end
end

user "root", "localhost", :identified=>"PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'" do
  on "''@''", :with=>"GRANT OPTION" do
    grant "PROXY"
  end

  on "*.*", :with=>"GRANT OPTION" do
    grant "ALL PRIVILEGES"
  end
end


MySQLユーザ追加

テスト用のMySQLユーザを追加してみます。

ユーザ名 testuser
パスワード testpassword
ホスト localhost
権限 SELECT


Grantfileに追記します。

user "testuser", ["localhost"], identified: "PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA'" do
  on "testdb.*" do
    grant "SELECT"
  end
end


パスワードは事前にハッシュ化しています。

mysql> select password('testpassword');
+-------------------------------------------+
| password('testpassword')                  |
+-------------------------------------------+
| *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 


ハッシュ化せずパスワードをそのまま書くと以下エラーが表示されます。

[ERROR] The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.


dry-run

適用前にdry-runを行い発行されるSQL文を確認します。

[root@test ~]# gratan -a --dry-run
Apply `Grantfile` to {"host":"localhost","username":"root"} (dry-run)
GRANT SELECT ON `testdb`.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' (dry-run)
FLUSH PRIVILEGES (dry-run)
No change
[root@test ~]#


適用(apply)

dry-runを行い問題がなければ適用します。

[root@test ~]# gratan -a
Apply `Grantfile` to {"host":"localhost","username":"root"}
GRANT SELECT ON `testdb`.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA'
FLUSH PRIVILEGES
[root@test ~]#


ユーザ確認

ユーザが作成されていることが確認出来ます。

mysql> select user,host from mysql.user;
+----------+---------------+
| user     | host          |
+----------+---------------+
| root     | 127.0.0.1     |
| root     | ::1           |
|          | localhost     |
| root     | localhost     |
| testuser | localhost     |
+----------+---------------+
5 rows in set (0.78 sec)

mysql> 


権限確認

Grantfileに記載した権限が付与されていることが分かります。

mysql> SHOW GRANTS FOR testuser@localhost;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
| GRANT SELECT ON `testdb`.* TO 'testuser'@'localhost'                                                            |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

以上です。


以下TIPSです。

MySQLパスワード指定

MySQLのパスワードが設定されている場合以下のようにパスワードを指定します。

gratan -e --with-identifier -o Grantfile --host localhost --user root --password rootpassword


複数権限付与

複数の権限を設定する場合の記述例です。

user "testuser", ["localhost"], identified: "*9F69E47E519D9CA02116BF5796684F7D0D45F8FA" do
  on "testdb.*" do
    grant "SELECT"
    grant "INSERT"
    grant "UPDATE"
    grant "DELETE"
    grant "CREATE"
    grant "INDEX"
    grant "ALTER"
    grant "LOCK TABLES"
    grant "CREATE VIEW"
    grant "SHOW VIEW"
    grant "TRIGGER"
  end
end