visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Can I restrict access in MySQL at the record level?
By: chumlyumly U.S.A.  Date: 28/04/2007 02:23:13  English  Points: 20 Status: Answered
Quality : Excellent
I've been all over the Internet and have asked people around me, but am not getting a solution that works.

I'm developing a website for a record label. They want their artists to be able to see their royalties, but they only want the appropriate people to see the royalties for their band/group. Some royalties relate not only to the band, but to outside songwriters.

Therefore, each record as it's entered needs to be available to be viewed by specific groups of people, and it varies by record. How can I do this in MySQL and PHP? THANK YOU!!!!
By: VGR Date: 28/04/2007 09:29:10 English  Type : Comment
not that much a problem, except if you intended to implement it 100% at the MySql level. You would have used stored procedures and the like. Note that IMHO this hasn't to do with the MySql row-locking mechanism (used for concurrent UPDATEs and SELECTs)

I encountered this problem, and it's not that hard to solve, given you've PHP at your disposition.

Here are rough steps. Ask for more detail if you need them.

1) define the "groups of persons" : this could be for instance "songwriters", "band members", ... and assign them a "type" = a smallint value between 0 and X
2) add a column "level" or "thetype" ("type" being a reserved keyword) to the royalties table - alter table royalties add thelevel smallint default 0;
3) each time you enter a record/row in the royalties table, whatever the band, make sure you assign the relevant "thetype" value to each line.
For instance, songwriter royalties would have thetype=2, band members lines would have thetype=1, publicly readable lines would have thetype=0 (hence the column's default value) etc

Coupled with your login mechanism, this will enable any site user to see only the lines pertaining to him/her, depending on the "thetype" value he or she receives in his/her user profile.

example : someone logs in, his profile says it's a songwriter, hence he will be able to see all royalties' lines having thetype=0 (public once logged in) and thetype=2. He wouldn't see lines having thetype=1 (band members)

the row selection would be done simply by appending a "WHERE thetype=2 OR thetype=0" or "WHERE thetype in (0,2)" clause to your existing SELECT.

that's just a suggestion though.

regards
By: chumlyumly Date: 29/04/2007 03:04:46 English  Type : Comment
Thank you for your response. I appreciate you taking the time to reply. I've thought your response through, and I do have an additional clarification/question:

You said in your response
"the row selection would be done simply by appending a "WHERE thetype=2 OR thetype=0" or "WHERE thetype in (0,2)" clause to your existing SELECT."

Can I have a column in MySQL with more than one value that can be separated out in a SELECT statement?

If I have a royalty row, and only thetype 0, 2, and 5 have access to it, would that column (thetype) be a SET, or can it be a VARCHAR? How do I separate out those values and enable them to be "read" separately by a SELECT statement?

If you can answer this for me, you may be a god.
By: VGR Date: 29/04/2007 08:55:51 English  Type : Comment
you've, indeed, plenty of solutions :

Given a row of a certain type is to be accessible only by a category of users, try to set it to a value that allows direct selection when the user is one of those types ;
In your example, user types 0,2 and 5 may access a given row of type X

you've many solutions :
- try to have a single row type that allows for selection. Don't store a list. Don't store multiple values unless you absolutely need it.
This is like saying : usertypes 0,2 and 5 are defined as "privilege 1" users, so they will see all royalty rows having thetype=1
Generally speaking, adapt the type of rows to the type of users but not on a one-to-one manner : try to include populations of users in shrinking categories, like "sets". This way, usertypes 3 can could see all lines, type=2 would be somewhat restricted, and type=1 would see only public lines.

- if you really want to have different user types and royalty lines and not implement the "groups" suggestion above, implement a mapping between X and (0,2 or 5) either in the PHP logic or at the DB level (probably more difficult)

- implement a relationship table. This is the "standard RDBMS answer". Say, you'll have to query the royalty rows with a JOIN on the relationship table (usually named user_royalties to indicate the direction of the relation). That table would be defined as an ID relationship (no private attributes) like (id, user_type, royalty_type) and include three rows : 1,0,X ; 2,2,X ; 3,5,X
This is probably a waste of your time, CPU time and resources.

Try to stick with the first suggestions.

in a much clearer explanation :
- if a royalty row is to be accessed by a set of user types, the probably best solution is to implement this "business logic" in your front-end script (PHP) so that you may maintain it easily.
Something like :

// build basic query with the start of the WHERE clause $query="SELECT * from royalties WHERE 1=1"; // build selector for royalty rows switch ($user_type) { case 0 : // user is a generic member : may see public R lines $query.=" AND thetype in (X,Y,Z)"; break; case 2 : // user is a band member : may see R lines $query.=" AND thetype in (X,Z)"; break; case 5 : // user is a songwriter : may see R lines $query.=" AND thetype in (X)"; // or thetype=X break; // add default: if you need it } // case of user_type // finish the query including the trailing slash $query.=" AND someotherfield='somevalue';"; // execute query // etc



there are other possibilities but it's relatively early in the morning for me and i'll wait for your feedback
By: chumlyumly Date: 29/04/2007 22:19:05 English  Type : Comment
Again, thank you, VGR.

I think I'm going to need to explore your second option - the one that you've dubbed as "probably more difficult."

Sadly, I don't think the first option will work because I'm dealing with several different bands which should only be able to have access to their own records. It might work if I had a different database for each band (and therefore, access levels within that), but I'm thinking with the number of tables I'm working with, that could be madness.

So there are no actual "levels" of access that are clear-cut, as no one band has omnipotent access over another band's records. Hm.

I've thought about creating different combinations of users and giving them a group number. For some of the bands, this would work, as there are only two types of users. But for other bands, there are up to six types of users, so to go through every possible combination of those six and assign a group number to each combo may make me a little crazy.

Argh.

Thank you for your thoughtful suggestions. They're helping me to think outside the box on this.
By: VGR Date: 30/04/2007 08:53:35 English  Type : Comment
I think you should perhaps rethink the whole design...

1) bands
They have members of 1, 2 or 6 types (whatever the number), each type can be mapped to a certain level of data access.
2) royalties
They have a "data protection" level, say 0,1,2,3
They are linked to a given band via a id_band column
They may also contain an id_user in case they're linked to an individual (usually, songwriter)

All you've to do is map the ones to the others. You ***should*** do this in your front-end script, 'cause it's softer to bend to your evolving needs.

For example (you gave me no real data to analyse, so I'll guess) :
group 1 has members 0 and 1 (interprets, songwriter)
group 2 has members 0, 1 and 6 (interprets, songwriter, impresario)

each royalty line is ***of course*** containing a "id_band" that tells which band it is linked to, and also contains a column I will dub as "data_level" of values 0,1,2 (0 = public for all viewers, 1 = private to all band members, 2 private to group's songwriters, 3 = private to agents and band's representatives)

if a bands songwriter queries the DB, he/she would be able to see all bands' royalties lines in which he/she was either band member or songwriter (and why not something else) ;
the query would be something like :
select * from royalties AS a where a.id_user=$myuserid OR a.id_band=$mybandid OR a.data_level<2;

idem for members of a band (or of multiple bands).

it's just a matter of saying "give me all publicly-available royalties lines" + "give me my bands' freely-available lines" + "give me my personally-privy lines"

a user that connects will probably be matched against the database to build a PHP session variable $mygroups which would contain a comma-separated list of all bands he is in, for the purpose of writing :

select * from royalties AS a where a.id_user=$myuserid OR a.id_band in ({$_SESSION['mybandsids']}) OR a.data_level<2;
By: chumlyumly Date: 01/05/2007 00:27:25 English  Type : Comment
This just might do the trick! I'm wrapping my head around it now, but it seems like it will work. I'll let you know either way. And thanks again for your expert advice. It's been invaluable.
By: OpConsole Date: 04/07/2007 16:52:12 English  Type : Answer
Dear,

If you found some of the above comments to have proved helpful in solving your issue, you shall Accept the Answer or sPlit points between the various useful comments. Each one can receive a quality evaluation from + (somewhat helpful) to +++ (working solution).

Given this Question has been Open for quite a while now, please accordingly "accept an Answer" ASAP

This Question will be forced-closed in one month from now.

Thanks and regards.

Admin.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 1341.942790 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page