我要求对 phpMyAdmin 进行配置,以便多个用户可以以完全权限访问他们自己的数据库,但不能查看、编辑或删除其他数据库。
我是这样安装 phpMyAdmin 的:
sudo apt-get install phpmyadmin
Web server to reconfigure automatically: <-- apache2
Configure database for phpmyadmin with dbconfig-common? <-- No
安装成功运行,但我只能以 root 身份访问 phpMyAdmin。如果我创建数据库“testdb”并授予如下权限:
GRANT ALL PRIVILEGES ON testdb.* TO 'user1'@'%' IDENTIFIED BY 'pass1';
FLUSH PRIVILEGES;
我仍然无法使用 user1 凭据访问 phpMyAdmin。为什么?如何授予 user1 访问 phpMyAdmin 的数据库的所有权限?
答案1
以下操作可授予多个用户访问权限:
首先更新 Ubuntu 的 phpmyadmin 版本:
sudo nano /etc/apt/source.list
## Untrusted PPA phpMyAdmin
deb http://ppa.launchpad.net/nijel/phpmyadmin/ubuntu trusty main
deb-src http://ppa.launchpad.net/nijel/phpmyadmin/ubuntu trusty main
sudo apt-get update
sudo apt-get install phpmyadmin
安装最新版本后配置phpmyadmin:
mysql > CREATE DATABASE phpmyadmin;
Import into phpmyadmin database „create_tables.sql.gz“
from the directory „/usr/share/doc/phpmyadmin/examples“
Add new user pma to database phpmyadmin:
CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapassword';
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapassword';
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';
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';
sudo nano /etc/phpmyadmin/ config-db.php
$dbuser='pma';
$dbpass=‘pmapassword‘;
$basepath='';
$dbname='phpmyadmin';
$dbserver='127.0.0.1';
$dbport='3306';
$dbtype='mysql';
sudo nano /etc/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['controluser'] = $dbuser;
$cfg['Servers'][$i]['controlpass'] = $dbpass;
$cfg['Servers'][$i]['hide_db'] = '^(information_schema|mysql|performance_schema|phpmyadmin)$';