improve security at "database layer" - only select queries allow

Question

I am building a application in silverlight which will enable users read information about their payment. Their login and password will be save in table in db.

It is possibility to improve security in my app by limiting what data a query has access to? For instance i want to prevent a user from selecting data they do not own. A limitation is that my application is using a its own table for users, so i cannot use GRANT PERMISSION :/. I am using Linq to build my sql queries.

This question is from my teacher who "suggest" me to improve security, so if it's impossible - it's no big deal ;)

Answer 1

Well, it is kind of a vague question you've asked, but I'll hazard a stab at it.

You must be doing some authentication on the user's identity to only be showing them their payment, as opposed to someone else's payment. So, if you can do that, you should be able to create triggers that disallow any insert, updates, or deletes on your tables from those same identities... I don't think this is a very robust or scalable solution, but it's an idea.

CREATE TRIGGER [x] ON [TABLE] FOR INSERT

/* Disallow Users to insert */
IF EXISTS( SELECT 1 1 FROM [Users] WHERE [Users].UserID = [Y])
--Rollback transaction, set error, etc

Do you have any more details? Anything else could be helpful in finding a better solution.

Answer 2

A fine tuned access control for database resources is a very uncommon method of securing your application. User level access control is best implanted by the application. The sql user account used by the application should be as restricted as possible. For instance it should only be able to use the database(s) it needs to function, and nothing more.

There is 1, and only 1 project that has fine grained access control for a sql database, and that project is SE-PostgreSQL.

"It can provide fine grained mandatory access control to various database objects such as tables, columns or tuples and can apply consistent authority of remote/local client integrated with operation system independent from database authorization."

Answer 3

Create a view named 'MyPayments', on the 'Payments' table. Make sure you have a WHERE clause in the view definition, so that the view returns only the relevant data to each user. Here is what the WHERE clause of your view will look like:

WHERE PaymentOwner = SUSER_SNAME()

The SUSER_SNAME() system function returns the currently logged in user's login name. If the first user logs in with the login name 'User1' and inserts a row, his/her login name is stored along with the row. The SUSER_SNAME() function in the WHERE clause of the view definition makes sure 'User1' see only those rows that have the 'PaymentOwner' column set to 'User1'.

More here