📖 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;
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;
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';