HiveでGRANT/REVOKEによるINSERT/SELECT/UPDATE/DELETE権限の設定ができる機能「SQL Standard Based Authorization」を動かしてみる。

環境は

CentOS 7.2
Hive 1.1.0 (CDH 5.6.0)
PostgreSQL 9.5.1 (メタストア用)

Hiveのインストール

以前の記事参照。

Hiveの設定

Hiveのwikiを参考に設定する。

追加でhive-site.xmlに設定する必要がある項目は以下のとおり。

<property>
  <name>hive.server2.enable.doAs</name>
  <value>false</value>
</property>

<property>
  <name>hive.users.in.admin.role</name>
  <value>hive</value>
</property>

<property>
  <name>hive.security.authorization.enabled</name>
  <value>true</value>
</property>

<property>
  <name>hive.security.metastore.authorization.manager</name>
  <value>org.apache.hadoop.hive.ql.security.authorization.MetaStoreAuthzAPIAuthorizerEmbedOnly</value>
</property>

<property>
  <name>hive.security.authorization.manager</name>
  <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdConfOnlyAuthorizerFactory</value>
</property>

<property>
  <name>hive.security.authorization.task.factory</name>
  <value>org.apache.hadoop.hive.ql.parse.authorization.HiveAuthorizationTaskFactoryImpl</value>
</property>

CDH5特有の注意点

CDH5を利用する場合、デフォルトではHiveのSQL Based Authorization機能が無効化されているため、Hiveのwikiに書かれている設定項目の加えてhive.security.authorization.task.factoryを設定する必要がある。

これを設定しないと、GRANT文などを実行しようとしても、以下のエラーが発生して何も動作しない。

0: jdbc:hive2://localhost:10000> SHOW ROLES;
Error: Error while compiling statement: FAILED: SemanticException The current builtin authorization in Hive is incomplete and disabled. (state=42000,code=40000)

非常に意味不明なので解決するのに時間がかかった。。。

HiveServer2の起動

起動時に以下のようなコマンドラインオプションを設定する必要があるらしい。

[hive@localhost ~]$ hiveserver2 --hiveconf hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory --hiveconf hive.security.authorization.enabled=true --hiveconf hive.security.authenticator.manager=org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator --hiveconf hive.metastore.uris=''

initスクリプト経由で起動する場合は、/etc/default/hive-server2に起動時のオプションを環境変数で設定してあげるといけるっぽい。

/etc/default/hive-server2

export HIVE_OPTS='--hiveconf hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory --hiveconf hive.security.authorization.enabled=true --hiveconf hive.security.authenticator.manager=org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator --hiveconf hive.metastore.uris='

動作確認

このブログで紹介されている例をやってみる。

ADMIN権限を持てるユーザで、特定のユーザにはSELECT権しかないROLEを付与してみる。

[hive@localhost ~]$ beeline 
Beeline version 1.1.0-cdh5.6.0 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000 hive password org.apache.hive.jdbc.HiveDriver
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 1.1.0-cdh5.6.0)
Driver: Hive JDBC (version 1.1.0-cdh5.6.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000> SET ROLE ADMIN;
No rows affected (0.102 seconds)

最初にSET ROLE ADMINを実行する必要がある。
これをやった後にreadonlyロールを作成。

0: jdbc:hive2://localhost:10000> CREATE ROLE readonly;
No rows affected (0.088 seconds)
0: jdbc:hive2://localhost:10000> SHOW ROLES;
+-----------+--+
|   role    |
+-----------+--+
| admin     |
| public    |
| readonly  |
+-----------+--+
3 rows selected (0.058 seconds)

ユーザaliceにロールreadonlyを付与。

0: jdbc:hive2://localhost:10000> GRANT ROLE readonly TO USER alice;
No rows affected (0.072 seconds)

適当なテーブルを作って、readonlyロールにSELECT権限を与える。

0: jdbc:hive2://localhost:10000> CREATE TABLE tbl_a (id int);
No rows affected (0.998 seconds)
0: jdbc:hive2://localhost:10000> LOAD DATA INPATH '/user/hive/tbl_a.data' INTO TABLE tbl_a;
INFO  : Loading data to table default.tbl_a from hdfs://localhost:8020/user/hive/tbl_a.data
INFO  : Table default.tbl_a stats: [numFiles=1, totalSize=10]
No rows affected (0.353 seconds)
0: jdbc:hive2://localhost:10000> SHOW TABLES;
+-----------+--+
| tab_name  |
+-----------+--+
| tbl_a     |
+-----------+--+
1 row selected (0.07 seconds)
0: jdbc:hive2://localhost:10000> DESC tbl_a
0: jdbc:hive2://localhost:10000> ;
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| id        | int        |          |
+-----------+------------+----------+--+
1 row selected (0.147 seconds)
0: jdbc:hive2://localhost:10000> GRANT SELECT ON TABLE tbl_a TO readonly;
Error: Error while compiling statement: FAILED: ParseException line 1:31 cannot recognize input near 'readonly' '<EOF>' '<EOF>' in user|group|role name (state=42000,code=40000)
0: jdbc:hive2://localhost:10000> GRANT SELECT ON TABLE tbl_a TO ROLE readonly;
No rows affected (0.144 seconds)
0: jdbc:hive2://localhost:10000> SHOW GRANT ROLE readonly;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| database  | table  | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| default   | tbl_a  |            |         | readonly        | ROLE            | SELECT     | false         | 1457444851000  | hive     |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
1 row selected (0.173 seconds)
0: jdbc:hive2://localhost:10000> SHOW GRANT ON TABLE tbl_a;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| database  | table  | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| default   | tbl_a  |            |         | hive            | USER            | DELETE     | true          | 1457445229000  | hive     |
| default   | tbl_a  |            |         | hive            | USER            | INSERT     | true          | 1457445229000  | hive     |
| default   | tbl_a  |            |         | hive            | USER            | SELECT     | true          | 1457445229000  | hive     |
| default   | tbl_a  |            |         | hive            | USER            | UPDATE     | true          | 1457445229000  | hive     |
| default   | tbl_a  |            |         | readonly        | ROLE            | SELECT     | false         | 1457445378000  | hive     |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
5 rows selected (0.114 seconds)

で、aliceユーザでHiveServer2に接続してみる。

[alice@localhost ~]$ beeline 
Beeline version 1.1.0-cdh5.6.0 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000
scan complete in 2ms
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: alice
Enter password for jdbc:hive2://localhost:10000: *****
Connected to: Apache Hive (version 1.1.0-cdh5.6.0)
Driver: Hive JDBC (version 1.1.0-cdh5.6.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000> SHOW TABLES;
+-----------+--+
| tab_name  |
+-----------+--+
| tbl_a     |
+-----------+--+
1 row selected (0.219 seconds)
0: jdbc:hive2://localhost:10000> DESC tbl_a;
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| id        | int        |          |
+-----------+------------+----------+--+
1 row selected (0.123 seconds)
0: jdbc:hive2://localhost:10000> SELECT * FROM tbl_a;
+-----------+--+
| tbl_a.id  |
+-----------+--+
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |
+-----------+--+
5 rows selected (0.525 seconds)
0: jdbc:hive2://localhost:10000> DROP TABLE tbl_a;
Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: Principal [name=alice, type=USER] does not have following privileges for operation DROPTABLE [[OBJECT OWNERSHIP] on Object [type=TABLE_OR_VIEW, name=default.tbl_a]] (state=42000,code=40000)

このように、テーブルのスキーマや内容は参照できるが、変更をしようとすると、Permission deniedが発生する。

ADMIN権限を得ようとしてもできない。

0: jdbc:hive2://localhost:10000> SET ROLE ADMIN;
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. alice doesn't belong to role admin (state=08S01,code=1)

以上！