CODEWITHSUNDEEP
mysqlsql
krikara
krikara

Reputation: 2435

How to enable users only to view certain rows in a table

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?

Upvotes: 0

Views: 1310

Answers (2)

Alma Do
Alma Do

Reputation: 37365

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?

Upvotes: 1

Sirko
Sirko

Reputation: 74046

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.

Upvotes: 1

Related Questions