Technical Interview Questions
Oracle Interview Questions
MsSql Interview Questions
My SQL Interview Questions and Answers
MySQL - When Privilege Changes Take Effect
When mysqld starts, all grant table contents are read
into memory and become effective at that point.
Modifications to the grant tables that you perform using
GRANT, REVOKE, or SET PASSWORD are noticed by the server
If you modify the grant tables manually (using INSERT,
UPDATE, etc.), you should execute a FLUSH PRIVILEGES
statement or run mysqladmin flush-privileges or
mysqladmin reload to tell the server to reload the grant
tables. Otherwise your changes will have no effect until
you restart the server. If you change the grant tables
manually but forget to reload the privileges, you will
be wondering why your changes don't seem to make any
When the server notices that the grant tables have been
changed, existing client connections are affected as
Table and column privilege changes take effect with the
client's next request.
Database privilege changes take effect at the next USE
Global privilege changes and password changes take
effect the next time the client connects.
Setting Up the Initial MySQL Privileges
After installing MySQL, you set up the initial access
privileges by running scripts/mysql_install_db. The
mysql_install_db script starts up the mysqld server,
then initializes the grant tables to contain the
following set of privileges:
The MySQL root user is created as a superuser who can do
anything. Connections must be made from the local host.
NOTE: The initial root password is empty, so anyone can
connect as root without a password and be granted all
privileges. An anonymous user is created that can do
anything with databases that have a name of 'test' or
starting with 'test_'. Connections must be made from the
local host. This means any local user can connect
without a password and be treated as the anonymous user.
Other privileges are denied. For example, normal users
can't use mysqladmin shutdown or mysqladmin processlist.
NOTE: The default privileges are different for Windows.
Because your installation is initially wide open, one of
the first things you should do is specify a password for
the MySQL root user. You can do this as follows (note
that you specify the password using the PASSWORD()
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
mysql> FLUSH PRIVILEGES;
You can, in MySQL Version 3.22 and above, use the SET
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');
Another way to set the password is by using the
shell> mysqladmin -u root password new_password
Only users with write/update access to the mysql
database can change the password for others users. All
normal users (not anonymous ones) can only change their
own password with either of the above commands or with
SET PASSWORD=PASSWORD('new password').
Note that if you update the password in the user table
directly using the first method, you must tell the
server to re-read the grant tables (with FLUSH
PRIVILEGES), because the change will go unnoticed
Once the root password has been set, thereafter you must
supply that password when you connect to the server as
You may wish to leave the root password blank so that
you don't need to specify it while you perform
additional setup or testing. However, be sure to set it
before using your installation for any real production
See the scripts/mysql_install_db script to see how it
sets up the default privileges. You can use this as a
basis to see how to add other users.
If you want the initial privileges to be different than
those just described above, you can modify
mysql_install_db before you run it.
To re-create the grant tables completely, remove all the
`.frm', `.MYI', and `.MYD' files in the directory
containing the mysql database. (This is the directory
named `mysql' under the database directory, which is
listed when you run mysqld --help.) Then run the
mysql_install_db script, possibly after editing it first
to have the privileges you want.
NOTE: For MySQL versions older than Version 3.22.10, you
should NOT delete the `.frm' files. If you accidentally
do this, you should copy them back from your MySQL
distribution before running mysql_install_db.
Page Numbers : 1