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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rustynail
Forum Newbie
Posts: 21
Joined: Wed Jul 05, 2006 12:14 pm

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

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

have a look at grant
rustynail
Forum Newbie
Posts: 21
Joined: Wed Jul 05, 2006 12:14 pm

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Code: Select all

GRANT ALL PRIVILEGES ON yourdatabase.yourtable...
vs

Code: Select all

GRANT SELECT PRIVILEGES ON yourdatabase.yourothertable...
rustynail
Forum Newbie
Posts: 21
Joined: Wed Jul 05, 2006 12:14 pm

Post 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?
rustynail
Forum Newbie
Posts: 21
Joined: Wed Jul 05, 2006 12:14 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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).
Post Reply