Back to normal view: https://oracle-base.com/articles/mysql/mysql-users-and-permisisons

MySQL : Users and Permissions

This article provides a quick guide to creating users and managing permissions for those users in MySQL.

Related articles.

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' WITH GRANT OPTION;
CREATE USER 'adminuser'@'%' IDENTIFIED BY 'MyPassword1';
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

We have in fact created two users, one for local access from the server and one for remote access.

mysql> SELECT host, user FROM mysql.user WHERE user = 'adminuser';
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | adminuser |
| localhost | adminuser |
+-----------+-----------+
2 rows in set (0.01 sec)

mysql>

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

Not surprisingly, if you want to lock down a user, so it can only be accessed from a single PC or server, specify that machine name or IP address in the user creation.

CREATE USER 'myuser'@'123.123.123.123' IDENTIFIED BY 'MyPassword1';
FLUSH PRIVILEGES;

MySQL also allows you to insert directly into the "mysql.user" table, but it is better to stick with the main commands.

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

There are several ways to modify an existing user. MySQL allows DML to be run directly on the "mysql.user" table, so you can make changes directly. For example, when we have the same user name defined against multiple hosts, we can update the passwords for all those users in a single step.

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.
SET PASSWORD = PASSWORD('MyPassword2');
FLUSH PRIVILEGES;

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

ALTER USER 'adminuser'@'localhost' PASSWORD EXPIRE;
FLUSH PRIVILEGES;

The RENAME USER comment, as the name suggests, renames a user.

RENAME USER 'adminuser'@'localhost' TO 'adminuser'@'127.0.0.1';

Drop a User

Users are removed using the DROP USER command.

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';
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| localhost | adminuser |
+-----------+-----------+
1 row in set (0.00 sec)

mysql>

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

DELETE FROM mysql.user WHERE user = 'adminuser';
FLUSH PRIVILEGES;

Manage Privileges

MySQL does not support roles in the same sense as Oracle roles. Some tools, like MySQL Workbench, allow you to model roles, but ultimately these are implemented using direct grants on users.

The full syntax for GRANT and REVOKE are listed in the documentation, but the following examples will give you an idea of how the privileges can be used at different levels. Notice how the same privilege can be used with a dramatically different results based on the scope of the grant.

-- Grant everything on all databases. Top level admin user.
-- 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;

Typically, you would want to grant access on a more granular level. Remember, you should always attempt to give people the least privilege possible to do their job.

-- 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;

On occasion you might need a user to perform a task that requires a very high degree of privilege, which you are not happy granting directly to them. In this case, you can perform the action in a stored procedure or function and grant EXECUTE on that stored procedure or function to the user, removing the need to grant the permission directly to the user.

CREATE DATABASE system_admin_db;
USE system_admin_db;

DROP FUNCTION IF EXISTS `system_admin_db`.`user_count`;

DELIMITER //
CREATE DEFINER = 'root'@'localhost' FUNCTION `system_admin_db`.`user_count` (
  p_user VARCHAR(16)
) 
RETURNS INT
READS SQL DATA
BEGIN
  DECLARE l_count INT DEFAULT 0;
  
  SELECT COUNT(*)
  INTO   l_count
  FROM   mysql.user
  WHERE  user = p_user;
    
  RETURN l_count;
END //
DELIMITER ; 

GRANT EXECUTE ON FUNCTION `system_admin_db`.`user_count` 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

As mentioned in the previous section, MySQL does not support roles in the same sense as Oracle roles. Some tools, like MySQL Workbench, allow you to model roles, but ultimately these are implemented using direct grants on users. There are some simple ways you can achieve similar results yourself.

In both these cases, it makes sense to start any role change with the removal of all privileges, then application of the necessary roles.

REVOKE ALL PRIVILEGES ON *.* FROM 'myuser'@'%';
CALL `system_admin_db`.`hr_user_role`('myuser', '%');
CALL `system_admin_db`.`crm_user_role`('myuser', '%');

Display DDL for Users and Permissions

To display the DDL required to recreate the user, including the permissions, run the following command, substituting the correct user name.

SHOW GRANTS FOR 'adminuser'@'%';

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

Back to normal view: https://oracle-base.com/articles/mysql/mysql-users-and-permisisons