1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

User & Privilege Management in MySQL

Discussion in 'MySQL' started by pradeep, Jun 13, 2012.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    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:
    CREATE USER 'anjali'@'%' IDENTIFIED BY 'MyMoMMy'
    Create an user without password who can login from a specific IP address:
    Code:
    CREATE USER 'anjali'@'192.168.1.2'
    Create an user with password who can login from a specific IP address range (using wildcard):
    Code:
    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:
    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:
    SET PASSWORD FOR 'anjali'@'%' = PASSWORD('MyDaDDy')
    Modify the user's allowed hostname:
    Code:
    RENAME USER 'anjali'@'%' TO 'anjali'@'localhost'
    Rename the user:
    Code:
    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:
    GRANT ALL PRIVILEGES ON bestofperl.* TO 'anjali'@'%'
    Grant selective privileges to a user on a single database:
    Code:
    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON bestofperl.* TO 'anjali'@'%'
    Grant selective privileges to a user on a single table:
    Code:
    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:
    REVOKE DELETE ON bestofperl.* FROM 'anjali'@'%'
     

Share This Page