Linux user mysql access

How to create MySQL admin user (superuser) account

H ow do I create an admin user in MySQL? How can I set up and create a superuser account in a MySQL server running on Linux or Unix-like systems? Can you provide a quick guide about making a superuser with root-like access to the MySQL or MariaDB databases?

Both MySQL and MariaDB (drop-in replacement for Oracle MySQL) are free and open-source database systems to build dynamic applications running on Linux, *BSD, and Unix-like systems. Typically we use PHP, Perl, Python, Java, and other server-side programming languages with MySQL server. This page explains how to create a MySQL admin user (superuser) account using your Linux or Unix shell. The commands also work on the MariaDB server.

Tutorial details
Difficulty level	Intermediate
Root privileges	Yes
Requirements	MySQL 8.x or MariaDB 10.4+
Est. reading time	6 mintues

Creating MySQL admin user in MySQL server

The steps to create a new user in MySQL and make it a superuser/admin are as follows:

Step 1 – Login to MySQL server

The syntax is:
$ mysql -u root -p
$ mysql -h host_name_ip -u root -p

Step 2 – Create admin user account

Run the following command at mysql> prompt:

Warning : For security reasons, you should not use % as this allows access to everyone. I strongly recommend restricting access to localhost or sysadmin/developers CIDR (Classless inter-domain routing) hidden with VLAN and firewalled port.

• No ads and tracking
• In-depth guides for developers and sysadmins at Opensourceflare✨
• Join my Patreon to support independent content creators and start reading latest guides:
  • How to set up Redis sentinel cluster on Ubuntu or Debian Linux
  • How To Set Up SSH Keys With YubiKey as two-factor authentication (U2F/FIDO2)
  • How to set up Mariadb Galera cluster on Ubuntu or Debian Linux
  • A podman tutorial for beginners – part I (run Linux containers without Docker and in daemonless mode)
  • How to protect Linux against rogue USB devices using USBGuard

Join Patreon ➔

We can create user for network access too for all users. The percent sign, ( % ) means all ip address:

Limit access to 10.147.164.0/24 CIDR (10.147.164.0/255.255.255.0 subnet):

In this example, create a new user called ‘sayali’ and restrict her access to the 192.168.1.0/24 subnet. Further, TLS/SSL is used to protect transmission with an encryption protocol.

In MySQL version 8.x, caching_sha2_password is the default strong authentication plugin rather than mysql_native_password , which was the default in MySQL 5.7. In other words, we can switch back to mysql_native_password plugin for backward compatibility purposes that implement native authentication. We do not recommend to use the mysql_native_password authentication plugin for new installations that require high password security. Say, if an attacker can both listen to the connection protocol and get a copy of the mysql.user table, then the person would be able to use this information to connect to the MariaDB/MySQL server. Hence the following is recommend syntax for MySQL 8.x server:

MariaDB v10.04+ example for ed25519 auth plugin:

Use the following command to list users with their plugins:

Click to enlarge

Step 3 – Grant PRIVILEGES to admin user

The SQL syntax is as follows to grant all privileges on database:
GRANT ALL PRIVILEGES ON *.* TO ‘admin’@’%’;
GRANT ALL PRIVILEGES ON *.* TO ‘vivek’@’10.147.164.0/255.255.255.0’;
The *.* means all databases on MySQL or MariaDB server. For security purposes, you should not set up and grant all permissions for a mysql admin user account with access via the website or any other means. The solution is to grant full permission to the specific database as follows:
# grant full access to proddb for admin user only #
GRANT ALL PRIVILEGES ON proddb.* TO ‘admin’@’%’;
# Give vivek admin access to blogdb only #
GRANT ALL PRIVILEGES ON blogdb.* TO ‘vivek’@’10.147.164.0/255.255.255.0’;
The blogdb.* means all tables of blogdb MySQL/MariaDB database.

Understanding MySQL grants for admin (superuser) account

Here is a list of standard privileges:

1. Data USAGE privileges includes: SELECT, INSERT, UPDATE, DELETE, and FILE
2. Structure privileges includes: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, and TRIGGER
3. Administration privileges includes: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, and CREATE USER
4. SSL privileges includes: REQUIRE NONE, REQUIRE SSL, REQUIRE X509
5. ALL PRIVILEGES : Shortcut to grants all privileges to a mysql user account.

We can list user GRANTS as follows:

Here is what you will see from the last SQL:

Step 4 – Reload all the privileges

Now we set up a new user MySQL admin account with the required password. It is time to reload changes:
FLUSH PRIVILEGES;

Step 5 – Testing

From the client machine run:
$ mysql -u admin -h 10.147.164.6 -p db_name
$ mysql -u vivek -h 192.168.1.100 -p
# if you set TLS/SSL requirements, give CA file in PEM format #
$ mysql —ssl-ca=/path/to/our.tls.pem —ssl-mode=VERIFY_CA -u vivek -h 192.168.1.100 -p
Where,

• —ssl-ca=/path/to/our.tls.pem : CA file bundle in PEM format.
• —ssl-mode=VERIFY_CA : TLS/SSL connection mode. PEM certificates are only validated for VERIFY_CA and VERIFY_FULL SSL mode values.
• -u vivek : Admin user name created in step #1.
• -h 192.168.1.100 : MySQL server IP address.
• -p : Prompt for MySQL server password.
• db_name : State database name

Creating secure login file

The mysql_config_editor command allows us to store authentication credentials in a secure encrypted format in a confused login path file named $HOME/.mylogin.cnf. The file is not readable for human eyes. Hence it is called a confusing format. The syntax is:
$ mysql_config_editor set \
—login-path=remote \
—host=10.147.164.6 \
—port=3306 \
—user=admin \
—password
State TLS/SSL pem file in your

/.my.cnf file too if encryption enabled for admin account:
$ vim

/.my.cnf
Append/modify as follows:

Verify it:
$ ls -l $HOME/.mylogin.cnf
$ file $HOME/.mylogin.cnf
$ cat $HOME/.mylogin.cnf
Print it:
$ mysql_config_editor print —all
Now all you have to do is type the following command to login as admin user:
$ mysql —login-path=remote

Please note that MariaDB doesn’t support mysql_config_editor feature. MariaDB users need to store info in the

/.my.cnf itself, which is not secure enough as your admin password stored in a plain text format:

Wrapping up

This quick tutorial explained how to securely create admin (superuser) on MySQL or MariaDB server using the CLI and grant additional permissions as per your requirements. We further explained how to store the password securely in

/.mylogin.cnf and TLS paths in

/.my.cnf file for ease of login. If you wish to drop admin user, try:
DROP USER `user`@`host`;
To remove PRIVILEGES run:
REVOKE ALL PRIVILEGES ON *.* FROM `user`@`%`;

🐧 Get the latest tutorials on Linux, Open Source & DevOps via

Category	List of Unix and Linux commands
Documentation	help • mandb • man • pinfo
Disk space analyzers	df • duf • ncdu • pydf
File Management	cat • cp • less • mkdir • more • tree
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Linux Desktop Apps	Skype • Spotify • VLC 3
Modern utilities	bat • exa
Network Utilities	NetHogs • dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • glances • gtop • jobs • killall • kill • pidof • pstree • pwdx • time • vtop
Searching	ag • grep • whereis • which
Shell builtins	compgen • echo • printf
Text processing	cut • rev
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04

Comments on this entry are closed.

Excellent tutorial. Just what the doctor ordered.

Источник

Create a MySQL User on Linux via Command Line

In this article, we will be discussing how to use MySQL to create a new user on Linux via the command line. We will be working on a Liquid Web core-managed server running CentOS version 6.5 as the root user. The commands used should also work on later versions of MySQL on CentOS as well.

MySQL is a relational database management application primarily used on Linux and is a component of the LAMP stack (Linux, Apache, MySQL, and PHP).

Preflight Check

• Log in as the root user.
• Have access to a terminal.
• Basic knowledge of the command line.

Step 1. Log in to MySQL

Initially, we will log in to the server as the root user, and then open a terminal to access the MySQL server from the command line using the following command:

In this case, we’ve specified the user as being root using the -u flag, and then utilized the -p flag so the MySQL log in prompts us for a password. Next, we can enter our current password to complete the login.

Note: If we need to change our root password (or any other users’ password) in the database, please review this tutorial on changing a password for MySQL via the command line.

You should now be at a MySQL prompt that looks very similar to this.

Step 2. Create the MySQL User

Now, we will create a user with the name testuser, and the password test123test! using the following command.

That’s it! We have created our first MySQL user using a single command. This user will not be able to work with any of the MySQL databases until they are granted additional privileges. In fact, they will not be able to login without granting them additional permissions. To give the new user the proper permissions, please see our next tutorial on granting permissions to a MySQL user via the command line.

View a List of MySQL Users

To view a full list of MySQL users, including the host they’re associated with, can be accomplished using the following select statement.

Conclusion

MySQL is an excellent open-source relational database management system. In 2010, MySQL was forked into MariaDB after its purchase by Oracle. MariaDb is a community driven and developed, relational database management system (RDBMS). It continues to be a free and open-source software licensed under the GNU General Public License.

Talk to an Expert Now

Our Support Team are experienced in maintaining and managing this type of software. Our Linux System administrators and technicians have intimate knowledge of multiple web hosting technologies, especially those discussed in this article. If you have questions, we are available to assist 24 hours a day, 7 days a week 365 days a year.

If you are a Fully Managed VPS server, Cloud Dedicated, VMWare Private Cloud, Private Parent server or a Dedicated server owner and you are uncomfortable with performing any of the steps outlined, we can be reached via phone @800.580.4985, a chat or support ticket to assist you with this process.

Источник

Linux user mysql access

MySQL enables the creation of accounts that permit client users to connect to the server and access data managed by the server. The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT , INSERT , UPDATE , and DELETE . Additional functionality includes the ability to grant privileges for administrative operations.

To control which users can connect, each account can be assigned authentication credentials such as a password. The user interface to MySQL accounts consists of SQL statements such as CREATE USER , GRANT , and REVOKE . See Section 13.7.1, “Account Management Statements”.

The MySQL privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify . When you issue requests after connecting, the system grants privileges according to your identity and what you want to do .

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com . MySQL handles this by enabling you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com , and a different set of privileges for connections by joe from home.example.com . To see what privileges a given account has, use the SHOW GRANTS statement. For example:

Internally, the server stores privilege information in the grant tables of the mysql system database. The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables.

MySQL access control involves two stages when you run a client program that connects to the server:

Stage 1: The server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password.

Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. For details about the conditions under which the server reloads the grant tables, see Section 6.2.13, “When Privilege Changes Take Effect”.

There are some things that you cannot do with the MySQL privilege system:

You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.

Источник