User permissions for geodatabases in Oracle

User permissions for geodatabases in Oracle
Release 9.3

The tables in this topic list database privileges for ArcSDE for Oracle users.

Table 1 lists the minimum required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator.

Table 2 lists the permissions needed by the ArcSDE administrator to install the ArcSDE component and upgrade the geodatabase.

Table 3 lists optional privileges that are commonly assigned to ArcSDE for Oracle users.

You can use Oracle's Enterprise Manager to administer user privileges. Or you can use SQL statements to grant and revoke privileges and permissions.

In most cases, owners of individual datasets should use ArcCatalog to set other users' permissions on these datasets. For information on this, see Granting and revoking privileges on datasets.

Note that to perform an upgrade, the ArcSDE administrator must have write permission to either the SDEHOME/geocode directory on the server or the directory to which the TEMP environment variable is set. This is because during an upgrade, ArcSDE attempts to make backups of the locator files in the geocode directory. If that fails, ArcSDE attempts to write the backups to the TEMP directory. If the ArcSDE administrator does not have write permission to either of these directories, a warning is generated and new locators are loaded.

In Oracle databases, the ArcSDE administrator also requires additional permissions in the database to install or upgrade the ArcSDE component. The first table lists the minimum privileges required by the ArcSDE administrator (the permissions the ArcSDE administrator needs just to perform day-to-day functions) and the permissions needed for other types of users. The second table lists the permissions the ArcSDE administrator requires to be able to install or upgrade the ArcSDE component.

NOTE: In addition to the privileges listed below, the database administrator must grant EXECUTE privileges on dbms_pipe and dbms_lock to PUBLIC.

Minimum privileges

Table 1

Type of user	Database permissions	Dataset permissions	Notes
Data viewer	CREATE SESSION	SELECT on database objects	If your database is configured to use shared ArcSDE log files (the default), additional privileges may be needed. See Log file configuration options for more information.
Data editor	CREATE SESSION	SELECT, INSERT, UPDATE, and DELETE on other users' datasets	If your database is configured to use shared ArcSDE log files (the default), additional privileges may be needed. See Log file configuration options for more information.
Data creator	CREATE SESSION CREATE SEQUENCE CREATE TRIGGER CREATE VIEW CREATE TABLE		CREATE VIEW privilege is only needed if the user creates database views, spatial views, or multiversioned views.
ArcSDE administrator	CREATE SESSION CREATE SEQUENCE CREATE TABLE CREATE TRIGGER CREATE PROCEDURE SELECT ANY TABLE		The SELECT ANY TABLE privilege is required to compress the geodatabase. It is also required if using autoregistration with Oracle Spatial. If not using autoregistration, the SELECT ANY TABLE privilege can be revoked, but will have to be regranted before a compress operation is performed. Beginning with Oracle 10g Release 2, access to ORACLE_HOME is more restrictive for increased security. To allow the ArcSDE administrator to access the files in ORACLE_HOME without granting elevated permissions to the ArcSDE administrator, install a compatible Oracle client under the operating system SDE account. On UNIX/Linux, set the client ORACLE_HOME in the SDE user's shell.

ArcSDE administrator installation/upgrade required permissions

The following table lists the privileges that must be granted to the ArcSDE administrator to install or upgrade ArcSDE and the reason the privilege or group of privileges is needed. Some of these privileges can be revoked after installation or upgrade is completed, as noted in the Purpose field and as indicated in the minimum ArcSDE administrator permissions shown in the previous table.

NOTE: If you use geodatabases created in user schemas in Oracle, the same permissions are needed for the schema owner in which the geodatabase resides to create or upgrade his/her geodatabase, except the ADMINISTER DATABASE TRIGGER; that permission is only needed for installing or upgrading the SDE master geodatabase.

Privileges are grouped by the purpose they serve during installation and upgrade.

Table 2

Privilege	Purpose
CREATE SESSION	Connect to Oracle.
CREATE TABLE CREATE TRIGGER	Create and upgrade ArcSDE repository.
CREATE SEQUENCE	Create sequences when the geodatabase is created.
CREATE PROCEDURE	Create and upgrade packages for maintaining the contents of ArcSDE repository tables.
CREATE INDEXTYPE CREATE LIBRARY CREATE OPERATOR CREATE PUBLIC SYNONYM CREATE TYPE CREATE VIEW DROP PUBLIC SYNONYM	Create the Spatial Type for Oracle (ST_Geometry) user-defined data type. CREATE OPERATOR and CREATE INDEXTYPE are also needed to upgrade ArcSDE. These privileges can be revoked after installation or upgrade.
SELECT ANY TABLE	Autoregistration with the geodatabase of Oracle Spatial layers created by third-party applications and for upgrading geodatabase contents. It is also necessary to perform an upgrade.
ALTER ANY INDEX ALTER ANY TABLE ANALYZE ANY CREATE ANY INDEX CREATE ANY PROCEDURE CREATE ANY SEQUENCE CREATE ANY TRIGGER CREATE ANY VIEW DROP ANY INDEX DROP ANY PROCEDURE DROP ANY SEQUENCE DROP ANY TABLE DROP ANY VIEW EXECUTE ANY PROCEDURE SELECT ANY SEQUENCE	Upgrade geodatabase contents.
ADMINISTER DATABASE TRIGGER	Allows creation of database event triggers needed to modify the ST_GEOMETRY_COLUMNS and ST_GEOMETRY_INDEX tables if a table with an ST_Geometry is dropped, altered, or renamed using SQL This privilege can be revoked after installation or upgrade.

Common optional privileges

Many organizations choose to take advantage of additional Oracle features to further enhance the capabilities of their geodatabases. Several common optional privileges for the ArcSDE administrator and the purposes of the privileges are listed in the following table. Privileges are grouped by the purpose they serve.

Table 3

Privilege	Purpose
ALTER SESSION PLUSTRACE	Enables SQL tracing, SQL*Plus AUTOTRACE feature, and modifying session-specific initialization parameters for performance tuning and troubleshooting Create PLUSTRACE role by running ORACLE_HOME/sqlplus/admin/plustrce.sql
ADVISOR (Oracle10g only) ALTER ANY INDEX ANALYZE ANY SELECT ANY DICTIONARY CREATE JOB (Oracle10g only)	Grant to the ArcSDE administrative user, to allow this user to monitor Oracle and perform basic maintenance tasks Useful for organizations where the ArcSDE administrator is not the Oracle DBA
CREATE DATABASE LINK CREATE MATERIALIZED VIEW CREATE VIEW	Useful for integrating the geodatabase with other nonspatial databases in the enterprise
RESTRICTED SESSION	Enables the ArcSDE administrative user to perform maintenance while the database is online, but not accessible by end users
UNLIMITED TABLESPACE	Granting this privilege to the ArcSDE administrator for installation and upgrade ensures that there is sufficient storage space in the ArcSDE administrator's tablespace in the database to complete the installation or upgrade; this privilege can be revoked after installing or upgrading ArcSDE if you have set quotas for space management. NOTE: You must assign quotas before granting the UNLIMITED TABLESPACE system privilege; otherwise, you will not be able to alter the quota through the Oracle Enterprise Manager Console after the fact. See Tuning memory in Oracle for information on using storage quotas.

User permissions for geodatabases in Oracle

User permissions for geodatabases in Oracle

See Also