Kategorien
SQL

Tabellenrechte für einen Benutzer abfragen – Oracle und MSSQL Statements

Mit diesem Statement kann man prüfen welche Rechte ein User in einem bestimmten Schema besitzt. Auch die PUBLIC Rechte werden dabei geprüft.

Oracle


select table_name, max(sel) as sel, max(ins) as ins, max(upd) as upd, max(del) as del from 
(
select table_name, nvl2(privilege, 1, 0)  as sel, 0 as ins, 0 as upd, 0 as del from ALL_TAB_PRIVS where grantee in('MYUSER','PUBLIC') and PRIVILEGE = 'SELECT' AND TABLE_SCHEMA = 'SO'
union
select table_name, 0, nvl2(privilege, 1, 0), 0, 0 from ALL_TAB_PRIVS where grantee in('MYUSER','PUBLIC') and PRIVILEGE = 'INSERT' AND TABLE_SCHEMA = 'SO'
union 
select table_name, 0, 0, nvl2(privilege, 1, 0), 0 from ALL_TAB_PRIVS where grantee in('MYUSER','PUBLIC') and PRIVILEGE = 'UPDATE' AND TABLE_SCHEMA = 'SO'
union 
select table_name, 0, 0, 0, nvl2(privilege, 1, 0) from ALL_TAB_PRIVS where grantee in('MYUSER','PUBLIC') and PRIVILEGE = 'DELETE' AND TABLE_SCHEMA = 'SO'
)  group by table_name order by table_name
 

Ergebnis:

SQL-Server

Das gleiche Statement für SQL-Server:

SELECT SYS.OBJECTS.NAME AS TABLE_NAME,    
MAX(CASE SYS.DATABASE_PERMISSIONS.PERMISSION_NAME WHEN 'SELECT' THEN 1 ELSE 0 END) sel,      
MAX(CASE SYS.DATABASE_PERMISSIONS.PERMISSION_NAME WHEN 'INSERT' THEN 1 ELSE 0 END) ins,      
MAX(CASE SYS.DATABASE_PERMISSIONS.PERMISSION_NAME WHEN 'UPDATE' THEN 1 ELSE 0 END) upd,       
MAX(CASE SYS.DATABASE_PERMISSIONS.PERMISSION_NAME WHEN 'DELETE' THEN 1 ELSE 0 END) del    
FROM SYS.DATABASE_PERMISSIONS     
JOIN SYS.OBJECTS ON SYS.DATABASE_PERMISSIONS.MAJOR_ID = SYS.OBJECTS.OBJECT_ID      
JOIN SYS.SCHEMAS ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID      
JOIN SYS.DATABASE_PRINCIPALS ON SYS.DATABASE_PERMISSIONS.GRANTEE_PRINCIPAL_ID = SYS.DATABASE_PRINCIPALS.PRINCIPAL_ID      
WHERE SYS.OBJECTS.TYPE IN ('U','V') AND SYS.DATABASE_PERMISSIONS.STATE_DESC = 'GRANT'  
AND SYS.DATABASE_PRINCIPALS.NAME = 'MYUSER' AND SYS.SCHEMAS.NAME = 'dbo'
GROUP BY SYS.SCHEMAS.NAME, SYS.OBJECTS.NAME, SYS.OBJECTS.TYPE_DESC, SYS.DATABASE_PRINCIPALS.NAME  

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.