Mysql

From adarshwiki.adarshinfosolutions.com

HOWTO: GRANT privileges in MySQL

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.

DETAILS:

---

For the purpose of this article we are going to use the 'SELECT' privilege. The text in italics should be replaced with data that is relevant to your service. You will want to make sure that you change “database” to the database name you are referring to, "username" to the user, and "password" to a strong password unique to that user.

You will start by logging into your server via ssh and logging into your MySQL entering the following:

CODE:
mysql –uadmin –p`cat /etc/psa/.psa.shadow` 

Your prompt should now look like this:

CODE:
mysql> 

Enter the following:

CODE:
GRANT SELECT ON database.* TO username@'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;

CODE:
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;

CODE:
FLUSH PRIVILEGES; 

To see a list of the privileges that have been granted to a specific user;

CODE:
select * from mysql.user where User='user' \G 

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 “no privileges”
GRANT OPTION	Enables privileges to be granted