Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   User & Privilege Management in MySQL (http://www.go4expert.com/articles/user-privilege-management-mysql-t28581/)

pradeep 13Jun2012 17:39

User & Privilege Management in MySQL
 
Most of the programmers are provided username/password pair which is used in their relevant programs to work with even in a development environment, which is replaced by deployment team when making the code live. So, generally a lot of programmers have no idea how users are created or how permissions are granted, etc. In this article we'll look into how these are done, so that next time you set up your own test database or you switch to a job where you'll need to do these things you'll have an idea of what needs to be done.

In all these examples below, I am assuming you would be using the root account.

Creating Users



First step would be to create a new user and set passwords for them. The username part is divided into 2 parts like this '<user_name>'@'<host_name_ip_or_mask>' (quotes are required), the username is the name that'll be used for logging in, the hostname/IP/IP mask decides from where the user may login, say you'd need the user to login only from any office. Here's how the commands look like varying on the scenario:

Create an user with password who can login from anywhere:
Code: SQL

CREATE USER 'anjali'@'%' IDENTIFIED BY 'MyMoMMy'


Create an user without password who can login from a specific IP address:
Code: SQL

CREATE USER 'anjali'@'192.168.1.2'


Create an user with password who can login from a specific IP address range (using wildcard):
Code: SQL

CREATE USER 'anjali'@'192.168.1.%' IDENTIFIED BY 'MyMoMMy'


Create an user with password who can login from a specific IP address range (using IP mask):
Code: SQL

CREATE USER 'anjali'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'MyMoMMy'


Now, anjali can login from any IP address 192.168.1.0 to 192.168.1.255

Modifying Users



This will most likely happen where you will need to update passwords, change allow hostname/IP/IP masks or change the user name. See the following SQL statements:

Update the user's password:
Code: SQL

SET PASSWORD FOR 'anjali'@'%' = PASSWORD('MyDaDDy')


Modify the user's allowed hostname:
Code: SQL

RENAME USER 'anjali'@'%' TO 'anjali'@'localhost'


Rename the user:
Code: SQL

RENAME USER 'anjali'@'%' TO 'anjali_pradeep'@'%'


Granting Permissions



Now, we would need to grant some privileges to the user, may be on a whole database or a single table, the privileges may range from creating tables, dropping, selecting rows, or deleting row etc, you can get a full list of permissions you can grant here.

The few examples given below should give you a basic idea, the rest you can imagine on your own.

Grant all privileges to a user on a single database:
Code: SQL

GRANT ALL PRIVILEGES ON bestofperl.* TO 'anjali'@'%'


Grant selective privileges to a user on a single database:
Code: SQL

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON bestofperl.* TO 'anjali'@'%'


Grant selective privileges to a user on a single table:
Code: SQL

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON bestofperl.posts TO 'anjali'@'%'


Removing Privileges



Last but not the least that you might do while managing users on MySQL is to revoke the specific or all permissions provided to a user.

Remove delete privilege from a user on a database:
Code: SQL

REVOKE DELETE ON bestofperl.* FROM 'anjali'@'%'



All times are GMT +5.5. The time now is 11:34.