Shulou(Shulou.com)06/01 Report--

This article is about how to create users and grant permissions in MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

How do I create a MySQL user and grant permissions? To achieve good security, you need to create a separate user account for each application, rather than root users accessing the database. This ensures that applications cannot access the databases of other applications. Therefore, mysql administrator (root) privileges are required to create user accounts and assign permissions to the database.

For information, the MySQL root account is different from the system root account and there is no relationship between them. (recommended: MySQL tutorial)

1. Create a new user in MySQL

Log in to the MySQL server with a root user with shell access and create a new user named "rahul". The following command only allows access to the MySQL server of user rahul from the localhost system.

Mysql > CREATE USER 'rahul'@'localhost' IDENTIFIED BY' password'

Now assign permissions to a specific database. The following command will allow user rahul to have all permissions for the database "mydb".

Mysql > GRANT ALL ON mydb.* TO 'rahul'@'localhost'

After you create the user and assign the appropriate permissions, be sure to reload the permissions.

Mysql > FLUSH PRIVILEGES

two。 Create a remotely accessible MySQL user

Allow any user to connect to the MySQL server from a remote system. You need to specify the hostname or IP address of the remote system. You can also use% to allow any host

Mysql > CREATE USER 'rahul'@'123.45.67.89' IDENTIFIED BY' password';mysql > CREATE USER 'rahul'@'%' IDENTIFIED BY' password';mysql > FLUSH PRIVILEGES

3. Grant specific user permissions in MySQL

The following is a list of common permissions for MySQL users. Visit here for a complete list of permissions for the MySQL user.

ALL [PRIVILEGES]-Grant all permissions to the user.

CREATE-Grant users permission to create new databases and tables.

DROP-Grant users permission to delete (drop) databases and tables.

DELETE-Grant the user permission to delete rows in the table.

ALTER-Grant the user permission to modify the table structure.

INSERT-Grant the user permission to insert (add) rows in the table.

SELECT-Grant the user permission to run the select command to read data from the table.

UPDATE-Grant the user permission to update the data in the table.

EXECUTE-Grant the user permission to execute stored routines.

FILE-Grant users access to files on the server host.

GRANT OPTION-Grant users the right to grant or delete other user rights.

Here, you can specify comma-separated permissions instead of all permissions. For example, allow CREATE,DELETE,INSERT,UPDATE access to 'rahul'@'localhost' on the database mydb.

Mysql > GRANT CREATE,DELETE,INSERT,UPDATE ON mydb.* TO 'rahul'@'localhost';mysql > FLUSH PRIVILEGES

4. Revoke the user rights in MySQL

Use the REVOKE command to remove any specific permissions from the user. For example, remove the DELETE permission from the user 'rahul'@'localhost' on the mydb database.

Mysql > REVOKE DELETE ON mydb.* TO 'rahul'@'localhost';mysql > FLUSH PRIVILEGES

5. Delete a user in MySQL

You can use the DROP command to remove any user from MySQL. For example, to delete the user 'rahul'@'localhost', you can use the following command.

Mysql > DROP USER 'rahul'@'localhost';mysql > FLUSH PRIVILEGES; Thank you for reading! This is the end of the way to create users and grant permissions in MySQL. I hope the above content can be helpful to you so that you can learn more. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.