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?

4709
3
Jump to solution
01-19-2015 11:14 PM
Highlighted
New Contributor

As per the below resources, ALTER SYSTEM” is required to disconnect Geodatabase Connections especially with the new “direct-connect” approach

this permissions is being considered as  an “excessive permission” from one of our end-user.

Any help will be highly appreciated.

Resource Links:

The geodatabase administrator in Oracle

http://resources.arcgis.com/en/help/main/10.2/index.html#//002n0000002q000000

User privileges for geodatabases in Oracle Release 10.2.x

http://resources.arcgis.com/en/help/main/10.2/index.html#//002n0000002v000000

User permissions for geodatabases in Oracle Release 9.3 

http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?topicname=user_permissions_for_geodatabases_in_o...

ArcSDE Administratoin Commmands

http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/Support_files/admincmdref.htm

What is an ArcSDE service?

http://resources.arcgis.com/en/help/main/10.2/index.html#//002900000045000000

A quick tour of connections to enterprise geodatabases

http://resources.arcgis.com/en/help/main/10.2/index.html#/A_quick_tour_of_connections_to_enterprise_...

Properties of a direct connection to an ArcSDE geodatabase

http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=Properties%20of%20a%20direct%20connect...

About stopping an ArcSDE service

http://webhelp.esri.com/arcgisdesktop/9.3/body.cfm?id=2850&pid=2845&topicname=Stopping%20an%20ArcSDE...

The giomgr.defs file and the SERVER_CONFIG system table

http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=The_giomgr.defs_file_and_the_SERVER_CO...

What type of maintenance is needed for a geodatabase?

http://resources.arcgis.com/en/help/main/10.2/index.html#/What_type_of_maintenance_is_needed_for_a_g...

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

ALTER SYSTEM

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm

Oracle® Database SQL Reference 10g Release 2 (10.2)

ALTER SYSTEM

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2013.htm

Tags (1)
Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
New Contributor III

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

3 Replies
Highlighted
New Contributor III

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

Highlighted
New Contributor

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!

Reply
0 Kudos
Highlighted
Occasional Contributor II

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