SQL User Management

The system uses user, db, and host tables to determine access control. Also tables, priv, and columns.

The case of letters in commands does not matter. The system is not case sensitive.

User Determination

The system determines the user based on the following two characteristics:

• User name.
• The host the user connects from.

Tables used for User Access Control

These tables are contained in the database, "mysql". The "mysql" database is used to control user access and privileges.

• user - Determines whether the user will be allowed to connect to the database.
• db - Determines which users can access the database.
• host - Used to allow users access from several hosts.

Functional Privileges

Users are given privileges using the GRANT command and provileges are removed using the REVOKE command.

The system will check each request from each user to determine if they have permission to perform the requested function.

GRANT and REVOKE Commands

Four privilege levels:

• Global - Use "ON *.*" with the GRANT command to set global privileges.
• Database - Use "ON dbname.*" with the GRANT command to set privileges on a database.
• Table
• Column

The GRANT statement can be used to add new users. GRANT Syntax:

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY 'password']

[, user_name [IDENTIFIED BY 'password'] ...]

[REQUIRE

[{SSL| X509}]

[CIPHER cipher [AND]]

[ISSUER issuer [AND]]

[SUBJECT subject]]

[WITH GRANT OPTION]

EX: Grant ALL on database_name.* TO username

If you use the GRANT statement to add a new user and do not include "IDENTIFIED BY" with a password in the statement, the user will be set up withoout a password. This is not very secure.

REVOKE Syntax:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

FROM user_name [, user_name ...]

Privilege Types

The following privilege types may be granted or revoked using the GRANT or REVOKE commands.

• ALL (ALL PRIVILEGES)
• ALTER - Table
• CREATE - Table
• DELETE - Table
• DROP - Table
• FILE
• GRANT OPTION - Used with the REVOKE command to prevent a user from using the GRANT command. Syntax is "REVOKE GRANT OPTION ON tablename FROM username".
• INDEX - Table
• INSERT - Table, column
• PROCESS
• REFERENCES - Not implemented yet.
• RELOAD
• SELECT - Table, column
• SHUTDOWN
• UPDATE - Table, column
• USAGE - No privileges

Setting Passwords

The SET PASSWORD command is used to set a user's password.