CODEWITHSUNDEEP
sqldatabaseauthenticationpermissionssql-grant
Jon
Jon

Reputation: 2128

GRANT Database Permissions for specific tables and the validity of this as a security practice?

my question is rather simple.

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

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?

Upvotes: 0

Views: 770

Answers (2)

beny23
beny23

Reputation: 35018

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).

Upvotes: 1

hkf
hkf

Reputation: 4520

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.

Upvotes: 1

Related Questions