Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 (中文) » SQL Anywhere 服务器 - SQL 参考 » 视图 » 兼容性视图

 

SYSUSERPERM 兼容性视图(不建议使用)

SYSUSERPERM 视图的每一行都描述一个用户 ID。您必须具有 SELECT ANY TABLE 系统特权才能查看此视图中的数据。

不建议使用该视图,因为它只显示以前版本中可用的特权和权限。您应该更改应用程序以使用 SYSROLEGRANTS 统一视图。

在以下的 SQL 语句中提供了组成该视图的表和列。要了解有关特定表或列的详细信息,请使用该视图定义下所提供的链接。



ALTER VIEW "SYS"."SYSUSERPERM"
  as select b.user_id,
    b.object_id,
    b.user_name,
    b.password,
    if AA.resourceauth is not null and AA.resourceauth > 0 then
      'Y' else 'N' endif as resourceauth,
    if AA.dbaauth is not null and AA.dbaauth > 0 then
      'Y' else 'N' endif as dbaauth,
    'N' as scheduleauth,
    if exists(select * from SYS.ISYSOPTION as opt
      where opt."option" like '%db_publisher%' and opt.setting not like '%-1'
      and b.user_id = cast(opt.setting as integer)) then
      'Y' else 'N' endif as publishauth,
    if AA.remotedbaauth is not null and AA.remotedbaauth > 0 then
      'Y' else 'N' endif as remotedbaauth,
    if b.user_type = (0x02|0x04|0x08) or b.user_name in( 'SYS','PUBLIC','diagnostics','SYS_SPATIAL_ADMIN_ROLE','rs_systabgroup','SA_DEBUG','dbo' ) then
      'Y' else 'N' endif as user_group,
    r.remarks
    from SYS.ISYSUSER as b
      left outer join SYS.ISYSREMARK as r on(b.object_id = r.object_id)
      left outer join(select sum(if sp_auth_sys_role_info.auth = 'RESOURCE' then 1 else 0 endif) as resourceauth,
        sum(if sp_auth_sys_role_info.auth = 'DBA' then 1 else 0 endif) as dbaauth,
        sum(if sp_auth_sys_role_info.auth = 'REMOTE DBA' then 1 else 0 endif) as remotedbaauth,
        ISYSROLEGRANT.grantee
        from SYS.ISYSROLEGRANT natural join dbo.sp_auth_sys_role_info()
        where ISYSROLEGRANT.grant_type <> (0x02|0x04)
        and sp_auth_sys_role_info.auth in( 'DBA','RESOURCE','REMOTE DBA' ) 
        group by ISYSROLEGRANT.grantee) as AA
      on(AA.grantee = b.user_id)
 另请参见