Page 1 of 1

User Permissions, login, and setup - yes, newbie question

Posted: Thu Jul 13, 2006 8:30 pm
by rustynail
Again sorry for the newbie question, but can someone point me to a good tutor for setting up MySQL so that I can setup permissions based on tables.

I have created an inventory database and need to set it up so that certain users have certain permisions on specified tables.

Right now there is no security on the database and anyone that brings up the page and do whatever they want.

Thanks in advance.

Posted: Thu Jul 13, 2006 9:31 pm
by Burrito
have a look at grant

Posted: Fri Jul 14, 2006 9:41 am
by rustynail
Thanks for the fast reply. I have learned a little more, but still have some permission questions related to specific databases.


I am new at mysql and php and what I have is php/mysql accessing a db that I created called "contacts". I forgot that I had a php script automatically loading a preconfigured username and password. I created a user with phpmyadmin in the mysql database with phpmyadmin.

After changing the input file that is automatically loaded to the user I created, I can now access "contacts.db" as that user.

My questions now:

1) If I want to give this user more permissions on one db/table and less on another, how do I assign the grants per db/table versus assigning on all tables of all db's? It looks like I need to digest -> http://dev.mysql.com/doc/refman/5.0/en/grant.html a little further.

2) Now say if I eliminate or blank out the file that automatically loads the username/password and create a login form, do I have to recall this login as the user navigates through the various pages to perform various tasks? Or is this going to be stored and passed automatically.

Sorry for the newbie questions.

Posted: Fri Jul 14, 2006 9:50 am
by Burrito

Code: Select all

GRANT ALL PRIVILEGES ON yourdatabase.yourtable...
vs

Code: Select all

GRANT SELECT PRIVILEGES ON yourdatabase.yourothertable...

Posted: Fri Jul 14, 2006 2:11 pm
by rustynail
Thanks again for the fast reply.

I see how the GRANT works. This stores all user grants in the MySQL database.

Now I have created a table in my contacts database that is called Members (in Contacts.db). This stores a username and password. Am I correct to say that when that user is added to the Members table of Contact.db, I will also have to add them to the USERS table of MySQL.db?

Posted: Fri Jul 14, 2006 3:15 pm
by rustynail
Maybe I am just missing the whole thing here. Say in an example, I create a database called "DOITALL" and in it I have the following:

(table) -> (assigned permissions to each user that would be toggled on a setup screen)
Member.tbl -> view, add, modify, delete
Products.tbl -> view, add, modify, delete
Jobs.tbl -> view, add, modify, delete
Inventory.tbl -> view, add, modify, delete
Suppliers.tbl -> view, add, modify, delete
Inventory_adj.tbl -> view, add, modify, delete

Now it seems that it would only make sense to put the assigned permissions in the "Member.tbl" instead of trying to add all the members to the "MySQL.db". Then in "MySQL.db" I would put the administrator user.

When the administrator logs in, they then have permission to modify the "Member.tbl" and assign the applicable permissions to the users.

As for my second question of the top post about logging in and staying logged in would be done with Session Handling as explained -> http://us3.php.net/session

Posted: Fri Jul 14, 2006 3:26 pm
by RobertGonzalez
You can do this from the phpMyAdmin GUI. From the login page/home page when you first launch PMA, choose priveledges. From there you can edit a users priveledges.

Remember, users have two levels of privileges. One is for MySQL, the other is for a table. Make sure you handle both when managing users.

Also remember that if you are handling DB interaction permissions, that each user will have to be a user in the MySQL database's user table. That means they are a MySQL user (like the user that you use in your scripts to connect to the MySQL server and select a database).