SQLでは特定のユーザーに権限を付けて、操作を制限することができます。
今回はMySQLを使い、特定のカラムだけselectできなくする方法をまとめました。
grant文を使用することを前提にするので、遠回りですが以下の手順で行います。
権限を持たないユーザーを作成し、selectしないカラム以外にselect権限を付与する（grant文）という方法です。
手順① 権限のないユーザーを作成する
/*ユーザーの確認*/
select user, host from mysql.user;
/*ユーザーの作成*/
/*hirakegomaというパスワードで、someuserというユーザーを作成*/
CREATE USER 'someuser'@'somehost' IDENTIFIED BY 'hirakegoma';
ユーザーを作成しました。
しかし、権限は何もつけていない状態です。ので、この「someuser」でDBにアクセスしようとしても、ログインできません。
手順② カラムレベルでselectできる権限をユーザーに与える
/*カラムレベルで SELECTの権限を付与*/
/*mytbl1というテーブルのcol1, col2, col3というカラムにselcetする権限をsomeuserに付与*/
GRANT SELECT (col1, col2, col3) ON mydb.mytbl1 TO 'someuser'@'somehost' ;
/*特定のユーザー(someuser)の権限を確認する*/
SHOW GRANTS FOR 'someuser'@'somehost';
権限を付与すると、someuserのユーザーでDBにアクセスできるようになります。
例えば、「col4」だけselectできないようにするのであれば、「col4」だけ権限を付けないようにします。
someuserでDBにアクセスし、selectできるか確認しましょう
/*現在のユーザーを確認する*/
select user(), current_user();
/*selectできるcol1を取得*/
select col1 from mytbl1;
/*selectできないcol4を取得*/
/*エラーが発生します。*/
select col4 from mytbl1;
/*mytbl の定義を確認*/
show full columns from mytbl1;
/*前列検索*/
/*エラーが発生します。*/
select * from mytbl1;
col1, col2, col3はselectできるようになりました。
show full columns ~ を使うと、すべての定義を確認できます。
権限も見ることができます。
また、テーブルの中に、selectできるカラムと、selectできないカラムがある場合、
「select * from mytbl1」はできないようです。エラーになります。
手順③ 他テーブルにselect権限を与える
/*テーブルレベルで mytbl2テーブルにselectの権限をsomeuserに付与*/
GRANT select ON mydb.mytbl2 TO 'someuser'@'somehost';
手順②でmytbl1にselectできる権限をsomeuserに付与しました。
この状況では、他テーブルには権限がありません。ので、テーブルレベルで権限を付与する必要があります。ここでは、select権限を付与しました。ちなみに、selectの部分をPRIVILEGESとすると、指定されたアクセスレベルにあるすべての権限を付与できるようです。
ユーザーを削除
/*ユーザー（someuser）の削除*/
DROP USER 'someuser'@'somehost';
ユーザーを削除するときは、こちらを使用します。
GRANT文では、レベルによって制御できるものが異なります。
ので、テーブルレベルで制御するのか、カラムレベルで制御するのかなど確認しておきましょう。
MySQL 5.6 リファレンスマニュアル
13.7.1.4 GRANT 構文
MySQL によってサポートされる権限
今回はGRANT文を使用しましたが、REVOKE文を使用してもよさそうですね！
参考
Qiita『MySQLユーザ作成について』（2021年6月13日利用）
https://qiita.com/gatapon/items/92b942fa7081cfe17482
DBOnline『ユーザーを作成する(CREATE USER文)』（2021年6月13日利用）
https://www.dbonline.jp/mysql/user/index1.html
HACKnote『【MySQL】 テーブル・カラム毎に権限を変更する』（2021年6月13日利用）
https://hacknote.jp/archives/24570/
Qiita『[MySQL]権限の確認と付与』（2021年6月13日利用）
https://hacknote.jp/archives/24570/
Qiita『MySQL の権限のコマンドまとめ。』（2021年6月13日利用）
https://qiita.com/PallCreaker/items/0b02c5f42be5d1a14adb
DBOnline『ユーザーに設定できる権限の種類と一覧』（2021年6月13日利用）
https://www.dbonline.jp/mysql/user/index5.html
MySQL 5.6 リファレンスマニュアル『13.7.1.4 GRANT 構文』（2021年6月13日利用）
https://dev.mysql.com/doc/refman/5.6/ja/grant.html#grant-column-privileges