control user

From PmaWiki
(Redirected from controluser)
Jump to: navigation, search

The controluser is a special MySQL user configured with limited permissions and needed by phpMyAdmin in these situations:

The controluser is created with a few SQL statements (explained below) and the appropriate values must be placed in ./config.inc.php for the controluser and controlpass directives.

Contents

[edit] Creating a controluser

The following example assumes you want to use pma as the controluser and pmapass as the controlpass, but this is only an example: use something else in your file! Input these statements from the phpMyAdmin SQL Query window or mysql command–line client. Of course you have to replace localhost with the webserver's host if it's not the same as the MySQL server's one.

[edit] MySQL version 4.0.2 or newer

According to the documentation at http://www.phpmyadmin.net/documentation/#linked-tables "Note: starting with phpMyAdmin 2.6.1, this section is only applicable if your MySQL server is running with --skip-show-database." This is only an excerpt, so it is suggested you visit that page.

GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
GRANT SELECT (
    Host, User, 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,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';

[edit] MySQL version older than 4.0.2

GRANT SELECT (
    Host, User, 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
    ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT (
    Host, User, 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,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';

Of course, the above queries only work if your MySQL version supports the GRANT command. This is the case since 3.22.11.

[edit] To use pmadb features

If you want to use advanced (phpMyAdmin configuration storage) features, run this line after the above statements:

GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';

Use your pmadb database name instead of phpmyadmin if needed. This of course requires that your phpMyAdmin configuration storage be set up, see the pmadb section for directions on how to do so. You may find a script like create_user.php useful. Note: this script allows the control user account to be used from any host. You may want to restrict this to localhost or the machine you run phpmyadmin on. Replace the % in each sql command.

<source lang="php"> <?php if (PHP_SAPI != 'cli')

   die('please run this from the command line');

// create control user - Tim@Rikers.org // run something like // php create_user.php | mysql -p -h <remotehost> // to create controluser on remotehost require('config.inc.php'); $controluser=$cfg['Servers'][1]['controluser']; $controlpass=$cfg['Servers'][1]['controlpass']; $pmadb=$cfg['Servers'][1]['pmadb']; echo "GRANT USAGE ON mysql.* TO '$controluser'@'%' IDENTIFIED BY '$controlpass'; GRANT SELECT (

   Host, User, 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,
   Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
   Execute_priv, Repl_slave_priv, Repl_client_priv
   ) ON mysql.user TO '$controluser'@'%';

GRANT SELECT ON mysql.db TO '$controluser'@'%'; GRANT SELECT ON mysql.host TO '$controluser'@'%'; GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)

   ON mysql.tables_priv TO '$controluser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON $pmadb.* TO '$controluser'@'%'; "; </source>

[edit] Edit config.inc.php

Now edit ./config.inc.php and add the following two lines (somewhere near the other directives like auth_type is good):

$cfg['Servers'][$i]['controluser']   = 'pma';
  //Use here whatever username was created above
$cfg['Servers'][$i]['controlpass']   = 'pmapass';
  //use here the password to match that user
Personal tools