Chapter 9 Notes: Authorization in Mysql

Chapter 9 Notes: Authorization in MySQL

MySQL has several SQL commands for managing user accounts, including CREATE USER, GRANT, REVOKE, RENAME USER, DROP USER and SET PASSWORD. The usual process for authorizing users to access objects is to create a user account with CREATE USER and then to authorize the user to perform operations using GRANT statements. The file called MySQL_Chap9GRANTStatements in the Chap9 subdirectory illustrates the commands discussed here. You should execute the commands in that file and see the results. Lab exercise 9.1 also covers these commands.

Creating Users

When MySQL is installed, a privileged account called root is created. By default, it has no password. However, the user has the option of choosing a password on installation, and it is advisable to do so. All other accounts stem from the root account. A user account can be created from the root account or from any account that has DBA privileges by writing a command of the form

CREATE USERaccount_name

IDENTIFIED BY [PASSWORD] ’password’;

The account name should normally have the form

'user_name'@'host_name'

For example, to create a user account on the local server which can be used to assign privileges for the UNIV database, we can connect to the database, and write

CREATE USER 'U101'@'localhost' IDENTIFIED BY 'sesame';

To change the password, the user needs to sign in and write a command of the form

SET PASSWORD=PASSWORD('new_password');

This statement uses the system function PASSWORD which encrypts the new password before storing it.

Granting Privileges

The GRANT statement can be used to grant privileges on objects to a user. The form of this command is

GRANT <privilege list>

ON [object_type] priv_level

TO user list

[WITH options ...];

Types of Privileges

The privileges can be categorized as administrative privileges, database privileges, or privileges on specific objects within a database, such as tables, views, or stored procedures.

Administrative privileges including CREATE DATABASE, CREATE USER, and SHOW DATABASES are also called global privileges because they apply to all objects on the server. Another type of global privilege is permitting operations on all tables in all databases. For example, to allow user U101to perform any operation on any table on the server, we could write

GRANT ALL ON *.* TO 'U101'@'localhost';

Database-level privileges apply to all objects in a named database. To assign database-level privileges, we use the form databasename.*as in

GRANT SELECT, UPDATE ON Univ.* TO ’U101’@’localhost’;

Object-level privileges include CREATE, ALTER, INDEX, SELECT, INSERT, DELETE, UPDATE, TRIGGER, CREATE VIEW, SHOW VIEW, DROP, and GRANT OPTION. A complete list can be found in the MySQL documentation. The object type may be a table, a view, or a stored procedure.

Options can includeWITH GRANT OPTION or specification of the maximum queries, updates, or connections per hour, or the maximum user connections. For example, we could write

GRANT SELECT, UPDATE ON Univ.Student TO ’U101’@’localhost’ WITH GRANT OPTION;

GRANT ALL ON *.* TO ’U101’@'localhost'WITH MAX_QUERIES_PER_HOUR 60;

Showing Privileges

To see what privileges a user has, we write a SHOW GRANTS, which shows the code that would be written to duplicate the privileges the user already has. For example to show what privileges U101 has, we write

SHOW GRANTS FOR 'U101'@'localhost';

To see what privileges all users have, write

SHOW GRANTS;

Removing Privileges

The REVOKE statement can be used to take privileges from a user, as in

REVOKE SELECT, UPDATE ON UNIV.* FROM 'U101'@'localhost';

REVOKE ALL PRIVILEGES ON UNIV.* FROM 'U101'@'localhost';

We can drop a user with a statement such as

DROP USER 'U101'@'localhost';