0

my question is rather simple.

Can i grant permissions on a database table wise? something in the lines:

  • User Management has permission to select, update, insert and delete on table Projects
  • User Supervisor has permission to select, update, insert on table Projects
  • User Colaborator has permission to select on table Projects

If so, I could set up a system to create database users based on the levels of access of my application, much like the examples above.

Is it a valid mechanism to use this to secure a application?
is it worth on a real world application?

i've used PHP with Oracle and MySQL, but I'm look for a database/language agnostic answer, but any example would be useful.

pushing my luck a bit, what about per record permission granting? also, what about table schemas, are they a more acceptable then table based permissions?

Jonathan DS
  • 2,050
  • 5
  • 25
  • 48

2 Answers2

1

Answer to part 1: Yes as long as you handle the responses correctly.

Part 2: It's not as good as implementating security in the application layer, as most applications will need flexibility in the solution (what if you want a user to get increased privledges, have to code in lots of alter/deny/grant scripts)

Part 3: (Speaking from purely MSSQL) Row-level permissions aren't possible. Create custom views for this purpose.

hkf
  • 4,440
  • 1
  • 30
  • 44
  • i see, in the case of a the need for better security, do you think it's a good tradeoff? – Jonathan DS Apr 13 '12 at 11:54
  • I think that database security, while stronger than application level, is more difficult to maintain and implement. YMMV. – hkf Apr 13 '12 at 12:05
1

The main problem with using database security would be that you need separate connections for each user rather than being able to use a "service user" for the connection from your application server to your DB server. That would mean that you would no longer be able to use database connection pooling have to "connect" and "disconnect" from the database for every user request, which is not very efficient as connections are relatively expensive.

Having said that, there is good reason for using separate users in the database, such as DATA_USER (which the application server connects as) and DATA_OWNER (which owns all the tables but is used only for DB maintenance) and then only give DATA_USER the permissions that it needs to, e.g. only select on a lookup table. By separating DATA_USER and DATA_OWNER you can add an additional level of confidence that your application won't issue DDL commands (e.g. dropping a table).

beny23
  • 34,390
  • 5
  • 82
  • 85