0

My Table is like this

ID   FileNumber   SellerRep   BuyerRep   Address
123  142366       John Doe    Steve Doe   123 Cute Ave
122  142365       Steve Doe   John Doe    412 Best Blvd
121  142364       John Doe    John Doe    234 Park Ave
120  132363       Steve Doe   Steve Doe   233 Cool St
119  132362       Steve Doe   Frank Good  432 Bad Street

users: johnd (lists his name as John Doe)
       steved (lists his name as Steve Doe)

How can I limit end user to view/see only his own entries. His entries always include his name either in SellerRep or BuyerRep columns, and sometimes in both though they may possibly be misspelled (e.g. Steven Do, etc). However, either SellerRep or BuyerRep columns may include name of the non user as in the entry with ID # 119 in the table example.

So, basically, if Steve Doe logs in, I want him to see only entries with ID # 119, 120, 122, 123, and if John Doe logs in, I would like to limit his views only to records with ID # 123, 122, 121. One issues though is that One of them may have specify the full name of another user in either SellerRep or BuyerRep column, and I wouldn't want in this case the other user to see this entry.

Is there a way to accomplish this with views or triggers and not in PHP page, even if I have to add another column like @user with auto_complete if that possible.

The least what I could think of to start with is:

CREATE VIEW saleslogview
AS
SELECT *
FROM saleslog
WHERE SellerRep = 'Steve Doe';

CREATE VIEW saleslogview
AS
SELECT *
FROM saleslog
WHERE BuyerRep = 'John Doe';

CREATE VIEW saleslogview
AS
SELECT *
FROM saleslog
WHERE BuyerRep = 'Steve Doe';

CREATE VIEW saleslogview
AS
SELECT *
FROM saleslog
WHERE SellerRep = 'John Doe';

However, when I tried to log in as user steved or johnd with like SQLBuddy and tried to search records it did not work as intended - all records were "visible".

Perhaps there is a way to accomplish this using SESSION_USER(), but I don't know anything about it.

user3387040
  • 65
  • 1
  • 2
  • 9
  • 2
    You shouldn't be granting direct MySQL access to users if you want to limit their access to certain records within a certain table. You will want to look into using some sort of actual front-end software for access control – Noah May 01 '14 at 17:40
  • I found solution here: http://www.sqlmaestro.com/resources/all/row_level_security_mysql/ It does sort of indirect access to mysql tables, because user only sees his/her own entries and deal with views. – user3387040 May 09 '14 at 15:09

0 Answers0