January 5, 2005 at 10:57 am
Due to Sarbanes-Oxley requirements, we've implemented a fairly strict separation of duties between developers and administrators; on a production server we want our dev folks to have read-only access to databases, but no ability to edit data or DTS packages. We just discovered today, however, that there is nothing preventing them from running DTS packages manually from EM. Outside of setting a password on every single package, is there any more manageable way to control execution access on these packages? Internal package security is generally via SQL authentication. I suppose another option would be to insert an initial step requiring adequate Windows authentication to proceed, but that's a huge hassle too. Any ideas - the simpler the better - would be appreciated.
Thanks
Jeff
January 6, 2005 at 7:59 am
I haven't found anything yet that prevents execution of packages other than the password options. I would be very interested if someone does have a simple solution to this also.
Good Luck.
January 6, 2005 at 8:09 am
Would preventing access to MSDB not accompish this?
January 6, 2005 at 9:08 am
Nicholas Cain: "Would preventing access to MSDB not accompish this?"
Possibly not. A DTS packags can be executed by a SQL Agent Job, and the SQL Agent Job can be given an owner of "sa". So, when the DTS package is executed, it is executed under the context of "sa".
January 6, 2005 at 9:24 am
However if you prevent access to MSDB the user will not be able to see the jobs, or start them.
January 7, 2005 at 4:05 am
cant you run a profiler trace on users or certain people, whom you suspect of doing so. And if you catch them, then fire them?
January 7, 2005 at 6:29 am
That's the dream of all DBA's everywhere....
January 7, 2005 at 8:40 am
Journeyman: cant you run a profiler trace on users or certain people, whom you suspect of doing so. And if you catch them, then fire them?
The only problem is, that's after the fact. If someone gets in and screws things up accidentally, or does it on purpose not caring if he gets caught, then the damage is already done. It's like a security camera in a 7-11: fine for helping to catch robbers and shooters, but not so good at keeping them out. You want to prevent them from getting in in the first place.
January 7, 2005 at 9:15 am
This isn't to try to catch somebody in the act; often developers will just overstep for the sake of convenience; due to SOX we have to prevent them from doing so.
It sounds like denying MSDB access is the way to prevent access to jobs, but nothing (short of passwords) will prevent package access. That's annoying. I don't want to have to manage any more passwords.
January 7, 2005 at 9:40 am
Jeff,
given that the packages are stored within MSDB, preventing MSDB access will also prevent package access.
January 7, 2005 at 10:52 pm
I have a problem with "If someone gets in and screws things up accidentally, or does it on purpose not caring if he gets caught.."
To prevent things to happen accidently, developers should try it first on Dev/Staging Servers. And if it done on purpose then you might need to replace HR dept, who hires those people at the first place. We have hundred of developers in my company amd I never heard that anyone does smth on purpose!
And if something bad really happen you could always restore from the last good backup (full/diff/tran log). You do it on the regular basis, right?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply