Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Managing User Accounts and Access Privileges

By: FYIcenter.com

Part:   1  2  3  4   5  6 

(Continued from previous part...)

What Are User Privileges?

MySQL offers many user privileges to control user accesses to different funtions and data objects. Here are some commonly used privileges:

  • ALL - All privileges.
  • CREATE - Allows the user to use CREATE TABLE commands.
  • ALTER - Allows the user to use ALTER TABLE commands.
  • DROP - Allows the user to use DROP TABLE commands.
  • DELETE - Allows the user to use DELETE commands.
  • INSERT - Allows the user to use INSERT commands.
  • UPDATE - Allows the user to use UPDATE commands.
  • SELECT - Allows the user to use SELECT commands.
  • SHUTDOWN - Allows the user to use "mysqladmin shutdown".
  • INDEX - Allows the user to create and drop indexes.
  • CREATE USER - Allows the user to manage user accounts.
  • CREATE VIEW - Allows the user to user "CREATE VIEW" commands.
  • USAGE - No privileges.

How Many Scope Levels Can User Privileges Apply?

MySQL supports 5 scope levels a user privilege can be granted:

  • Global Level - A privilege granted at this level applies to all databases on the server. Privileges granted at the global level stored in "mysql.user" table.
  • Database Level - A privilege granted at this level applies to all tables in the specified database. Privileges granted at the global level stored in "mysql.db" table.
  • Table Level - A privilege granted at this level applies to all columns in the specified table. Privileges granted at the global level stored in "mysql.tables_priv" table.
  • Column Level - A privilege granted at this level applies to only the specified column. Privileges granted at the global level stored in "mysql.columns_priv" table.
  • Routine Level - A privilege granted at this level applies to only the specified stored function procedure. Privileges granted at the global level stored in "mysql.procs_priv" table.

How To Grant User Privileges at the Global Level?

If you want to grant a user privilege at the global level, you can use the "GRANT privilegeName ON *.* TO userName" command. The argument "*.*" in the command stands for all database and all tables. The following tutorial exercise shows you how to create a new database and grant access privilege to a user on this new database:

>cd \mysql\bin
>mysql -u root -pretneciyf

mysql> CREATE DATABASE fyi;
Query OK, 1 row affected (0.01 sec)

mysql> QUIT;

>mysql -u dev -piyf

mysql> USE fyi;
ERROR 1044 (42000): Access denied for user 'dev'@'%' 
 to database 'fyi'

mysql> QUIT;

>mysql -u root -pretneciyf

mysql> GRANT CREATE ON *.* TO dev;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;

>mysql -u dev -piyf

mysql> USE fyi;
Database changed

mysql> CREATE TABLE TEST (id INTEGER);
Query OK, 0 rows affected (0.09 sec)

(Continued on next part...)

Part:   1  2  3  4   5  6 


Selected Developer Jobs:

More...