Allowing users to compile / run PLSQL and SQL

Question

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data has undergone ETL. The tables contain a large numbers of rows but not necessarily large rows in size. The analysis - will be - I assume of calculus in nature. The users are wanting to run their own code on the data for the purposes of ad-hoc analysis - within their own schemas. They come from a SAS background which from my understanding has allowed them to write and run their own analysis of data. Albeit, SAS runs on its own dataset - extracted from Oracle or elsewhere.

In my opinion this is a dangerous set-up - but without answering my own question - what are the pitfalls?

Answer 1

I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of:

1. You admitted that you are working with large record counts in your tables. What is your game plan when a user inadvertently issues a Cartesian Join?
2. If you are talking about read/write access how are you going to hold those users accountable for their actions? If financial data is involved (SOX) then it is not enough to just prove you are auditing the modifications to sensitive data you have to show you have removed their ability entirely.
3. If this is a production environment have you reconfigured your DR/backup solution? If your backup solution involves taking the DB off-line then what is your solution if a user puts a procedure in DBMS_JOB to run during it?
4. Lastly, and probably most importantly, who is going to support their code? If your org has a dev team and they are not on board for support I can guarantee that as the DBA you are going to get a finger pointed at you as soon as something goes wrong. Make that expectation up front.

I think thats enough devils advocate but I guess in summary my answer would be:
"Never in production and never in QA. If you want to run code you write it you support it and then you package it in a deployable format so I can push it out. If you want to act like a developer I'm going to treat you like one."

Answer 2

I think there is no problem, but you need to take care about data. For example, you can grant them privileges to create tables and procedures for their needs in theirs schemas, and privileges to select data from another schemas, but not to grant privileges on insert/update/delete. If they don't have privileges on update and delete data in main tables, everything will be OK.
Main pitfalls, I think, will be:

• grant privileges using roles. It is not dangerous, but you need to check all chain of privileges (because you can grant role to another role and so on, there can be many levels, and "trees" with many branches).
• grant one user privileges to run code of another user (another user can grant this privilege himself). In this case first user can get indirect access to privileges of second user (procedure by default executed with privileges of owner).
• performance problem. Users can create very big load, but they can do this using any other tools too.

Answer 3

Be happy that your users seem to be going to use SQL to do the analysis, instead of pumping to excel. Make sure you have separated tasks and don't give privs to update the source data. Resource manager is your friend to prevent runaway queries.

give them resources that they need, make sure other users don't get pushed out.