arcobjects - For a SDE FeatureClasses list all Roles that have been Granted any Privileges on it, and which Privileges each Role have on it

I have searched through the ArcObjects 10 SDK API and googled, but havnt been able to find what I am looking for, so thats the reason for me posting this question.

Q: How can I for a SDE FeatureClasses list all Roles that have been Granted any Privileges on it, and which Privileges each Role have on it?

The equivalent in normal Oracle would be something like this:

SELECT dtp.PRIVILEGE,

       dtp.grantable ADMIN,
       dtp.grantee,
       dtp.grantor,
       dbo.owner,
       dbo.object_type,
       dbo.object_name,
       dbo.status,
       '' column_name
  FROM ALL_TAB_PRIVS dtp, ALL_OBJECTS dbo
 WHERE dtp.TABLE_SCHEMA = dbo.owner AND dtp.table_name = dbo.object_name

       AND dbo.object_type IN ( 'TABLE', 'VIEW' )
UNION
SELECT dcp.PRIVILEGE,
       dcp.grantable ADMIN,
       dcp.grantee,
       dcp.grantor,
       dbo.owner,
       dbo.object_type,
       dbo.object_name,
       dbo.status,

       dcp.column_name
  FROM ALL_COL_PRIVS dcp, ALL_OBJECTS dbo
 WHERE dcp.TABLE_SCHEMA = dbo.owner AND dcp.table_name = dbo.object_name
       AND dbo.object_type IN ( 'TABLE', 'VIEW' )

The query would list out all object of the types in the list provided (in the example I have listed TABLE and VIEW) which privileges have been granted on them like SELECT, UPDATE etc... and to which Roles.

Related Q: SDE FeatureClass Granting and Revoking Privilege with ArcObjects

Answer

I ended up having to query the underlying DBMS, Geodatabase, to get the information I needed. Here is the query I came up with for SDE 10+:

SELECT DISTINCT *

    FROM (SELECT GDBI.PHYSICALNAME NAME, GDBIT.NAME TYPE_NAME
            FROM SDE.GDB_ITEMS GDBI, SDE.GDB_ITEMTYPES GDBIT
           WHERE GDBI.TYPE = GDBIT.UUID) GDBITJ,
         (SELECT A.OWNER,
                 A.GRANTABLE ADMIN,
                 A.GRANTEE,
                 A.GRANTOR,
                 A.PRIVILEGE,
                 A.TABLE_NAME
            FROM DBA_TAB_PRIVS A

           WHERE     A.PRIVILEGE IN ('SELECT', 'UPDATE', 'INSERT', 'DELETE')
                 AND A.OWNER NOT IN ('SYS', 'SDE', 'SYSTEM')) AR
   WHERE        TYPE_NAME = 'Feature Class'
            AND UPPER (REGEXP_SUBSTR (NAME,
                                      '.[^.]+.',
                                      1,
                                      2)) = UPPER (TABLE_NAME)
         OR UPPER (NAME) = UPPER (TABLE_NAME)
ORDER BY TABLE_NAME;

If somebody have tips and tricks or improvements to this query please let me know. As Travis stated in his answer, I will have to make such a query for all database types, also, the SDE Datamodel was changed from SDE 9.3.1 to SDE 10+ so that also needs to be taken into account.

Also there is no guarantee that the SDE Schema you want to query will be named SDE in SDE 10+, as a database can hold multiple SDE Schemas. However in the table SDE.Instances all schemas will be listed.

EDIT1:

The comments on the answer I think are rather useful, so I added them to the answer. Here is how to retrieve the schema name for an IWorkspace instance:

public string GetSchema()
{
    var versionWorkspace = _workspace as IVersionedWorkspace;
        if (versionWorkspace == null)
            throw new InvalidCastException("Failed to cast IWorkspace to IVersionedWorkspace");
    try

    {
        var versionName = versionWorkspace.DefaultVersion.VersionName;
        var schema = versionName.Split('.')[0];
        return schema;
    }
    catch (Exception ex)
    {
        throw new InvalidOperationException("Failed to split versionName to get schemaName for the current SDE Workspace", ex);
    }
}

With the use of the GetSchema() method we can now modify the Query in the top to use the schema we get back as an input parameter, how you modify the query string is up to you, you only need to switch out the SDE.* schema name with the one retrieved with GetSchema() e.g: OTHER_SDE_SCHEMA.*