A characteristic feature of MySQL is its own security, relying on external protection. As a modern, full-featured and efficient database management system, MySQL has its own tools for managing users and their access to the resources it controls.
If you do not know the correct username and password, it is very difficult to access the database through MySQL.
In normal hosting mode, this is enough. Unforeseen situations, hacker attacks and other troubles are a matter of external system administration and security services. This concept has become traditional and is practically not discussed.
Install MySQL server and user root
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, work withserver is not possible. This user's privileges are sufficient to:
- create and administer new users;
- create and manage databases.
It is fundamentally possible for "passwordless" users to exist in MySQL, but this is unacceptable.
Common practice:
- server installed on own computer, where hosting can be installed (local option);
- server is on public hosting on the Internet.
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.
Own administration tools
Feeling of kinship with the Unixoid family and with the past from Apache servers is a hallmark of MySQL: create user is a command line with strange syntax. For professionals working with Linux and similar systems, this is as familiar as it looks wild in the eyes of Windows users who have never “entered the real life.”
Creating a user starts with starting the server command line. In a Windows environment, this is done as follows.
First (1) you need to run the command line as an administrator, then go to the folder where MySQL is located (2), thenstart the server itself (3):
mysql -u… -p
here "-u…" and "-p" are keys that point to the name "…"=root (or other name) and its password. In principle, a user may not be root, but one that has "root" (administrative) rights.
Important: the server is actually always running, here mysql -u… -p is the command to access the server, not start it.
In a Linux environment and similar systems, such a command is a "native" action and, as a rule, is determined by simply starting mysqld in the right place (at the right path), this should be checked with the administrator. There is usually a different name here: not mysql, but mysqld. Also here, this action is not always available to all users (of the operating system, not of the MySQL server). Unlike Windows, in Linuxoids, order and security are a natural and non-negotiable requirement, which is always treated in a civilized manner.
In any case, once mysql has started, it will announce 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 set to default paths, but it is recommended to use more compact and closer 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 not only simplify administration, but also expand the developer's ability to move between product versions and manage their functionality.
Working on the MySQL command line
Once the server has responded and provided its command line, users can be created and assigned permissions.
In this example, the create user command created the user Petrov with the password 123DFG. If an error is made when entering a command, the server offers to correct it, but it's better to never make mistakes when working on the command line!
The following command grant all privileges gives all rights to everything. The flush command can be omitted, but it 'pops' the buffer of commands, that is, it fixes their execution.
MySQL: create a user and grant rights to the database
Command used in the example:
GRANT ALL PRIVILEGES ON. TO 'Petrov'@'localhost';
actually grants user Petrov access to all databases (first asterisk) to all tables (second asterisk).
As a general MySQL rule, 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 drop 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 a user" implies three options for rights:
- all rights to all databases and all users;
- read and write;
- read only.
Other options for granting rights are rarely required. In the Linux environment, there are much more grounds for "legal" freedom (and necessity), but there are much more opportunities there than in Windows.
The reverse operation of MySQL "create user" is drop.
drop user 'Petrov'@'localhost';
After executing this command, Petrov will no longer be a user 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 delete that, but changing privileges is also a valid operation (rarely requested).
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 once phpMyAdmin is successfully installed, the user has many convenient features and a comfortableinterface.
Using phpMyAdmin, you can tell MySQL to create a user for any host and manage existing users in near-surgical ways.
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 exercise, but it should be borne in mind that in some cases only the server command line can save the database or user, provide information import or export.
Software versions are evolving so fast that developers simply don't have time to combine the features of, for example, PHP and MySQL, MySQL and phpMyAdmin. If something happens, the command line will always save the day.
One should also never forget: MySQL administration is only about accessing its databases and through its functionality. The database files are open to access outside of MySQL. Externally securing MySQL and the resources it controls is a real and important need.
