db2 "create view" 缺乏权限

db2 "create view" 缺乏权限

问题:

 根据信息中心的说明，create view需要以下权限：
The privileges held by the authorization ID of the statement must include at least one of the following authorities:

IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the view does not exist

CREATEIN privilege on the schema, if the schema name of the view refers to an existing schema

DBADM authority

and at least one of the following authorities for each table, view, or nickname identified in any fullselect:

CONTROL privilege on that table, view, or nickname

SELECT privilege on that table, view, or nickname

DATAACCESS authority 

但某用户db2user1所在的组db2group1拥有DBADM和DATAACCESS的权限的情况下，创建视图的时候却报错，说没有权限。

问题原因：

Create View是一个比较特殊的操作，创建的时候，用户所属组的权限会被忽略,说明如下：

A privilege that is granted to a group is not used for authorization checking on:

Static DML statements in a package

A base table while processing a CREATE VIEW statement

A base table while processing a CREATE TABLE statement for a materialized query table

Create SQL routine

Create trigger

解决方法：

可以单独给用户赋予相应的权限，或者给PUBLIC组赋予相应的权限（注意，有些权限是无法赋予PUBLIC组的，比如ACCESSCTRL, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, or SECADM）

参考资料：

https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000935.html
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000958.html