2

I am trying to emulate row level security on a webapp I am developing using MySQL.

Using this method: Creating a database with the the required tables where the data pertaining to all the users will be stored with proper indexing of columns of the tables.

Creating mysql 'views' to specific users based on the user-id.

To achieve row level security I will also have to create mysql account for every user to and set 'grant' permissions on the views.

For the web interface a PHP based MVC framework will be used.

But, according to my research:

1] Having separate mysql account per user "make the webapp less secure".
2] Having separate mysql account per user "increases the disk I/O".

Questions:
1] How does creating mysql user per webapp user make the webapp less secure?
2] Does the disk I/O increase considerably?
3] Is there a better way to implement row-level-security in MySQL?
4] What are the pros/cons of implementing row-level-security by the above method?

Why am I looking at Row Level Security?
I need row level security because there are rows which will be shared between multiple users & have 1 or 2 owners to it. Only these owners can delete/modify them.

ThinkingMonkey
  • 12,539
  • 13
  • 57
  • 81
  • 4
    Instead of mocking me, could you suggest what I can do/read? – ThinkingMonkey Nov 30 '11 at 19:04
  • It's not mocking you, but I apologize if it looks like that. I'm just wondering why you're even trying to do it this way. Why do you even need "row level security", what's the reasoning for going this way and what are you preventing by doing that? As for disk I/O, creating users, views etc. is not something that will affect disk I/O to the point you need to worry about it (unless you have like thousands of new users per day, that adds up to disk I/O when processing views). – N.B. Dec 01 '11 at 12:47
  • I need `row level security` because there are rows which will be shared between multiple users & have 1 or 2 owners to it. Only these owners can delete/modify them. ***If you have/know where I can learn about better approaches then do suggest them.*** – ThinkingMonkey Dec 01 '11 at 15:24
  • What I'll suggest is to scratch row "security" implementation on database level and use your PHP application to determine what user can see or modify. Implementing it on the data storage level is counter productive as databases aren't meant to be used in this way. However, if you still persist doing it via database rather than via PHP - I can't say much except that I wish you good luck and that you're going to be shooting your own foot. Anyone telling you it's a good idea to implement "row level security" has no clue what they're talking about. – N.B. Dec 01 '11 at 15:52
  • *Thanks for the comments & skepticism*. I will do some more research/studying and decide on what to do. – ThinkingMonkey Dec 01 '11 at 15:57
  • I'm not being sceptic, relational database is being used to **store data** and **create relations** between tables to ensure for the integrity - meaning that `payment_id = 5` cannot enter `invoices` table if such `payment_id` isn't specified in `payments` table. Database **should** be oblivious of actual logic, it just stores data and makes sure you don't enter invalid data (preserving of data integrity). Now, as for how it can be "easier" to define the logic on the database level rather on application level - it can't since it isn't. – N.B. Dec 01 '11 at 16:08
  • I understand your view point and do comply as well. – ThinkingMonkey Dec 01 '11 at 19:44
  • 3
    A little late but better late than never :) Just as a comment for anyone else that passes by... You can do row level security without having to create a view per user. Details can be found here: http://www.sqlmaestro.com/resources/all/row_level_security_mysql/ Depending on what you are doing it may be safer to actually implement security using MySQL rather than PHP.. in fact even if your users database details get compromised technically only a portion of your data is at risk. – Michael Tremante Jun 09 '12 at 15:04

2 Answers2

0

My opinions:

1] How does creating mysql user per webapp user make the webapp less secure?

There are multiple points of entry to your database. It's different from having a users table in which your users' information are stored in, and then using a properly restricted mysql account that they all use (as part of your web app config file)

2] Does the disk I/O increase considerably?

Not sure how to properly answer this one, because the article you might have read was about multiple sql accounts used by developers/database administrators/etc who directly access your database..

3] Is there a better way to implement row-level-security in MySQL?

With both SQL functions and views in your implementation for MySQL, maybe you can just try to control the access not from SQL account levels but on user access via table data?

4] What are the pros/cons of implementing row-level-security by the above method?

One thing that is a consequence of having multiple accounts is maintenance of the accounts.

Using views and functions and stored procedures will be one good way to help control the amount or kind of data you allow your visitors to see, so that's a pro.

Again, these are just my opinions; I may have misunderstood a few parts and I may be wrong with others. :)

Nonym
  • 6,199
  • 1
  • 25
  • 21
  • Thanks for the reply: Not sure how to properly answer this one, because the article you might have read was about multiple sql accounts used by developers/database administrators/etc who directly access your database.. Thanks for pointing this out.. The article was for using triggers & automated logging. – ThinkingMonkey Nov 28 '11 at 14:41
  • [link](http://dba.stackexchange.com/a/3348) The disk i/o would increase when triggers are used. Your 1st and 3rd point is my original idea for implementation. Are there any reference material that I can refer to? P.S. I accidentally hit enter instead of shift enter previously – ThinkingMonkey Nov 28 '11 at 14:50
0

an answer for question 3:

A much simplier aproach might be using separate tables. It might be better in terms of performance as well. Is there a reason why you want to keep data in the same table? As I understood, no rows are shared between users.

newtover
  • 31,286
  • 11
  • 84
  • 89
  • Rows will be shared between the users. That is the reason why I am looking at row level security. I should have mentioned this earlier. And moreover.. More the tables, 1] More the size of my Database table. 2] More I/O. 3] Higher the size of my database dump. – ThinkingMonkey Nov 29 '11 at 16:44