Discussion on SDE user usage in ArcSDE spatial Database (reproduced)

As a spatial database solution, ARCSDE is widely used, and this article will attempt to describe how SDE works and briefly describe how SDE users are used in a spatial database.
How ARCSDE Works
ARCSDE is a middleware technology, and it is not able to store spatial data, its role can be understood as the "spatial expansion of the database." In the Oracle-based ARCSDE spatial database, ARCSDE holds a series of Oracle objects for managing spatial information. These objects are collectively known as the Data Archive (Repository), which contains spatial data dictionaries and ARCSDE software packages. ARCSDE requires the SDE user to manage the spatial repository, similar to the need for SYS user management data dictionaries in Oracle. Oracle's data dictionary is stored in the system table space, and accordingly, when storing the ArcSDE spatial repository, you also need to use a specific table space. Typically, for convenience, the default name is also the Tablespace Management Spatial data dictionary for SDE.
In the working mechanism of ARCSDE, the SDE user is responsible for the interaction between ARCSDE and Oracle, ensuring the read/write consistency of the spatial database by maintaining the spatial data dictionary in the SDE mode and running the packages in its schema. During the start of the ARCSDE service, the SDE user authenticates with Oracle and creates and maintains an Oracle session connection, which is the giomgr, the ARCSDE server management process, which persists and is responsible for listening for user connection requests. Assign the appropriate GSRVR management process (see note 1) To maintain the spatial data dictionary.
Security for ARCSDE
The security mechanism of ARCSDE relies entirely on Oracle, where spatial database users (including SDE) require Oracle's user password to access spatial data, and ARCSDE itself does not store any authentication information.
In Oracle, the minimum system permission setting requirements for the SDE user are:
Create procedure/create Table/create sequence/create trigger/create session
This shows that SDE also belongs to the normal user in Oracle database.
For Oracle, although SDE belongs to a non-DBA user, SDE is a special position in the ARCSDE schema and is an ARCSDE administrator. Only SDE can perform certain tasks, such as starting/stopping an ARCSDE service, terminating some user connections, compressing a multi-version database, and so on. The SDE user is not a true Oracle DBA user, but during ARCSDE work, the software does some specific object permissions operations. Therefore, the SDE user should be treated as an Oracle DBA user, and as with SYS or system, its password must be strictly protected.
In the ArcSDE spatial database, from the Rights management level, users can be divided into two main categories:
1, Spatial database administrator, only and can only be SDE
2, the general users of the spatial database, including the creation, browsing the spatial data in addition to the SDE of other Oracle users using the SDE user, highly recommended to follow the two principles:
SDE user does not facilitates spatial data
The table space of the SDE store repository is not used to hold spatial data
Special object permissions for the SDE user
The SDE user, as the general user of the Oracle database, can create its own tables or stored procedures, and as an ARCSDE spatial database administrator, ArcSDE automatically grants SDE some object permissions in the object permissions settings. These object permissions are required by the SDE user to ensure the integrity of the ARCSDE geodatabase. The general user of the spatial database when creating a new Geodatabase object, ARCSDE grants the SDE user permission for these new objects. For example, a ACTC user creates a feature class called Country Geodatabase, where the corresponding support table for country (i.e. B table, business table) is generated simultaneously, i.e. F table (Feature table) and S table (Spatial Index Table). At this point, the SDE user will automatically get the SELECT permission for the tables of country, F, and S tables. When the user registers country as versioned, a table (additions table) and a D table (deletions table) are generated for the record editing information in ACTC mode. At this point, the SDE user obtains the Select/insert/update/delete permissions for the A and D tables. During these object permission grants, the ACTC user did not get any notification information.
In ArcGIS Desktop's spatial database connection, it does not reflect these object permissions of the SDE user, if using the SDE user for spatial database connection, only can observe the country table in the above example, the other support tables are filtered out. If you need to fully view the object permissions that the SDE user is granted, you can get it through the USER_TAB_PRIVS_RECD view of Oracle.
What specific work does the SDE user do
In a spatial database, an administrator's SDE completes operations that the general user cannot complete, as illustrated below:
1. Start/Stop ARCSDE service
Only SDE can interact with Oracle to start or stop the ARCSDE service. Actions are:
Sdemon–o Start/shutdown (Start/stop)
The SDE user password needs to be submitted.
2. Terminating a space user connection
In a spatial database connection, sometimes when a connection process hangs or is illegally connected, you can use SDE to terminate its connection. Actions are:
First, get information about the connection from the list of connections
Sdemon–o Info–i Users
After getting the connection ID that needs to be terminated, use the KILL command
Sdemon–o Kill–t < connection id>
< Connect id> Completing this operation requires the SDE user password to be submitted.
3. Compress multi-version database (multi-versioned geodatabase)
In ArcSDE Geodatabase, with the work of data editing, the records of the corresponding metadata table in the SDE spatial repository and the table A and D in user mode increase gradually, which will affect the efficiency of spatial data access, so it is often necessary to compress the database version. After you have determined that there are no locks on the database, you can compress your work by doing the following:
Sdeversion–o Compress–u SDE
Completing this operation requires the SDE user password to be submitted.
As an ARCSDE administrator, SDE has to do some other work. For example, the SDE user can use the Sdedbtune command to improve database efficiency when controlling data segments and index segments of spatial data.
Note 1: This is the way the traditional ARCSDE application Server connection (Application-server connection) works, in this way, The ARCSDE server process (GIOMGR) assigns a process named Gsrvr to be fully responsible for the meta data communication between the client and the server. After the ArcSDE 8.1 release, a new connection method, the Direct Connect method (Direction-connection), in which the GSRVR process functionality is embedded in the client connection application, such as Arccatalog or other ESRI software products, appears. In this way, the functionality of GSRVR is done by the client connection application.
Test environment: ArcSDE 9.0,oracle 9.2.0.4.0,windows NT
Reference:
1, Config_tuning_gd_oracle
2. Understanding ArcSDE
3. Website: Support.esri.com