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