0

Our current system architecture is fairly simple. There's a global list of devices, which move between gateways. The idea is such that the global list of devices is stored in one table, such that any gateway can read the information about any device, but can only write to the devices resident on their specific gateway.

At the moment, this must be enforced in client logic, which is obviously not quite what I'd want, for a few important (and less important) reasons. I'd much prefer it enforced at the server level.

Does there exist a common database which makes this sort of permissions granularity definable?

user27989
  • 3
  • 2
  • Yes, I see the problem. We're still very much on architecture level design, so it's really a 'does there exist a database' question. – user27989 Sep 09 '13 at 09:20
  • If I'm understanding the problem correctly, any RDBMS can handle this for you. It's still business logic, not built-in logic, so I'm really not sure what you're trying to get out of asking this question. Could you please clarify? – Jon Seigel Sep 09 '13 at 17:17
  • That's the conclusion I've come to. Basically, I was just looking for a simple 'yes' 'no' answer from someone who which the answer is just recall such that I can continue my design without introducing a critical flaw. I tried to research the problem on Google but was clearly using the wrong keywords. Many thanks. – user27989 Sep 10 '13 at 09:02

1 Answers1

0

You can grant access to views and stored procedures without granting direct access to the underlying tables (see https://stackoverflow.com/questions/4134740/grant-select-permission-on-a-view-but-not-on-underlying-objects amongst similar questions for some detail). This will certainly work for reading from views, though I'm not sure about writing to them (it would be difficult for it to know if they should be writing specific things, so you may have to manage this with INSTEAD OF triggers instead of it being done for you).

With the permissions correctly set the users can only access what the procedures/views expose to them, so you can control their access by giving different users different procs/views that each have different filtering clauses. You can even give the different views/procs the same name if you give each user (or class of user) its own schema, so you don't need different code/configuration elsewhere for User1, User2, ..., UserN as they can all run SELECT <something> FROM FilteredView and get results (User1 might see results from the view u1.FilterdeView, User2 and User3 might have reports as their default schema so they get results from reports.FilteredView instead, and so forth).

My knowledge of this is MSSQL specific, but I'm pretty sure other major DMBSs (such as Oracle or postgres) have very similar access granularity controls.

Community
  • 1
David Spillett
  • 32,103
  • 3
  • 49
  • 89
  • Excellent, thank you. This makes sense, I'll research into this some more - stored procedures definitely seem like a good place to start. – user27989 Sep 09 '13 at 15:41