Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

closed as primarily opinion-based by Phil, bluefeet, billinkc, Jon Seigel, Mike Walsh Sep 9 '13 at 17:54

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

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 9 '13 at 9: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 9 '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 9:02
up vote 0 down vote accepted

You can grant access to views and stored procedures without granting direct access to the underlying tables (see 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.

share|improve this answer
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 9 '13 at 15:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.