授予用户在表SYSIBMADM.SNAPUTIL上select权限，访问报错
各位：昨天我赋权给一个用户select表SYSIBMADM.SNAPUTIL表的权限，刚赋权的时候是查询的，但是今天查询报如下错误：
SQL1092N "INT" does not have the authority to perform the requested command or operation.
1、查看视图，这个用户有select权限：
AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTNAME OBJECTSCHEMA OBJECTTYPE
---------- ---------- ----------- --------- -------------------- ------------ ------------------------
INT U SELECT N SNAPUTIL SYSIBMADM VIEW
2、该用户的所有权限：
select substr(AUTHORITY,1,25) as AUTHORITY,D_USER,D_GROUP,D_PUBLIC,ROLE_USER,ROLE_GROUP,ROLE_PUBLIC,D_ROLE FROM TABLE(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID('INT','U'));
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
SYSADM * N * * * * *
DBADM Y N N N N N *
CREATETAB Y N Y N N N *
BINDADD Y N Y N N N *
CONNECT Y N Y N N N *
CREATE_NOT_FENCED_ROUTINE Y N N N N N *
SYSCTRL * N * * * * *
SYSMAINT * N * * * * *
IMPLICIT_SCHEMA Y N Y N N N *
LOAD Y N N N N N *
CREATE_EXTERNAL_ROUTINE Y N N N N N *
QUIESCE_CONNECT Y N N N N N *
SECADM N N N N N N *
SYSMON * N * * * * *
SQLADM N N N N N N *
WLMADM N N N N N N *
EXPLAIN Y N N N N N *
DATAACCESS Y N N N N N *
ACCESSCTRL Y N N N N N *
3、GRANT USAGE on WORKLOAD SYSDEFAULTUSERWORKLOAD to user INT；也执行了
以上是权限信息，请问这个是怎么回事呢？
SQL1092N "INT" does not have the authority to perform the requested command or operation.
1、查看视图，这个用户有select权限：
AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTNAME OBJECTSCHEMA OBJECTTYPE
---------- ---------- ----------- --------- -------------------- ------------ ------------------------
INT U SELECT N SNAPUTIL SYSIBMADM VIEW
2、该用户的所有权限：
select substr(AUTHORITY,1,25) as AUTHORITY,D_USER,D_GROUP,D_PUBLIC,ROLE_USER,ROLE_GROUP,ROLE_PUBLIC,D_ROLE FROM TABLE(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID('INT','U'));
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
SYSADM * N * * * * *
DBADM Y N N N N N *
CREATETAB Y N Y N N N *
BINDADD Y N Y N N N *
CONNECT Y N Y N N N *
CREATE_NOT_FENCED_ROUTINE Y N N N N N *
SYSCTRL * N * * * * *
SYSMAINT * N * * * * *
IMPLICIT_SCHEMA Y N Y N N N *
LOAD Y N N N N N *
CREATE_EXTERNAL_ROUTINE Y N N N N N *
QUIESCE_CONNECT Y N N N N N *
SECADM N N N N N N *
SYSMON * N * * * * *
SQLADM N N N N N N *
WLMADM N N N N N N *
EXPLAIN Y N N N N N *
DATAACCESS Y N N N N N *
ACCESSCTRL Y N N N N N *
3、GRANT USAGE on WORKLOAD SYSDEFAULTUSERWORKLOAD to user INT；也执行了
以上是权限信息，请问这个是怎么回事呢？
2同行回答
Authorization
One of the following authorizations is required:
SELECT privilege on the SNAPUTIL administrative view
CONTROL privilege on the SNAPUTIL administrative view
DATAACCESS authority
In addition, one of the following privileges or authorities is also required:
EXECUTE privilege on the SNAP_GET_UTIL table function
DATAACCESS authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
SYSMON
SYSCTRL
SYSMAINT
SYSADM
我测试了一下，把id=test加入到sysmon的group中，并grant select on snaputil，grant dataaccess on databaes，那么就有了权限去访问snaputil。
看你的auth_list_authorities，对PUBLIC是赋权的，那么sysdefaultuserworkload应该不需要去手动赋权了。收起
One of the following authorizations is required:
SELECT privilege on the SNAPUTIL administrative view
CONTROL privilege on the SNAPUTIL administrative view
DATAACCESS authority
In addition, one of the following privileges or authorities is also required:
EXECUTE privilege on the SNAP_GET_UTIL table function
DATAACCESS authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
SYSMON
SYSCTRL
SYSMAINT
SYSADM
我测试了一下，把id=test加入到sysmon的group中，并grant select on snaputil，grant dataaccess on databaes，那么就有了权限去访问snaputil。
看你的auth_list_authorities，对PUBLIC是赋权的，那么sysdefaultuserworkload应该不需要去手动赋权了。收起