Controlling Developers and Application
-
I am being asked to allow applications and/or developers the rights to create and drop tables in production. Would you allow that? Before answering you need to understand the business requirements:
- A canned application that requires these permissions is being implemented. The vendor will not change the code.
- There are ADHOC reporting requests that require tables to be created to house data temporarily to help support building the final ADHOC report. It may take hours, days, or weeks to produce the ADHOC report so these temporary tables need to stay around for this long.
If you where forced to allow this activity, how might you implement it? Provide a login with rights to create/drop tables? Give the login DB_OWNER rights? Are there other methods?
If we give rights to create tables, are there any rules we should enforce related to how we manage these user created tables? Like:
- delete all user defined tables 1 week after they are created.
- never allow a user defined table to be bigger than x.
- all user defined tables will be in a separate database.
- all user defined tables will be contained on a different server and linked server requests will be used to access them.
Do you other DBA’s have these issues? If so how do you handle them. Or do you use a big hammer to say non-dba can’t create table in production, period, end of story, etc?
Gregory A. Larsen, MVP
-
I hate applications like this.
, my buddy builds an accounting app like this and I always complain about this crap. He says they can't get around it since each customer wants to customize everything. I say he's full of @#$@$@#$.Anyway, if it's a canned app, you might not be able to put these in a separate db or server. If you can, I'd go separate db so I could limit the size, rights, etc.
I wouldn't grant dbo, I'd grant DDL admin and go from there, maybe create a separate role for this. As far as archive/cleanup, 1 week is probably good, assuming they don't do any alters, if so, then you might whack something they're using, but oh well.
Not sure how'd you'd limit the size of the table. Maybe monitor and then complain or deal with those that are growing large (whatever that is for your db).
-
We have canned application in our environment and unfortunately you have to stay with them.
Regarding security, we have a separate id which requires the create table functionality and we delete all objects owned by that user after a week. We change the ownership to dbo for all other user tables. Not sure if this is an option but hope you should be able to find out some criteria like naming convention etc. which you can use to drop the objects owned by the application id. This can help you in not creating a separate database for user defined objects.
We grant only create table permissions to the application id and not db_ddladmin.
Regarding limiting the size, we haven't run into this. But, you can use filegroups probably. You can create all user defined tables on a different filegroup and all the tables by the application on the default filegroup and restrict the space.
-
Thanks for the replies. I hate this situation too! I don't mind the temporary table thing so much, where we can delete these tables periodically. But I'm guessing they may not go for that since there application might break. What I really don't want to happen is have the create table access circumvent the normal change management process of getting new application tables into production without proper review. Any ideas on how to make this discuss with the developer a win-win situation.
Gregory A. Larsen, MVP
-
If your company is subject to Sarbanes-Oxley, you may not have the option of letting developers or anyone else run free in the database. If this is the case it will force the vendor to fix their design flaws, or your company will have to find something else to use.
Chris
-
why is there a problem with the user creating there own Temptable.that is deleted once there application loses its connection?
-
My suggestion would be to approach the developers with a goal of compromise and that you are "helping them" accomplish what they need to accomplish. If the vendor's application requires the access to create tables, then there is not much choice there, but I would not grant dbo privileges, just add the create privilege.
As for the Adhoc reporting, I would probably require the developers to use a different database for those temporary tables. Then, you don't run the risk of the developers changing other tables. Going to a linked server might be going too far, but if your policy is not to let anyone have that kind of access to anything on the production box, then the linked server is a good option.
-
do not give DBO access to any user.
Setup 3 environments and take your changes throught them >> develoment >> testing >> staging >> production, I have the same problem and this is the only way I have been able to deal with it.
As for the ADHOC report: create a seperate db call it "reports", setup transactional replication from the production to the "reports" db and let your developers loose in the reports db.
do what is politically correct
to maintain control of the production dbGood luck.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply