Home arrow MySQL arrow Page 2 - Working with the MySQL Access Privilege System

4.4.3 Privileges Provided by MySQL - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we continue our discussion of security issues with the MySQL access privilege system. The third of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

TABLE OF CONTENTS:
  1. Working with the MySQL Access Privilege System
  2. 4.4.3 Privileges Provided by MySQL
  3. 4.4.4 Connecting to the MySQL Server
  4. 4.4.5 Access Control, Stage 1: Connection Verification
  5. 4.4.6 Access Control, Stage 2: Request Verification
  6. 4.4.7 When Privilege Changes Take Effect
  7. 4.4.9 Password Hashing in MySQL 4.1
By: Sams Publishing
Rating: starstarstarstarstar / 6
June 08, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Information about account privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database. The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 4.4.7, "When Privilege Changes Take Effect." Access-control decisions are based on the in-memory copies of the grant tables.

The names used in this manual to refer to the privileges provided by MySQL are shown in the following table, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies. Further information about the meaning of each privilege may be found in the MySQL Language Reference.

Privilege

Column

Context

ALTER

Alter_priv

tables

DELETE

Delete_priv

tables

INDEX

Index_priv

tables

INSERT

Insert_priv

tables

SELECT

Select_priv

tables

UPDATE

Update_priv

tables

CREATE

Create_priv

databases, tables, or indexes

DROP

Drop_priv

databases or tables

GRANT

Grant_priv

databases or tables

REFERENCES

References_priv

databases or tables

CREATE TEMPORARY TABLES

Create_tmp_table_priv

server administration

EXECUTE

Execute_priv

server administration

FILE

File_priv

file access on server host

LOCK TABLES

Lock_tables_priv

server administration

PROCESS

Process_priv

server administration

RELOAD

Reload_priv

server administration

REPLICATION CLIENT

Repl_client_priv

server administration

REPLICATION SLAVE

Repl_slave_priv

server administration

SHOW DATABASES

Show_db_priv

server administration

SHUTDOWN

Shutdown_priv

server administration

SUPER

Super_priv

server administration


The CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, and SUPER privileges were added in MySQL 4.0.2.

The EXECUTE and REFERENCES privileges currently are unused.

The SELECT, INSERT, UPDATE, and DELETE privileges allow you to perform operations on rows in existing tables in a database.

SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. Some SELECT statements do not access tables and can be executed without permission for any database. For example, you can use the mysql client as a simple calculator to evaluate expressions that make no reference to tables:

mysql> SELECT 1+1;
mysql> SELECT PI()*2;

The CREATE and DROP privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables. If you grant the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored!

The INDEX privilege allows you to create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index definitions in the CREATE TABLE statement.

The ALTER privilege allows you to use ALTER TABLE to change the structure of or rename tables.

The GRANT privilege allows you to give to other users those privileges that you yourself possess.

The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also allows the user to create new files in any directory where the MySQL server has write access. Existing files cannot be overwritten.

The remaining privileges are used for administrative operations. Many of them can be performed by using the mysqladmin program or by issuing SQL statements. The following table shows which mysqladmin commands each administrative privilege allows you to execute:

Privilege

Commands Permitted to Privilege Holders

RELOAD

flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload

SHUTDOWN

shutdown

PROCESS

processlist

SUPER

kill


The reload command tells the server to re-read the grant tables into memory. flush-privileges is a synonym for reload. The refresh command closes and reopens the log files and flushes all tables. The other flush-xxx commands perform functions similar to refresh, but are more specific and may be preferable in some instances. For example, if you want to flush just the log files, flush-logs is a better choice than refresh.

The shutdown command shuts down the server. This command can be issued only from mysqladmin. There is no corresponding SQL statement.

The processlist command displays information about the threads executing within the server (that is, about the statements being executed by clients associated with other accounts). The kill command terminates server threads. You can always display or kill your own threads, but you need the PROCESS privilege to display threads initiated by other users and the SUPER privilege to kill them. Prior to MySQL 4.0.2 when SUPER was introduced, the PROCESS privilege controls the ability to both see and terminate threads for other clients.

The CREATE TEMPORARY TABLES privilege allows the use of the keyword TEMPORARY in CREATE TABLE statements.

The LOCK TABLES privilege allows the use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege. This includes the use of write locks, which prevents anyone else from reading the locked table.

The REPLICATION CLIENT privilege allows the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.

The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers when they connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.

The SHOW DATABASES privilege allows the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option.

It is a good idea in general to grant privileges to only those accounts that need them, but you should exercise particular caution in granting administrative privileges:

  • The GRANT privilege allows users to give their privileges to other users. Two users with different privileges and with the GRANT privilege are able to combine privileges.

  • The ALTER privilege may be used to subvert the privilege system by renaming tables.

  • The FILE privilege can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using SELECT to transfer its contents to the client host.

  • The SHUTDOWN privilege can be abused to deny service to other users entirely by terminating the server.

  • The PROCESS privilege can be used to view the plain text of currently executing queries, including queries that set or change passwords.

  • The SUPER privilege can be used to terminate other clients or change how the server operates.

  • Privileges granted for the mysql database itself can be used to change passwords and other access privilege information. Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to the user table Password column can change an account's password, and then connect to the MySQL server using that account.

There are some things that you cannot do with the MySQL privilege system:

  • You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

  • You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: