New to mySQL - Adding Users Q.

[mothra]

Please bare with me as I'm new to mySQL. I want to allow users to create accounts for themselves to access a mySQL database, rather than me having to use phpMyAdmin and do it for them. I have a couple of questions:

1. Is there any problem with setting up a php script & a form that they can use to create an account? I assume this is possible, it looks like using the GRANT statement.

2. I think that the above is possible, so assuming that... does my php script have to connect to the db as the root user to add accounts? Is there any danger in doing this?

3. ..and I want to assign all the users the same permissions, which includes allowing them to delete records. I only want to allow them to delete their own entries which I suppose will have to be done in my UI for them, does this pose any concerns? I was considering not allowing them to delete and instead just making it appear that they have deleted their records by flagging those records to not be displayed in the UI; this would also let me keep a running total of everything that has been added to the db.

Thx

[raf]

1. Is there any problem with setting up a php script & a form that they can use to create an account? I assume this is possible, it looks like using the GRANT statement.

Indeed. It's rather simple. check the manual for the correct syntax if you're not sure


2. I think that the above is possible, so assuming that... does my php script have to connect to the db as the root user to add accounts? Is there any danger in doing this?.

The account you use to open a connection in PHP will indeed need to have grant-permission. So basically, it'll need to be a root-account
It is extremely dangerous to use an account with grant-permissions inside your PHP-files, because with a little sql-injection, any user could create his own root-user. I certainly woudn't recommend this


3. ..and I want to assign all the users the same permissions, which includes allowing them to delete records. I only want to allow them to delete their own entries which I suppose will have to be done in my UI for them, does this pose any concerns? I was considering not allowing them to delete and instead just making it appear that they have deleted their records by flagging those records to not be displayed in the UI; this would also let me keep a running total of everything that has been added to the db.

You can not set concitional permissions (like on delete permission if record contains blable). If you create them an account, then they can connect directly to the db with a db-front or the comandline and delete whatever records they want.

If you only wan't them to delete records through your PHPapp, then their mysql accounts shouldn't have delete permission.

But do they realy need to have a mysql-account?

[mothra]

But do they realy need to have a mysql-account?

I don't know? Like I said I'm basically at day 1 with mySQL. If I want others to be able to add data to the database should I just set up a generic account(s) with the permissions I want it to have then forward all their data through that? What is typically done in this scenario?

The account you use to open a connection in PHP will indeed need to have grant-permission. So basically, it'll need to be a root-account

I guess pending my above questions this may not be an issue, but is there any "safe" way of letting users create mysql accounts with predefined permissions, I always sanitize form data but none the less...

[raf]

Usually, you have a webapplicaion with a MySQL db where you interact with through PHP. Inside your appllication (PHP) you include forms etc to allow the user to select, add, delete, update data. It's then your PHP script that will open the connection and perform the db-manipulations.
This PHP script will allways use the same MySQL account for all users.

Your users will most likely need to login to be identifyed (you create a usertable for this inside your db where you store the username and pwd +their permissions and other userdata), and get to the forms you created to allow them to make the datamanipulations you previewed in your application and where they have permissin for (--> the permissions are determined at the applicationlevel. Stored in one of your db-tables and selected/tested against using PHP.

So in short: a webapplication uses one MySQL account for all users. The allowed dataoperations are limited by the operations you implement inside your application (forms, formprocessing scripts etc). Restrictions (like only allowing to delete there own records ) is done inside the applicationlayer and has nothing to do with the mysql-accounts permissions.
The mysql-account for your application should definitely NOT have grant-permission since this is a huge securityrisk.

[Kiwi]

One further addition: you can use multiple users for your database, accessed by different PHP applications (or application engines). You can entirely ring-fence parts of your application. This can be reinforced, using application (PHP) security, HTTP security and mySQL security.

This isn't necessary and in all but a very small number of cases, security privledges should never be given to PHP users; even data structuring statements (CREATE etc) should be restricted.