8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Home » Articles » Mysql » Here
MySQL : Users and Permissions
This article provides a quick guide to creating users and managing permissions for those users in MySQL.
- Create a New User
- Modify a User
- Drop a User
- Manage Privileges
- Roles
- Display DDL for Users and Permissions
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.
- Scripts : Maintain scripts containing all the relevant grants for a specific role. When that role is required by a user, simply run the script to perform all the necessary grants.
- Stored Procedures: Maintain stored procedures containing all the relevant grants for a specific role. When that role is required by a user, simply run the stored procedure to perform all the necessary grants.
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...