Re: MySQL permission

From: Joe (joe_at_jretrading.com)
Date: 05/26/05 

Date: Thu, 26 May 2005 21:21:59 +0000

Timothy Murphy wrote:
> Dave Uhring wrote:
>
>
>>If you had bothered to read and implement the instructions presented to
>>you after installing the initial database you would not have this problem.
>>
>> echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !"
>> echo "This is done with:"
>> echo "$bindir/mysqladmin -u root password 'new-password'"
>> echo "$bindir/mysqladmin -u root -h $hostname password 'new-password'"
>> echo "See the manual for more instructions."
>
>
> I don't recall getting any such message on my system (Fedora-3).
> What exactly do you mean by "installing the initial database"?
> I just gave the command "sudo yum install mysql-server".

I gather this does the initial setup, though not the setting of root
passwords. This is also done for you by Debian.

>
> However, I have set a password for root
> on my MySQL server (alfred), and have not had any problems on that score.
>
> On the other hand, I have had problems setting a password for root
> on the machine (martha) which I am using to access the server.
>
> I take it the second mysqladmin above is intended for this purpose?

They are effectively two different users and can have different
passwords. If you just ask for user root, e.g. mysql -u root -p, the
localhost one is assumed. You have to ask for -u root@<hostname> for
the other one. The intention is that they should have the same password
so that whether the hostname is given or not, the same things happen.

> [It seems to me incidentally that there should be some explanation
> if two different commands are given.
> Are they meant to be alternatives?
> Do you consider that to be obvious?]
>
> For some reason that is not clear to me,
> I cannot just give the name of the machine "martha" as $hostname,
> nor can I give the IP address (192.168.3.6)
> although the documentation seems to suggest I can.
> I have to give the name "martha.murphy.ie",
> the last part being a domain name I invented at some point long ago
> when some piece of software required me to give it.
> The command "hostname" just returns "martha",
> so I am not really sure where MySQL got the "full" domain name from.
> However, that is the least of my worries.

That depends on how name resolution is done. I have only a few machines
so I make sure the /etc/hosts file on each machine contains both the
full domain name and hostname of all of them. This makes life a lot
simpler. Windows machines have hosts files too.

>
> Some queries I have.
> Are root on the MySQL machine and root@martha.murphy.ie
> completely unrelated users, or is there some relation between them?

Yes, they are unrelated, and that *is* a vital piece of information.
I can remember trying to work out what the connection was between
a Unix root user, a /root directory and a / directory. Function
overloading is a bad idea.
>
> I have the following conversation on alfred (the server machine):
> ==========================================
> [tim@alfred ~]$ mysql -u root -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 77 to server version: 3.23.58
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> grant all privileges on *.* to tim@martha.murphy.ie
> identified by 'tim';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> \q
> Bye
> ==========================================
>
> Am I right in thinking that this will give user tim
> on the client machine access to everything on the server?
>
Not sure. I've never seen the 'identified by tim' bit, and
I think any update query that works gives a non-zero result
for 'rows affected'.

Start interactive mode as root and check like this:
show databases;
<list of databases including mysql>

use mysql;
show tables;
<list of the tables including user>
select * from user;
<vast ream of stuff, which you can slowly and painfully decode
to see which users have which privileges>

To see a user and host list, just do:
select user, host from user;

It's a lot easier with MySQLCC, for which there is a FC2 version
which seems OK on FC3. Find it with your favourite RPM search.

Oh, by the way, if you specify a user from any machine on a network,
the wildcard is %, e.g. fred@192.168.0.%