MySQL User Management and Database Security
in One Tool
How to Secure a MySQL Server Database
Efficient database administration depends on many factors. Security is a key point that guarantees stability of both a separate database and a large database infrastructure. Here are five tips to improve MySQL data security.
Tip 1 - Change MySQL Default Port
In most cases, hackers try to attack default values. That's why it's required to change the MySQL port in the my.cnf file. After changing the port, you should create a new superuser and delete all root accounts.
Tip 2 - Drop Test Databases
Test databases are easy targets for hackers because they are available to all users. List all MySQL databases to find test ones and delete them in order to avoid possible risks.
Tip 3 - Set up SSL Encryption
You can set up SSL for MySQL secure connection. Thus, you will be sure that data is transferred securely and nobody can steal it.
Tip 4 - Disable the LOAD DATA LOCAL INFILE Command
If the LOAD DATA LOCAL INFILE command is enabled, users can read local files and retrieve other files available on the operating system. To prevent data leakage, you must disable the command by setting local-infile=0 in the my.cnf file.
Tip 5 - Set the Files Permissions
Ensure that MySQL config files and log files are available only for users with the "mysql" privileges.
These best practices for MySQL will protect you from security issues. However, it turns to be a tough task in practice. DB administrators know that in fast-paced environment it's impossible to avoid errors, while controlling a huge amount of accounts and their privileges, unless you have a reliable tool.
This article describes how to improve database administration and ensure better MySQL database security using Security Manager — a professional tool for convenient and easy management of MySQL users accounts and their privileges. It is integrated into dbForge Studio for MySQL or supplied as a part of dbForge Fusion for MySQL.
Secure MySQL Easily with Security Manager Tool
As a powerful tool for secure database administration, Security Manager makes a significant difference in administration of MySQL users accounts and privileges. Extended functionality and well-designed user interface give an accurate and easy way to do routine administration tasks.
With Security Manager you can benefit in the following way:
- Replace command line operations with visual management of MySQL user accounts and their privileges
- Simplify administration and reduce errors
- Increase security of MySQL databases thanks to simplified management
- Speed up your work and enhance productivity
Visual MySQL User Management
Now you can effortlessly create, edit, or delete user accounts, grant or revoke privileges either at global or object levels, and fully control database security without a monotonous code typing into the command line. Graphical user interface provides a clear way to do all these tasks.
The Security Manager window consists of two parts — the left one shows a list of all user accounts, the right one allows you to enter and manage the user account's data. All data is divided into 5 groups, which are placed on separate tabs. They are as follows:
General — contains the name, host, password, the maximum quantity of
connections/queries/updates per hour related to the user account.
- Roles — contains roles that may be assigned to a user account.
- Global Privileges — allows setting global privileges of the user account.
- Object Privileges — allows setting object privileges of the user account.
- SSL - contains options of SSL-connection related to the user account.
MySQL User Account Management in Several Clicks
Each user account has a set of various parameters, which should be properly entered and then managed. It requires good experience in MySQL syntax, as well as attention and time. For example, you need to create a new user account with the following parameters:
- Username — "michael",
- Host — %,
- Password - "testpassword",
- Maximum number of connections to a server per hour — 10,
- Maximum quantity of queries and updates per hour — 20,
- Maximum quantity of updates per hour — 5,
- Maximum of simultaneous connections to this account — 2,
- SSL connection to be used for connection to a MySQL server
Traditionally, to create such an account, you should enter the following statement:
CREATE USER 'michael' @'%'; GRANT USAGE ON *.* TO 'michael' @'%' IDENTIFIED BY 'testpassword' REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 5 MAX_CONNECTIONS_PER_HOUR 10 MAX_USER_CONNECTIONS 2; SET PASSWORD FOR 'michael'@'localhost' = PASSWORD ('testpassword')
Now you can create and then edit a user account without a command line operation. In the Security Manager window, right-click the list of user accounts and select the New User option from the menu. Visual creating or editing of user accounts and their privileges shifts your work to a new level where you can avoid errors while speeding up your work and automating routine tasks.
As all user account's parameters are divided into 5 categories and placed on the separate tabs with the corresponding names, you should only switch to the required tab and enter the data for the selected user account. No required parameters will be missed. On the General tab enter the aforementioned user account's parameters into the corresponding fields. Then switch to the SSL tab and select SSL option from the drop-down list. To save the new account, click the Save button on the toolbar. That's all, no errors and efforts. You can see the newly-created account in the left part of the Security Manager window.
Next time when you need to change any user's parameters, just select the required user in the left part of the Security Manager window and effortlessly edit its parameters in the right part. To duplicate a user account, select the corresponding option from the right-click menu. One more useful thing is available - Security Manager can generate DDL statement for each created user account , so you can use this error-free statement when required.
Secure Granting/Revoking of Privileges for MySQL Users
Each DB administrator manages lots of user accounts and updates their privileges every day. It's a time-consuming and sometimes confusing task where many errors occur. As a result, it affects database security. Security Manager offers you a perfect solution to reduce errors and get accurate administration of user privileges.
Let's take a standard situation when you should grant some global privileges (e.g., Drop, Insert, and Select) and object ones (e.g., Select, Create, and Alter for demobase.transactions) to the [email protected]% user account. With the list of privileges in mind you remember the account name and create the statement like the following:
GRAND SELECT, CREATE ALTER ON sakila.* TO 'michael'@'%';
Thanks to the convenient user interface of Security Manager, you get a clear view as well as a quick access to the list of user accounts and their privileges. Granting and revoking privileges at global and object levels becomes easy, as the only thing you have to do is to select the tab with the corresponding name in the Security Manager window.
On the Roles tab, you can assign roles to user accounts. This feature allows granting sets of privileges at once without checking each privilege in the general list of available privileges. To see privileges granted by each role, go to the Global Privileges tab. Here you can add privileges to the ones granted to the role, or revoke privileges that are not needed.
Switch to the Global Privileges tab and select checkboxes next to the aforementioned global privileges in the list to grant them. You can revoke privileges, by clearing the corresponding checkboxes.
Moreover, for your convenience the With Grant Option option is placed at the top of the list. It allows the user to grant privileges to other users. The logic of granting privileges is obvious and simple. You can apply any of the privileges in one click and see the whole picture of what is granted and what is not. To revoke a privilege, choose a required one in the list and clear the checkbox next to it.
To grant privileges at the object level, switch to the Object Privileges tab. Here you can see all database objects on the left and a list of privileges to choose from on the right. Now you already know what to do. No chance for scratching your head and be bewildered. Expand a database tree, select the required object from the sakila database and check the required privileges on the right. At the bottom of the window you can see a list of all object privileges for the current user account.
You can also right-click the required database object in Database Explorer (a convenient tool for enhanced work with databases) and select Edit Privileges option from the menu. The Object Privileges tab will open with the highlighted object in the schema tree.
With Security Manager, you will get clear and accurate management and increase your productivity. Waste no time remembering valid statements to type into the command line. Use the five tabs of the Security Manager window to create user accounts in several clicks, to grant privileges both at the global and object levels for the selected account, and get a good view of the account's parameters at a glance.