topic Re: How to delete old feature classes from Oracle Geodatabase in Data Management Questions

How to delete old feature classes from Oracle Geodatabase

AlexanderGray — Mon, 22 Sep 2014 16:46:11 GMT

I have some featureclasses in an sde database that were created by users no longer in the organization. These have been determined as no longer useful. The problem is only the creator of the data has select and insert/update/delete. I am trying to use the sde account to delete the featureclass but it has insufficient privileges. On feature classes I create, if I assign delete privilege to sde, sde can delete the feature class (other users granted delete can only delete rows!) However, I cannot grant myself (sde) privileges to delete these feature classes. I would like to pyScript this up help with data management so geoproc tools are nice. Any one know how to delete the old stuff? Do I need to use SQL and start messing with the SDE tables?

Re: How to delete old feature classes from Oracle Geodatabase

AsrujitSengupta — Mon, 22 Sep 2014 17:02:31 GMT

You have to use the data owner to delete those old data. So connect as that user to delete those data.

In case you are unaware of the credentials, ask your DBA to alter the password for that user and then try to connect.

Re: How to delete old feature classes from Oracle Geodatabase

AlexanderGray — Mon, 22 Sep 2014 17:14:35 GMT

We use OS authentication so I cannot log in as another user. The passwords are not stored in the database. It would also be a major policy violation to impersonate another user...

Re: How to delete old feature classes from Oracle Geodatabase

AsrujitSengupta — Mon, 22 Sep 2014 17:17:39 GMT

Hence my suggestion of requesting your DBA to alter the password and not try yourself.

Re: How to delete old feature classes from Oracle Geodatabase

AlexanderGray — Mon, 22 Sep 2014 17:27:02 GMT

The DBA has no control over passwords, the passwords are not stored in the database. Passwords are maintained by the network administators and would not reactivate the accounts, change the passwords or let me login with their account under any circumstance. They would sooner tear down the database and get me to rebuild it than do that. I am looking for a way to avoid that. If I have to rebuild the database every time someone leaves behind some featureclasses we are in for some serious problems.

Re: How to delete old feature classes from Oracle Geodatabase

AsrujitSengupta — Mon, 22 Sep 2014 17:43:57 GMT

thats true and I can understand the scenario...

this is the reason, it is never recommended to load data using a particular OS Authenticated user.....to avoid these scenarios.....

You should always try using a separate db user as the data owner and then grant permissions to other users as required.

Re: How to delete old feature classes from Oracle Geodatabase

AlexanderGray — Mon, 22 Sep 2014 18:02:28 GMT

Well users need to create their own feature classes from time to time. We had all users logging in as the same user before but that is not very secure, and then any user can alter any other user's data. All the main feature classes and tables are created/loaded with a database user/schema owner. Our users work with many sessions of ArcMap at the same time and share workstations and mxds we don't want to prompt for a password every time the user starts a new session of ArcMap. Moving to external authentication solves this. New users come in, accounts are created, space allocated, roles assigned, bob's your uncle.

If OS authentication is not suitable for regular user of a database this would be very disappointing. We then have to rethink our entire architecture, including the technology we are using.

Back in the 9.3 days, the sde user could always delete stuff, I am not sure why but this changed at 10.0. I need a management tool that will do this. If not at the geodatabase level, it will have to be at the Oracle level. Meaning going in and messing around with the SDE tables... There has to be a way for a dba to manage the database, controlling the content of a database is a most basic feature, c'mon.

Re: How to delete old feature classes from Oracle Geodatabase

Anonymous User — Tue, 23 Sep 2014 17:40:48 GMT

1. The SDE-owned tables are not in any way related to this situation.

2. All you need is any user with dbo permissions. The user can be OS authenticated, or DB -- it doesn't matter. Your DBA can do this. I don't recall the Oracle word; "dbo" might be SQL Server-specific.

Here is a link to Oracle permissions in the geodatabase. In my experience, you might get better results asking for temporary permissions for cleanup activities. ArcGIS Help 10.1

The other alternative, unless the tables are huge, is to revoke SELECT so they don't bother anyone anymore. It sounds like you can likely do that with your current credentials.

In your 9.3 days, the SDE user was possibly granted elevated permissions as part of the geodatabase creation process. After the process was complete, the elevated permissions may have been removed. Whatever happened, you can rest assured that all versions support OS authentication and you do not need to mess with SDE tables or rebuild your database!

Re: How to delete old feature classes from Oracle Geodatabase

AlexanderGray — Tue, 23 Sep 2014 18:43:41 GMT

Thanks Marianne, I already updated the code that creates the featureclasses to grant delete to the SDE user. The rest will have to be handled as one offs, which is a little annoying since we have to hand this over to a support team for 24/7 operational support. Support are usually not dedicated database or GIS specialists so I will work with the DBA to write this into an SOP.