Security Manager - Full Control of MySQL Users and Their Privileges

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.

Correct and accurate administration of user accounts and their privileges greatly contributes to security. 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 security using Security Manager - a new FREE PROFESSIONAL tool for convenient and easy management of MySQL users and their privileges. It is integrated into dbForge Studio for MySQL.  

Why use Security Manager?

As a powerful tool for secure database administration, Security Manager makes a significant difference in administration of MySQL users 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 Management of MySQL User Accounts and Privileges

Now you can effortlessly create, edit, or delete user accounts, grant or revoke privileges either at system or object levels, and fully control database security without 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 4 groups, which are placed on separate tabs. They are the following:

• General - contains the name, host, password, the maximum quantity of connections/queries/ updates per hour related to the user account.
• SSL - contains options of SSL-connection related to the user account.
• System - allows setting system privileges of the user account.
• Object - allows setting object privileges of the user account.

Creating a User Account 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;

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 4 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.

Accurate Granting/Revoking of Privileges

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, database security is affected. 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 system privileges (e.g., Drop, Insert, and Select) and object ones (e.g., Select, Create, and Alter for demobase.transactions) to the michael@% user account. With the list of privileges in mind you remember the account name and create the statement like the following:

GRANT DROP, INSERT, SELECT ON *.* TO 'michael' @'%' IDENTIFIED BY 'testpassword' WITH GRANT OPTION;
GRANT SELECT, CREATE, ALTER ON demobase.transactions 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 system 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. Switch to the System tab and select checkboxes next to the aforementioned system privileges in the list to grant them. You can revoke privileges, by unselecting the corresponding checkboxes.

Moreover, for your convenience two options are placed on the top of the list. They are All privileges, which selects all privileges at once to grant them, and Grant Options, which 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 object level, switch to the Object tab. Here you can see all database objects on the left and a list of privileges to choose on the right. Now you already know what to do. No chance for scratching your head and be bewildered. Expand a schema tree, select the table "transactions" from "demobase" 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 System tab will open with the highlighted object in the schema tree.

Summary

There are many ways to improve database security. You can choose any of them to assist in your database administration. But the main idea is to avoid errors and guarantee the desired result, you should entrust management of MySQL user accounts and privileges to a powerful tool.

With Security Manager you will get clear and accurate management and be able to increase your productivity. Don't lose your time while remembering valid statements to type into command line. Use four tabs of the Security Manager window to create user accounts in several clicks, to grand privileges both at system and object levels for the selected account, and then to get the good view of the account's paramenters at one glance. Download dbForge Studio for MySQL  here.