May 12, 2020 at 3:13 pm
I am trying to understand and ensure i grant minimal rights instead of more than needed. We have environment, they are meant to be a staging area for inbound data. Then we have Data Architect support for ETL activities, and need full access to the staging environment. They want to use their master accounts than to when making database changes than to use the SQL account for better audit and accountability. For schema changes i am proposing the deployment process instead of db owner, but they are asking at a minimum, have truncate rights. Whatever rights are decided, they want the same rights to extractuser. Any input?
May 12, 2020 at 3:54 pm
My opinion is to give them least permission possible for them to do their job but also that they accept responsibility for permissions that they claim to require. For example, if they claim to need database owner permissions, then they may be the ones who will be responsible if the database gets dropped. Are they willing to accept that responsibility? If not, then they don't need database owner permissions.
Where I work, on the LIVE databases, only DBA's have permissions to do things that could be destructive. On DEV, the development teams are full sysadmins because if they hose the dev environment, they just piss each other off but we can recover it from live.
My opinion, I would not have the data architect changing the ETL process on the live environment. They can make changes on test/dev to ensure their changes do what they expect and don't break anything, then their code gets reviewed by someone else and an alternate person pushes changes to live. At my workplace, ONLY the DBA's make changes on the live systems from the ETL to stored procs to tables to users to logins. But it really depends on how large your team is. That may not be realistic in your environment.
As for using "master accounts" vs "SQL account", we use windows authentication for all database connections so we can audit things based on the actual user making the change. Best practice is to have SQL in Windows Authentication mode instead of Mixed Mode wherever possible (some 3rd party tools don't support windows authentication).
My workplace has this quite restrictive, but it reduces the chance of a non-DBA deploying something to live that is broken or dangerous.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 8, 2020 at 6:03 pm
Thanks Gale. I agree with you giving the least minimum access. But now they claim that they have to fix data related issue within 6 hrs and sometimes the data they get is on a Friday.
June 8, 2020 at 7:39 pm
I am glad they got things fixed. Something to note though - depending on what type of audits you have and certifications your company has (SOX, ISO, etc), having technical staff who are not DBA's with permissions to view and modify LIVE data may violate some of those.
On top of that, let's say one of the devs thinks they can fix the problem by running a DELETE to remove the one problematic row. They highlight their code and run it. Immediately, users are concerned as their reports are blank and the tools are no longer working. You, as the DBA, go in to have a peek and the table is empty. The dev forgot to highlight the "WHERE" when they hit run. ALL of the data is gone, and now it is up to you, as the DBA, to fix this.
Now, lets take this from a different perspective where they can modify the DEV environment all they want, but your release team is the only ones who can run this on live. That DELETE would have failed when they tried it on live and thus no data would be lost.
It is just one scenario of many why I don't like developers having the ability to just run CRUD statements on live.
Same problems can happen with ETL. My opinion - all code being run against a live environment should have at minimum 2 people review it - original developer and at least 1 other subject matter expert.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply