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 professional tool for convenient and
easy management of MySQL users and their privileges. It is integrated into
dbForge Studio for MySQL
or supplied as part of
Fusion 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
- 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 global 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 5 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.
- 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.
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
- 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
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 -
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
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 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 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 unselecting
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 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
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 Object Privileges tab will open with the highlighted object in
the schema tree.
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 five tabs of the Security Manager window to create
user accounts in several clicks, to grant privileges both at global and object levels
for the selected account, and then to get the good view of the account's parameters
at a glance.