Tag: MariaDB

   phpMyAdmin has an infrastructure named Configuration storage since version 3.4.2. Logging in phpMyAdmin at the first time, you have the message “The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.” because this is deactivated by default. When activated, you can use features like bookmarks, comments, SQL history, relations, PDF schema, and MIME transformations. For me, bookmarks feature is convenient. Well, I’m going to write how to activate it.
   By the way, when we install phpMyAdmin on our servers, we must consider a lot of things for secure. But I don’t write about it here. Please read Official Documentation and take full responsibility for your actions.

   At the very first time for activating this, you need to take the following three steps.

1. Create a user and its database by create_tables.sql in MySQL.
2. Make the above user to a control user
3. Customize your config.inc.php.

   Now, I’ll start.

1. Open the file create_tables.sql by a text editor and uncomment the next two lines.
   ————
   GRANT SELECT, INSERT, DELETE, UPDATE ON `phpmyadmin`.* TO
   ‘pma’@localhost;
   ————

   Log in your phpMyAdmin as a root and import the create_tables.sql. After that you have a database phpmyadmin and a user pma with no password.

   Note) In my opinion, you’d better change the names of database and user to other unique names because there are a lot of mal-attacks given clues by them. Editing the create_tables.sql before your import can makes this easy and possible.

2. Input following statements from the phpMyAdmin SQL Query window.
   ————
   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’;
   ————
   Of course, you should change ‘pmapass’ to ‘your proper controluser password’. If you use your pmadb database name and a controluser name instead of phpmyadmin and pma, you also need to edit them.
    
   Log out your phpMyAdmin.
3. Open your config.inc.php and uncomment the following twenty lines. If you use your pmadb database name and a controluser name instead of phpmyadmin and pma, you also need to edit them. Of course, about pmapass.
   ————
   /*
   * phpMyAdmin configuration storage settings.
   */

   /* User used to manipulate with storage */
   // $cfg[‘Servers’][$i][‘controlhost’] = ”;　⇐It’s need or not depends on your environment.
   // $cfg[‘Servers’][$i][‘controlport’] = ”;　⇐It’s need or not depends on your environment.
   $cfg[‘Servers’][$i][‘controluser’] = ‘pma’;
   $cfg[‘Servers’][$i][‘controlpass’] = ‘pmapass’;

   /* Storage database and tables */
   $cfg[‘Servers’][$i][‘pmadb’] = ‘phpmyadmin’;
   $cfg[‘Servers’][$i][‘bookmarktable’] = ‘pma__bookmark’;
   $cfg[‘Servers’][$i][‘relation’] = ‘pma__relation’;
   $cfg[‘Servers’][$i][‘table_info’] = ‘pma__table_info’;
   $cfg[‘Servers’][$i][‘table_coords’] = ‘pma__table_coords’;
   $cfg[‘Servers’][$i][‘pdf_pages’] = ‘pma__pdf_pages’;
   $cfg[‘Servers’][$i][‘column_info’] = ‘pma__column_info’;
   $cfg[‘Servers’][$i][‘history’] = ‘pma__history’;
   $cfg[‘Servers’][$i][‘table_uiprefs’] = ‘pma__table_uiprefs’;
   $cfg[‘Servers’][$i][‘tracking’] = ‘pma__tracking’;
   $cfg[‘Servers’][$i][‘designer_coords’] = ‘pma__designer_coords’;
   $cfg[‘Servers’][$i][‘userconfig’] = ‘pma__userconfig’;
   $cfg[‘Servers’][$i][‘recent’] = ‘pma__recent’;
   $cfg[‘Servers’][$i][‘favorite’] = ‘pma__favorite’;
   $cfg[‘Servers’][$i][‘users’] = ‘pma__users’;
   $cfg[‘Servers’][$i][‘usergroups’] = ‘pma__usergroups’;
   $cfg[‘Servers’][$i][‘navigationhiding’] = ‘pma__navigationhiding’;
   $cfg[‘Servers’][$i][‘savedsearches’] = ‘pma__savedsearches’;
   ————

   Log in your phpMyAdmin again.

   You don’t have the message “The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.” anymore.

   That’s it!

   You can use phpMyAdmin configuration storage features now.

Edit(Jul.5):
   I forgot to write.
   At upgrades, you simply re-import the new create_tables.sql file after backing up your database, importing the file will not overwrite existing data, but will create any new tables. After that, you maybe need to edit your config.inc.php file.
   You already have your control user, so you must not uncomment the lines in the create_tables.sql file. You also keep in mind about your pmadb database name and a controluser name instead of phpmyadmin and pma.

Edit2(Jul.9):
   When I wrote the reply for くりくりさん, I suddenly thought I am scared of 1. and 2. having the time lag. My sql server doesn’t have the open port to the Internet and it has the only one user, me! So, I might have no need for such nerve. But if you have much busier server than mine, such a server has more occasion that is attacked by someone. The time lag gives attackers that they penetrate your server as the new pma with no password. This I am scared!!

   Well, at first make a controluser with password and give it the privileges. After that, import the file create_tables.sql. My guess is this is better.

   Anyway, I’ll write my controluser current privileges:
————
GRANT USAGE ON *.* TO ‘pma’@’localhost’ IDENTIFIED BY PASSWORD ‘pmapass’;
GRANT SELECT, INSERT, UPDATE, DELETE ON pma_main.* 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’;
————