IST366: The MySQL access privilege system.

The MySQL access privilege system

MySQL access controls involves two steps:

The server checks whether or not the user with the given name is allowed to connect from the host he/she is currently connecting from
Assuming the user can connect, the server checks each request you the user issues to see whether or not he/she has sufficient privileges to perform it.

Please note, that MySQL considers both the hostname and username in identifying the user, that is, the same user can have different privileges depending where he/she is connecting from.

MySQL does not have a special command to create users instead it useses the same GRANT command that gives them privileges. The general form of this command is:

GRANT privileges [(columns)]
   ON item
   TO full_user_name [IDENTIFIED BY 'password']
[WITH GRANT OPTION];

Full user name

As we mentioned before a user is identified by name and host. So, the full user name should include both user name and host name. These two fields are separated with "@" sign. Example:

danil@localhost - user with login danil connected from the local computer

bob@'%.marshall.edu' - user with login bob connecting from any computer in marshall.edu domain

dummy@'170.140.%' -user with login dummy connected from any computer whose IP starts with 170.140

Types and Levels of Privilege

Privileges for Users
Privilege	Applies to	Description
`SELECT`	tables, columns	Allows user to select rows from tables
`INSERT`	tables, columns	Allows users to insert new rows into tables
`UPDATE`	tables, coulmns	Allows users to modify data in the existing tables
`DELETE`	tables	Allows users to delete existing table rows
`INDEX`	tables	Allows users to create and drop indexes on particular tables
`ALTER`	tables	Allows users to alter the structure of the existing tables
`CREATE`	databases, tables	Allows users to create new databases or tables. If a particular database or table is specified in the `FRANT` statement, they can only create that database or table, which means they have to `DROP` it first.
`DROP`	databases, tables	Allows users to delete databases or tables

Privileges for Administrators
Privilege Description Permitted commands

RELOAD Allows an administrator to reload grant tables and flush privileges, hosts, logs, and tables reload, refresh, flush privileges, flush hosts, flush logs, flush tables

SHUTDOWN Allows an administrator to shutdown the server shutdown

PROCESS Allows an administrator to view server processes and kill them show processlist, kill

FILE Allows an administrator to read data from files on a server load_file, load data infile

It is possible to grant these privileges to nonadministrators, but extreme caution should be used if you are considering doing so. An average user should have no need in use these commands (except may be file commands).

Privileges for Administrators
Privilege	Description	Permitted commands
`RELOAD`	Allows an administrator to reload grant tables and flush privileges, hosts, logs, and tables	`reload, refresh, flush privileges, flush hosts, flush logs, flush tables`
`SHUTDOWN`	Allows an administrator to shutdown the server	`shutdown`
`PROCESS`	Allows an administrator to view server processes and kill them	`show processlist, kill`
`FILE`	Allows an administrator to read data from files on a server	`load_file`, load data infile

Special Privileges
Privilege Description

ALL All privileges listed in the previous tables

USAGE No privileges except a privilege to connect to the server. Usually used to create a user adn add more privileges later

Special Privileges
Privilege	Description
`ALL`	All privileges listed in the previous tables
`USAGE`	No privileges except a privilege to connect to the server. Usually used to create a user adn add more privileges later

How to change user's password

There are two ways to do it. More official way is to use SET PASSWORD command:

SET PASSWORD 
FOR full_user_name = PASSWORD('newpassword');

Examples

Create user dummy with ability connect from the localhost only. Set the password to be test:
```
GRANT USAGE
   ON *.*
   TO dummy@localhost IDENTIFIED BY 'test';
```

Create user bob with password bobby17 with all user privileges for the database library if he connects locally and with select privilege only if he connects from any other host:

 GRANT select, insert, update, delete, create, index, drop, alter
	ON library.* 
	TO bob@localhost IDENTIFIED BY 'bobby17';
 GRANT select 
	ON library.*
	TO bob@'%';
 SET PASSWORD FOR bob = password('bobby17');

Create a user with administrator privileges

 GRANT ALL
	ON *.*
	TO admin@localhost IDENTIFIED BY 'new_admin_pass'
  WITH GRANT OPTION;

Grant an existing user bob update and insert privileges for library if he connects from a Marshall computer
```
 GRANT update, insert
	ON library.*
	TO bob@'%.marshall.edu';
 
```
Grant the user danil select privilege on books table of the library database
```
 GRANT select
	ON library.books
	TO danil@localhost;
 
```
Allow the user danil to update checked field in the books table of the library database
```
 GRANT update (checked)
	ON library.books
	TO danil@localhost;
 
```

How to Revoke Privileges

The same way we can grant privileges we can revoke them. The basic form of the REVOKE command is:

REVOKE privileges [(columns)]
	ON item
  FROM full_user_name;

REVOKE GRANT OPTION
	ON item
  FROM full_user_name;

Please note that all changes in the privileges you made are not active until you FLUSH by using FLUSH PRIVILEGES command.

Principle of Least Privilege

A user should have the lowest level of privileges required in order to perform his assigned task.

Back door

All these operations can be performed in a different way. MySQL server keeps all information about user's accounts and privileges in

database: mysql

tables: user, db, host, tables_privm culumns_priv
For example, you can set a new password for user bob (no matter where he's connecting from) by using

USE mysql;
UPDATE user
   SET password = PASSWORD('new+password')
 WHERE user = 'bob';
FLUSH PRIVILEGES;

You can find more information about this here and more examples here.