MYSQL GRANT etc etc
 

I've created a number of users with mysql, they all need a username and password work.

However; when I type any username out of thin air.

mysql -h foobarz -p
Password (no password I just hit ENTER key)


It gives me the prompt
mysql>

foobarz is not on the sysem it is just an arbitrary word I made up on the spot. How can I set my permissions to not allow this from happening

when that prompt happens, try to select information from a database. it should give you an error saying you don't have permissions.

Yes
 

For the arguement of security why would it let a user even go that far.

Thanks by the way, you are correct I could not create a table.

this is a wide open guess and may be dead wrong:


maybe mysql is allowing the connection because it is comming from localhost? check your mysql table for permissions.

i have found that the best way to look at it is by using webmin rather than trying to do select * from <table> at the command line.

Hi

post the output of select user, host from mysql.user. That would tell why a random user was allowed to connect in the first place.

regards
theN

I'm really sorry man but...
 

I'm new to mysql
(doing more reading before I break anything)

When I see:
mysql>

What would I type in order to get the results in the above post.

Please and thanks.

./mysql -u root -h host-ip|localhost -p (hit enter) *
mysql >use mysql;
mysql >select user, host from user;

* only root can access the mysql database
* use either the mysql-server-host-ip-address or localhost
* if your mysql-root doesn't have a password, then don't use the -p argument at all.

regards
theN
ps: don't forget to start the mysql-server ;)

I feel bad wasting your time with this stuff but maybe this will clear things up:
This is what I have on my debian box:

$ ls -ld /var/lib/mysql
drwxr-xr-x 5 mysql mysql 4096 Sep 17


This is what I was told to do that messed things up a bit for me.
1)
Kill mysqld. Since you don't have a working root password, you must do
this from the system level, rather than with mysqladmin. On
RedHat-ish systems, this can be done with:
# /etc/init.d/mysql stop

2)
Find out who owns the mysql's root directory on your machine. On my machine,
this looks like:
$ ls -ld /var/lib/mysql
drwxr-xr-x 5 mysql root 4096 Mar 25 20:39 /var/lib/mysql/

So it's "mysql" on my system. You will need to su to this user if mysqld
will not allow itself to be run by your system's root. This may also
require you to run passwd on that user.

3)
su to [owner-of-mysql-root-directory]

4)
Run mysqld this way:
$ /path/to/mysqld --skip-grant-tables &

Starting mysqld this way allows all users full access to all databases,
so we'll change that later.

5)
Run mysql. Once in the monitor, do:
mysql> USE mysql;
[...]
mysql> UPDATE user SET password = password('yournewpassword')
-> WHERE User = 'root';
[...]
mysql> Exit

6)
Exit from user [owner-of-mysql-root-directory]. Then, as user, do:
$ mysqladmin reload

This is the step that establishes your new grant tables settings,
restricting access back to normal.

7)
Start the mysql monitor as its root user:
$ mysql -u root -p
Enter password:[yournewpassword]
mysql> USE whatever;


-------------------------------------------------------------------------------
Questions:
In the example he has "root" and I have "mysql" on my debian box. Does that make a difference????

*In step 3 it says "su to [owner-of-mysql-root-directory]"
I'm being really specific but would that mean I type:
$su mysql

*Run mysqld this way:
$ /path/to/mysqld --skip-grant-tables &
When I run this command it jumps to a blank line. I hit enter and then I get the shell, is this the way it is supposed to be?? Once at the shell I type just type "mysql" as instructed and then I get mysql> Is this correct???


---This is where it gets confusing:----

Run mysql. Once in the monitor, do:
mysql> USE mysql;
[...]
mysql> UPDATE user SET password = password('yournewpassword')
-> WHERE User = 'root';
[...]
mysql> Exit

If you notice there are two ways "user" is printed, (user and User). Do I replace "User" with an actual user or something else, and does 'root'== 'mysql' in my situation.



I'm really sorry to bug man, I know you guys have better things to discuss, but this is my first machine with mysql that I have actually running I would just like to make it a bit more secure.

Please and thanks.

Hi

IMHO, not necessary. If your mysql-root does not have a password then don't use the -p argument at all.

I have RH and MDK, no Debian. I use mysql binary downloaded from mysql.com, not the one which comes with the OS CD. I don't know much about starting and stopping services either :(.

not that I know of. The only confusion is that mysql-server-admin is by default called 'root' and so is the Linux admin.

Yes.

Usually I get a msg about mysql-server starting. I've never seen a blank line, maybe its peculiar to your distro or due to the & which pushes the service to the background (not sure about this).

Anyway, when you get a blank line do ps aux and find out if mysqld is running. If its running, then do this -
* ./mysql (no arguments necessary coz grants are skipped)
* mysql > set password for 'root'@'localhost'=PASSWORD('new_password'); (sets password for mysql's root on localhost)
* mysql > flush privileges; (reload privilege tables)

quit mysql-client and restart mysql-server, this time without the --skip-grant-tables option.

:), you should've checked the mysql manual for UPDATE syntax. The first 'user' is the name of a table in mysql database, the second 'User' is a column-name in table user. Column-names are NOT case sensitive.

:) you aren't. Please read the online manual first. All your questions are already answered in that very clearly.

SUMMARY

ensure mysql server is NOT running
* su mysql (switch-user to owner of mysql-directory)
* cd /mysql-directory/bin
* ./mysqld --skip-grant-tables (start mysql server with access to all databases)
* ./mysql (connect to mysql)
* mysql > SET PASSWORD FOR 'root'@'localhost'=PASSWORD('new_password'); (set password for mysql's root)
* mysql > flush privileges; (reload privilege tables)
* mysql > quit;
then repeat the 1-3 steps, but DON'T use --skip-grant-tables this time.

hth
theN