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

[3 Nov 2005 21:18] Christian Hammers
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:
...
[4 Nov 2005 7:05] Valeriy Kravchuk
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.
[4 Nov 2005 16:16] Valeriy Kravchuk
Sorry for describing it as non-supported, it was my fault. 

In any case, please, try to provide the information I asked for.
[5 Dec 2005 0:00] Bugs System
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".
[23 Feb 2007 23:31] Michael Koppelman
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.
[2 Mar 2007 12:55] Sveta Smirnova
Thank you for the report.

Verified as described using last BK sources.
[2 Mar 2007 12:56] Sveta Smirnova
Problem occurs only if one tries to add user with same name and host as existing one.
[20 Jul 2007 16:59] Bugs System
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
[1 Aug 2007 10:00] Bugs System
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).
[2 Aug 2007 19:13] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:15] Bugs System
Pushed into 5.0.48
[2 Aug 2007 19:16] Bugs System
Pushed into 4.1.24
[3 Aug 2007 16:19] Paul Dubois
Noted in 4.1.24, 5.0.48, 5.1.21 changelogs.

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