Creating User(s)

In the past I have had great success with installing and configuring MySQL servers. One time in particular I had some issues with using the Administorator gui to create a user and setting permissions for that user. I wound up using the follwing SQL script to create the user.

— Global level

–Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.

GRANT ALL ON *.* TO ‘someuser’@’somehost’;
GRANT SELECT, INSERT ON *.* TO ‘someuser’@’somehost’;

–Database level

–Database privileges apply to all objects in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.

GRANT ALL ON mydb.* TO ‘someuser’@’somehost’;
GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@’somehost’;

–*
–Table level

–Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.

GRANT ALL ON mydb.mytbl TO ‘someuser’@’somehost’;
GRANT SELECT, INSERT ON mydb.mytbl TO ‘someuser’@’somehost’;

–If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database.

–Column level

–Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same columns that were granted. The column or columns for which the privileges are to be granted must be enclosed within parentheses.

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@’somehost’;

–Routine level

–The CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT privileges apply to stored routines (functions and procedures). They can be granted at the global and database levels. Also, except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines and are stored in the mysql.procs_priv table.

GRANT CREATE ROUTINE ON mydb.* TO ‘someuser’@’somehost’;
GRANT EXECUTE ON PROCEDURE mydb.myproc TO ‘someuser’@’somehost’;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s