Non_Sys User Privilege-优快云博客

本文提供了一段SQL查询语句，用于在Oracle数据库中按权限排名并筛选特定用户，包括系统管理员、普通用户等。通过使用UNION ALL、PARTITION BY、ORDER BY和RANK()函数，实现对用户权限的详细统计和排名，适用于数据库权限管理场景。

1
SELECT GRANTEE,
PRIVILEGE,
RANK() OVER (PARTITION BY GRANTEE ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_TAB_PRIVS WHERE GRANTEE IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS'')
GROUP BY   GRANTEE, PRIVILEGE
UNION ALL
SELECT GRANTEE,
PRIVILEGE,
RANK() OVER (PARTITION BY GRANTEE ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_SYS_PRIVS WHERE GRANTEE IN/NOT IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS'')
GROUP BY   GRANTEE, PRIVILEGE ;

2
SELECT DISTINCT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS')
UNION ALL
SELECT DISTINCT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS
WHERE GRANTEE IN/NOT IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS'')
ORDER BY GRANTEE
3:

select GRANTEE,PRIVILEGE from (
select distinct   GRANTEE,
PRIVILEGE from (
SELECT distinct GRANTEE,
PRIVILEGE,
RANK() OVER (PARTITION BY GRANTEE ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_TAB_PRIVS WHERE GRANTEE IN(
SELECT USERNAME
    FROM DBA_USERS D
   WHERE USERNAME NOT IN
         (SELECT NAME
            FROM (SELECT SCHEMA#
                    FROM SYS.REGISTRY$
                  UNION ALL
                  SELECT B.SCHEMA#
                    FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B
                   WHERE A.CID = B.CID) A,
                 SYS.USER$ B
           WHERE A.SCHEMA# = B.USER#)
AND    D.default_tablespace NOT IN('SYSAUX','SYSTEM','USERS')
)
GROUP BY   GRANTEE, PRIVILEGE
UNION
SELECT distinct GRANTEE,
PRIVILEGE,
RANK() OVER (PARTITION BY GRANTEE ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_SYS_PRIVS WHERE GRANTEE IN(
SELECT USERNAME
    FROM DBA_USERS D
   WHERE USERNAME NOT IN
         (SELECT NAME
            FROM (SELECT SCHEMA#
                    FROM SYS.REGISTRY$
                  UNION ALL
                  SELECT B.SCHEMA#
                    FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B
                   WHERE A.CID = B.CID) A,
                 SYS.USER$ B
           WHERE A.SCHEMA# = B.USER#)
AND    D.default_tablespace NOT IN('SYSAUX','SYSTEM','USERS'))
GROUP BY   GRANTEE, PRIVILEGE ))
GROUP BY   GRANTEE, PRIVILEGE
order by 1