Technical Interview Questions
Oracle Interview Questions
MsSql Interview Questions
My SQL Interview Questions and Answers
Privileges Provided by MySQL
Information about user privileges is stored in the user,
db, host, tables_priv, and columns_priv tables in the
mysql database (that is, in the database named mysql).
The names used in this manual to refer to the privileges
provided by MySQL are shown below, along with the table
column name associated with each privilege in the grant
tables and the context in which the privilege applies:
Privilege Column Context
select Select_priv tables
insert Insert_priv tables
update Update_priv tables
delete Delete_priv tables
index Index_priv tables
alter Alter_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
reload Reload_priv server administration
shutdown Shutdown_priv server administration
process Process_priv server administration
file File_priv file access on server
The select, insert, update, and delete privileges allow
you to perform operations on rows in existing tables in
SELECT statements require the select privilege only if
they actually retrieve rows from a table. You can
execute certain SELECT statements even without
permission to access any of the databases on the server.
For example, you could use the mysql client as a simple
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
The index privilege allows you to create or drop
The alter privilege allows you to use ALTER TABLE.
The create and drop privileges allow you to create new
databases and tables, or to drop (remove) existing
databases and tables.
Note that 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 grant privilege allows you to give to other users
those privileges you yourself possess.
The file privilege gives you permission to read and
write files on the server using the LOAD DATA INFILE and
SELECT ... INTO OUTFILE statements. Any user to whom
this privilege is granted can read or write any file
that the MySQL server can read or write.
The remaining privileges are used for administrative
operations, which are performed using the mysqladmin
program. The table below shows which mysqladmin commands
each administrative privilege allows you to execute:
Privilege Commands permitted to privilege holders reload
reload, refresh, flush-privileges, flush-hosts,
flush-logs, and flush-tables
process processlist, kill
The reload command tells the server to re-read the grant
tables. The refresh command flushes all tables and opens
and closes the log files. flush-privileges is a synonym
for reload. The other flush-* commands perform functions
similar to refresh but are more limited in scope, 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.
The processlist command displays information about the
threads executing within the server. The kill command
kills server threads. You can always display or kill
your own threads, but you need the process privilege to
display or kill threads initiated by other users.
It is a good idea in general to grant privileges only to
those users who need them, but you should exercise
particular caution in granting certain privileges:
The grant privilege allows users to give away their
privileges to other users. Two users with different
privileges and with the grant privilege are able to
The alter privilege may be used to subvert the privilege
system by renaming tables.
The file privilege can be abused to read any
world-readable file on the server into a database table,
the contents of which can then be accessed using SELECT.
This includes the contents of all databases hosted by
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.
Privileges on the mysql database 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). If they can access the mysql.user password
column, they can use it to log into the MySQL server for
the given user. (With sufficient privileges, the same
user can replace a password with a different one.)
There are some things that you cannot do with the MySQL
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.
MySQL - How the Privilege System Works
The MySQL privilege system ensures that all users may do
exactly the things that they are supposed to be allowed
to do. When you connect to a MySQL server, your identity
is determined by the host from which you connect and the
user name you specify. The system grants privileges
according to your identity and what you want to do.
MySQL considers both your hostname and user name in
identifying you because there is little reason to assume
that a given user name belongs to the same person
everywhere on the Internet. For example, the user bill
who connects from whitehouse.gov need not be the same
person as the user bill who connects from microsoft.com.
MySQL handles this by allowing you to distinguish users
on different hosts that happen to have the same name:
you can grant bill one set of privileges for connections
from whitehouse.gov, and a different set of privileges
for connections from microsoft.com.
MySQL access control involves two stages:
Stage 1: The server checks whether or not you are even
allowed to connect.
Stage 2: Assuming you can connect, the server checks
each request you issue to see whether or not you have
sufficient privileges to perform it. For example, if you
try to select rows from a table in a database or drop a
table from the database, the server makes sure you have
the select privilege for the table or the drop privilege
for the database. The server uses the user, db, and host
tables in the mysql database at both stages of access
control. The fields in these grant tables are shown
Table name user db host
Scope fields Host Host Host
User Db Db
Privilege fields Select_priv Select_priv Select_priv
Insert_priv Insert_priv Insert_priv
Update_priv Update_priv Update_priv
Delete_priv Delete_priv Delete_priv
Index_priv Index_priv Index_priv
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Drop_priv Drop_priv Drop_priv
Grant_priv Grant_priv Grant_priv
For the second stage of access control (request
verification), the server may, if the request involves
tables, additionally consult the tables_priv and
columns_priv tables. The fields in these tables are
Table name tables_priv columns_priv
Scope fields Host Host
Privilege fields Table_priv Column_priv
Other fields Timestamp Timestamp
Each grant table contains scope fields and privilege
Scope fields determine the scope of each entry in the
tables, that is, the context in which the entry applies.
For example, a user table entry with Host and User
values of 'thomas.loc.gov' and 'bob' would be used for
authenticating connections made to the server by bob
from the host thomas.loc.gov. Similarly, a db table
entry with Host, User, and Db fields of 'thomas.loc.gov',
'bob' and 'reports' would be used when bob connects from
the host thomas.loc.gov to access the reports database.
The tables_priv and columns_priv tables contain scope
fields indicating tables or table/column combinations to
which each entry applies.
For access-checking purposes, comparisons of Host values
are case insensitive. User, Password, Db, and Table_name
values are case sensitive. Column_name values are case
insensitive in MySQL Version 3.22.12 or later.
Privilege fields indicate the privileges granted by a
table entry, that is, what operations can be performed.
The server combines the information in the various grant
tables to form a complete description of a user's
Scope fields are strings, declared as shown below; the
default value for each is the empty string:
Field name Type
Db CHAR(64) (CHAR(60) for the tables_priv and
In the user, db and host tables, all privilege fields
are declared as ENUM('N','Y') -- each can have a value
of 'N' or 'Y', and the default value is 'N'.
In the tables_priv and columns_priv tables, the
privilege fields are declared as SET fields:
Table name Field name Possible set elements
tables_priv Table_priv 'Select', 'Insert', 'Update',
'Delete', 'Create', 'Drop', 'Grant', 'References',
tables_priv Column_priv 'Select', 'Insert', 'Update',
columns_priv Column_priv 'Select', 'Insert', 'Update',
Briefly, the server uses the grant tables like this:
The user table scope fields determine whether to allow
or reject incoming connections. For allowed connections,
any privileges granted in the user table indicate the
user's global (superuser) privileges. These privileges
apply to all databases on the server.
The db and host tables are used together:
The db table scope fields determine which users can
access which databases from which hosts. The privilege
fields determine which operations are allowed.
The host table is used as an extension of the db table
when you want a given db table entry to apply to several
hosts. For example, if you want a user to be able to use
a database from several hosts in your network, leave the
Host value empty in the user's db table entry, then
populate the host table with an entry for each of those
The tables_priv and columns_priv tables are similar to
the db table, but are more fine-grained: they apply at
the table and column levels rather than at the database
Note that administrative privileges (reload, shutdown,
etc.) are specified only in the user table. This is
because administrative operations are operations on the
server itself and are not database-specific, so there is
no reason to list such privileges in the other grant
tables. In fact, only the user table need be consulted
to determine whether or not you can perform an
The file privilege is specified only in the user table,
too. It is not an administrative privilege as such, but
your ability to read or write files on the server host
is independent of the database you are accessing.
When you modify the contents of the grant tables, it is
a good idea to make sure that your changes set up
privileges the way you want.
A useful diagnostic tool is the mysqlaccess script,
which Yves Carlier has provided for the MySQL
distribution. Invoke mysqlaccess with the --help option
to find out how it works. Note that mysqlaccess checks
access using only the user, db and host tables. It does
not check table- or column-level privileges.
Page Numbers : 1