Learning Mysql

Table level
You can use, for example, music.album to grant a privilege for one or more tables in a database.
Column level
Grants access for one or more columns in a table in a database (but isn’t available for ALTER. You grant column-level access using a comma-separated list in parentheses after the privilege, as in, for example:
GRANT SELECT (album_name, album_id) ON music.album
In this chapter, we explain how to manage privileges using the
GRANT
statement. Many of the statements affected by the privileges are discussed elsewhere as follows The statements DELETE, INSERT, SELECT, SHOW DATABASES, and
UPDATE
are introduced in Chapter 5 and discussed further in Chapters 7 and 8.
• The statements ALTER, CREATE, DROP, LOAD DATA INFILE
, and SELECT ... INTO
are described in Chapter 6.
• The statements LOCK TABLES
and UNLOCK TABLES are discussed in Chapter The EXECUTE, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE, CHANGE MASTER,
KILL
,
and PURGE MASTER LOGS
statements are outside the scope of this book seethe MySQL
manual for more on these. We discuss GRANT OPTION
in the next section.
Table 9-1 shows the levels at which the privileges can be configured Global (G, Database (D, Table (T, and Column (C. For example, the first row shows that the
ALL
option is available at all levels except for columns.
Table 9-1. Privileges and their levels in MySQL
Privilege
Application
G
D
T
C
ALL
All simple privileges except the ability to grant privileges (
GRANT
OPTION
).
✓
✓
✓
✗
ALTER
The ALTER TABLE
statement.
✓
✓
✓
✗
CREATE
The
CREATE
statement.
✓
✓
✗
✗
CREATE TEMPORARY TABLES
The CREATE TEMPORARY TABLES
statement; user is allowed to create a temporary table in the active database for her own session.
✓
✓
✗
✗
DELETE
The
DELETE
statement.
✓
✓
✓
✗
DROP
The
DROP
statement.
✓
✓
✓
✗
EXECUTE
Stored procedures (MySQL version 5 and later only).
✓
✗
✗
✗
FILE
Reading and writing of disk files with SELECT ... INTO
and
LOAD DATA INFILE
✓
✗
✗
✗
GRANT OPTION
Ability to grant own privileges to others. For most applications,
there is generally no need for this, because the root user decides on access privileges.
✓
✓
✓
✗

Download 4.24 Mb.

Share with your friends: