SQL User Privileges

Functions

Select - table. EX: "SELECT VERSION(), CURRENT_DATE;" Syntax:
SELECT what
FROM tablename
WHERE conditions

The SELECT condition allows selection of columns such as "colname1, colname2". The WHERE condition allows the selection of specific rows (items).
EX: SELECT * FROM tablename
Insert - Create tables
Update - table
Delete - table
Index - Used to create or remove indexes for tables.
Alter - Used for tables, allows the use of the "ALTER TABLE" command.
Create - Make new database, table, or index.
Drop - Remove database or table.
Grant - Allows a user to give other users the privilege that they have. Used to grant access to databases or tables.
References - Database or table.
Reload - Have the database engine re-read the grant tables.
Shutdown
Process
File - Can read and write files on the SQL server using "SELECT...INTO OUTFILE" or "LOAD DATA INFILE".

Specific functions

LOAD
DATA
INFILE

Additional Commands

OPTIMIZE TABLE - Used after many changes have been made to a table.
ANALYZE TABLE - Analyze and store the table key distribution.
FLUSH - Need RELOAD privilege to use this. This command flushes any unsaved buffers to permanent storage. Syntax:
FLUSH flush_option [,flush_option]

Options:
- HOSTS - Empty host cache table.
- LOGS - Close, then open all log files.
- PRIVILEGES - Reload privileges from the grant tables.
- TABLES - Close all tables
- STATUS - Used for debugging queries, it will clear status variables.
KILL - Can kill a connection thread to the MySQL server. Requires the PROCESS privilege to kill processes that are not the user's processes. The command syntax is "KILL thread_id".
SHOW (4.5.5) - Presents information about databases, tables, or columns. Options:
- SHOW DATABASES - List databases
- SHOW [OPEN] TABLES [FROM] databasename - List the tables in a database.
- SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] - Show columns in a table.
- SHOW FIELDS - A synonym for SHOW COLUMNS.
- SHOW INDEX FROM tbl_name [FROM db_name]
- SHOW KEYS - A synonym for SHOW INDEX.
- SHOW TABLE STATUS [FROM db_name] [LIKE wild] - Like SHOW TABLE, but with additional information.
- SHOW STATUS [LIKE wild] - Show server information describing data sent and received..
- SHOW VARIABLES [LIKE wild] - Shows some system variable values.
- SHOW LOGS - Shows information about some log files.
- SHOW [FULL] PROCESSLIST - Shows threads that are running which the user has control over or access to.
- SHOW GRANTS FOR user - Shows the permissions that the user currently has by showing it as grant commands required to duplicate the grants.
- SHOW CREATE TABLE table_name - Show a statement that will create a specified table.
- SHOW MASTER STATUS
- SHOW MASTER LOGS
- SHOW SLAVE STATUS

MySQL Reference Contents Page