0

I currently have two tables. One is accounts and one is tbl_units_info. My boss wants me to make it so that accounts are restricted from reading certain rows in a table. Frankly, I think my boss has no idea what he is talking about, but I'm hoping someone here can prove me wrong.

For example, accountname krikara can only view the entries of the tbl_units_info table where the TBID column is 0909.

Is this even possible? To make krikara only able to view the rows in that table where column TBID = 0909?

krikara
  • 2,395
  • 10
  • 37
  • 71
  • 3
    how about creating a view? – Scary Wombat Oct 23 '13 at 07:54
  • Yup. View should do the job. –  Oct 23 '13 at 07:55
  • It depends on if they have access to the actual db, or if they can only access it through your code. If that weren't possible, everyone with access to (let's say) his bank account data, would be able to see all bank accounts in that table. – mavrosxristoforos Oct 23 '13 at 07:56
  • The user has access to the actual DB. Also, is there a way to make it so the user can read/write for rows that have TBID 0909 – krikara Oct 23 '13 at 07:57

2 Answers2

1

You could solve it by giving accounts just the reading rights to a view instead of the whole table.

CREATE VIEW `tbl_units_info_krikara` AS
SELECT * FROM `tbl_units_ino` WHERE `TBID`='0909';

And then assign the respective rights to your user.

Sirko
  • 72,589
  • 19
  • 149
  • 183
  • So krikara can be denied access to the table tbl units info, but have access to a view of tbl units info? – krikara Oct 23 '13 at 08:03
  • @krikara According to [this question](http://stackoverflow.com/q/3108656/1169798), it is possible. – Sirko Oct 23 '13 at 08:09
1

It can not be implemented plainly on DBMS level since SELECT privilege has table level. You can not restrict rows reading. And this is good, I think - because data could be changed, so in general there is no solid condition for rows restriction (and, therefore, there could not be valid implementation for that on DBMS level).

You can, however, use VIEW - but it is a middlepoint, not common solution (I still not think it will help with tracking rows changes, but may be I'm wrong due to your application logic)

You can try to implement it in your application, but it still has problem I've described above: in table, data is changing. You'll probably have troubles with tracking all changes. I think you can separate your rows on two (several) tables and then build your permissions model. But - if some basically similar entities must have different permissions - probably you should reconsider application security model?

Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • You make a valid point. TBID represents the unit's location, so when the unit switches cities, it gets a new TBID. With that said, if one unit travels 2 cities within a day, the user in charge of the last city will be the only user that has read permission for that unit info. This whole system is flawed. As for the security model, there is no security model at the moment. My boss doesn't believe in security. Things need to change – krikara Oct 23 '13 at 08:11