NOTE: In addition to the privileges listed below, the database administrator must grant EXECUTE privileges on dbms_pipe and dbms_lock to PUBLIC.
Table 1Type 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. |
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.
Table 2Privilege |
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. |
Table 3Privilege |
Purpose |
|
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. |