0

I'm developing a web application which will shared by a few colleges in my city. The cumulative number of clerks and office staffs will be around a few ten thousands.

I have created users like 'admission clerk','account staff', etc. and have defined the tables that can be accesses by each user type( say 'admission clerk' can access only admission_table and not payment_table).

I have enforced a programmatic security, where, when an admission clerk of a college logs in, he can delete an entry in admission_table where student_college field equals admission_clerk organization . Can I achieve the same by creating users in the dbms ?

user3388324
  • 572
  • 5
  • 18
  • You've tagged both `mysql` and `sql-server`. Is your question asked in the generic sense (applies to more than one rdbms)? Or are you asking for just one rdbms? – Dave Mason Apr 02 '14 at 13:18
  • I thought it comes under SQL standard. If not, I should rather tag it as mysql – user3388324 Apr 02 '14 at 13:29

2 Answers2

1

You can try to multiply admission_tables to have one main table admission_table with columns

id, college_code, reference_id

The last field references to a college's admission_table

So you will have multiple admission_table_ with the same structure separate one for each college.

Thus you can use table roles.

It's complicated way and you can't get aggregate of the multiple tables data but it could work.

UPDATE

Alternatively you can prevent deleting from the table and create a special secured_delete() STORED PROCEDURE. So an admission clerk 1 can't delete directly but can call secured_delete_college1() only (moved from comment)

StanislavL
  • 56,971
  • 9
  • 68
  • 98
  • Is it a good practice to create a table for every new college ? Also, I'm looking for practices that are done in enterprises in such a scenario. I shall stick on to the row level control in app logic, if this is the way things are done commercially – user3388324 Apr 02 '14 at 13:34
  • It's not good practice but your requirement is also not usual requirement for MySQL. – StanislavL Apr 02 '14 at 13:36
  • 1
    Alternatively you can prevent deleting from the table and create a special secured_delete() STORED PROCEDURE. So an admission clerk 1 can't delete directly but can call secured_delete_college1() only – StanislavL Apr 02 '14 at 13:39
  • Why is it uncommon ? A bunch of ecommerce sites like ebay, where vendors register with ebay and have control only over things that they own in the table. Is it achieved at db level or app logic level ? My situation is similar – user3388324 Apr 02 '14 at 13:39
  • You use MySQL where the feature is not supported. ecommerce sites could support this on app level or use another DB – StanislavL Apr 02 '14 at 13:42
  • The real answer is in comments. Do edit your answer with thoughts in comments – user3388324 Apr 02 '14 at 13:46
0

MySQL does not support row level security. You may workaround this by using views but that comes with certain quirks. I'd suggest sticking into implementing the row level security on the application level.

vhu
  • 12,244
  • 11
  • 38
  • 48