I've been attempting to create a mySql database while granting
administration user permissions on just that database to a
particular user. I suppose I'm overlooking something simple, but I
just can't get it after a full day of playing. My root user and its
password work fine, but my other user does not. Note that root uses
a ~root/.my.cnf file to set user and password. It does not matter if
the administrator user uses a .my.cnf file or not (the sample does NOT).
I've created a small script to demonstrate my problem that I've attached
to this E-mail. This script drops the database then recreates it from
scratch along with running some (failing) operations as the
administration user (gilbert. While I know that mySql user names are
not related to UNIX login names... but I am having login gilbert use
m ySql user gilbert).
The key commands are:
grant all privileges on Baseball.* to gilbert identified by "first"
with grant option;
use mysql;
SET PASSWORD FOR gilberet=PASSWORD("first");
flush privileges;
I've also attached the output of the script. Please notice that the
following shows many 'N' permissions:
use msql;
select * from user where User = "gilbert";
quote
su -
mysqlaccess gilbert Baseball; #also shows nothing but NOs
while the following shows the expected 'Y' permissions:
use msql;
select * from id where User = "gilbert";
I am using Red Hat 7.1, (2.4 kernel) and mysql Ver 11.13 Distrib
3.23.36, for redhat-linux-gnu (i386)
Attachments are also available at http://www.exit109.com/~ghealton/
problem.txt
problem.mysql
problem.log
(these are all text files)
----------------------------------------------------------------------
[EMAIL PROTECTED] http://www.exit109.com/~ghealton/
----------------------------------------------------------------------
Computers are like air conditioners:
they don't work well when Windows are left open
#!/bin/bash -x
### demonstrate my problem
#### EXECUTE AS USER ROOT
echo " =================================== $*"
#### configurations users must leave alone unless they are true gurus
user=gilbert; #user name to test, as know to mySql
db=Baseball; #database to use
table=Abbott; #table name to build
password=first; #password to assign gilbert
die ()
{
echo 1>&2 "$id: $*";
exit 1;
}
id=`basename $0`; #our script name
echo "$id: creating mySql tables in $db database"
id $user || die "USER $user IS NOT PRESENT";
### VERIFY THAT THE .my.cnf FILE DOES NOT EXIST
for u in $user; do
eval "cnf=~${u}/\$my_cnf"; #standard configuration file for `mysql`
if [ -f $cnf ]; then
die "$cnf CONFIGURATION FILE EXISTS";
fi
done
echo " ======= create database and set password ======="
echo "update user set Password=PASSWORD('$password') where user='$user';"
/usr/bin/mysql -u root<<EOF; ### --host=$host
drop database if exists $db;
create database if not exists $db;
use $db;
create table if not exists $table
(
position character(20) not null,
player character(20)
);
grant all privileges on $db.* to $user identified by "$password" with grant option;
insert into $table values ( 'First', 'Who' );
insert into $table values ( 'Second', 'What' );
insert into $table values ( 'Third', "I Don't Know" );
insert into $table values ( 'Catcher', 'Today' );
use mysql
update user set Password=PASSWORD("$password") where user="$user";
flush privileges;
# SET PASSWORD FOR $user=PASSWORD("$password");
# flush privileges;
select * from user where User = "$user";
select * from db where User = "$user";
#### show encrypted version of this password
select password("$password");
EOF
status=$?; #save mySql exit status
echo " ====== showing access permissions "
if [ $status -eq 0 ]; then
mysqlaccess "$user" "$db"
status=$?
fi
echo " ======= testing selection under -u $user ======="
### this does not work, but I don't see why. The .html documentation
### seems to claim that is is a bad password (see previous mysqlaccess error),
### but the password sure looks good to me.
/usr/bin/mysql -u $user -p$password $db <<EOF; ### --host=$host
select * from $db;
EOF
if [ $status -eq 0 ]; then
echo "===== showing basic database info ====="
mysqlshow -u$user -p$password $db $table
status=$?
fi
if [ $status -eq 0 ]; then
echo "$id: successful AmpWaveUsers database creation"
else
echo "$id: DID NOT PROPERLY CREATE AmpWave DATABASE"
fi
exit $status;
#end
+ echo ' =================================== '
===================================
+ user=gilbert
+ db=Baseball
+ table=Abbott
+ password=first
++ basename ./problem.mysql
+ id=problem.mysql
+ echo 'problem.mysql: creating mySql tables in Baseball database'
problem.mysql: creating mySql tables in Baseball database
+ id gilbert
uid=1000(gilbert) gid=100(users)
groups=100(users),19(floppy),44(pppusers),27(mysql),1000(adult),1008(windows),1044(public)
+ eval 'cnf=~gilbert/$my_cnf'
++ cnf=/home/gilbert/
+ '[' -f /home/gilbert/ ']'
+ echo ' ======= create database and set password ======='
======= create database and set password =======
+ echo 'update user set Password=PASSWORD('\''first'\'') where user='\''gilbert'\'';'
update user set Password=PASSWORD('first') where user='gilbert';
+ /usr/bin/mysql -u root
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
% gilbert 5ba6098671b952ea N N N N N N
N N N N N N N N
Host Db User Select_priv Insert_priv Update_priv Delete_priv
Create_priv Drop_priv Grant_priv References_priv Index_priv
Alter_priv
% Baseball gilbert Y Y Y Y Y Y Y
Y Y Y
password("first")
5ba6098671b952ea
+ status=0
+ echo ' ====== showing access permissions '
====== showing access permissions
+ '[' 0 -eq 0 ']'
+ mysqlaccess gilbert Baseball
Could not open outputfile ~/mysqlaccess.log for debugging-info
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier ([EMAIL PROTECTED])
Changes by Steve Harvey ([EMAIL PROTECTED])
This software comes with ABSOLUTELY NO WARRANTY.
Access-rights
for USER 'gilbert', from HOST 'localhost', to DB 'Baseball'
+-----------------+---+ +-----------------+---+
| Select_priv | N | | Shutdown_priv | N |
| Insert_priv | N | | Process_priv | N |
| Update_priv | N | | File_priv | N |
| Delete_priv | N | | Grant_priv | N |
| Create_priv | N | | References_priv | N |
| Drop_priv | N | | Index_priv | N |
| Reload_priv | N | | Alter_priv | N |
+-----------------+---+ +-----------------+---+
BEWARE: Everybody can access your DB as user `gilbert' from host `localhost'
: WITHOUT supplying a password.
: Be very careful about it!!
BEWARE: Accessing the db as an anonymous user.
: Your username has no relevance
The following rules are used:
db : 'No matching rule'
host : 'Not processed: host-field is not empty in db-table.'
user : 'localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N'
BUGs can be reported by email to [EMAIL PROTECTED]
+ status=0
+ echo ' ======= testing selection under -u gilbert ======='
======= testing selection under -u gilbert =======
+ /usr/bin/mysql -u gilbert -pfirst Baseball
ERROR 1045: Access denied for user: 'gilbert@localhost' (Using password: YES)
+ '[' 0 -eq 0 ']'
+ echo '===== showing basic database info ====='
===== showing basic database info =====
+ mysqlshow -ugilbert -pfirst Baseball Abbott
mysqlshow: Access denied for user: 'gilbert@localhost' (Using password: YES)
+ status=1
+ '[' 1 -eq 0 ']'
+ echo 'problem.mysql: DID NOT PROPERLY CREATE AmpWave DATABASE'
problem.mysql: DID NOT PROPERLY CREATE AmpWave DATABASE
+ exit 1
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
