DB2 基础: 如何从 DB2 UDB 系统编目中获得有用的信息
2010-07-20 00:00:00 来源:WEB开发网如果您感兴趣的话,甚至还可以访问视图定义本身,方法是从弹出式菜单(图 3)选中 Alter。
图 3. 通过 Alter View 窗口可以查看视图定义
检索授权数据
如前所述,没有哪个单独的系统编目视图可以包含所有可用的授权数据。虽然关于授权和权限的元数据包含在多个编目视图中,但是可以构造一条查询,检索所有拥有特权的授权 ID。我们将利用 UNION 操作来完成查询任务,并(在这里和在其他例子中)使用 SUBSTR 内置函数来帮助格式化结果集:
清单 4. 识别拥有特权的授权 ID
select distinct substr(grantee,1,16) as grantee, granteetype, 'Database'
from syscat.dbauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, '表空间'
from syscat.tbspaceauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Schema'
from syscat.schemaauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Table'
from syscat.tabauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Index'
from syscat.indexauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Column'
from syscat.colauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Package'
from syscat.packageauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Routine'
from syscat.routineauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Server'
from syscat.passthruauth
order by grantee, granteetype
GRANTEE GRANTEETYPE 3
---------------- ----------- -----------
MELNYK U Database
MELNYK U Index
MELNYK U Package
MELNYK U Table
MELNYK U 表空间
PUBLIC G Database
PUBLIC G Package
PUBLIC G Routine
PUBLIC G Schema
PUBLIC G Table
PUBLIC G 表空间
11 record(s) selected.
更多精彩
赞助商链接