MySQL Privileges

JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

MySQL Privileges

The user privilege system built into MySQL dictates who can do what within each particular database. The mysql database stores the specifics in terms of users, passwords, hosts, and database access. It also records what individual, allowable actions particular users can doin other words, what SQL commands they can run on the database. As newer features are added to MySQL, such as stored procedures and views, those permissions are added to the mysql database as well.

Tables B.4, B.5, and B.6 list the available privileges. I've grouped them into my own rough categories: basic, administrative, and new. These are entirely artificial distinctions of my own making but should help give you a sense of how to assign privileges based upon the version of MySQL you are using and what a user needs to be able to do. As a rule, always give each user the minimum required privileges on a database.

Table B.4. This is the list of basic privileges that can be assigned to MySQL users on a case-by-case basis. It is generally safe to assign these to users for a specific database (except for the mysql database, which must be kept off-limits).

Basic MySQL Privileges

Privilege

Allows For

SELECT

Reading of rows from tables

INSERT

Adding new rows of data to tables

UPDATE

Altering existing data in tables

DELETE

Removing existing data in tables

SHOW DATABASES

Listing the available databases

INDEX

Creating and dropping indexes in tables

ALTER

Modifying the structure or properties of a table

CREATE

Creating new tables or databases

CREATE TEMPORARY TABLES

Creating temporary tables

DROP

Deleting existing tables or databases


Table B.5. These privileges should be given only to administrative users and preferably on specific databases.

Administrative MySQL Privileges

Privilege

Allows For

RELOAD

Reloading the grant tables (and therefore enact user changes)

SHUTDOWN

Stopping the MySQL server

FILE

Importing data into tables from text files

GRANT OPTION

Creating new users with the same permissions as current user

CREATE USER

Creating new users

REVOKE

Removing the permissions of users

PROCESS

Showing currently running processes

SUPER

Terminating running processes


Table B.6. These privileges have been added in newer versions of MySQL (mostly 5.0 and up). Note that it's safer and normal for some users to be able to use a view or run a stored procedure, whereas limited users can create or modify them.

Newer MySQL Privileges

Privilege

Allows For

CREATE VIEW

Creating a view

SHOW VIEW

Using a view

ALTER ROUTINE

Modifying a stored procedure

CREATE ROUTINE

Creating a stored procedure

EXECUTE

Running a stored procedure

LOCK TABLES

Locking tables

REPLICATION CLIENT

Showing replication status

REPLICATION SLAVE

Performing replication



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©