MySQL administration: how to create a user and determine his rights

A characteristic feature of MySQL is its own security, counting on external protection. As a modern, fully functional and efficient database management system, MySQL has its own tools for managing users and their access to the resources that they control.

mysql create user

If you do not know the correct user name and password, it is very difficult to get access to the database through MySQL .

In the usual mode of hosting, this is enough. Unforeseen situations, hacker attacks and other troubles are a matter of external system administration and security service. Such a concept has become traditional and is practically not discussed.

MySQL server installation and root user

In whatever operating environment the database management system is installed, it always has at least one user: root. Install MySQL, create a user with all root rights - without this, working with the server is impossible. This user's privileges are enough to:

  • Create and administer new users
  • Create and manage databases.

mysql create a user and give rights to the database

In principle, the existence of "passwordless" users in MySQL is possible, but doing this is unacceptable.

Simple practice:

  • the server is installed on its own computer, on which hosting can be installed (local version);
  • the server is hosted on a public Internet hosting service.

In the first case, it is possible to work with the server from the command line and use phpMyAdmin, in the second case only phpMyAdmin or a similar tool, but the command line can be accessed via remote SSH access.

Native administration tools

A sense of kinship with the Unixoid family and with the past from Apache servers is a feature of MySQL: creating a user is a command line with strange syntax. For specialists working with Linux and similar systems, this is as familiar as it looks wild in the eyes of Windows users who have never "gone out into real life."

Creating a user begins by starting the server command line. In a Windows environment, this is done as follows.

mysql create user with all rights

First (1) you need to run the command line as administrator, then go to the folder where MySQL is located (2), then start the server itself (3):

  • mysql -u ... -p

here "-u ..." and "-p" are keys that point to the name "..." = root (or another name) and its password. In principle, the user may not be root, but one that has “root” (administrative) rights.

Essentially : the server actually always works, here mysql -u ... -p is the command to access the server, and not its launch.

In the Linux environment and similar systems, such a command is a “native” action and, as a rule, is determined simply by starting mysqld in the right place (along the right path), this should be checked with the administrator. This is usually a different name: not mysql, but mysqld. Also here, this action is not always available to all users (operating system, not MySQL server). Unlike Windows, in Linux, order and security are a natural and non-negotiable requirement, to which there is always a civilized attitude.

In any case, as soon as mysql starts up, it will report this with a prompt (4):

  • mysql>

and it will be possible to work with both users and databases.

Note. When installing in a Windows environment, everything: Apache, MySQL, PHP, phpMyAdmin can be installed using the default paths, but it is recommended to use more compact and close locations for these important tools:

  • c: \ SCiA \ Apache;
  • c: \ SCiA \ PHP;
  • c: \ SCiA \ MySQL;
  • ...
  • c: \ SCiB \ localhost \ www \ phpMyAdmin \;
  • c: \ SCiB \ site1 \ www \;
  • c: \ SCiB \ site2 \ www \;
  • ...
  • c: \ SCiB \ siteN \ www \.

This logic will simplify not only administration, but also expand the developer’s ability to move between versions of products and manage their functionality.

Work at the MySQL command line

Once the server responded and provided its command line, you can create users and assign them rights.

mysql create user for any host

In this example, the create user command created the Petrov user with the password 123DFG. If an error is made when entering the command, the server offers to fix it, but it is better to never make mistakes when working on the command line!

The following grant all privileges command gives all rights to everything. The flush command can be omitted, but it “pops” the command buffer, that is, secures their execution.

MySQL: create a user and give rights to the database

The command used in the example:

  • GRANT ALL PRIVILEGES ON *. * TO 'Petrov' @ 'localhost';

actually opens access for the Petrov user for all databases (first asterisk) to all tables (second asterisk).

mysql create user with all rights

As a general rule of MySQL, creating a user is:

  • GRANT [privilege type] ON [database name]. [Table name] TO '[user]' @ 'localhost';

The following privileges are allowed:

  • ALL PRIVILEGES - all rights.
  • CREATE - the right to create new tables / databases.
  • DROP - the right to delete tables / databases.
  • DELETE - the right to delete information in tables.
  • INSERT - the right to write information to tables.
  • SELECT - the right to read information from tables.
  • UPDATE - the right to update information in tables.
  • GRANT OPTION - the right to work with the privileges of other users.

From a practical point of view, in MySQL "create user" implies three options for rights:

  • all rights to all databases and all users;
  • reading and writing;
  • only reading.

Other options for granting rights are few when required. There are much more reasons for "legal" freedom (and necessity) in the Linux system environment, but there are much more opportunities than in Windows.

The inverse of MySQL 'create user' operation is drop.

  • drop user 'Petrov' @ 'localhost';

After executing this command, Petrov will not become and his privileges will be lost. To change privileges, use the command:

  • REVOKE [privilege] ON [DB]. [Table] TO '[user]' @ 'localhost';

The usual action in MySQL is to create a user or just delete it, but changing privileges is also a valid operation (rarely needed).

Using phpMyAdmin

There are many implementations of this wonderful tool. Depending on the version of Apache, PHP and MySQL used, it often takes a long time to find the right version of this product, but as soon as phpMyAdmin is successfully installed, the user has many convenient features and a comfortable interface.

mysql create user for any host

Using phpMyAdmin, you can tell MySQL to create a user for any host and manage existing users with almost surgical methods.

phpMyAdmin is not the only tool with a comfortable, intuitive and feature-rich interface, but it is the most popular tool for administering MySQL servers.

About the command line and security

Of course, using the MySQL command line is an unattractive activity, but it should be borne in mind that in some cases only the server command line can save the database or user, and import or export information.

mysql create user for any host

Software versions are developing so fast that developers simply do not have time to combine features, for example, PHP and MySQL, MySQL and phpMyAdmin. If any opportunity happens, the command line will always save the situation.

You should also never forget: MySQL administration is concerned only with access to its databases and through its functionality. Database files are open for access outside of MySQL. The external protection of MySQL and its controlled resources is a real and important need.


All Articles