Creating a MySQL database from PHP with username, password and all privileges

[titosd]

Hello,

how to create mysql database and grant privileges via php code

I need to created a new database with new user and to set privileges for this user within my php script, how can i do this?

Regards,

TitoSd

[Biiim]

something like this

CREATE USER 'testing'@'localhost' IDENTIFIED BY '1234';

GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON  *.* TO 'testing'@'localhost' WITH GRANT OPTION;

<?php 
$user = 'testing';
$host = 'localhost';
$pass = '1234';
$createQ = "CREATE USER '{$user}'@'{$host}' IDENTIFIED BY '{$pass}'";
$grantQ = "GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON  *.* TO '{$user}'@'{$host}' WITH GRANT OPTION";

if(mysql_query($createQ)){
	echo 'user created<br/>';
	if(mysql_query($grantQ)){
		echo 'permissions granted<br/>';
	}else{
		echo 'permissions query failed:'.mysql_error().'<br/>';
	}
	
}else{
	echo 'user create query failed:'.mysql_error().'<br/>';
}
?>

[weekendrockstar]

Most servers will not allow a PHP script the necessary level of privileges to create a new database and/or a new user/password. Theoretically it would be possible as phpmyadmin and I believe that one-click installers such as Fantastico can do it. But most server hosts that run CPanel will allow those types of software to actually create a new database and an associated user with all privileges.

Which is why if you've installed any largely used PHP-based system such as a CMS, blog, forums, etc... you have to specify the name of an already existing database but you can tell it the table names it can create. But again you have to specify an existing DB name as well as the associated username/password.

It's also a HUGE security risk on your end. If you weren't 100% solid with how it were handled it could allow someone to go even further than one normally can if they've figured out how to exploit your database.

I will point out the following link but I'd definitely say "use at your own risk". I haven't tested it and have never used it because I can just do it in cPanel. But this page offers a URI method of doing what you seem to want to do on a server that runs cPanel but without their own script. It may require cURL be installed and available for your use on your server. I am not stating that I'm necessarily against its use but it's doing something that the server host installed cPanel and phpmyadmin/mysqladmin to do and they may not like someone overriding it if they find out. http://www.zubrag.com/scripts/cpanel-database-creator.php

That still isn't 100% what you were asking for but close. It's sort of a piggyback method of using the fact that the servers who operate cPanel have their MySQL database servers accept database creation queries from cPanel. So the linked code attempts to send a cPanel-esque database creation query.

[titosd]

I tried to copy and paste your code, but id didn"t work

I get this error:

user create query failed:Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

How could I fix this

thanks

[weekendrockstar]

I'm afraid that I don't believe that it is fixable. You need to have root access to the MySQL server. Servers hosts will set it up so that cPanel and phpMyadmin are allowed to do it. Which is why I offered my link above which tries to mimic a cPanel database/user creation query. But otherwise I don't think it can really be done without actually owning the MySQL server and having such rights. Again, I could be mislead but I am fairly certain this is the case and so far all the research I've done points to this.

The only references I'm finding online for doing what you want are if you have SUPER privileges to the MySQL server and even those seem to be command line only options. And for that you need to have command line privileges.

[Biiim]

I tried to copy and paste your code, but id didn"t work

I get this error:

user create query failed:Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

How could I fix this

thanks

You need to connect with a mysql user that has full privileges to create users, the standard one is called "root" and can only log on via localhost. It can also create other users with full access so you can either log on with php using that or create another user for php to log on with.

You should be really careful with keeping that logon safe though that is full on access to the mysql, eg make sure its never echo'ed and keep security on who can ftp to it.

[Biiim]

I'm afraid that I don't believe that it is fixable. You need to have root access to the MySQL server. Servers hosts will set it up so that cPanel and phpMyadmin are allowed to do it. Which is why I offered my link above which tries to mimic a cPanel database/user creation query. But otherwise I don't think it can really be done without actually owning the MySQL server and having such rights. Again, I could be mislead but I am fairly certain this is the case and so far all the research I've done points to this.

The only references I'm finding online for doing what you want are if you have SUPER privileges to the MySQL server and even those seem to be command line only options. And for that you need to have command line privileges.

you can create users with php, the mysql user just needs the permissions