📖 MySQL Database Security

Database administrations starts with the creation of user accounts, granting user access to database objects and managing the security of the database. There are useful tools built into MySQL to help manage database security.

Managing Users

Prevention of unauthorized access to a database is critical. Database administrator determines types of access various users have to a database. Views provide some security by hiding sensitive information but the main mechanism for providing access to a database is the GRANT command.

Each user account begins with creating a user account. This will give USAGE privileges to the user for login but no access to the database. Database access is assigned by granting privileges to specific roles, resources and actions.

Users are not necessarily individual users. They are often groups of users that need similar access to the database. When you create users, you are normally creating user groups.

Create, Rename, or Drop a User

Format
CREATE USER userName[@host IDENTIFIED BY 'password'];
-- Creates a user. If @host is used, the user can only connect from the specified host.

RENAME USER userName[@host] TO userName[@host];
-- Renames the user to the new username.

DROP USER userName[@host];
-- Drops the specified user.

Checking User Status

SHOW GRANTS FOR userName[@host];
-- Shows the db privileges for selected user.

Managing Privileges

Every user on the database must have privileges issued. These privileges control the level of access a user has to database objects. There are 7 types of access that can be granted.

  • SELECT
  • UPDATE
  • INSERT
  • DELETE
  • INDEX
  • ALTER
  • ALL

Grant Database Privileges

Format
GRANT accessType
Required. Identifies the type of access the users are given. accessType may be:
(columnlist)
optional - states the columns that the user may access. If omitted, user will have access to all columns in the table or view.
ON table_viewName
Required. Indicates the table or view that the user will have access to.
TO username1, username2 . . .
Required. Specifies the users who are granted access
WITH GRANT OPTION
Optional. Specifies that the user can grant access to other users
; (semi-colon)
Required. Indicates the end of the SQL statement.
Example
GRANT SELECT ON invoice TO userName;

View Privileges

Format
SHOW GRANTS [FOR userName[@host]];
-- if FOR clause is omitted - shows for the current user

Change Password

Format
SET PASSWORD [FOR userName[@host]]] = PASSWORD ('password');
-- If FOR clause is omitted - changes the password for the current user

-- Password can also be changed with the grant statement
GRANT USAGE ON *.* to username IDENTIFIED BY 'password';

Testing User Permissions

After creating user accounts and assigning privileges, it's important to verify that each account works as expected. Use the following procedure:

  1. Log in as the user using MySQL Workbench or another client
  2. Run SELECT CURRENT_USER(); to confirm you're logged in as the intended user
  3. Run SHOW GRANTS; to view that user's effective privileges
  4. Try running a query that should succeed (e.g., SELECT from a permitted view)
  5. Optionally, run a query that should fail to confirm access limits (e.g., unauthorized UPDATE)

Document the test results in your SQL file as inline comments:

-- As staff_user
SELECT CURRENT_USER();
-- Output: 'staff_user'@'localhost'

SHOW GRANTS;
-- Output: GRANT SELECT ON classdb.staff_view TO 'staff_user'@'localhost';

SELECT * FROM staff_view;
-- Output: (Sample results…)

Removing Users

To remove a user from the database, you must first revoke their privileges and then drop the user account.

Revoke Database Privileges

Format
REVOKE accessType
Required. Identifies the type of access being revoked. accessType may be:
(columnlist)
optional - states the columns that the user may not access. If omitted, user will not have access to any columns in the table or view.
ON table_viewName
Required. Indicates the table or view that the user will not have access to.
FROM username1, username2 . . .
Required. Specifies the users who are granted access
; (semi-colon)
Required. Indicates the end of the SQL statement.
Example
REVOKE SELECT ON invoice FROM userName;

Last updated: October 15, 2025 at 2:57 PM