News & UpdatesProgrammingWeb programming Store My Projects
Links
Affiliates

SQL Tutorial – 07 – Account

The third group of SQL keywords is the Data Control Language (DCL) which handles account management.

Create User

For creating one or more user accounts there is the CREATE USER statement.

CREATE USER 'John', 'Matt';

In MySQL, you may also specify a valid hostname after the username.

CREATE USER 'John'@'domain.com', 'Matt'@'localhost';

If you instead use a wildcard for the domain that will allow the user to connect from any computer, which is the same as leaving out the host part all together.

CREATE USER 'John'@'%', 'Matt'@'%';
CREATE USER 'John', 'Matt';

You can set a password with the IDENTIFIED BY keyword.

CREATE USER 'John' IDENTIFIED BY 'pass', 'Matt' IDENTIFIED BY 'pass';

Retrieving accounts

In MySQL, you can retrieve all user accounts from the user table in the default MySQL database. By default there is only the ROOT account.

SELECT * FROM mysql.user;

Rename User

The RENAME USER statement can change the name of one or several users.

RENAME USER 'Matt' TO 'Alex', 'John' TO 'George';

Set Password

The SET PASSWORD statement assigns a new password for a user. If you leave out the FOR clause the password will be set for the current account.

SET PASSWORD FOR 'Alex' = PASSWORD('pass');
SET PASSWORD = PASSWORD('pass');

You can query the USER() function to see which account you’re using.

SELECT USER();

Grant

To see the permissions for your account you can type SHOW GRANTS. By adding a FOR clause to SHOW GRANTS, followed by an account name, you can see the privileges for that specific account. A newly created account will have no privileges other than USAGE.

SHOW GRANTS 
SHOW GRANTS FOR 'George'

To allow a user to perform any commands at all you have to authorize them using the GRANT statement. You can either set permissions for a specific table and database, or just for a specific database, or globally for all databases and tables. As for the privileges, you simply list the keywords that the account will be allowed to use or grant full access using the ALL PRIVILEGES keyword.

GRANT ALL PRIVILEGES ON mydatabase.mytable TO 'George';
GRANT SELECT, UPDATE ON mydatabase.* TO 'George';
GRANT SELECT ON *.* TO 'George';

With GRANT you can create a new account without having to use the CREATE USER statement, complete with permissions and password.

GRANT ALL PRIVILEGES ON *.* TO 'Sara' IDENTIFIED BY 'pass';

Revoke

For canceling granted permissions there is the REVOKE keyword.

REVOKE SELECT, UPDATE ON mydatabase.* FROM 'George';

You can also remove all permissions with the following statement.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'George';

Drop User

One or more users can be removed with the DROP USER statement.

DROP USER 'Alex', 'George', 'Sara';
Recommended additional reading:
Sams - Teach Yourself SQL in 10 Minutes