LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 09-18-2003, 05:41 PM   #1
markopolox
LQ Newbie
 
Registered: Sep 2003
Posts: 12

Rep: Reputation: 0
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

Last edited by markopolox; 09-18-2003 at 05:47 PM.
 
Old 09-18-2003, 06:14 PM   #2
sk8guitar
Member
 
Registered: Jul 2003
Location: DC
Distribution: mandrake 9.1
Posts: 415

Rep: Reputation: 30
when that prompt happens, try to select information from a database. it should give you an error saying you don't have permissions.
 
Old 09-18-2003, 07:39 PM   #3
markopolox
LQ Newbie
 
Registered: Sep 2003
Posts: 12

Original Poster
Rep: Reputation: 0
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.
 
Old 09-20-2003, 03:49 PM   #4
Robert0380
LQ Guru
 
Registered: Apr 2002
Location: Atlanta
Distribution: Gentoo
Posts: 1,280

Rep: Reputation: 47
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.
 
Old 09-21-2003, 05:03 AM   #5
then
Member
 
Registered: Oct 2002
Location: FDR India
Distribution: RH9, Knoppix, MEPIS
Posts: 65

Rep: Reputation: 15
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
 
Old 09-21-2003, 03:52 PM   #6
markopolox
LQ Newbie
 
Registered: Sep 2003
Posts: 12

Original Poster
Rep: Reputation: 0
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.
 
Old 09-22-2003, 03:06 AM   #7
then
Member
 
Registered: Oct 2002
Location: FDR India
Distribution: RH9, Knoppix, MEPIS
Posts: 65

Rep: Reputation: 15
./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
 
Old 09-22-2003, 10:09 AM   #8
markopolox
LQ Newbie
 
Registered: Sep 2003
Posts: 12

Original Poster
Rep: Reputation: 0
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.
 
Old 09-23-2003, 03:58 AM   #9
then
Member
 
Registered: Oct 2002
Location: FDR India
Distribution: RH9, Knoppix, MEPIS
Posts: 65

Rep: Reputation: 15
Hi

Quote:
1)...Since you don't have a working root password, you must do this from the system level...
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 .

Quote:
In the example he has...Does that make a difference?
not that I know of. The only confusion is that mysql-server-admin is by default called 'root' and so is the Linux admin.

Quote:
I'm being really specific ...I type: $su mysql
Yes.

Quote:
/path/to/mysqld --skip-grant-tables &...jumps to a blank line...Once at the shell I type just type "mysql" as instructed and then I get mysql> Is this correct?
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.

Quote:
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.
, 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.

Quote:
I'm really sorry to bug man...
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Grant Privileges - MySQL Jannyno Linux - General 2 06-23-2012 03:00 AM
Why mysql GRANT does not work? robertoneto123 Linux - Software 2 01-23-2004 08:33 AM
Updating MySQL grant tables... sigep739 Linux - Newbie 2 01-04-2004 12:57 AM
mysql grant issues Robin01 Linux - Newbie 1 10-29-2003 10:56 PM
Mysql User grant problems ubers0ldat Linux - Software 7 07-30-2003 01:33 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 11:22 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration