[SOLVED] Creating mysql db user

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post by dull1554 »

yea, i cant believe i let that slip, first off your trying to connect to a account that does not exist, and 2 to create a user, correct me if im wrong, you have to be connected as root user

so if you did this it should work

Code: Select all

<?php
$db = @mysql_connect("localhost","root","PASSWORD") or die("Could not connect: " . mysql_error());
?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You aren't encrypting the password so you won't be able to log in. MySQL will automatically encrypt the password you are trying to connect with and will end up comparing it to an unencrypted password.

Have a read of the manual:
http://www.mysql.com/doc/en/Adding_users.html

and compare your SQL statement to the one presented there.

Mac
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

i was about to suggest the encrypt problem too, twig beat me to it, what you could've done/can do is to download mysql front (there is a linkfor it in general i think) and add a user with that, then see waht tables are affected and how.

btw, you have to use password(); to encrypt your passwords when you adda user, when you connect, there is no need to it cos mysql will do it for you.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

[Edit-damn deleted posts, this one makes no sense now but I was sure at the time I was replying to something :? :lol:]

And your code is? And you've followed the directions in the documentation?

Mac
Last edited by twigletmac on Wed Dec 17, 2003 5:08 am, edited 1 time in total.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

you talking to me twig? :?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Err, no, there was another post at the end from Yaron when I posted???

Mac
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

I have encrypted my password in the Insert command but when I try to connect I still get access denied.
Funny thing: If I don't use a password i.e. creating a user with an empty passowrd , I pass the mysql_connect function but fails on the mysql_select_db function with an access denied error
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You will fail to select a database because you may not have set the permissions for that user on that database. Unless you keep us uptodate on the state of your code we will not be able to help you.

I ask again:

And your code is? And you've followed the directions in the documentation?

Mac
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

I have posted my code in one of my previous posts.
The user is entered successfully to the right place with ALL of the privilages.
the new record looks exatcly like a different record that works fine (except of the user name all is the same) so I can't see why can't I log on with the new user...
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You posted code from before you made changes to encrypt the password, without you showing your SQL statements as you go along how are we supposed to help you work out what is going wrong?

Maybe if you showed us a dump of the data as well so we could test it locally? Without this sort of information we are just scratching around in the dark and will have trouble helping you solve this problem.

Mac
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

agian..get mysql front, just for the sake of it, add a new user with it, see what happens, if you can connect with that user then you will know your code is messing up somewhere.
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

here is my updated code

Post by yaron »

Code: Select all

<?php
$db = @mysql_connect("localhost", "root") or die("Could not connect: " . mysql_error());
 mysql_select_db("mysql",$db);
   $sqlDbUsers="INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv) VALUES ('localhost', 'stam', PASSWORD('5194351'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')";
   mysql_query($sqlDbUsers) or die('Query failed: ' . mysql_error());
   mysql_close($db);


$host = "localhost"; 
$user = "stam"; 
$password = "5194351"; 

$db = @mysql_connect($host,$user,$password) or die("Could not connect: " . mysql_error());
   echo $db;
   if(!mysql_select_db("myDB",$db))
   {
     echo "<br>No database selected<br>". mysql_error();
	 exit;
   }

?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Doesn't look like you reloaded the grant tables before trying to connect as the new user. You really, really, really should carefully read:
http://www.mysql.com/doc/en/Adding_users.html

I have found all of the problems I've mentioned thus far via that page, you should also RTFM.

Mac
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

Thanks twig
This one worked:
GRANT ALL PRIVILEGES ON *.* TO stam@localhost
IDENTIFIED BY '5194351' WITH GRANT OPTION;
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

Why use INSERT with the mysql.user table?
It would be a lot easier just using grant, you dont have to worry about the password encoding nor locking and rehashing and such..

GRANT SELECT,INSERT,UPDATE ON * TO 'username'@'localhost' IDENTIFIED BY 'password'
Post Reply