Restricting access by record??

[chumlyumly]

Hi -

I'm creating a MySQL database for a record company and they want to restrict access to certain records. I know that I can restrict access to databases, table, and certain columns, but I've scoured the web and have been unable to find how I can limit access to particular records.

For instance:

This company wants band members to be able to view all of their royalties statements, past to present. Sometimes, however, a band member will drop out of the band and a new one will take his/her place. They would want the rest of the band to be able to view all statements, the old band member to view statements up to the point he/she left, and new members to view statements after their join date.

I have several tables for the different types of royalties statements...how can I restrict access by row or record??

Thanks for your help!

[leatherback]

You could add a table with record number and user number. For the records that a person is allowed to view you add a record to that permissions table.

[chumlyumly]

Thank you - I'll wrap my head around that solution and see if it will work for this scenario. I hope it does - it seems simple! 🙂

...the more I think of it, I run into the same problem. I need several different users to be able to access the same record. And which users are given permission varies depending on the record.

So let's say I had record1 and user1, user5, and user9 have permission to access that record.

And I also have record2 with user1, user3, user5 and user6 have permission to access that record.

Do I create separate columns for the users? Or separate rows for each record and user combo?

How does this query work in either structure?

Oh goodness...thanks for your help!