English

How To: Repair permissions on ArcSDE repository tables and objects for the public role in ArcSDE for Oracle geodatabases

Summary

During the install and configuration of an ArcSDE geodatabase, grants are made to the PUBLIC role on tables for DELETE, INSERT, SELECT AND UPDATE permissions. Additionally, there are grants given to the PUBLIC role to add EXECUTE to various SDE and SYS objects.

These permissions are needed to provide access to geodatabase functionality, such as viewing certain geometry types and performing administration tasks.

10.0 and earlier - prior to 10.1 the ArcSDE Post Installation Wizard made these grants.
10.1 and newer - starting at version 10.1 the Create and Enable Enterprise Geodatabase tools perform these grants.

Warning:
Use of the following scripts should be done with caution: The troubleshooting steps within this article should be guided by Esri Support Analysts and it should never be performed on a production instance without taking a high level of precaution. It is recommended to test this first on a separate staging server and make a full backup of database before implementation and ensure that the backup and restore workflows have been thoroughly tested.

Procedure

If these permissions cannot be granted to the PUBLIC role they must be explicitly granted to the GIS users.

Note:
Certain EXECUTE permissions must be granted to individual users, including the SDE user. Granting these permissions via a user-defined role does not work.
Below are a few examples of issues that may arise from not having adequate permissions granted to the public role:
• ST_GEOMETRY feature classes do not display geometry
• Failure to connect to the geodatabase

The following select statement helps to query objects within a database that the public role has EXECUTE on. This can be used in comparison between two databases / instances:

select table_name from dba_tab_privs where privilege='EXECUTE' and grantee='PUBLIC' and owner='SDE' order by table_name;

The following select statement can be run against a new functional database of the same DBMS and geodatabase version/release in which privileges have not been altered. This can then be granted on the database experiencing symptoms from permissions being revoked from the public role:

ORACLE:

select 'GRANT '||privilege||' on '||OWNER||'.'||table_name||' to PUBLIC'||decode(grantable,'YES',' with grant option;',';') 
from dba_tab_privs where grantor='SDE' and grantee='PUBLIC';