Oracle: Rechte auf Tabellen auslesen

edit | delete

Autor: Varsamis Karamanidis (Macky); Ralf v.d.Mark

eingetragen: Donnerstag, 11. März 2021 um 14:49 Uhr (10/2021 Kalenderwoche)

geändert: Donnerstag, 11. März 2021 um 14:57 Uhr (10/2021 Kalenderwoche)

Keywords: Oracle Rechte Tabellen auslesen roles privileges granted

Kategorien: DB: Oracle,

Text:

SQL Tests auf User Rollen Rechte
https://qastack.com.de/dba/14901/oracle-list-users-with-access-to-...

Quellcode:   

-- #
-- # Listen Sie alle Benutzer auf, denen Rollen zugewiesen wurden
select * from dba_role_privs;
-- Listet alle Rollen auf, die einem Benutzer zugewiesen wurden
select * from dba_role_privs where GRANTEE = 'AHSTRDB';
select * from dba_role_privs where GRANTEE = 'AHST_PFLEGE';
-- # Listen Sie alle Benutzer auf, denen eine bestimmte Rolle zugewiesen wurde
select * from dba_role_privs where granted_role = 'AHSTRDB_PFLEGE';
select * from dba_role_privs where granted_role = 'R_AHST_PFLEGE';
-- # Liste die einem Benutzer zugewiesen wurde mit bestimmte Rolle
select * from dba_role_privs
where GRANTEE = 'AHST_PFLEGE'
  and granted_role = 'R_AHST_PFLEGE';

-- Listet alle Berechtigungen auf, die einem Benutzer erteilt wurden
select
        lpad(' ', 4*level) || granted_role "User, his roles and privileges"
from
    (
        /* THE USERS */
        select
            null     grantee,
            username granted_role
        from
            dba_users
        where
                username like upper('%ahst%')
            /* THE ROLES TO ROLES RELATIONS */
        union
        select
            grantee,
            granted_role
        from
            dba_role_privs
            /* THE ROLES TO PRIVILEGE RELATIONS */
        union
        select
            grantee,
            privilege
        from
            dba_sys_privs
    )
start with grantee is null
connect by grantee = prior granted_role;

-- Auflisten, auf welche Tabellen eine bestimmte Rolle z.B. SELECT Zugriff gewährt.
-- select * from role_tab_privs where role='DBA' and privilege = 'SELECT';

-- Rollen von User AHST_PFLEGE
SELECT * FROM dba_role_privs WHERE GRANTEE = 'AHST_PFLEGE';

-- Alle Tabellen auflisten, aus denen ein Benutzer AUSWÄHLEN kann.
select * from dba_tab_privs where GRANTEE ='AHSTRDB_PFLEGE' and privilege = 'SELECT';
select * from dba_tab_privs where GRANTEE ='R_AHST_PFLEGE' and privilege = 'SELECT';

-- Alle Rechte pro Tabelle für Rolle AHSTRDB_PFLEGE
SELECT * FROM dba_tab_privs WHERE GRANTEE = 'AHSTRDB_PFLEGE';
-- Alle Rechte pro Tabelle für Rolle R_AHST_PFLEGE
SELECT * FROM dba_tab_privs WHERE GRANTEE = 'R_AHST_PFLEGE';

-- Alle Rechte pro Tabelle für Rolle R_AHST_PFLEGE
-- GROUP_CONCAT mit Allen Feldern
-- select grantee, owner, table_name, grantor, LISTAGG(privilege, ', ') WITHIN GROUP (ORDER BY privilege) "privilege",
--        grantable, hierarchy, common, type, inherited
-- from dba_tab_privs
-- where grantee ='R_AHST_PFLEGE'
-- GROUP BY grantee, owner, table_name, grantor, grantable, hierarchy, common, type, inherited
-- ;

-- Alle Rechte pro Tabelle für Rolle R_AHST_PFLEGE
-- GROUP_CONCAT nicht mit allen Feldern
select grantee, owner, grantor, table_name, type,
       LISTAGG(privilege, ', ') WITHIN GROUP (ORDER BY privilege) as privilege
from dba_tab_privs
where grantee ='R_AHST_PFLEGE'
--   and owner = 'AHSTRDB'
--   and table_name = 'AGS_KNKN'
GROUP BY grantee, owner, grantor, table_name, type
;


-- Alle Rollen mit Rechten vom Owner AHSTRDB pro Tabelle
SELECT *
FROM TABLE_PRIVILEGES
WHERE OWNER = 'AHSTRDB'
-- AND GRANTEE = 'AHSTRDB_PFLEGE' -- R_AHST_PFLEGE
-- AND DELETE_PRIV = 'N'
;

--Alle Benutzer auflisten, die für eine bestimmte Tabelle AUSWÄHLEN können
-- (entweder durch Zuweisung einer relevanten Rolle oder durch direkte Zuweisung
-- (dh durch Zuweisung von select on atable to joe))?
-- Das Ergebnis dieser Abfrage sollte auch anzeigen, über welche Rolle der Benutzer
-- über diesen Zugriff verfügt oder ob es sich um eine direkte Erteilung handelt.

select * from role_tab_privs;
select * from dba_role_privs;


select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
  and table_name = 'AGS_KNKN'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'AGS_KNKN'
;