MySQL user and privilege management

privileges management

MySQL provides privileges that apply in different contexts and at different levels of operation:

·  Administrative  privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.
·  Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.

Grant or revoke privileges

>grant [priviledge] on object to ‘user’@’host’

>revoke [priviledge] on object from ‘user’@’host’

Object can be

• ON *.*
• ON db_name.*
• ON db_name.table_name
• ON db_name.routine_name

Flush the privilege so it will take effect immediately

>flush privileges

 

Common Administrative privileges:

CREATE USER	create drop or rename other users
PROCESS	display of information about the threads executing within the server
RELOAD	use flush and reset
REPLICATION CLIENT	enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS
REPLICATION SLAVE	accounts that are used by slave servers to connect to the current server as their master
SHOW DATABASES	see the databases
SHUTDOWN	shutdown the server
SUPER	miscellaneous administrative operations
ALL [PRIVILEGES]	shorthand. It stands for “all privileges available at a given privilege level” (except GRANT OPTION).
USAGE	no privileges

Common database privileges:

alter	modify the table
alter routine	modify the store procedure
create	create database and table
create routine	create store procedure
create view	create the view
delete	do delete SQL
drop	drop db or tables
execute	execute the store procedure
grant option	grant privileges to other accounts
index	modify index
insert	do insert SQL
select	do select SQL
update	do update SQL

MySQL user management

The user is identified by userid  + host, two users with same userid but different host are considered as different user. The host field indicates where the user is logged from. The user information is stored in mysql.user table

Create user:

Users can be created by the “create user” command

>create user ‘user’@’host’ identified by ‘password’

Host can be ip address, localhost or %. % means every host. sample command:

>create user 'rafax'@'%' identified by '123456';

show online users

>show processlist

The above command can see the online users and their activities, but you may need privilege to see other users, otherwise you will only see your own activities.

Rename user

User name or host can be renamed by the following command:

>rename  user 'user1'@'host1'  to 'user2'@'host2';

Delete the user

>drop user [user]@[host]

For example:

>drop user ‘rafax’@’%’