Fine-grained Access Control for Stored Procedures

Question

Fine-grained Access Control for Stored Procedures

maxtardiveau

SSC-Addicted

Points: 409
More actions
June 14, 2024 at 12:00 am

#4421235

Comments posted to this topic are about the item Fine-grained Access Control for Stored Procedures

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

al-243208 SSC Rookie Points: 31 More actions · Answer 1

I think there are much more potential drawbacks, then just “Slight latency penalty”

If our proxies consist of more than few rows, then our system become much more complex and will ask for much more different resources

1) Probably we need to have a new application (?) server (real or virtual) to install and run all the proxies

2) Probably we need a few extra Java / JavaScript / C# / etc programmers with the understanding of our business logic on top of the existing stuff of SQL programmers

3) Probably we need to take care of the security of our proxies. We protected our database server, but the malicious hackers can attack our proxies. The final result will be the same

jbowers Old Hand Points: 369 More actions · Answer 2

I was perturbed to learn, only after reading over half of this article, that the security-by-proxy functionality is not a feature of SQL Server, but rather a third party application.

Joe Bonomo SSC Veteran Points: 242 More actions · Answer 3

Good article on the possibilities of the proxy. It would be better to point out you were using Gallium Data earlier in the article, potentially in the intro. It's not clear this is a requirement until almost finished reading the doc. I'd also suggest mentioning the out of the box features in SQL to compare/contrast. For instance, granting or denying execute to a stored procedure is possible in SQL Server. A lot of options here. Yet the article comes off as if there is no capability to control who can or cannot execute a procedure, which is untrue. The flexibility is limited (as you state, if the data to control execution is outside SQL, then a proxy would be great) but you can grant or deny execute to different groups, use schemas to control the access to a grouping of procedures, to name a few.

Joe Bonomo

maxtardiveau SSC-Addicted Points: 409 More actions · Answer 4

Hi al-243208,

I think the right way to think about this is to compare it to the alternative. If you need fine-grained access control for your stored procedures, the first thing you should look at is obviously to do it in the stored procedures: that's the natural place for this.

The proxy comes in if that's not an option, for instance:

if you cannot change the stored procedures (e.g. in third-party packages)
if you don't have the expertise, or the permissions
if you need to do something that is difficult or impossible in SQL (like using external services or libraries)
if you need to do something that is expensive and you don't want to put that load on the database server(s)
etc...

In these situations, you will need something outside the database. That could be an app server, but I would argue that it is actually simpler in many cases to use a proxy, which provides the desired functionality without changing the interface -- all the database clients will continue to work as expected, without any changes.

maxtardiveau SSC-Addicted Points: 409 More actions · Answer 5

Hi Joe,

thanks for the excellent feedback! I can see now that I should have structured the article a bit differently to make it clearer.

Regarding the built-in security, I'd like to push back a bit. All SQL databases provide only a yes/no switch for stored procedures: either you can execute it, or you can't. Sometimes you need something more, like "users in group A can call procedure X but only if parameter 2 is less than 100". This is a level of granularity that (as far as I know) has never been offered by the big SQL vendors, even though they offer a ton of flexibility when it comes to fine-grained access control for tables/views. I guess they figured that this can just be done in the stored procedures, but I've never been crazy about that because it conflates implementation and access control.

This type of fine-grained access control should usually be done in the stored procedures regardless because it's usually the simplest approach -- a proxy should be used only when that's not the best option (e.g. cannot change the SP code, too difficult, too much load on the database, etc...). Adding a proxy to the system has a cost, so it should be brought in only when necessary.