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