1 Reply Latest reply: Feb 25, 2013 11:44 AM by 993222 RSS

    Privileges in dba_sys_privs

    978434
      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
          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