[Access MySQL Server], mysql Server
To access a MySQL server, you must use a user account to log on to it. Each MySQL user account has many related attributes, such as the user name, password, permissions, and resource restrictions. "Permission" defines what a specific user can do on the MySQL server, and "Resource Restriction" sets a series of server resource usage licenses for the user. Creating or updating a user involves managing all attributes of the user account.
The following shows how to create and set a MySQL user in Linux. First, log on to the MySQL server as the root user.
- $ Mysql-u root-p
When the verification prompt appears, enter the password of the MySQL root Account.
Create a MySQL user and use the following command to create a user with the username and password "myuser" and "mypassword" respectively.
- Mysql> create user 'myuser' @ 'localhost' identified by 'mypassword ';
Once a user is created, all account details, including encrypted passwords, permissions, and resource restrictions, will be stored in a table named "user, this table exists in the special mysql database. Run the following command to verify that the account is successfully created
- Mysql> SELECT host, user, password FROM mysql. user WHERE user = 'myuser ';
Grant MySQL user permissions. A new MySQL user does not have any access permissions, which means that you cannot perform any operations in the MySQL database. You must grant necessary permissions to the user. The following are some available permissions:
- ALL: ALL available Permissions
- CREATE: Creates databases, tables, and indexes.
- LOCK_TABLES: locking a table
- ALTER: modify a table
- DELETE: DELETE a table
- INSERT: INSERT table or column
- SELECT: Retrieves table or column data.
- CREATE_VIEW: Create a view
- SHOW_DATABASES: list databases
- DROP: deletes databases, tables, and views.
Run the following command to grant specific permissions to the "myuser" user.
- Mysql> GRANT <privileges> ON <database>. <table> TO 'myuser' @ 'localhost ';
In the preceding command, <privileges> indicates a list of permissions separated by commas. If you want to grant permissions to any database (or table), use the asterisk (*) instead of the database (or table) Name. For example, grant CREATE and INSERT permissions to all databases/tables:
- Mysql> grant create, insert on *. * TO 'myuser' @ 'localhost ';
Verify the full permissions granted to the user:
- Mysql> show grants for 'myuser' @ 'localhost ';
Copy the code to grant all permissions to all databases/tables:
- Mysql> grant all on *. * TO 'myuser' @ 'localhost ';
You can also delete your existing permissions by copying the code. Run the following command to revoke the existing permissions of the "myuser" account:
- Mysql> REVOKE <privileges> ON <database>. <table> FROM 'myuser' @ 'localhost ';
Add resource limits to MySQL for users. You can set resource limits for MySQL for individual users. The following restrictions apply to available resources:
- MAX_QUERIES_PER_HOUR: Maximum number of requests per hour
- MAX_UPDATES_PER_HOUR: Maximum number of updates per hour allowed
- MAX_CONNECTIONS_PER_HOUR: Maximum number of connections allowed per hour
- MAX_USER_CONNECTIONS: number of simultaneous connections to the server
Run the following command to add a resource limit for the "myuser" account:
- Mysql> grant usage on <database>. <table> TO 'myuser' @ 'localhost' WITH <resource-limits>;
In <resource-limits>, you can specify multiple resource limits separated by spaces. For example, add MAXQUERIESPERHOUR and MAXCONNECTIONSPERHOUR resource restrictions:
- Mysql> grant usage on *. * TO 'myuser' @ 'localhost' WITH MAX_QUERIES_PER_HOUR 30 MAX_CONNECTIONS_PER_HOUR 6;
Verify the user's resource restrictions:
- Mysql> show grants for 'myuser' @ 'localhost;
Copy the code to create and set a MySQL user's last important step:
- Mysql> flush privileges;
The change takes effect. Now the MySQL user account can be used.