SQLのgrantについてまとめています。
Grantは権限を付与するSQL
GrantはSQLを実行する権限をユーザに付与するためのSQLです。
SQLのGrantの書き方
付与する権限と対象テーブル、対象ユーザを指定します。たとえばMySQLで以下のSQLは、employeesデータベースの全テーブルに対する全権限をユーザuser0@localhostに付与することになります。
grant all privileges on employees.* to user0@localhost;
全データベースの全テーブルを指定するには、以下のように対象を*.*と指定します。単一のテーブルを指定することも可能です。
grant all privileges on *.* to user0@localhost;
MySQLの場合は、show grantで付与した権限を確認できます。
mysql> show grants for user0@localhost; +--------------------------------------------------------------+ | Grants for user0@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user0'@'localhost' | | GRANT ALL PRIVILEGES ON `employees`.* TO 'user0'@'localhost' | +--------------------------------------------------------------+
GRANT USAGE は、USAGEという権限が設定されているという意味ですが、これは「権限なし」を表します。作成したばかりのユーザには、USAGE権限のみが設定されています。
また、一部のデータベースでは権限の付与先をユーザーグループに指定できたり、複数の権限をSQLロールとしてまとめることも可能です。
【関連記事】
▶MySQLでユーザ作成 GRANTでユーザ作成と権限設定を同時に行う方法
権限の削除はrevoke
grantで付与した権限は、revokeで取り消すことが可能です。
なお、revokeは権限のみを取り消す命令で、ユーザを削除することはありません。
以下のSQLは、ユーザuser0@localhostの全てのデータベース、テーブルに対するinsert権限を取り消します。
REVOKE INSERT ON *.* FROM 'user0'@'localhost';
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.6 REVOKE 構文
データベースによって、既存の権限の確認方法が異なる
grant文はSQL87という標準SQLで定義されているため、各データベースとも差はありません。ただし、一部、Grantに機能拡張がされているケースがあります。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
Grantで付与した権限を確認するコマンドは標準SQLではないため、それぞれ全く違っています。
OracleのGrantの注意点
oracleでのgrantは以下のように記述します。以下はsh.salesテーブルに対し、ユーザwarehouse_userに対して、select権限を付与するSQLです。
GRANT SELECT ON sh.sales TO warehouse_user;
OracleでGrantの設定内容を確認するには、USER_TAB_PRIVSをselectします。
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED ----- PUBLIC SYS xxxx xxxx INHERIT PRIVILEGES NO NO NO USER NO
SQL ServerのGrantの注意点
SQL ServerでGrantを使うには以下のようにします。以下は、AdventureWorks212データベースのテーブルsys.sqlに対してユーザSylverster1にselect権限を付与するSQLです。
USE AdventureWorks2012; GRANT SELECT ON sys.sql_logins TO Sylvester1; GO
GRANT (システム オブジェクトの権限の許可) (Transact-SQL) – SQL Server | Microsoft Docs
SQL Serverでは簡単に権限を表示するSQLが用意されていません。以下のようにmasterテーブルのデータをselectして、権限に関する情報を取得します。
SELECT * FROM master.sys.database_permissions AS dp
JOIN sys.system_objects AS so
ON dp.major_id = so.object_id
WHERE dp.class = 1 AND so.parent_object_id = 0 ;
GO
PostgreSQLでのGrantの注意点
以下は、PostgreSQLでのgrant例です。
1行目のSQLでテーブルmytableへのselect権限を全ユーザに付与します。2行目のSQLでテーブルmytableへのselect、update、insert権限をtodosグループに属するユーザに付与します。
GRANT SELECT ON mytable TO PUBLIC; GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos;
PostgreSQLで権限を確認するには、psqlコマンド内で\zを使用します。
\z mytable
Access privileges for database "lusitania"
Schema | Name | Type | Access privileges
--------+---------+-------+-----------------------------------------------------------
public | mytable | table | {miriam=arwdxt/miriam,=r/miriam,"group todos=arw/miriam"}
(1 row)
まとめ
- grantは、指定したユーザに特定テーブルへのアクセス権限を付与するSQL
- grantは標準SQLで定められているが、データベースにより一部機能が拡張されているケースあり。
- 付与した権限を確認するコマンドは、データベースによって異なる
