MySQL Privilges

Privileges allow administrators have a control over accesses to objects by users. The users who are not the owners of objects are restricted from accessing other users objects. This helps prevent damage.

Below are privileges that can be granted to normal users :

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • SHOW DATABASES
  • INDEX
  • ALTER
  • CREATE
  • CREATE TEMPORARY TABLES
  • DROP

Below are privileges that can be granted to administrative accounts :

  • RELOAD
  • SHUTDOWN
  • FILE
  • GRANT OPTION
  • CREATE USER
  • REVOKE
  • PROCESS
  • SUPER

New MySQL 5.0 and later privileges :

  • CREATE VIEW
  • SHOW VIEW
  • ALTER ROUTINE
  • CREATE ROUTINE
  • EXECUTE
  • LOCK TABLES
  • REPLICATION CLIENT
  • REPLICATION SLAVE

Creating users with permissions :

  • grant <privileges> on <database>.<tablename> to 'usernmae'@'hostname' IDENTIFIED BY 'password';
  • grant <privileges> on <database>.* to 'usernmae'@'hostname' IDENTIFIED BY 'password'; -- All tables in database
  • grant <privileges> on *.* to 'usernmae'@'hostname' IDENTIFIED BY 'password'; - All databases all tables

-ALL for privileges will grant all privileges to user

-IDENTIFIED BY 'password' is optional

Limit set on Userame is :

16 chars max
case sensitive

Wildcharacter for <hostname> is %

Creating Database

connect to root
create database <database name>

Creating User

grant select, insert, update, delete, create, drop, alter, index on ALPACAS.*to 'llama'@'localhost' identified by 'camel';

Adding privilges to exisitn users

GRANT FILE ON *.* TO 'llama'@'localhost';
GRANT CREATE ROUTING, ALTER ROUTING, EXECUTE, CREATE VIEW, SHOW VIEW ON alpacas.* to 'llama'@'localhost';

The below privileges can be granted only globally, ie, to all databases

  • FILE
  • LOCK_TABLES
  • RELOAD
  • SHOW DATABASES

To create a user without having any modify privileges (only SELECT privilege)

GRANT USAGE ON *.* to 'webuser'@'%' identified by 'password'; GRANT SELECT ON *.* TO 'webuser'@'%' identified by 'password'; -- Also involves MySQL database

Checking grants of users

show grants for 'llama'@'localhost';

Checking grants of self

show grants

Connecting to a particular database by a particular user from mysql client

path/to/mysql -u <user> -h <hostname> -p <db name>

Creating user (another way)

CREATE USER <username>;

Changing password of a user as root (SET PASSWORD is available as of mysql 4.0)

SET PASSWORD FOR 'username'@'host' = PASSWORD('new password');

Changing our own password

SET PASSWORD = PASSWORD ('new password');

Dropping a user

DROP USER <username>

Revoke privileges from user

REVOKE <privileges> ON database.* FROM 'username'@'hostname';

To see all users

mysql> select user, host, password from mysql.user;
Anonymous users dont have names. They are identified by ''@'hostname'

Dropping anonymous users

drop user ''@'hostname';
Comments