SDE FeatureClass Privilege Management with ArcObjects

[SanderStruijk1]

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

Q1: How can I with ArcObjects list all Oracle privileges granted on SDE FeatureClasses to Roles?

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.

Now over to SDE FeatureClasses again, with that information at hand I would like to start and GRANT or REVOKE Privileges on the SDE FeatureClasses to/from Roles according to external configurations

Q2: How can I with ArcObjects GRANT or REVOKE Privileges like SELECT, UPDATE on SDE FeatureClasses to/from Roles?

The equivalent in normal Oracle would be something like this:

GRANT SELECT ON SCHEMA.TABLE TO SOME_ROLE; REVOKE SELECT ON SCHEMA.TABLE FROM SOME_ROLE;

I cant seem to find the interface to perform this task, and it may not exists?

My dream would be to have an Interface like this:

public interface IPrivilegeManager {     void Grant(string Privilege, string schemaOwnerName,                string featureClassName, string roleName);     void Revoke(string Privilege, string schemaOwnerName,                string featureClassName, string roleName); }

[SanderStruijk1]

The same question was posted on gis.stackexchange.com where I got the following answer to Q2... (click link to get answer) http://gis.stackexchange.com/questions/65243/sde-featureclass-privilege-management-with-arcobjects

I Included The answer below

---

[/HR]

You can obtain the name object for the given feature class and cast it to ISQLPrivilege, which allows you to enumerate, grant and revoke permissions.

E.g.:

var name = ((IDataset) featureClass).FullName;
var sqlPrivilege = name as ISQLPrivilege;
if (sqlPrivilege != null)
{
    sqlPrivilege.Grant("USER_OR_ROLE_NAME", (int) esriSQLPrivilege.esriSelectPrivilege, false);
}

[SanderStruijk1 · Accepted solution]

Answer to Q1:

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.

For a SDE FeatureClasses list all Roles that have been Granted any Privileges on it, and which Privi...