SDE User Permissions

access deniedThe SDE user is responsible for several geodatabase maintenance tasks including compression and version management among others.  The SDE user also owns the geodatabase system tables, triggers and procedures.   The DBMS privileges necessary to manage and alter the geodatabase are granted to the SDE user upon creation of the geodatabase during the post installation process.  Therefore it is absolutely unnecessary to grant every permission and add the SDE user to every admin role under the sun.  Sometimes it can even have an adverse effect on performance an usability.  The SDE user will only need membership in the Public role.  I promise.

It is recommended that the ArcSDE administrator and its schema only be used to manage and store ArcSDE system tables. You should create separate user schemas in which to store your ArcSDE data objects, such as feature classes and raster datasets. You should not store these objects in the ArcSDE administrator’s storage space, since you could possibly crash the ArcSDE service by filling up the ArcSDE administrator’s space. Following the practice of storing only system tables in the ArcSDE administrator’s storage space simplifies the management of ArcSDE.

Another good idea is to create a user with the necessary privileges to create and upgrade geodatabases.  Like the SDE user, this account should not be used to create data.

Basically, I beg you…although SYSADMIN or SERVERADMIN or GRANT DBA TO SDE sound really cool and powerful, it is 100% not necessary.  I ask for just a little faith in the trusty ol’ post-install wizard.  Faith that it will grow your SDE user and make it the best little geodatabase administrator it can be.

Advertisements

5 Comments on “SDE User Permissions”

  1. Chane says:

    Thank you very much for the post.

    There is something about permissions sde 10 for oracle that I want to know about. Maybe you can help me.

    What permissions does the sde user needs to be granted in order to compress tables out of his own schema?

    I have found that in sde 9.3, SELECT ANY TABLE was necessary, but not in version 10.

    I’m setting up a versioned geodatabase with different user schemas. Whenever I try to compress the geodatabase, I use the sde account, but never works.

    I did set the geodatabase in a standard post-instalation process. After that, I added some users. Those users imported data into their own schemas (featureclasses, tables, relationships, topologies, etc.). Finally, those users versioned their own datasets. I believe it is a very tipical deploy.

    I have searched through on-line help, but never found the key idea. Maybe I missed something.

    Thank you very much

    • Chane says:

      I forgot to mention that we use Oracle 10g beneath arcsde and that the proper SP3 was installed

      • Ken says:

        Hi, thanks for reading the blog. I appreciate the comments and questions.

        To own and administer a user-schema geodatabase, the schema owner must have nearly all the same permissions as the sde user. This holds true in version 10 as well.

        These are the permissions are that required to create and administer a geodatabase in a user’s schema. The last 8
        can be revoked after the geodatabase is compressed.

        • CREATE SESSION
        • CREATE TABLE
        • CREATE TRIGGER
        • CREATE PROCEDURE
        • SELECT ANY TABLE
        • CREATE OPERATOR
        • CREATE INDEXTYPE
        • ALTER ANY INDEX
        • CREATE ANY INDEX
        • CREATE ANY TRIGGER
        • CREATE ANY VIEW

        Grant these to the user that owns the database and compress. You can revoke the last 7 after the compress is complete. I’ll keep an eye out for anything relevant and send it your way. Thanks again.

  2. […] SDEINTERCEPT blog where Ken posts ArcSDE […]

  3. Johne112 says:

    You have brought up a very good details , thankyou for the post. dkkdgadgkgek


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s