[Lydon]

Hi all,

I'm just setting up a database but could use some advice on something I'll be needing to do.
I have a table of users which includes name, email etc, but also a column which indicates whether the user is an admin or a client.

I then will have a (yet unknown) number of tables which follow the exact same structure but have a different name. For example:
A
B
C

When the website I'm building is finished (which will get its information from this database), there is going to be a number of clients who can log in.
Depending on who they are, they might have access to 1 or more of the tables.

For example:
Client 1 should be able to see the table name A and B
Client 2 should be able to see the table name B and C
Client 3 should be able to see the table name A and B and C

I know it's not the best idea to display the actual table names, but I can display them as a list of readable words, rather than db_names_like_this:

$result = mysql_query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES
	  WHERE table_schema = 'database_name'") or die(mysql_error());
while($array=mysql_fetch_row($result))
{ 
 $table_name = str_replace('_',' ',$array[0].'');
 echo ucwords($table_name) . '<br />'; 
}

Question is (finally)...HOW on earth do I set this up? It's possible that in future, a single client could have access to see a list of tens of table names, while another client should only be able to see one table name.
What columns am I going to need to add (and where?) which states "This client can see These table names"

I have no idea how to go forward on this one.

If anyone could give some pointers or hints as to how, I'd be most grateful.

Cheers

[e_i_pi]

You might need a user-rights setup. Imagine you have your tables A, B, C, and then you have these following three tables:

Users

ID (PK, int)
Username
FirstName
Surname

Rights

ID (PK, int)
Name

UserRights

ID (PK, int)
UserID (FK -> Users.ID)
RightID (FK -> Rights.ID)

This way you can define multiple rights (e.g. - "Has Access to table A", "Has Access to table B"), and then define many-to-many relationships between Users and Rights using the UserRights table. The UserRights table is a type of bridge table, a.k.a. junction table, map table, link table etc. It defines relationships between one table's data (e.g. Users) and another's (e.g. Rights).

Once you have that table, then you can determine if a user has a right to a table using server-side code. You may want to add a column to the Rights table which has the table name that the right grants access to, to make things easier once you hit your server-side code.

This post has been edited by e_i_pi: 10 September 2012 - 01:57 PM

[Atli]

I think you may be thinking about this the wrong way.

Instead of creating multiple tables with different levels of access, create only one table where each row has a different level of access. This way you can use the same concept e_i_pi suggested but implemented and enforced on the database level.

You'd just have to expand his table structure to include that one table, and another bridge table.

Users

ID (PK, int)
Username
FirstName
Surname

TheTable

ID (PK, int)
Whatever
IsIn
ThisTable

Rights

ID (PK, int)
Name

UserRights

UserID (PK, FK -> Users.ID)
RightID (PK, FK -> Rights.ID)

TableRowRights

TableRowID(PK, FK -> TheTable.ID)
RightID (PK, FK -> Rights.ID)

Quote

I know it's not the best idea to display the actual table names, but I can display them as a list of readable words, rather than db_names_like_this:

You should look into the SHOW TABLES syntax. It's a shortcut to what the query you used does.