MYSQLユーザー権限レベルの種類について

「グローバル レベル」

既存サーバ上では、グローバル権限は全てのデータベースに適応します。
これらの権限は mysql.user テーブル内に格納されています。
GRANT ALL ON *.* と REVOKE ALL ON *.* はグローバル権限だけを供与、廃止します。

「データベース レベル」

データベース権限は既存データベース内の全てのオブジェクトに適応します。
これらの権限は mysql.db と mysql.host テーブル内に格納されています。
GRANT ALL ON db_name.* と REVOKE ALL ON db_name.* はグローバル権限だけを供与、廃止します。

「テーブル レベル」

テーブル権限は既存テーブル内の全てのカラムに適応します。
これらの権限は mysql.tables_priv テーブル内に格納されています。
GRANT ALL ON db_name.tbl_name と REVOKE ALL ON db_name.tbl_name は
グローバル権限だけを供与、廃止します。

「カラム レベル」

カラム権限は既存テーブル内の単一カラムに適応します。
これらの権限は mysql.columns_priv テーブル内に格納されています。
REVOKE を利用している時は、供与されたカラムと同じカラムを指定する必要があります。

「ルーチン レベル」

CREATE ROUTINE、ALTER ROUTINE、EXECUTE、そして GRANT 権限は
ストアド ルーチンに適応します。(ファンクションとプロシージャ)それらは、
グローバルとデータベース レベルで供与されます。また、CREATE ROUTINE 以外は、
これらの権限は各ルーチンに対してルーチン レベルで供与する事ができ、
mysql.procs_priv テーブル内で格納されます。

GRANT は最大60文字のホスト名をサポートします。
データベース、テーブル、カラム、そしてルーチン名は最大64文字です。
ユーザ名は最大16文字です。
注意:ユーザ名の許容長さは、mysql.user テーブルを変更しても変える事はできません。
もしそれをすると、ユーザが MySQL サーバにログインできなくなってしまうような、
予想不可能な動作に陥る可能性があります。

===============================================================================
GRANT と REVOKE ステートメントに対しては、priv_type は次の表にある物として指定する事ができます。
===============================================================================
権限 意味
===============================================================================
ALL [PRIVILEGES] GRANT OPTION 以外の全てのシンプルな権限を設定します。
ALTER ALTER TABLE の使用を可能にします。
ALTER ROUTINE ストアド ルーチンの変更、ドロップを可能にします。
CREATE CREATE TABLE の使用を可能にします。
CREATE ROUTINE ストアド ルーチンの作成を可能にします。
CREATE TEMPORARY TABLES CREATE TEMPORARY TABLE の使用を可能にします。
CREATE USER CREATE USER、DROP USER、RENAME USER、そして
REVOKE ALL PRIVILEGES の使用を可能にします。
CREATE VIEW CREATE VIEW の使用を可能にします。
DELETE DELETE の使用を可能にします。
DROP DROP TABLE の使用を可能にします。
EVENT イベントスケジューラがイベントを作成するのを可能にします。
EXECUTE ユーザがストアドルーチンを起動させるのを可能にします。
FILE SELECT … INTO OUTFILE と LOAD DATA INFILE の使用を可能にします。
INDEX CREATE INDEX と DROP INDEX の使用を可能にします。
INSERT INSERTの使用を可能にします。
LOCK TABLES SELECT 権限を持つテーブル上の LOCK TABLES の使用を可能にします。
PROCESS SHOW FULL PROCESSLIST の使用を可能にします。
REFERENCES インプリメントされていません。
RELOAD FLUSH の使用を可能にします。
REPLICATION CLIENT ユーザがスレーブとマスタの場所を問い合わせる事を可能にします。
REPLICATION SLAVE 複製スレーブが必要とします。 (マスタからバイナリ ログ イベントを読み込む為)
SELECT SELECT の使用を可能にします。
SHOW DATABASES SHOW DATABASES は全てのデータベースを表示します。
SHOW VIEW SHOW CREATE VIEWの使用を可能にします。
SHUTDOWN mysqladmin shutdown の使用を可能にします。
SUPER CHANGE MASTER、KILL、PURGE MASTER LOGS、そして SET GLOBAL
ステートメントの使用を可能にし、 the mysqladmin debug コマンドは
max_connections が達成していても接続を (一回) 許可します。
TRIGGER ユーザがトリガを作成、ドロップするのを可能にします。
UPDATE UPDATE の使用を可能にします。
USAGE 「権限が無い」 の同義語です。
GRANT OPTION 権限を与えるのを可能にします。
===============================================================================
EVENT と TRIGGER 権限は MySQL 5.1.6 で追加されました。
トリガはテーブルと関連しているので、トリガを作成したり、
ドロップしたりするには、トリガではなくテーブルに TRIGGER 権限を持つ
必要あります。
(MySQL 5.1.6 以前では SUPER 権限はトリガを作成したりドロップしたりする為に必要でした。)

テーブル、カラム、ルーチンの権限は、各権限レベルの権限の論理的な OR として
相加的に形成されています。例えば、もし mysql.user テーブルが、ユーザはグローバル
SELECT 権限を持つ、と指定すると、その権限はデータベース、テーブル、
またはカラムレベルのエントリによって否定する事はできません。

カラムの権限は次のように計算する事ができます。

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

=========================
メモ
=========================
※ユーザーがDROPされても接続されているユーザーは、自動的にTERMINATEされません。
※GLOBAL PRIVILEDGEの変更は既に接続している状態のユーザーには影響しません。
次回接続時から反映されます。
※DATABASE権限に関しては、USERが「USE DATABASE」コマンドを実行したときに
反映されます。
※TABLE & COLUMN権限は変更時に即時反映されます。


    ユーザー作成と削除


mysql> grant usage on DB001.* to 'test_user';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| test_user | % |
| variable | % |
| root | 127.0.0.1 |
| root | colinux |
| admin | localhost |
| root | localhost |
| super_admin | localhost |
+-------------+-----------+
7 rows in set (0.00 sec)

mysql> drop user 'test_user';
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| variable | % |
| root | 127.0.0.1 |
| root | colinux |
| admin | localhost |
| root | localhost |
| super_admin | localhost |
+-------------+-----------+
6 rows in set (0.00 sec)

mysql>

    パスワードを付けて再度、ユーザーを作成。

mysql> grant usage on DB001.* to 'test_user' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| test_user | % |
| variable | % |
| root | 127.0.0.1 |
| root | colinux |
| admin | localhost |
| root | localhost |
| super_admin | localhost |
+-------------+-----------+
7 rows in set (0.00 sec)

mysql>

    ユーザーに追加で権限を割り振る

mysql> grant select on DB001.* to 'test_user';
Query OK, 0 rows affected (0.00 sec)

mysql> grant insert on DB001.* to 'test_user';
Query OK, 0 rows affected (0.01 sec)

    ユーザー作成スクリプトを確認して権限の確認

mysql> show grants for 'test_user';
+----------------------------------------------------------------------------------------------------------+
| Grants for test_user@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, INSERT ON `DB001`.* TO 'test_user'@'%' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

追記:その他の例


mysql> show grants for 'admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*3210C0C06DEE12SD1618BB00005TGCA2EC9C1A77' |
| GRANT ALL PRIVILEGES ON `DB02`.* TO 'admin'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

    =========================================================
    作成したユーザーから付与した権限をなくす。
    =========================================================

    付与した権限を一つ一つ剥奪する。


mysql> show grants for 'test_user'\G
*************************** 1. row ***************************
Grants for test_user@%: GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASS
WORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
*************************** 2. row ***************************
Grants for test_user@%: GRANT SELECT, INSERT ON `DB001`.* TO 'test_user'@'%'
2 rows in set (0.00 sec)

mysql> revoke insert on DB001.* from 'test_user';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'test_user'\G
*************************** 1. row ***************************
Grants for test_user@%: GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASS
WORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
*************************** 2. row ***************************
Grants for test_user@%: GRANT SELECT ON `DB001`.* TO 'test_user'@'%'
2 rows in set (0.00 sec)

mysql>

mysql> revoke all privileges,grant option from 'test_user';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test_user'\G
*************************** 1. row ***************************
Grants for test_user@%: GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASS
WORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
1 row in set (0.01 sec)

mysql>

    上記のように、revoke all privileges,grant optionしても接続権限は消えていない。
    接続権限、「SHOW VARIABLES」、「SHOW STATUS」、「SHOW DATABASE」
    なども利用できる。

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    +——————–+
    1 row in set (0.01 sec)

    mysql>
    全ての権限をrevokeするには、以下のようにdrop userして法が良いかと思う。


mysql> drop user 'test_user';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test_user'\G
ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host
'%'
mysql> show grants for 'test_user'\G

revoke

警告:もし新しいユーザを作成して IDENTIFIED BY 条項を指定しないと、そのユーザは
パスワードを持ちません。これはとても不安定です。しかし、新規ユーザに空ではない
パスワードを提供する為に IDENTIFIED BY が与えられない限り、GRANT が新規ユーザ
を作成するのを防ぐ為に NO_AUTO_CREATE_USER SQL モード
有効にする事ができます。

showroot



mysql> create user 'super_admin'@'localhost' identified by 'super-password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'super_admin'@'localhost'
-> with grant option;
Query OK, 0 rows affected (0.00 sec)

account

上記のコマンドをひとつにまとめてユーザー作成する事の方が自分は多いです。


mysql> grant all privileges on *.* to 'super_admin'@'localhost' identified by 'super-password' with grant option;

account2

※セキュリティ上rootは残しておきたくない管理者の方は、管理者アカウントを別名で作成したら
rootアカウントも不要なアカウントと同時に削除しても良いかもしれません。


mysql> delete from mysql.user where user = '' or user ='root';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.db where user = '' or user ='root';
Query OK, 0 rows affected (0.00 sec)

———————-
MEMO (DEFAULT ROOT権限確認)
———————-


mysql> show grants for 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*G41ECFBE2002DDE5224F2B6F5C6CD2D3D3DDDC35' WITH GRANT OPTION
1 row in set (0.00 sec)


MYSQLでパスワードの付いていないアカウントを確認し削除


mysql> select user,host from mysql.user where password = '';
Empty set (0.00 sec)

mysql_password

パスワードの付いていないユーザーを削除


mysql> delete from mysql.user where password = '';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

※  flush privileges;でMYSQLに権限テーブルをメモリーに再読み込みさせる。

インストールした時に不要なアカウントがMYSQLにインストールされている場合があるので削除する。

fuyou_account_delete


mysql> select user,host from mysql.db;
+-------+-----------+
| user | host |
+-------+-----------+
| | % |
| | % |
| admin | localhost |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> delete from mysql.user where user = '';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where host = '';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.db where user = '';
Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

既存ユーザーパスワード変更方法(2通り)


mysql> set password for 'test_user'@'%' = PASSWORD('password_new');
Query OK, 0 rows affected (0.01 sec)

mysql> grant usage on DB001.* to 'test_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

■set passwordでの変更
change_password

■grant での変更
password_user_change2


MS SQLやOracleにもProfile機能を利用して、パフォーマンスや問題確認を行う
機能がありますが、OSSのMYSQLにも同じようにProfile機能が存在してます。
本日は、Profile機能の簡単な確認。

===============================================================

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> SET profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql>

===============================================================

profiling config


mysql> select * from Y2008 limit 1;
+----+----------+
| id | comment |
+----+----------+
| 1 | comment1 |
+----+----------+
1 row in set (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------+
| 1 | 0.02000000 | select * from Y2008 |
| 2 | 0.02000100 | SHOW VARIABLES LIKE 'tmp_table_size' |
| 3 | 0.01000000 | SHOW VARIABLES like 'profile%' |
| 4 | 0.02000100 | SHOW VARIABLES like 'profi%' |
| 5 | 0.01000000 | SHOW VARIABLES LIKE 'profile%' |
| 6 | 0.00000000 | SHOW VARIABLES LIKE 'profile%' |
| 7 | 0.01000000 | SHOW VARIABLES LIKE 'profil%' |
| 8 | 0.00000000 | SHOW VARIABLES LIKE 'profil%' |
| 9 | 0.00000000 | select * from Y2008 limit 1 |
+----------+------------+--------------------------------------+
9 rows in set (0.01 sec)

mysql>

プロファイル履歴

mysql> SHOW PROFILE;
+——————–+———-+
| Status | Duration |
+——————–+———-+
| starting | 0.000000 |
| Opening tables | 0.000000 |
| System lock | 0.000000 |
| Table lock | 0.000000 |
| init | 0.000000 |
| optimizing | 0.000000 |
| statistics | 0.000000 |
| preparing | 0.000000 |
| executing | 0.000000 |
| Sending data | 0.000000 |
| end | 0.000000 |
| query end | 0.000000 |
| freeing items | 0.000000 |
| logging slow query | 0.000000 |
| cleaning up | 0.000000 |
+——————–+———-+
15 rows in set (0.00 sec)

mysql>

profile_2

profile_3


StatusとDurationを表示


mysql> SHOW PROFILE SOURCE;
+--------------------+----------+-----------------------+--------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------+----------+-----------------------+--------------+-------------+
| starting | 0.000000 | NULL | NULL |NULL |
| query end | 0.000000 | mysql_execute_command | sql_parse.cc |4799 |
| freeing items | 0.000000 | mysql_parse | sql_parse.cc |5805 |
| logging slow query | 0.000000 | log_slow_statement | sql_parse.cc |1608 |
| cleaning up | 0.000000 | dispatch_command | sql_parse.cc |1575 |
+--------------------+----------+-----------------------+--------------+-------------+
5 rows in set (0.01 sec)

mysql> SHOW PROFILE BLOCK IO;
+--------------------+----------+--------------+---------------+
| Status | Duration | Block_ops_in | Block_ops_out |
+--------------------+----------+--------------+---------------+
| starting | 0.010000 | 0 | 0 |
| Opening tables | 0.000000 | 0 | 0 |
| query end | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 0 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+--------------+---------------+
6 rows in set (0.00 sec)
mysql>

mysql> SHOW PROFILE CONTEXT SWITCHES;
+--------------------+----------+-------------------+---------------------+
| Status | Duration | Context_voluntary | Context_involuntary |
+--------------------+----------+-------------------+---------------------+
| starting | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 1 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+-------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE CPU;
+--------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------+----------+----------+------------+
| starting | 0.000000 | 0.000000 | 0.000000 |
| freeing items | 0.000000 | 0.000000 | 0.000000 |
| logging slow query | 0.000000 | 0.000000 | 0.000000 |
| cleaning up | 0.000000 | 0.000000 | 0.000000 |
+--------------------+----------+----------+------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE IPC;
+--------------------+----------+---------------+-------------------+
| Status | Duration | Messages_sent | Messages_received |
+--------------------+----------+---------------+-------------------+
| starting | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 0 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+---------------+-------------------+
4 rows in set (0.01 sec)

mysql>

mysql> SHOW PROFILE MEMORY;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000000 |
| freeing items | 0.000000 |
| logging slow query | 0.000000 |
| cleaning up | 0.000000 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE PAGE FAULTS;
+--------------------+----------+-------------------+-------------------+
| Status | Duration | Page_faults_major | Page_faults_minor |
+--------------------+----------+-------------------+-------------------+
| starting | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 0 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE SWAPS;
+--------------------+----------+-------+
| Status | Duration | Swaps |
+--------------------+----------+-------+
| starting | 0.000000 | 0 |
| freeing items | 0.000000 | 0 |
| logging slow query | 0.000000 | 0 |
| cleaning up | 0.000000 | 0 |
+--------------------+----------+-------+
4 rows in set (0.00 sec)

mysql>

======================================================
過去に取得した値を確認
======================================================


mysql> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------+
| 6 | 0.00000000 | SHOW VARIABLES LIKE 'profile%' |
| 7 | 0.01000000 | SHOW VARIABLES LIKE 'profil%' |
| 8 | 0.00000000 | SHOW VARIABLES LIKE 'profil%' |
| 9 | 0.00000000 | select * from Y2008 limit 1 |
| 10 | 0.00000000 | select * from Y2008 limit 1 |
| 11 | 0.00000000 | show processlist |
| 12 | 0.83001300 | SHOW LOCAL STATUS |
| 13 | 0.86001300 | SHOW LOCAL STATUS |
| 14 | 0.01000000 | SHOW VARIABLES LIKE 'tmp_table_size' |
| 15 | 0.00000000 | SET tmp_table_size=64*1024*1024 |
| 16 | 0.00000000 | SHOW VARIABLES LIKE 'tmp_table_size' |
| 17 | 0.80001200 | SHOW LOCAL STATUS |
| 18 | 0.89001400 | SHOW LOCAL STATUS |
| 19 | 0.01000000 | SET tmp_table_size=16*1024*1024 |
| 20 | 0.00000000 | SHOW PROFILE CONTEXT SWITCHS |
+----------+------------+--------------------------------------+
15 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE SOURCE FOR QUERY 20;
+--------------------+----------+--------------------+--------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------+----------+--------------------+--------------+-------------+
| starting | 0.000000 | NULL | NULL | NULL |
| freeing items | 0.000000 | mysql_parse | sql_parse.cc | 5805 |
| logging slow query | 0.000000 | log_slow_statement | sql_parse.cc | 1608 |
| cleaning up | 0.000000 | dispatch_command | sql_parse.cc | 1575 |
+--------------------+----------+--------------------+--------------+-------------+
4 rows in set (0.00 sec)

mysql>

profile_4


mysql> SET profiling=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql>


12.5.5.33. SHOW PROFILES Syntax