August 25, 2020 at 1:30 pm
@scottpletcher: Thanks so much Scott for providing this answer. This actually worked, and I believe this was the first/only technical answer that wasn't an HR answer!
I also gathered another interesting answer from elsewhere and thought share it with anyone who is looking for a technical/DB solution to this: using a certificate. Binding it to a view, they can only access the view and nothing else.
August 25, 2020 at 1:38 pm
@greg Edwards-268690 Thank you very much for this answer. It is a good way of managing it and makes it elegant, however, unfortunately I cannot move or change the schemas as there are many applications with (set in stone) table structures that cannot be changed. Most are on DBO unfortunately which made this solution not feasible. However, I am thinking of proposing/implementing this for future tables where we have control. It is a good way of managing it. Thank you
In regards to the HR alarmists, honestly I didn't read their replies, but found the ideas funny how everyone assumes that other companies are just like theirs. I work in a small shop, we do not have a large team or a perm DBA or sensitive data. However, we still need to manage the requests because there needs to be separation/provisioning of the data to ensure mistakes aren't made. There is no point in my attempt to give everyone a life story. I presented the technical challenge.
August 25, 2020 at 2:03 pm
You have better options you can see moving forward.
Even though you work in a small shop, security is important. They do not want uncontrolled access as they have a process already. They might grow, and needs will change. Or you could end up moving to a different company and they might have a whole new set of requirements.
Just some food for thought - you mention other applications. Keep in mind applications are important to use least privilege concept. And an application can use elevated privileges for some things, but user security for others. Locked in stone might not be so.
Glad to share some of the experience with you.
August 25, 2020 at 2:34 pm
If you want better answers to your questions, you might want to consider rephrasing your topic titles and initial posts. Reframing this situation might have gotten your answer faster and without the judgement you are so obviously mad about.
Also, the solution you like doesn't actually cut out the DBA. It requires the DBA to set up the permission to grant permissions in the first place, which is not what your topic title indicated.
Please understand that this isn't a "your shop must be like my shop" situation. It is literally a "we've seen this question before from people trying to hack the company or cause damage" situation. So our first response to the way you phrased your request (without the small shop context that could have helped) was to prevent security damage. You also made it sound like you aren't the DBA, which is another red flag.
I'm glad you have a solution that works for you, but you need to ensure the people granting these permissions are judicious in their use of them. Small shop or no, giving people access to grant security permissions to whomever they feel like it could potentially be a disaster in the making. So be sure you can trust whomever is getting and giving permissions on that data. Bad actors don't just work for large companies.
August 25, 2020 at 2:37 pm
Thank you again and I certainly agree with both posts/replies.
For sure, will rephrase differently in the future.
And it is a good consideration to give apps least privileges.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply