понедельник, 3 июля 2017 г.

Generate create user statement

BEGIN
dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
END;
/

WITH usr AS ( 
  SELECT ',USER1,USER2,' usr FROM dual
  ),
statements(txt, username, ord) AS (  
  SELECT dbms_metadata.get_ddl('USER', username), username, 1 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%'
  UNION ALL
  SELECT dbms_metadata.GET_GRANTED_DDL('ROLE_GRANT', username), username, 2 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%' 
    AND EXISTS (SELECT NULL FROM dba_role_privs WHERE grantee = username)
  UNION ALL
  SELECT dbms_metadata.GET_GRANTED_DDL('SYSTEM_GRANT', username), username, 3 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%'
   AND EXISTS (SELECT NULL FROM dba_sys_privs WHERE grantee = username)
  UNION ALL
  SELECT dbms_metadata.GET_GRANTED_DDL('OBJECT_GRANT', username), username, 4 AS ord
  FROM dba_users u, usr WHERE usr LIKE '%,' || username || ',%'
   AND EXISTS (SELECT NULL FROM dba_tab_privs WHERE grantee = username)
  )
SELECT txt
FROM statements
ORDER BY username, ord
;

Комментариев нет: