Users Question use of generic or developed table 1

[comboy]

Hi all,

I'm new to MySQL and advanced SQL queries so hopefully my question will be clear.
I'm designing a DB that will hold details of client training dates etc. I will be building a front end in PHP but was wondering how to easily grant privileges to users based on user categories.

Can I ammend the generic User table that arrives with MySQL and create a column called User Category and link that to a User Category table and grant privileges to tables etc on my DB to users based on their user category.

Or would it be more secure to create my own table e.g. tblMyUsers and link that to a user category table and grant privileges to the users based on their user category (This would leave the ROOT ac on the generic table and not accesabile to users accessing the DB via the site I think).

Hope this makes sense


Graham.

 

[vacunita]

If you are using PHP as the front end, setting up specific users for DB access becomes irrelevant, as the front end will connect to the Db on its own with a single user you define, in the connection.

You could restrict permissions from the front end to what they can do. but actually creating users in the DB's user table is pointless. If you restrict a user to update, the only thing you'll get is an error page saying that Mysql was unable to update, but your user will have no idea what is going on.

I would do all the restricting on the front end, based on a user table of your creation.

That way you can, based on their access level, determine what buttons to show. or what actions to allow.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[comboy]

Hi Vacunita,

Thanks for the reply so I set up a user on the DB and use them as part of the connection string to the DB and then pass control over to my user table where the page will display their options.
But if I've just one user set up on the DB user table and say do not grant INSERT privilegs can I allow a user on my own front end user table complete an INSERT based on their access level.
Time to study up on PHP


Graham

 

[vacunita]

You can design your PHP application to only show actions the "client" can perform based on their user level.

Suppose you have three "clients" or front-end users.

One can Only view the tables but not change them. The other one can Update the existing data, but cannot create new records, and the third can create new records but can;t delete anything.

You would have a table that looks like:

ID username password permissions
1 Reader read 1
2 Updater update 1,2
3 DataCapture capture 1,2,3

When your PHP App looks at the table when the "client" logs in, if your "client" is Reader you know you only have to show the view button and a table selector to choose the tables to view. No need to add an update button or a page to insert records.

Had it been the DataCapture client, you would need to give him all those abilities. Show him a page to insert records, showing the table in editable form objects such as text boxes etc...

Using PHP you can choose what to show and what not to.


What I'm trying to tell you is that the user table for MYSQL is for admin purposes only. When several people will be using a db manager to connect to the DB and to modify the table add more tables add Databases etc... then you can limit those users, but for a web based interface, its just easier to not create those actions on a per user basis.








----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[comboy]

Thanks for the last post Vacuntia that clears alot up of what I need to think about in this pre development stage.

Star for you.


Graham.