adding user to mysql

[DigitalExpl0it]

is it possible to create a script to add a user to mysql to the table mysql and then grant that user access to a database?

GRANT ALL ON username.* TO user_table;

if so, for the password do I just used md5?

I want to create a script in php to add users instead of doing it from the command prompt

-thanx

[diego25]

No, there's is a function in MySQL called "password". Use that function instead of md5.

Diego

[DigitalExpl0it]

ok I got it to inser the password in correctly, but now if I try to use the user I had created it doesnt work... do I need to do a GRANT insert or only a insert?

heres the code

	$pass = $_REQUEST[upass];
	$fakepass = md5($_REQUEST[upass]);
	echo "<b>---- Creating User ----</b><br>";
	echo "Creating Username: ".$_REQUEST[uname]."<br>"; 
	echo "Creating Password: ".$fakepass."<br>"; 
	echo "Creating Host: ".$_REQUEST[host]."<br>"; 
	//echo "Creating Database: ".$_REQUEST[ubase]."<br>"; 
	echo "<b>--- User Privileges ---</b><br>"; 
	echo "Setting Select Privileges: ".$_REQUEST[sp1]."<br>"; 
	echo "Setting Insert Privileges: ".$_REQUEST[ip1]."<br>"; 
	echo "Setting Update Privileges: ".$_REQUEST[up1]."<br>"; 
	echo "Setting Delete Privileges: ".$_REQUEST[dp1]."<br>"; 
	echo "Setting Create Privileges: ".$_REQUEST[cp1]."<br>"; 
	echo "Setting Drop Privileges: ".$_REQUEST[dp2]."<br>"; 
	echo "Setting Reload Privileges: ".$_REQUEST[rp1]."<br>"; 
	echo "Setting Shutdown Privileges: ".$_REQUEST[sp2]."<br>"; 
	echo "Setting Process Privileges: ".$_REQUEST[pp1]."<br>"; 
	echo "Setting File Privileges: ".$_REQUEST[fp1]."<br>"; 
	echo "Setting Grant Privileges: ".$_REQUEST[gp1]."<br>"; 
	echo "Setting References Privileges: ".$_REQUEST[rp2]."<br>"; 
	echo "Setting Index Privileges: ".$_REQUEST[ip2]."<br>"; 
	echo "Setting Alter Privileges: ".$_REQUEST[ap1]."<br>"; 

$connect = mysql_connect("localhost", "root", "") or die ("<b><center>".mysql_errno().": 

".mysql_error()."</b></center>\n");
$db = mysql_select_db("mysql", $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
$sql = "INSERT INTO user SET Password = password('$pass'), Host = '$_REQUEST[host]', 
User = '$_REQUEST[uname]', Select_priv = '$_REQUEST[sp1]', Insert_priv = '$_REQUEST[ip1]', 
Update_priv = '$_REQUEST[up1]', Delete_priv = '$_REQUEST[dp1]', Create_priv = '$_REQUEST[cp1]', 
Drop_priv = '$_REQUEST[dp2]', Reload_priv = '$_REQUEST[rp1]', Shutdown_priv = '$_REQUEST[sp2]', 
Process_priv = '$_REQUEST[pp1]', File_priv = '$_REQUEST[fp1]', Grant_priv = '$_REQUEST[gp1]', 
References_priv = '$_REQUEST[rp2]', Index_priv = '$_REQUEST[ip2]', Alter_priv = '$_REQUEST[ap1]'";
$sql_result = mysql_query($sql, $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");

[diego25]

Did you grant access the user from all domains? 'cause I think by default the user is only allowed to access from localhost.

Diego

[DigitalExpl0it]

humm well all this does is this

it inserts a Y or N into all the priv fields then inserts the username and password with the correct encryption, then it inserts the host ... localhost, do I need to have it insert % as a host also?

[diego25]

Yeah, host should be'%' to allow the user to access from anywhere.

Diego

[DigitalExpl0it]

ok ive add this, but I still cant login with the user with phpmyadmin, but i have another user I have created but thru the command prompt with mysql and it works fine without a host from %

$connect = mysql_connect("localhost", "root", "") or die ("<b><center>".mysql_errno().": 

".mysql_error()."</b></center>\n");
$db = mysql_select_db("mysql", $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
$sql = "INSERT INTO user SET Password = password('$pass'), Host = '$_REQUEST[host]', 
User = '$_REQUEST[uname]', Select_priv = '$_REQUEST[sp1]', Insert_priv = '$_REQUEST[ip1]', 
Update_priv = '$_REQUEST[up1]', Delete_priv = '$_REQUEST[dp1]', Create_priv = '$_REQUEST[cp1]', 
Drop_priv = '$_REQUEST[dp2]', Reload_priv = '$_REQUEST[rp1]', Shutdown_priv = '$_REQUEST[sp2]', 
Process_priv = '$_REQUEST[pp1]', File_priv = '$_REQUEST[fp1]', Grant_priv = '$_REQUEST[gp1]', 
References_priv = '$_REQUEST[rp2]', Index_priv = '$_REQUEST[ip2]', Alter_priv = '$_REQUEST[ap1]'";
$sql_result = mysql_query($sql, $connect) or die ("<b><center> Error1: ".mysql_errno().": ".mysql_error()."</b></center>\n");

$sql2 = "INSERT INTO user SET Host = '%', User = '$_REQUEST[uname]', Select_priv = 'N', Insert_priv = 'N', 
Update_priv = 'N', Delete_priv = 'N', Create_priv = 'N', Drop_priv = 'N', Reload_priv = 'N', Shutdown_priv = 'N', 
Process_priv = 'N', File_priv = 'N', Grant_priv = 'N', References_priv = 'N', Index_priv = 'N', Alter_priv = 'N'";
$sql_result2 = mysql_query($sql2, $connect) or die ("<b><center>Error2: ".mysql_errno().": ".mysql_error()."</b></center>\n");

[DigitalExpl0it]

ok I found out how to do it

//Grant user to database
$db = mysql_select_db("mysql", $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
$sql = "GRANT ALL ON $_REQUEST[dbase] TO $_REQUEST[uname]@$_REQUEST[host] IDENTIFIED BY '$_REQUEST[upass]'";
$sql_result = mysql_query($sql, $connect) or die ("<b><center> Error1: ".mysql_errno().": ".mysql_error()."</b></center>\n");

I must use grant

well now thats done, how does one create a database with php not a table but a database ....

[DigitalExpl0it]

ok everything seems to be working, but now when ever I login with a user which I created with this code it displays two database's

the one that was created with the user account and the mysql database, now it see's it but the user cannot do anything with it. is my grant permissions messed up?

$connect = mysql_connect("localhost", "root", "") or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
//Create Database
mysql_create_db("$_REQUEST[uname]") or die("Create table Error: ".mysql_error());
//Grant user to database
$db = mysql_select_db("mysql", $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
$sql2 = "GRANT ALL ON $_REQUEST[uname] TO $_REQUEST[uname]@localhost IDENTIFIED BY '$_REQUEST[upass]'";
$sql_result2 = mysql_query($sql2, $connect) or die ("<b><center> Error2: ".mysql_errno().": ".mysql_error()."</b></center>\n");
$sql = "GRANT ALL ON $_REQUEST[uname].* TO $_REQUEST[uname]";
$sql_result = mysql_query($sql, $connect) or die ("<b><center> Error1: ".mysql_errno().": ".mysql_error()."</b></center>\n");

[diego25]

No, it should be correct. The mysql database is used by MySQL to store users and passwords and stuff that MySQL needs....so the user you created shouldn't have permission to do anything with that database. Only root should have permission.

Diego

[DigitalExpl0it]

yeah, I thought the scripts look correct, but everytime I log into phpmyadmin, I can see the mysql database and the database that was setup for the user. But the user cannot do anything with the mysql database ....

is there a way to not even show or have read or whatever to the mysql database?

here is a screenshot

the mysql database should not be there...the user cannot do anything with it, but he can see it.

[DigitalExpl0it]

ok ok, I think I figured this out... it seems to be working how I want it to now..

//---------Insert User------------\\
$sql_user = "
INSERT INTO mysql.user
SET Host = 'localhost',
User = '$_REQUEST[uname]',
PASSWORD = PASSWORD( '$_REQUEST[upass]' ) ,
Select_priv = 'Y',
Insert_priv = 'Y',
Update_priv = 'Y',
Delete_priv = 'Y',
Create_priv = 'Y',
Drop_priv = 'Y',
Reload_priv = 'Y',
Shutdown_priv = 'Y',
Process_priv = 'Y',
File_priv = 'Y',
Grant_priv = 'Y',
References_priv = 'Y',
Index_priv = 'Y',
Alter_priv = 'Y'";
$sql_result = mysql_query($sql_user, $connect) or die ("<b><center> Error1: ".mysql_errno().": ".mysql_error()."</b></center>\n");

//----------Grant User--------------\\
$sql_grant = "
GRANT SELECT ,
INSERT ,
UPDATE ,
DELETE ,
CREATE ,
DROP ,
RELOAD ,
SHUTDOWN ,
PROCESS ,
FILE ,
REFERENCES ,
INDEX ,
ALTER ON `$_REQUEST[uname]` . * TO '$_REQUEST[uname]'@'localhost' WITH GRANT OPTION";
$sql_result2 = mysql_query($sql_grant, $connect) or die ("<b><center> Error2: ".mysql_errno().": ".mysql_error()."</b></center>\n");

//---------Revoke Databases from user------------\\
$sql_revoke1 = "REVOKE ALL PRIVILEGES ON *.* FROM '$_REQUEST[uname]'@'localhost'";
$sql_result3 = mysql_query($sql_revoke1, $connect) or die ("<b><center> Error3: ".mysql_errno().": ".mysql_error()."</b></center>\n");
$sql_revoke2 = "REVOKE GRANT OPTION ON *.* FROM '$_REQUEST[uname]'@'localhost'";
$sql_result4 = mysql_query($sql_revoke2, $connect) or die ("<b><center> Error4: ".mysql_errno().": ".mysql_error()."</b></center>\n");