Create a new user in MySQL and grant MySQL database permission.

When connecting to the MySQL server, the user specifies a username and password, and the server checks if the client has the right to access the server and, if successful, gives the user the appropriate privileges. User privilege data is stored in the mysql system database.

User verification is performed using three fields of the user table (host, user and password) of the mysql database. The server establishes a connection only if it finds an entry in the user table in which the username and password match the entered values.

You can see how passwords are stored in the user table using the following commands:

mysql> USE mysql

mysql> SELECT password FROM user;

You can get acquainted with all the tables of the mysql database by issuing the command

mysql> show tables;

After establishing a connection, the server starts a session with the user. For each request that comes in, the server checks to see if the user has sufficient privileges to complete the request. Privilege information is found in tables - user, db, host, tables_priv, or columns_priv.

The list of privileges is presented below:

The MySQL server reads the contents of these tables at startup time and when privilege changes take effect.

The SELECT, INSERT, UPDATE, and DELETE privileges allow you to perform operations on rows in a database table. The index privilege allows indexes to be created or destroyed. The alter privilege allows you to use the alter table command. The create and drop privileges allow you to create new or drop existing databases and tables. GRANT privilege gram allows you to grant other users privileges that you yourself have.

The rest of the privileges are used to administer the SQL server. The shutdown command shuts down the server.

When the MySQL server starts up, all user privilege information is loaded into memory, and from that point on, the privileges take effect.

The grant and revoke commands allow sysadmins to create MySQL users and grant or revoke user rights at four levels.

· Global level.

Global privileges apply to all databases on the specified server. These privileges are stored in the mysql.user table.

· Database level.

Database privileges apply to all tables in the specified database. These privileges are stored in the mysql .db and mysql .host tables.

· Table level.

Table privileges apply to all columns in the specified table.

These privileges are stored in the mysql table. tables_priv.

· Column level.

Column privileges apply to individual columns in the specified table. These privileges are stored in the mysql .columns_priv table.

The grant command allows you to add new server users:

mysql> GRANT SELECT, INSERT ON taxi. * TO [email protected]

IDENTIFIED BY "secret";

This command gives insert and select rights to the taxi base (for all tables - this is set by the asterisk symbol) to the user Mike, who connects locally (localhost), moreover, when connecting to the server, the user must specify the password "secret". The password is stored encrypted on the server.

mysql> GRANT ALL PRIVILEGES ON *. * TO [email protected]"%"

-> IDENTIFIED BY "some_pass" WITH GRANT OPTION;

Here Anna gets all privileges to all tables of all server databases (*. *), And she can connect both locally and remotely via the network (this is indicated by the% symbol after [email protected]). In addition, Anna can transfer her rights to other users (with grant option), that is, she herself can issue a grant command and create new users of the system.

mysql> GRANT USAGE ON *. * TO [email protected]

IDENTIFIED BY `password";

The dummy user created in this example can connect to the server, but only from the local computer. The USAGE privilege means that the user has no other privileges. This may be required when a person is hired, but his responsibilities have not yet been determined. It is expected that database-specific privileges will be granted later. It will not be possible to create a user with an empty password - the system does not allow this for security reasons.

The REVOKE command allows the administrator to revoke user rights:

REVOKE SELECT (use_on) ON taxi.timetable FROM Mike @ localhost;

User Mike cannot view the values ​​of the use_on field in the timetable.

The set command can set various options that affect the operation of the server or client. A password for the root user can also be set using this command:

mysql> SET PASSWORD FOR [email protected]= PASSWORD ("new_password");

Users can work directly with privilege assignment tables:

mysql> USE mysql;

Mysql> UPDATE user SET Password = PASSWORD ("new_password")

-> WHERE user = "root";

After that, you should issue a command that forces the server to re-read the privilege tables - then they will take effect:

mysql> FLUSH PRIVILEGES;

If the password is set using the grant statement. ... .identified by, there is no need to use the password () function. This command independently encrypts the password, so the password should be specified in plain text, for example, like this:

mysql> GRANT USAGE ON *. * TO [email protected]"%" IDENTIFIED BY "biscuit";

Changes made to the grant tables using the grant, revoke, or set password commands are posted by the server as soon as these instructions are executed.

If you're interested in web development or software development in general, you've probably heard of databases. Databases are virtual repositories for data needed to build web applications. They are used to store user information such as username, password, email address, and others. In general, any kind of information that you may need in the future can be stored in the database.

Of course, the data you store must be in order. This is where database management systems come into play. These systems are used to work with the database and allow developers to structure, store, query and modify data.

MySQL is one such database management system. It is one of the most famous systems for its ease of use and community support. By the way, its prevalence has attracted even such large companies as Facebook and Twitter, which to some extent use it.

In this tutorial, we'll go over the basics of MySQL and explore how to create a MySQL user. We will also explore how to grant access privileges to MySQL users. In this tutorial, we'll do all of this from the command line.

Before you start, you will need the following:

• MySQL installed on your computer. User manual on how to install it on.

Step 1 - Create MySQL User and Grant Unlimited Access Rights

Once you start using MySQL, you will be presented with a username and password. These initial credentials give you ‘root access’ privileges. A user with root access has full access to all databases and tables within these databases.

But often, you need to give someone else access to the database without giving it full control. For example, companies that hire developers to maintain databases but do not want to give them the ability to delete or modify any confidential information are likely to grant them non-root access rights. In this case, the company can control what its developers can and cannot do with the data.

It is very easy to create a MySQL user. We will show you how to create a MySQL user and how to assign database permissions to it. If the user is not root, then giving him all the rights is impractical and this is a good reason to study how you can assign access rights to users. Follow these steps to create a new user:

1. Start a command line and from there the MySQL server: mysql
2. Run the following command: CREATE USER "non-root" @ "localhost" IDENTIFIED BY "123";

   In this command, 'non-root' is the name we assign to our new user. And '123' is his password. You can replace with your values ​​inside quotes.

3. Just creating a new user isn't enough. You need to assign access rights to it. To assign the newly created user unrestricted access to the database, run the following command: GRANT ALL PRIVILEGES ON *. * TO "non-root" @ "localhost";
4. In order for the changes to take effect, run the update command: FLUSH PRIVILEGES;

It's all! Your new user has the same database authority as root.

Step 2 - Assign Special Privileges to the MySQL User

As stated above, it is not very smart to give root access to the user, not root. More often than not, you will need to provide different levels of access for different users. MySQL allows you to do this quickly and easily by running the following command:

GRANT [permission type] ON [database name]. [Table name] TO ‘non-root’ @ "localhost";

You just need to replace the ‘type of rights’ value with the kind of access rights you want to grant to the new user. Also you need to specify the database and the names of the tables to which access is provided. Similar to the previous example, 'non-root' is the username, so you can replace it with the one you want. MySQL has several types of permissions, some of which are described below:

• CREATE- Allows users to create databases / tables
• SELECT- Allows users to sample data
• INSERT- Allows users to add new records to tables
• UPDATE- Allows users to modify existing records in tables
• DELETE- Allows users to delete records from tables
• DROP- Allows users to delete records in database / tables

To use any of these options, simply replace [type of rights] necessary word. To assign multiple types of permissions, separate them with commas as in the following command. For example, we can assign privileges such as CREATE and SELECT for our non-root MySQL user with the following command:

GRANT CREATE, SELECT ON *. * TO "non-root" @ "localhost";

Of course, you may encounter a situation when you need to take away data from the user who previously had access rights. This can be done by running the following command:

REVOKE [permission type] ON [database name]. [Table name] FROM ‘non-root’ @ ‘localhost’;

For example, to revoke all privileges from a non-root user, you need to do:

REVOKE ALL PRIVILEGES ON *. * FROM "non-root" @ "localhost";

Finally, you can delete an existing user using the command:

DROP USER ‘non-root’ @ ‘localhost’;

Remember that you need root authority to run any of these commands. Also, be sure to follow FLUSH PRIVILEGES after any change in access rights.

Conclusion

In this tutorial, we learned some of the basics of working with MySQL permissions and how to create a MySQL user. In particular:

• Create a new user and assign him all access rights
• Assigning special access rights to users, as well as their cancellation and removal of users

Assigning access rights may seem difficult when developing a new web application, but it is very important to manage and maintain a database. Almost all modern applications are based on databases, so it is very important that the developer knows how to assign / revoke database permissions in MySQL.

Finally, I got my hands on the databases, today we will continue to deal with the MySQL server. As you may have guessed, we are going to talk about MySQL users and creating a MySQL server user account. Let me remind you that the very first user is created during the automatic installation of the MySQL server. You enter the password for the MySQL user account named root. But sometimes there are situations when there are too many root user rights, for example: some users should only have rights to view data, but not to edit.

Rights, MySQL users can have very different, and we will talk about access rights to MySQL databases in a separate publication. Our task is to figure out how to connect to a server that has a specific TCP port. In fact, creating a new MySQL user is not that difficult, but there are some subtleties you should be aware of so that you don't have any problems administering MySQL databases.

How to access MySQL server

When you, you created the first MySQL user, his name is root, you came up with the password for this account yourself. The root user is the most important user of the MySQL server, the main administrator with unlimited access rights, this user can create new accounts to access the MySQL databases, in other words: the main administrator can create a MySQL user with fewer rights than himself.

Before, how to create a MySQL user, do not forget . To access the MySQL server, you need to establish a connection, for this you need to enter your username and password. If the database server is remote, then you will also need to enter the name of the host on which it is installed, if you do not specify the host name, then the connection will be established with the local server.

To establish a connection to the local server, you can use the following command in the command line of the operating system:

mysql –h localhost –p –u root

We confirm the command by pressing the Enter key, after which MySQL will prompt you to enter the password for this user, enter and press Enter. Please note: if you are working in the Windows 7 operating system, then the command line must be run as administrator. Sometimes the MySQL server may not be found, we will talk about the solution to this problem in another article.

Let's deal with this command. This command starts the MySQL server console, a shell that allows you to work with databases, the operations that can be performed with data can be very different, it all depends on the MySQL user access rights, in this case you can perform any operations with all databases, because you are logged in as the root MySQL user. Note for novice users of UNIX-like operating systems: root MySQL user server and operating system root user are two different users. The MySQL server username is written after the "-u" construction.

The MySQL server user password is written after the "-p" construction. You don't have to enter it right away, then you will have to enter it after you press Enter. After the "-h" construction, the name of the host where the MySQL server is located is written; in this case, we explicitly indicate that the connection must be established with the local database server, and this can be omitted.

After this command is executed, you will need to enter the MySQL user password. If the MySQL user does not have a password, then after executing the command, press the Return key. I hope that we have figured out how to get access to the MySQL server and that there will be no questions here.

How to view information about existing MySQL server databases

Please note: the use command is not a SQL query, so you do not need to put a semicolon at the end of this command, but you can put it, the result will be the same: you will start working with the specified MySQL server database. Database_name is the name of the database with which the MySQL user will work, it can be any of the resulting list, which was issued by the show databases command. I think this is all you need to know in order to further deal with the fact how to create a MySQL user.

How to create a MySQL user. GRANT command.

So, let's move on to the question: how to create a MySQL user. A new MySQL user account is created with the GRANT command. The GRANT command creates more than just MySQL users server, but also assigned the access rights and privileges of MySQL users. All MySQL users fall into two categories, one small and the other large:

1. Root MySQL user. Able to do everything, this is the main administrator of the MySQL server, often root rights are unnecessary, with these rights you can harm yourself.
2. MySQL users created by root. These MySQL server users have different rights, the rights are assigned by the root user using the SQL GRANT query.

So let's figure out how to create a MySQL user, if you haven't forgotten yet: MySQL users are created with a GRANT query:

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON (tbl_name | * | *. * | Db_name. *) TO user_name [, user_name ...]

The example shows the general syntax for the GRANT command, it is quite simple, but you can create MySQL users with different privileges. Let's see which MySQL users can be created using this command for a specific example.

We will create a new MySQL user. There is one named mydb on my server, you can create your own database with the name you want. Note that even if the MySQL user already exists, he will still have to grant certain privileges with the GRANT command. So, let's create a new user who can perform any actions with the MySQL server mydb database. I will give some introductory data. MySQL username: "newusermysql", MySQL user password: "userpassword". Host on which the server is installed: localhost. Actions that a new user can perform: any actions with the mydb database. GRANT request to create a MySQL user with the specified rights:

The semicolon at the end of the command is required, since GRANT is an SQL query. Let's analyze the command for creating a MySQL user in parts. IDENTIFIED BY "userpassword": Set the password for the MySQL user to "userpassword". "newusermysql" @ "localhost": specify the host to which the user with the name "newusermysql" will be connected. The GRANT statement can only be used by the root MySQL user. The ALL ON statement grants full authority to the user newusermysql on the mydb database.

Note: it is managed by a MySQL server, so WordPress users can be created manually by filling in the appropriate fields and giving them privileges, on the other hand: WordPress is a CMS that makes life easier for a webmaster and provides a graphical interface for creating users.

I think I went into detail on the question of how to create a MySQL user, now you can create new users, we will talk about MySQL user privileges in one of the following publications, if you still have questions on this topic, please ask them in the comments if you think that the article helped you and you want to help the resource - use the block of social buttons on this page, I will be very grateful and grateful to you! Thank you for your attention!