MySQL Users and Permissions

–Create a New User–

When creating a new user, the CREATE USER command expects both a username and host. If the host is not supplied, a host of ‘%’ is used,
meaning any host other than localhost. As a result, if you want to create new admin user on the database, you may do something like this.

CREATE USER ‘adminuser’@’localhost’ IDENTIFIED BY ‘MyPassword1’;
GRANT ALL PRIVILEGES ON *.* TO ‘adminuser’@’localhost’;
FLUSH PRIVILEGES;

use mysql;
CREATE USER ‘testuser’@’%’ IDENTIFIED BY ‘testuser’;
GRANT SELECT ON *.* TO ‘testuser’@’%’;
FLUSH PRIVILEGES;

CREATE USER ‘myuser’@’123.123.123.123’ IDENTIFIED BY ‘MyPassword1’;
FLUSH PRIVILEGES;

mysql> select user,host from mysql.user;

mysql> show grants for ‘root’@’%’;

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

========================================================================================================

mysql> SELECT host, user FROM mysql.user WHERE user = ‘adminuser’;
mysql> SELECT host, user FROM mysql.user;

==============================================================================================================================
Another alternative is to create using the GRANT command. Using GRANT USAGE creates the user, but grants it no privileges.

GRANT USAGE ON *.* TO ‘myuser’@’%’ IDENTIFIED BY ‘MyPassword1’;
FLUSH PRIVILEGES;

–Modify a User–

UPDATE user SET password = PASSWORD(‘MyPassword2’) WHERE user = ‘adminuser’;
FLUSH PRIVILEGES;

Alternatively, the SET PASSWORD command can be used to reset a users password.

— Specified user.
SET PASSWORD FOR ‘adminuser’@’localhost’ = PASSWORD(‘MyPassword2’);
FLUSH PRIVILEGES;

— Current user.
use mysql;
SET PASSWORD = PASSWORD(‘newrootpassword’);
FLUSH PRIVILEGES;

The ALTER USER command can be used to expire a password.

ALTER USER ‘adminuser’@’localhost’ PASSWORD EXPIRE;
FLUSH PRIVILEGES;

–Renames a User–

RENAME USER ‘adminuser’@’localhost’ TO ‘adminuser’@’127.0.0.1’;

–Drop a User–
use mysql;
DROP USER ‘adminuser’@’%’;
FLUSH PRIVILEGES;

If you have the same user defined for multiple hosts, remember to drop all of them if required.

mysql> SELECT host, user FROM mysql.user WHERE user = ‘adminuser’;

Alternatively, just delete all users with the same user name.

DELETE FROM mysql.user WHERE user = ‘adminuser’;
FLUSH PRIVILEGES;

Manage Privileges
——————-

— Grant everything on all databases– Think of this like a DBA user in Oracle terms.

GRANT ALL PRIVILEGES ON *.* TO ‘adminuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

— Grant everything on a specific database. Admin user for a specific database.
— Think of this like the schema-owner in Oracle terms.

GRANT ALL PRIVILEGES ON mydb.* TO ‘dbadminuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

— Grant everything on a specific table.

GRANT ALL PRIVILEGES ON mydb.mytable TO ‘tableadminuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

— Allow read-only access to all tables in database.

GRANT SELECT ON mydb.* TO ‘rouser’@’%’;
FLUSH PRIVILEGES;

— Allow read-only access to specific tables.

GRANT SELECT ON mydb.tab1 TO ‘rouser’@’%’;
GRANT SELECT ON mydb.tab2 TO ‘rouser’@’%’;
FLUSH PRIVILEGES;

— Allow a variety of access to a variety of objects.

GRANT SELECT ON mydb.tab1 TO ‘myuser’@’%’;
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.tab2 TO ‘myuser’@’%’;
GRANT SELECT, UPDATE ON mydb.tab3 TO ‘myuser’@’%’;
GRANT SELECT, DELETE ON mydb.tab4 TO ‘myuser’@’%’;
GRANT EXECUTE ON mydb.proc1 TO ‘myuser’@’%’;
FLUSH PRIVILEGES;

——————————————————————————————————————————————————–

Removing privileges is essentially the opposite of what you’ve just seen.

— Remove all privleges on a specific database.
REVOKE ALL PRIVILEGES ON mydb.* FROM ‘adminuser’@’localhost’;

— Remove specific privileges from specific objects.
REVOKE EXECUTE ON FUNCTION `system_admin_db`.`user_count` FROM ‘myuser’@’%’;
GRANT SELECT ON mydb.tab2 TO ‘rouser’@’%’;

FLUSH PRIVILEGES;

Notice the FLUSH PRIVILEGES command, which reloads the privilege information from the relevant tables in the “mysql” schema.
Roles

——————————————————————————————————————————————————–

Display DDL for Users and Permissions

SHOW GRANTS FOR ‘adminuser’@’%’;

——————————————————————————————————————————————————–

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: