minimal privileges to sde user after geodb creation

[giuseppeprocino]

Dear all,
I created oracle11gr2 geodatabase using "Create enterprise geodatabase" toolset.

http://resources.arcgis.com/en/help/main/10.1/index.html#//002n0000002v000000

This procedure has created a sde user into oracle with the following privileges/roles:

privileges:
ADMINISTER DATABASE TRIGGER
ALTER ANY INDEX
ALTER SYSTEM
CREATE ANY INDEX
CREATE ANY TRIGGER
CREATE ANY VIEW
CREATE INDEXTYPE
CREATE LIBRARY
CREATE OPERATOR
CREATE PROCEDURE
CREATE PUBLIC SYNONYM
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DROP ANY INDEX
DROP ANY VIEW
DROP PUBLIC SYNONYM
SELECT ANY TABLE
roles:
SELECT_CATALOG_ROLE

Now, I would like revoke the privileges that are't necessary.   I would like grant sde the priviges necessary to execute typical geodatabase management operation as registrer/unregister feature class, compress, analyze and disconnect user.

Which privileges I must revoke/grant?

In this document (http://resources.arcgis.com/en/help/main/10.1/index.html#//002n0000002v000000) there are the following kinds of users and relative privileges:
- Oracle SDE user privileges for creating an SDE master geodatabase
- Oracle SDE user privileges for upgrading an SDE master geodatabase
- Oracle privileges for creating a user-schema geodatabase
- Oracle privileges for upgrading a user-schema geodatabase
- Common optional privileges (where there are privileges as ALTER SYSTEM and SELECT_CATALOG_ROLE that are necessary for disconnect the users.
but which are the privileges for an sde user in a production system?

Regards
Giuseppe P.

[WilliamCraft]

The MINIMAL System privileges for the SDE user in Oracle are these:

CREATE SESSION
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE PROCEDURE
SELECT ANY TABLE

However, these won't do everything you mentioned.  If you want to disconnect sessions, you will need to grant DBA role to the SDE user or grant these to SDE instead:

ALTER SYSTEM
SELECT_CATALOG_ROLE

If you want to analyze objects outside of the SDE schema (user-schema geodatabases) then grant these to SDE:

ALTER ANY INDEX
ANALYZE ANY
SELECT ANY DICTIONARY

What you decide should be based on whether or not your ArcSDE administrator is the same person as your Oracle DBA.

[EmadAl-Mousa]

I agree with what William have mentioned, if you the ArcSDE Administrator is NOT the database administrator then its better to minimize the privilege to what is required to perform Geodatabase administration. Otherwise, granting DBA role to "sde" wont be an issue since the account is under the control of the DBA.