MySQL : Users and Permissions:

MySQL

–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();

========================================================================================================
Note: IP Address of hostame any one can be used for user creation (Reccomend to use hostname)

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

use mysql;
GRANT ALL PRIVILEGES ON . To ‘dbaadmin’@’10.10.10.127’ IDENTIFIED BY ‘dba1’;
FLUSH PRIVILEGES;

use mysql;
GRANT SELECT ON . To ‘nagios’@’10.10.9.169’ IDENTIFIED BY ‘nagios’;
FLUSH PRIVILEGES;

use mysql;
GRANT ALL PRIVILEGES ON . To ‘dba1’@’10.10.10.127’ IDENTIFIED BY ‘dba1’;
FLUSH PRIVILEGES;

use mysql;
GRANT ALL PRIVILEGES ON . TO [email protected]’10.10.10.158′ IDENTIFIED BY ‘maxis*231’;
FLUSH PRIVILEGES;

use mysql;
GRANT SELECT ON . TO [email protected]’%’ IDENTIFIED BY ‘testuser’;
FLUSH PRIVILEGES;

Permissions to create and run stored procedure

CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALTER ROUTINE, CREATE ROUTINE, EXECUTE ON . TO ‘username’@’localhost’ ;

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

use mysql;
GRANT SELECT ON . TO [email protected]’10.10.10.158′ IDENTIFIED BY ‘maxis*231’;
FLUSH PRIVILEGES;

use mysql;
CREATE USER ‘devuser’@’%’ IDENTIFIED BY ‘devuser’;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON . TO ‘devuser’@’%’;
FLUSH PRIVILEGES;

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

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;

UPDATE user SET Password = PASSWORD(‘root’) WHERE User = ‘root’;
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(‘root’);
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’@’%’;