Mysql THEORY user access through custom user table

Question

I'm new to mysql and trying to get my head around user authentication for a db in mysql. The background is, in its simplest form, I am creating a document control DB. The principal table is called document control which contains a doc_id, name, date_added, blob etc.

The DB has a set of users, defined under the userid table, that contains general information such as the user_id, name, address, email, phone etc. To keep it simple for discussion, there is a relationship between the user_id and doc_id under table userdoclink. If the userid is associated with the doc_id, then that user has access to the doc_id recordset.

Tables summarised below:

**documentcontrol table**
doc_id 
name
date_added
blob_data

**userid table**
user_id
name
address_line1
address_line2
email
phone

**userdoclink table**
user_id
doc_id

The question I have is, I understand mysql has a table called USER which contains the username and password and used for DB authentication as well setting privileges to table and recordsets. If each userid will have its own username and userid what would be the best practice of linking userid to the USER usernames?

Do I need to this or is there some other way of authenticating users to a DB via a custom user table?(very likely the email of the user will be username to access the DB). The only way I know is to authenticate through php as follows. Ultimately my intention is to have granular control on user access to certain recordsets only for UPDATE and READ within a table, any guidance on best way to proceed would be appreciated.

$con = mysql_connect("localhost", "username", "password") or  die('Sorry, could not connect to server');
mysql_select_db("database", $con) or die('Sorry, could not connect to database');

Unless you want each user to have direct access to your MySQL database (i.e. via phpMyAdmin) there is no need to do anything with MySQL's USER table. Your script, which connects to the database, needs to provide a user-name, which must be a valid MySQL USER. All the other users of your PHP-Application need to be managed through your own script. — Mario Werner
Thanks for the prompt response. Assuming it is a webapp, if I understand correct, there is a generic username and password to connect the mySQL db, in the PHP script. Once connected, the user supplies their own username/password that is linked to userid table and then through custom PHP based on userid/docid relationship the applicable recordsets are displayed. Is this correct? — Ka Tech
Yes. Please also have a look at this example: 9lessons.info/2009/09/php-login-page-example.html — Mario Werner
Thanks this exactly what I was after and clears everything up, also as my next question was going to be how do you create sessions which this link answers. Thanks — Ka Tech

Maaz Rehman Maaz Rehman · Accepted Answer · 2015-03-15T08:10:08

for practice sake you must use the default settings provided by phpmyadmin as you are new , when you are done practicing and you are ready to host your site to hosting server , the hosting service will provide you a username and password for authentication , all you have to do is to replace the parameters , otherwise you are doing all right..

Mysql THEORY user access through custom user table

1 Answers