Export System-, Rollen- und Objektrechte mit dbms_metadata
In einem Projekt hatte ich kürzlich die Aufgabe System-, Rollen– und Objektrechten diverser Datenbankuser zu extrahieren. Ich hatte mich für das von Oracle bereitgestellte Supplied Package dbms_metadata entschieden, da mir das aufwendige Auslesen aus dem Oracle Data Dictionary für nicht mehr zeitgemäß erschien.
Die Besonderheit bestand zudem darin, lediglich die GRANTs des SYS Users zu exportieren und nicht die Objektberechtigungen anderer DB-USER.
Mit der Procedure dbms_metadata.get_granted_ddl erhalte ich leider alle Rechte/Berechtigungen. Hier ist keine weitere Filterung/Eingrenzung möglich – z.B. gebe mir nur Berechtigungen vom USER SYS.
Lösungsansatz dbms_metadata.get_granted_xml
Ich lasse mir anstelle der generierten DDLs eine XML-Struktur zurück geben. Diese wird mittels XMLTABLE Funtkion aufbereitet, sodass ich mit Standard SQL meine WHERE Bedingung formulieren kann.
Des Weiteren habe ich den ORACLE BUG zum Extrahieren der CONSUMER GROUPS – nur wichtig wenn der RESOURCE MANAGER genutzt wird – umgangen (11.2.0.4.0).
Das folgende SQLPlus Script erstellt ein SQL-File mit den Berechtigungen des gewünschten DB-Users, wobei bei den vergebenen Objektberechtigungen nur die des Users SYS betrachtet werden.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
/*********************************************** * Author: Karsten Besserdich * Firma: Besserdich & Redmann GmbH * Date: 03.11.2014 * URL: http://www.besserdich.com * EMail: karsten.besserdich@besserdich.com * * PARAMETER 1 - Pfad inkl. Name der zu erstellenden SQL-Datei * PARAMETER 2 - Rolle- oder Username von dem die Rechte * extrahiert werden sollen * * Hinweis: Der Aufruf muss vom User sys oder system erfolgen * * Beispielaufruf * sqlplus / as sysdba @extract_grants.sql /tmp/my_user1.sql MY_USER1 ************************************************/ SET LONG 10000000 SET PAGESIZE 0 SET TRIMSPOOL ON SET LINESIZE 2000 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET TERM OFF SET VERIFY OFF DEFINE _FILENAME = &1 DEFINE _USER_ROLE_NAME = &2 spool &_FILENAME Select CASE type_num -- TYPE_NUM 48 ist keine Scheduler Class sondern eine Consumer Group - Bug seitens Oracle? WHEN 48 THEN 'EXEC DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => ''' || x.grantee || ''', consumer_group => ''' || x.obj || ''', grant_option => FALSE)' -- TYPE_NAM 23 ist ein Directory - hier ist die Syntax etwas anders WHEN 23 THEN 'GRANT ' || x.priv || ' ON DIRECTORY ' || x.grantor || '.' || x.obj || ' TO ' || x.grantee || ';' ELSE 'GRANT ' || x.priv || ' ON ' || x.grantor || '.' || x.obj || ' TO ' || x.grantee || ';' END FROM (SELECT XMLTYPE(dbms_metadata.get_granted_xml('OBJECT_GRANT', dtp.grantee)) xml FROM dba_tab_privs dtp where dtp.grantee = '&_USER_ROLE_NAME' and rownum <= 1) t, XMLTABLE ('/ROWSET/ROW/OBJGRANT_T' PASSING t.xml COLUMNS grantee VARCHAR2(30) PATH 'GRANTEE', grantor VARCHAR2(30) PATH 'GRANTOR', priv VARCHAR2(30) PATH 'PRIVNAME', obj VARCHAR2(30) PATH 'BASE_OBJ/NAME', obj_type VARCHAR2(30) PATH 'BASE_OBJ/TYPE_NAME', type_num NUMBER PATH 'BASE_OBJ/TYPE_NUM' ) x WHERE 1=1 AND grantor = 'SYS' /*Hier findet dich Einschränkung statt - Gebe mir alle Objektberechtigungen vom USER SYS*/ UNION ALL Select 'GRANT ' || x.rol || ' TO ' || x.grantee || ';' FROM (SELECT XMLTYPE(dbms_metadata.get_granted_xml('ROLE_GRANT', drp.grantee)) xml FROM dba_role_privs drp where drp.grantee = '&_USER_ROLE_NAME' and rownum <= 1) t, XMLTABLE ('/ROWSET/ROW/ROGRANT_T' PASSING t.xml COLUMNS grantee VARCHAR2(30) PATH 'GRANTEE', rol VARCHAR2(30) PATH 'ROLE' ) x UNION ALL Select 'GRANT ' || x.priv || ' TO ' || x.grantee || ';' FROM (SELECT XMLTYPE(dbms_metadata.get_granted_xml('SYSTEM_GRANT', dsp.grantee)) xml FROM dba_sys_privs dsp where dsp.grantee = '&_USER_ROLE_NAME' and rownum <= 1) t, XMLTABLE ('/ROWSET/ROW/SYSGRANT_T' PASSING t.xml COLUMNS priv VARCHAR2(100) PATH 'PRIVNAME', grantee VARCHAR2(30) PATH 'GRANTEE' ) x; spool off quit |
SCHREIBEN SIE EINEN KOMMENTAR