DB2 V9.7中的DATAACCESS权限

最近碰到一个有意思的问题，在某个 DB29.7的系统中，有一个用户名曰A，A只是一个普通用户，没有SYSADM的权限，也不属于其他特殊的用户组。原本该A被赋予了对表T的DELETE特权，可以对表T进行数据删除的操作。某日管理员因故取消了A对表T的DELETE权限，却发现用A连接数据库之后，仍然可以执行DELETEFROM T的操作。于是管理员抓狂崩溃莫名。

   初听这个问题，怀疑方向落在user A所属的组是否具有相应权限上。于是通过select * fromsysibmadm.privileges查看了所有特权，发现：

AUTHID AUTHIDTYPE PRIVILEGE  GRANTABLE OBJECTNAME OBJECTSCHEMA OBJECTTYPE
"A","U","REFERENCE",,"T","DB2INST1","TABLE"
"A","U","SELECT","Y","T","DB2INST1","TABLE"
"A","U","INSERT","Y","T","DB2INST1","TABLE"
"A","U","INDEX","Y","T","DB2INST1","TABLE"
"A","U","ALTER","Y","T","DB2INST1","TABLE"

    A所在的组包括public组，并没有T上的DELETE特权。于是问题变得愈加有意思了。

    无奈怀疑到sysibmadm.privileges所列示的信息未必完备，于是深入查了一下DBAUTH这个系统表。列示相关信息如下：

"DB2INST1","U","A","U","N","N","N","N","N","N","N","N","N","N","N","N","N","N","Y","Y"

db2 "describe table syscat.dbauth"

                               Datatype                    Column
Columnname                    schema    Datatypename     Length    Scale NullsGRANTOR                        SYSIBM   VARCHAR                   128    0 No   
GRANTORTYPE                    SYSIBM   CHARACTER                   1    0 No   
GRANTEE                        SYSIBM   VARCHAR                   128    0 No   
GRANTEETYPE                    SYSIBM   CHARACTER                   1    0 No   
BINDADDAUTH                    SYSIBM   CHARACTER                   1    0 No   
CONNECTAUTH                    SYSIBM   CHARACTER                   1    0 No   
CREATETABAUTH                  SYSIBM   CHARACTER                   1    0 No   
DBADMAUTH                      SYSIBM   CHARACTER                   1    0 No   
EXTERNALROUTINEAUTH            SYSIBM   CHARACTER                   1    0 No   
IMPLSCHEMAAUTH                 SYSIBM   CHARACTER                   1    0 No   
LOADAUTH                       SYSIBM   CHARACTER                   1    0 No   
NOFENCEAUTH                    SYSIBM   CHARACTER                   1    0 No   
QUIESCECONNECTAUTH             SYSIBM   CHARACTER                   1    0 No   
LIBRARYADMAUTH                 SYSIBM   CHARACTER                   1    0 No   
SECURITYADMAUTH                SYSIBM   CHARACTER                   1    0 No   
SQLADMAUTH                     SYSIBM   CHARACTER                   1    0 No   
WLMADMAUTH                     SYSIBM   CHARACTER                   1    0 No   
EXPLAINAUTH                    SYSIBM   CHARACTER                   1    0 No   
DATAACCESSAUTH                 SYSIBM   CHARACTER                   1    0 No   
ACCESSCTRLAUTH                 SYSIBM   CHARACTER                   1    0 No 

    发现用户A被实例用户db2inst1赋予了DATAACCESS的特权。正想我常说的那样，“事情的发生总有原因。”是的，这就是事情的根源。

    DATAACCESS特权是DB2新引入的权限，对于数据库中所有表、视图、具体化查询表和昵称，它会提供下列权限和特权：

• 对数据库的 LOAD 权限
• SELECT 特权（其中包括对系统目录表和视图的 SELECT 特权）
• INSERT 特权
• UPDATE 特权
• DELETE 特权

    感兴趣的读者请自行参考DB2的信息中心吧，不赘述了。

db2文档
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000958.html?cp=SSEPGG_9.7.0%2F2-10-6-130&lang=en