Overview
Since Plesk does not allow GRANT privileges to users via the Plesk Control Panel, you will need to create those permissions via the command line.
If you're having trouble with the steps in this article, additional assistance is available via Advanced Support, our premium services division. For more information on what Advanced Support can do for you, please click here.
NOTE:
Changing the grants on your "admin" user could potentially lock you out of your Plesk Control Panel. Should this happen please consult http://kb.swsoft.com/article_16_346_en.html for additional help.
For official MySQL documentation, please refer to http://www.MySQL.com.
Requirements
- You must have SSH access set up for root or a sudo user.
READ ME FIRST
This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is outside the scope of support provided by (mt) Media Temple. Please take a moment to review the Statement of Support.
Instructions
For the purpose of this article, we are going to use the 'SELECT' privilege. All code provided are examples. You will want to make sure that you change:
- database to the database name you are using.
- username to your database user.
- password to a strong password unique to that user. Please read our article: Strong Password Guidelines.
Start by logging into your server via SSH and logging into MySQL entering the following:
mysql -u admin -p`cat /etc/psa/.psa.shadow`The prompt should now look like this:
mysql>Enter the following if the database user already exists.:
GRANT SELECT ON database.* TO user@'localhost';
If you intend to create a brand new user, then run this:
GRANT SELECT ON database.* TO user@'localhost' IDENTIFIED BY 'password';
To enable more options, you would separate them with a comma. So to enable SELECT, INSERT, and DELETE your syntax would look like this:
GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';
Once you have given the desired privileges for your user, you will need to run this command within the MySQL command prompt:
FLUSH PRIVILEGES;
To see a list of the privileges that have been granted to a specific user:
select * from mysql.user where User='username';
This is a list of privileges that you can grant:
| Privilege | Meaning |
| ALL [PRIVILEGES] | Sets all simple privileges except GRANT OPTION |
| ALTER | Enables use of ALTER TABLE |
| CREATE | Enables use of CREATE TABLE |
| CREATE TEMPORARY TABLES | Enables use of CREATE TEMPORARY TABLE |
| DELETE | Enables use of DELETE |
| DROP | Enables use of DROP TABLE |
| EXECUTE | Not implemented |
| FILE | Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE |
| INDEX | Enables use of CREATE INDEX and DROP INDEX |
| INSERT | Enables use of INSERT |
| LOCK TABLES | Enables use of LOCK TABLES on tables for which you have the SELECT privilege |
| PROCESS | Enables the user to see all processes with SHOW PROCESSLIST |
| REFERENCES | Not implemented |
| RELOAD | Enables use of FLUSH |
| REPLICATION CLIENT | Enables the user to ask where slave or master servers are |
| REPLICATION SLAVE | Needed for replication slaves (to read binary log events from the master) |
| SELECT | Enables use of SELECT |
| SHOW DATABASES | SHOW DATABASES shows all databases |
| SHUTDOWN | Enables use of MySQLadmin shutdown |
| SUPER | Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached |
| UPDATE | Enables use of UPDATE |
| USAGE | Synonym for privileges |
| GRANT OPTION | Enables privileges to be granted |
Comments