Can we disconnect database connections to the ArcSDE Geodatabase from “Oracle” side, instead of using “sde” account and this won’t affect the Geodatabase system? if this is not possible can anyone please elaborate technically the justification?

Yes, it is possible to disconnect user from geodatabase from Oracle side HOWEVER it may leave an orphan entry in process_information table in SDE schema (or any other user schema if any). You can read more about this table on following link:

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

Entries in this table without actual connection to the database may make 'ArcGIS' think that there are still connections to the database. So we cannot say that a user was successfully disconnected from the 'geodatabase' unless a row corresponding to it is removed from 'process_information' table.

Having said that the other way would be to disconnect the user from Oracle side and then manually delete its corresponding row from 'process_information' table. But ESRI may not recommend this approach.

On the other hand, end user's concern about 'ALTER_SYSTEM' privilege is also correct since it enables user to alter other things (apart from disconnecting user) as well.

Hope this helps!

View solution in original post

FalconESRIKW · ‎01-20-2015

Yes, it is possible to disconnect user from geodatabase from Oracle side HOWEVER it may leave an orphan entry in process_information table in SDE schema (or any other user schema if any). You can read more about this table on following link:

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

Entries in this table without actual connection to the database may make 'ArcGIS' think that there are still connections to the database. So we cannot say that a user was successfully disconnected from the 'geodatabase' unless a row corresponding to it is removed from 'process_information' table.

Having said that the other way would be to disconnect the user from Oracle side and then manually delete its corresponding row from 'process_information' table. But ESRI may not recommend this approach.

On the other hand, end user's concern about 'ALTER_SYSTEM' privilege is also correct since it enables user to alter other things (apart from disconnecting user) as well.

Hope this helps!

EdmarTabamo1 · ‎01-25-2015

Hi Falcon,

Thank you for the information, I got the same advice from Esri tech support regarding the orphaned entries to occur within the SDE repository tables if the user will not going disconnect connections using the geodatabase administrator user, please find below additional information from Esri Technical Support.

For security purposes, the Oracle DBA at the organization could be an in-charge of the SDE user and revoke the ALTER SYSTEM and SELECT_CATALOG_ROLE from this account until connections need to be killed and then can be re-granted. Alternatively, the Oracle DBA can be in charge of the SDE user and not circulate the password to anyone, and they would subsequently be in charge of disconnecting users and the geodatabase administrator user.

Appreciate your help!

MartinAmeskamp · ‎02-18-2015

Hi Edmar,

if a geodatabase administrator needs high system privileges for certain activities (e.g. disconnecting users) and you are uncomfortable with assigning the necessary Oracle system privileges, why not write a procedure (SYS.DISCONNECT_USER) that performs the action with DBA privileges, and then grant your geodatabases admin execution privileges on that procedure?

Martin