Grant ALTER SYSTEM Privileges to Data Owner

Question

As an Oracle geodatabase administrator, I have data owners who are competent when it comes to managing their datasets, tables, etc. in the geodatabase. One thing they can't do as data owners is manage any connections that may be locking any of their tables. Any time they need to do any sort of maintenance on their tables (changing a domain for example), they would need to reach out to me to remove locks.

It looks like data owners in Oracle may be able to be given ALTER SYSTEM privileges which would allow them to remove connections from the database (see this doc).

Would I be able to give data owners this one privilege without giving them a whole slew of other privileges? If so, could there be any drawbacks to granting ALTER SYSTEM privileges to a data owner?

VinceAngelo · Answer

Rather than give generous permissions to the users, create a power_user role, then 
grant that role access to run a utility procedure with the minimum needed privileges, 
and grant the power_user role privilege to execute that procedure. In this way you only 
grant the necessary access to terminate users according to the rules established in the 
PL/SQL procedure (the PL/SQL equivalent of Unix sudo or some other setuid script).

- V

ChrisUnderwood · Answer

Hello @JoshBillings , it looks like you want to grant a lesser privilege than ALTER SYSTEM but still be able to disconnect users from the database. If there were such a privilege I would expect the docs to list that, so I think you will need to use it as documented.

This approach seems a little concerning however "need to do any sort of maintenance ... reach out to me to remove locks.". The locks are normally there to protect the tables from structural changes etc while it is being used. So if someone is editing the data or there is a service using it, then a better answer might be to finish editing or stop the service so that the lock disappears.

Note, you may be able to disable schema locking on your services.
https://enterprise.arcgis.com/en/server/latest/administer/windows/disabling-schema-locking-on-a-map-service.htm#ESRI_SECTION1_8923E3BAD2C5486194DD8540FBE03CDF

Having only the SDE user (presumably you) able to disconnect users seems correct, all others users should ideally be working in a way that would not need them to disconnect other users to remove their locks.