HOW DO I GRANT PRIVILEGES IN MYSQL?

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:

INSTRUCTIONS

Start by logging into your via and logging into 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 [email protected]'localhost';

If you intend to create a brand new user, then run this:


GRANT SELECT ON database.* TO [email protected]'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 [email protected]'localhost' IDENTIFIED BY 'password';

Once you have given the desired privileges for your user, you will need to run this within the MySQL 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:

PrivilegeMeaning
ALL [PRIVILEGES]Sets all simple privileges except GRANT OPTION
ALTEREnables use of ALTER TABLE
CREATEEnables use of CREATE TABLE
CREATE TEMPORARY TABLESEnables use of CREATE TEMPORARY TABLE
DELETEEnables use of DELETE
DROPEnables use of DROP TABLE
EXECUTENot implemented
FILEEnables use of SELECT … INTO OUTFILE and LOAD DATA INFILE
INDEXEnables use of CREATE INDEX and DROP INDEX
INSERTEnables use of INSERT
LOCK TABLESEnables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESSEnables the user to see all processes with SHOW PROCESSLIST
REFERENCESNot implemented
RELOADEnables use of FLUSH
REPLICATION CLIENTEnables the user to ask where slave or master servers are
REPLICATION SLAVENeeded for replication slaves (to read binary log events from the master)
SELECTEnables use of SELECT
SHOW DATABASESSHOW DATABASES shows all databases
SHUTDOWNEnables use of MySQLadmin shutdown
SUPEREnables 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
UPDATEEnables use of UPDATE
USAGESynonym for privileges
GRANT OPTIONEnables privileges to be granted
Print Friendly, PDF & Email

Comments

comments

Bài viết liên quan

Be the first to comment

Để lại lời nhắn