MySQL Bugs: #14618: mysql_setpermission doest not work anymore

Bug #14618	mysql_setpermission doest not work anymore
Submitted:	3 Nov 2005 21:18	Modified:	3 Aug 2007 16:19
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.15, 5.1 BK, 5.0 BK	OS:	Linux (Debian GNU/Linux)
Assigned to:	Jonathan Perkin	CPU Architecture:	Any
Tags:	qc

Description:
Debian bug report: http://bugs.debian.org/328287

mysql_setpermission is broken in this package. Attempting
to use menu option #4 (``Create/append database
administrative privileges for an existing database and
host combination'') causes the script to crash with

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES at /usr/bin/mysql_setpermission line 260, <STDIN> line 15.

Transcript as follows:

ious:/etc/apt# mysql_setpermission
Password for user to connect to MySQL:
######################################################################
## Welcome to the permission setter 1.3 for MySQL.
## made by Luuk de Boer
######################################################################
What would you like to do:
1. Set password for an existing user.
2. Create a database + user privilege for that database
and host combination (user can only do SELECT)
3. Create/append user privilege for an existing database
and host combination (user can only do SELECT)
4. Create/append broader user privileges for an existing
database and host combination
(user can do SELECT,INSERT,UPDATE,DELETE)
5. Create/append quite extended user privileges for an
existing database and host combination (user can do
SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,
LOCK TABLES,CREATE TEMPORARY TABLES)
6. Create/append database administrative privileges for an
existing database and host combination (user can do
SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,
CREATE TEMPORARY TABLES,SHOW DATABASES,PROCESS)
7. Create/append full privileges for an existing database
and host combination (user has FULL privilege)
8. Remove all privileges for for an existing database and
host combination.
(user will have all permission fields set to N)
0. exit this program

Make your choice [1,2,3,4,5,6,7,0]: 2

Which database would you like to add: fishsoup
The new database fishsoup will be created

What username is to be created: fishsoup
Username = fishsoup
Would you like to set a password for fishsoup [y/n]: n
We won't set a password so the user doesn't have to use it
We now need to know from what host(s) the user will connect.
Keep in mind that % means 'from any host' ...
The host please: localhost
Would you like to add another host [yes/no]: no
Okay we keep it with this ...
The following host(s) will be used: localhost.
######################################################################

That was it ... here is an overview of what you gave to me:
The database name : fishsoup
The username : fishsoup
The host(s) : localhost
######################################################################

Are you pretty sure you would like to implement this [yes/no]: yes
Okay ... let's go then ...

Everything is inserted and mysql privileges have been reloaded.

######################################################################
## Welcome to the permission setter 1.3 for MySQL.
## made by Luuk de Boer
######################################################################
What would you like to do:
1. Set password for an existing user.
2. Create a database + user privilege for that database
and host combination (user can only do SELECT)
3. Create/append user privilege for an existing database
and host combination (user can only do SELECT)
4. Create/append broader user privileges for an existing
database and host combination
(user can do SELECT,INSERT,UPDATE,DELETE)
5. Create/append quite extended user privileges for an
existing database and host combination (user can do
SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,
LOCK TABLES,CREATE TEMPORARY TABLES)
6. Create/append database administrative privileges for an
existing database and host combination (user can do
SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,
CREATE TEMPORARY TABLES,SHOW DATABASES,PROCESS)
7. Create/append full privileges for an existing database
and host combination (user has FULL privilege)
8. Remove all privileges for for an existing database and
host combination.
(user will have all permission fields set to N)
0. exit this program

Make your choice [1,2,3,4,5,6,7,0]: 6

Which database from existing databases would you like to select:
You can choose from:
- fishsoup
- mysql
Which database will it be (case sensitive). Type * for any:
fishsoup
The database fishsoup will be used.

What username is to be created: fishsoup
Username = fishsoup
Would you like to set a password for fishsoup [y/n]: n
We won't set a password so the user doesn't have to use it
We now need to know from what host(s) the user will connect.
Keep in mind that % means 'from any host' ...
The host please: localhost
Would you like to add another host [yes/no]: no
Okay we keep it with this ...
The following host(s) will be used: localhost.
######################################################################

That was it ... here is an overview of what you gave to me:
The database name : fishsoup
The username : fishsoup
The host(s) : localhost
######################################################################

Are you pretty sure you would like to implement this [yes/no]: yes
Okay ... let's go then ...

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES at /usr/bin/mysql_setpermission line 260, <STDIN> line 15.

How to repeat:
...

Suggested fix:
...

Thank you for a problem report. Sorry, but this utility (mysql_setpermission) is neither created nor supported by MySQL. The problem you encountered may be caused by a bug in MySQL server, misconfiguration of your server or a bug in this utility.

So, please, try to get the real SQL statement it tries to execute when you perform these actions. Send the results of SHOW PROCESSLIST statement executed in mysql command line client while you are getting this error message.

The content of your my.cnf file may be useful too.

Sorry for describing it as non-supported, it was my fault. 

In any case, please, try to provide the information I asked for.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This bug still exists. Is this script not in your test suite? I just tried again and got:

######################################################################

That was it ... here is an overview of what you gave to me:
The database name       : www_maxforcetmspro_com
The username            : maxforce
The host(s)             : %
######################################################################

Are you pretty sure you would like to implement this [yes/no]: yes
Okay ... let's go then ...

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES at /usr/bin/mysql_setpermission line 260, <STDIN> line 10.

It says it is version 1.3:

lepton [5:29pm] % mysql_setpermission --help
----------------------------------------------------------------------
                 The permission setter for MySQL.
                      version: 1.3

                 made by: Luuk de Boer <luuk@wxs.nl>
----------------------------------------------------------------------

This is a handy script but it hasn't worked in the last few versions of mysql.

Thank you for the report.

Verified as described using last BK sources.

Problem occurs only if one tries to add user with same name and host as existing one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31272

ChangeSet@1.2676, 2007-07-20 17:58:00+01:00, jperkin@chorlton.adsl.perkin.org.uk +1 -0
  mysql_setpermission.sh:
    Remove option 6 - trying to grant global privileges at the database
    level does not work.  The spirit of the program appears to be aimed
    at database privileges, so do not give the option of granting global
    privileges as it may be unexpected.  Fixes bug#14618

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31946

ChangeSet@1.2675, 2007-08-01 11:58:25+02:00, jperkin@production.mysql.com +1 -0
  Option 6 tries to grant global privileges at the database level
  which does not work.  Removing these attempted privileges makes
  this identical to option 5 so remove it completely.  The spirit
  of the program appears to be aimed at database privileges, so do
  not add another option for granting global privileges as it may
  be unexpected.  Fixes bug#14618 (same as previous patch, this
  time applied to -maint tree).

Pushed into 5.1.21-beta

Pushed into 5.0.48

Pushed into 4.1.24

Noted in 4.1.24, 5.0.48, 5.1.21 changelogs.

mysql_setpermission tried to grant global-only privileges at the
database level.