Skip to Main Content
  • Questions
  • Deny developers access to schema owner of tables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vitor.

Asked: January 19, 2017 - 1:32 pm UTC

Last updated: January 21, 2017 - 3:11 am UTC

Version: 12

Viewed 1000+ times

You Asked

Hi Tom,


I work in a company where several developers have the login of certain schemas for development. We want to implement a policy where each user accesses his own user, but can create, change, execute, delete objects in other schemas. Developers will no longer access object owners. There is a native way to implement such a solution?

and Chris said...

What's your goal here? What are you trying to achieve by doing this?

To allow developers the ability create and drop objects in other schemas you need to give them "create any" and "drop any" privileges! This is potentially a bigger security problem than allowing access to the owner schemas in the first place.

Personally I'd give developers full access to owner schemas in development databases. They need a place to experiment and try things out freely. Once they're happy with the scripts, then they hand them over to a DBA/trusted database developer for review and deploy into test. Only a few would have this access. Then as few people as possible would have production access.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vitor Alves, January 20, 2017 - 4:03 pm UTC

Hi Chris, thanks for answer

My goal is to make development flexible and at the same time traceable. When a developer needs to change an object and debug it, he can access with his own user. Our environment is a great data wharehouse, with several schemas of different subjects. Giant tables containing millions or in some cases billions of rows. We do not have a development environment because of the size of the tables and the speed at which changes are requested. 

My idea is to create these users with Grant Any and a "BEFORE DDL" database trigger that restricts which schemas each user can compile, and another trigger that records the history of DDL changes.
No mais alterei u
Chris Saxon
January 20, 2017 - 4:43 pm UTC

For heaven's sake BUILD A DEV ENVIRONMENT! Seriously.

Allowing people to muck around with objects in prod is a recipe for disaster. It's only a matter of time before someone drops a table accidentally (or on purpose...)

It doesn't have to be a complete copy of prod. Though that's nice if possible. Then you don't have to worry about people having access to the wrong thing.

I have to Suppport what Chris has said....

David Pulliam, January 20, 2017 - 6:12 pm UTC

I work in a medium sized University environment here. We have up to three copies of every system. One for Devs to play and muck with, one to test their changes on and get approvals from end users that is kept close to prod's state, and our prod environment. One thing that is common in many industries is that when there is a failure of any IT system, no mater who is at fault.....If you have not seen it then look up the "The Blame Game" comic from the "The Adventures of ACE, DBA" comic series. Easy to find on Google. Exempt its IT, not the DBA who usually takes the hit. Having your prod environment screwed to kingdom come and loosing data could be a "RGE*" for your entire IT Staff in the right situations. *RGE = Resume Generating Event, in other words, a mistake so bad that it could very likely mean updating your Resume as you are looking for a new job.
Connor McDonald
January 21, 2017 - 3:11 am UTC

I approach from the other way .... As a Dev I *refuse* to have access to Production.

Because if things all go to hell in Production - I can definitively prove that I did not have access to it.

Unless *forced* to, as a Developer, I *never* want Production access.

No DEVs in PROD!!

J. Laurindo Chiappa, January 20, 2017 - 6:24 pm UTC

One more here, demonizing Devs poking around PROD - brrrrr, I got chills over my spine only in thinking about that... Not only due erroneous/malicious DDLs or DMLs but even that proverbial query-from-hell could put PROD on a freeze, and this always WILL (not CAN but WILL) occurs ath the worst time possible.... Ugh... And I will not even start to talk about the SECURITY ISSUES in giving to DEVs real data....

At the worst case, if absolutely no DEV environment/server/database exists, I would create one user/schema for each, give SELECT-only grants to PROD tables and give CREATE TABLE (on his/her schema, only) and CREATE SYNONYM for each DEV - thus, they would be able to test their routines againts the current columns/ tables (using the synonym pointing to PROD), could test the addition of new columns (dropping the synonym and creating on his/her schema a table with the same name but new columns, even inserting data from PROD into it)... OF COURSE, *** all *** these schemas WILL HAVE a very very very restrictive profile , limiting the logical I/O and things like that, trying to avoid that dev´s hodgepodges freeze PROD....

Best regards,

J. Laurindo Chiappa

Vitor Alves, January 20, 2017 - 8:45 pm UTC

I agree with all, but I believe in rare exceptions .. Our transactional systems have a very restrictive policy, with development, homologation and production steps. However, our DW is very changeable. In that case it is Datawharehouse and it would not be a problem restoring a backup of 5 or more days case something very bad. We can perform the backup and load the data up to the most current state. But there are answers that we need to solve with complex PL / SQL processes, and we do not have the time to synchronize data between servers. Our Datawharehouse is a very large Stagging area and we change procedures and packages all day long.