August 19, 2020 at 3:14 pm
My situation: we have a team of analysts who create reports from a couple of databases. Analysts have sufficient access to read reports and write to tables. They create reports for end-users and create queries for applications that end users have. For example, in addition to providing reports that they run, they create the queries that their apps use in order to do analysis.
Problem: every time they ask DBA to provide end users or apps with access to a particular table or database.
Solution1: Have analyst give them access, without involving DBA. We do not want to make analysts SAs or give them too much access, but enough to be able to give end users read access. Is there a way to allow someone with read access to give it to someone else?
IDEAL solution: using a neat tactic allow their views or queries to be user-independent. Is it possible? I am looking for any help or direction.
Other considerations: isn't adding users (logins) which we need to create every single time a user accesses database, adding to the costs?
Environment: SQL Server 2016 Standard
August 19, 2020 at 5:18 pm
Taking a guess here but it looks like you are assigning permissions for select access on each view created by your analysts for the users using the views.
Are these view being created in a single schema like dbo?
August 19, 2020 at 6:10 pm
My situation: we have a team of analysts who create reports from a couple of databases. Analysts have sufficient access to read reports and write to tables. They create reports for end-users and create queries for applications that end users have. For example, in addition to providing reports that they run, they create the queries that their apps use in order to do analysis.
Problem: every time they ask DBA to provide end users or apps with access to a particular table or database.
Solution1: Have analyst give them access, without involving DBA. We do not want to make analysts SAs or give them too much access, but enough to be able to give end users read access. Is there a way to allow someone with read access to give it to someone else?
IDEAL solution: using a neat tactic allow their views or queries to be user-independent. Is it possible? I am looking for any help or direction.
Other considerations: isn't adding users (logins) which we need to create every single time a user accesses database, adding to the costs?
Environment: SQL Server 2016 Standard
To be honest, trying to go around the DBA is a great way to be terminated from the company. It's not up to you or anyone else on the "analyst" team to provide privs. Learn how to work with the DBA... it's their ass if privs are incorrectly assigned, not yours. That also means it's not your job nor anyone else's on the analyst team. Period.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2020 at 6:31 pm
First - I 100% agree with everything Jeff said above...
Second - All your problems go away if you were to use Windows Active Directory security groups. Rather than assigning permissions on an individual, per-person basis, you create one or more groups in AD and assign team members to those groups. The group owner/manager then works with the DBA to figure out the access levels required by each group.
August 19, 2020 at 6:49 pm
I don't believe that these replies are accurate.
First, the question of getting fired is outside of the scope and not relevant to the question being asked.
Second, I was able to find functions such as CASCADE but they seem to be working in Oracle, not in SQL Server. Hoping to have some input from DBA experts.
August 19, 2020 at 6:50 pm
The issue remains the same, regardless of AD Group or a user. What different would that make? You'll run into the same issue: how best to assign permissions. Whether that's a user or AD group is not relevant.
August 19, 2020 at 6:51 pm
You are correct. However, there are multiple schemas (actually too many). So the solution shouldn't really be permitting a single schema.
I am really not sure what the best way is.
August 19, 2020 at 7:21 pm
I don't believe that these replies are accurate.
That's a part of the problem. It's not up to analysts to decide who gets access to company owned data. 😉 That's how companies end up having data compromises and reading about themselves in the morning news. Not trying to be nasty here. I'm trying to provide you with facts.
First, the question of getting fired is outside of the scope and not relevant to the question being asked.
Not true. We're all in this together and I am actually concerned about your continued employment. I'm trying to tell you what could happen, especially since it seems that you've not actually read a company policy about such things. And, if there is no such company policy, it won't necessarily stop the company for firing you for trying to circumvent the DBA.
Second, I was able to find functions such as CASCADE but they seem to be working in Oracle, not in SQL Server. Hoping to have some input from DBA experts.
See? That's a part of the problem with you trying to circumvent having to work with the DBA on privs. You're not an expert at it which also means that you're not qualified to assign privs with data safety in mind.
Again... not trying to be nasty here. Just stating facts to, first, protect you and second, protect the company that pays you.
Perhaps you've heard the term "DevOps"? Sure you have... and a part of that is working with the DBA on such things. Stop fighting it... just do it. 😉 It's the right way to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2020 at 8:55 pm
If your company did not care about security, why is it secured in the first place? Since a select few are creating the queries today, I imagine access was a concern.
And do they care enough to audit access? If not now, will they when there is a breach?
To dismiss the advice you are getting shows you will be news worthy at some point in your career. And not in a good way. Go to your DBA with this thread and discuss it. Let us know the results.
The AD approach offers more flexibility, and is auditable. But you still want controls in place.
The cost of not doing this in the correct manner can easily exceed what you perceive it costs using the current process.
In case you decide to dismiss my reply as not from a “good” DBA, I spent a couple decades for a Fortune 500 company, working as both a Developer for the data warehouse, and as the one in charge of securing our data corporate wide. In fact, when code red came through, I was on vacation. When I got back, in the meeting about this problem, my servers were the only ones that were not breached. The other 2 who replied likely had similar experiences. They are highly respected by many in the field.
In years, I have never seen either one post less than the best of help. And many times go to great lengths to help others.
I’d go so far as to say if any of us could post what you are asking for in the manner you want, we’d go directly to Microsoft and report it.
August 19, 2020 at 9:41 pm
First, I agree with Jeff on how cutting out the middle man can cause all sorts of legal problems and also get you (or the people handing out permissions) fired.
Second, I understand the situation you've presented. It sounds, however, as if your workplace is a lot more ad hoc and unorganized than it should be for this to work properly.
Here is how our office does it.
Our reporting team worked with our end users to discover what data they use on a daily basis for their job. Then put together a database with different schemas for different teams that contains this data and gets updated constantly from the OLTPs. Then the reporting team worked with the help desk to create Active Directory groups for each team, then worked with the DBAs (my team) to set up access for each group to the different schemas.
At that point, the only security work that needs to be done is get the managers to put in Security Access Requests to the help desk for adding and removing users from those AD groups.
From that point, the users can write their own reports based on the tables they have access to or the analysts can pull those reports. No further DBA intervention needed. And the data is "sanitized" to prevent PII and PCI from wandering loose in the world to people who don't need it for their work.
August 19, 2020 at 10:26 pm
And what I was trying to get at was instead of having to add permissions to each of the newly written views, the views could be created in the appropriate schema which has the appropriate permissions assigned at the schema level. New view created in the appropriate schema, the appropriate users have appropriate access without assigning permissions on the view. This is something I am working on in a database I have setup for test results reporting. Right now, the permissions are on the object level. Each time I create a new object I have to remember to assign the appropriate permissions. Once I have the schemas setup, I just have to create the appropriate objects in the appropriate schemas and the permissions will already be handled at the schema level.
August 19, 2020 at 10:36 pm
Oh, and I also agree with everything said by Jeff, Brandie, Greg, and Jason. Your DBA should be 100% involved in assigning appropriate permissions. Using AD groups is also a solid way to go.
August 19, 2020 at 11:46 pm
I had my own OU, to which I could create groups and add users. There was still a documented, audited process that was followed. Role in the business was part of the equation, while business unit was another part. User level was enforced for Outside Sales and Commissions so they could only see there own Office Sales, and their individual Commissions.
We interfaced with both the Domain Admin and HR so we were in the loop for their AD requests.
This was setup through discussion with the Domain Admin, who agreed we being the data owners were in a better position to handle this. He also liked the fact that this was more out in the open, so it was easily reviewed.
There still was a request generated for most user changes. But the underlying SQL Server and SSAS cube mirrored the AD setup. So many times adding new tables, dimensions, and measures only needed to be done on our end.
August 20, 2020 at 4:09 am
SQL has an easy built-in way. When the analysts are given SELECT permission, the DBA can add the "WITH GRANT OPTION" to their permissions. That gives them permissions to grant others SELECT permissions on those object(s).
That said, whether that's a good idea in your shop or not is a totally separate q. It could be a bad idea for all I know. Or it could be very useful for you here, in which case the DBA will likely agree to do that grant to the analysts.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply