book musings



missle defense



cultcha blog


scripts & programs

on the town



creds (PDF)



MySQL: Adding or Dropping a User

Adding a new user is a three-step process. First you add the user account, and then you grant privileges to whatever databases that person will be using.

1. To add a user, you use the Create User command. Log onto Mysql as root, then, at the prompt, type:

mysql>create user [name of new user];
[MYSQL BUG: The documentation says you can add password onto this line, i.e. "identified by '[password]' but YOU CAN'T!!! You could spend all morning wondering why your new client can't log in because of this SNAFU.

Instead, you have to add the password in a separate command (see the section on adding passwords). So, step 2 is:

2. Add password for new user (see separate blog entry).

3. To grant database privileges to the user, you use the grant command. First you stipulate the privileges you want to grant, specify the name of the databases these privileges would apply to, and finally whom all this should apply to. Intuitively backwards.

mysql>GRANT ALL ON [databasename].* TO '[username]'@'[somehost]';
mysql>GRANT SELECT, INSERT ON *.* TO '[someuser]'@'[somehost]';
Note that somehost specifies where the host is working from, either remotely (over a network) or on the terminal of the machine where mysql resides (localhost). You can also specify specific IP numbers, I think. If you leave @ off altogether, mysql assumes the user can access these privileges from anywhere.

Also note that using a database usually involves working with multiple files, all under the name of the database but with different suffixes. So that it is usually good to use a wildcard for the suffix of these files for grant privileges.

[MYSQL BUG: The '%' wildcard is deceptive, insofar as it does not grant privileges from the local host, as the documentation states. Instead, you have to grant a set of privileges especially for the localhost, i.e. '[someuser]'@'localhost'.]

* * *
Dropping a user account can be done thusly:
mysql>drop user [Name of user]

Taken from here

From this compilation of MySQL commands..