This discussion is archived
1 Reply Latest reply: Feb 25, 2013 9:44 AM by 993222 RSS

Privileges in dba_sys_privs

978434 Newbie
Currently Being Moderated
Hi All, I am tyring to return a dataset to XML. I am attempting to get a list of ALL the roles in my database, and then all the privileges assigned to each role. The issue i seem to be running into is that the query only pulls back info for the DBA privilege. any idea what might be wrong here?

SELECT XMLAgg(XMLElement("ROLE", A.GRANTED_ROLE,
(SELECT XMLAgg(XMLELement("PRIV", B.PRIVILEGE))FROM sys.dba_sys_privs B
)))
FROM sys.dba_role_privs A JOIN sys.dba_sys_privs B
ON A.GRANTED_ROLE = B.GRANTEE
  • 1. Re: Privileges in dba_sys_privs
    993222 Newbie
    Currently Being Moderated
    The reason you are only getting the DBA roll back is because you are aggregating all the rolls with the initial XMLAGG. you should remove the fist XMLAGG and then add a group by clause at the end.
    I have taken the liberty of re-writing your query and cleaning up the XML. This should get you what you want.


    SELECT (XMLElement("ROLE", XMLATTRIBUTES(A.ROLE AS NAME),
    (SELECT XMLAgg(XMLELement("PRIVILEGE", XMLATTRIBUTES(B.PRIVILEGE as NAME)))FROM dba_sys_privs B
    Where a.role = b.grantee
    )))
    FROM DBA_ROLES A JOIN dba_sys_privs B ON A.ROLE = B.GRANTEE
    GROUP BY A.ROLE ORDER BY A.ROLE;


    Hope that helps!
    -Neal

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points