• Choose a category
• All
• Classic-Fiction

By Root 22184 0

IDENTIFIED BY clause. You don’t need to use the PASSWORD⁠(⁠ ⁠ ⁠) function to encrypt the password; this is done automatically. However, if you wish to provide the hash value of the password, precede the password with IDENTIFIED BY PASSWORD. If the password clause is not given, a blank password is assumed and will be accepted. This is a potential security problem and should never be done. If you do this by mistake, use the SET PASSWORD statement to set the password.

Multiple user accounts may be specified in a comma-separated list.

The CREATE USER statement was introduced in version 5.0.2 of MySQL. For previous versions, use the GRANT statement. This new statement operates similarly to the GRANT statement, except that you cannot specify user privileges with the CREATE USER statement. As a result, the process is to create a user with the CREATE USER statement and then to grant the user privileges with the GRANT statement. This two-step process is a more logical process, especially to a newcomer to MySQL. However, you can still use just the GRANT statement to create and set privileges for a new user.

This statement requires CREATE USER privilege or INSERT privilege for the mysql database, which contains user account information and privileges. To remove a user, use the DROP USER statement and possibly also the REVOKE statement:

CREATE USER 'paola'@'localhost'

IDENTIFIED BY 'her_password',

'paola'@'caporale.com'

IDENTIFIED BY 'her_password';

In this example, two user accounts are created along with their passwords, but both are for the same person. The difference is that one allows the user to log into the server hosting the database and to run the mysql client or some other client on the server, the localhost. The other account allows the user to connect from a host named caporale.com using a client from that host. No other host will be allowed for this user.

Name

DROP USER

Synopsis

DROP USER 'user'@'host'

Use this statement to delete a user account for the MySQL server. As of version 5.0.2 of MySQL, this statement will delete the user account and its privileges from all grant tables. The username is given within quotes, followed by the at sign (@) and the host IP address or hostname within quotes. This statement requires a CREATE USER privilege or DELETE privilege for the mysql database, which contains user account information and privileges. Dropping a user account does not affect current sessions for the user account. It will take effect when any sessions opened by the user terminate. Use the KILL statement (explained in Chapter 7) to terminate an open client session for a user that has been dropped.

Some users may have more than one user account (i.e., user and host combinations). You should check the server’s mysql.user table to be sure:

SELECT User,Host

FROM mysql.user

WHERE User LIKE 'paola';

+-------+--------------+

| User | Host |

+-------+--------------+

| paola | localhost |

| paola | caporale.com |

+-------+--------------+

DROP USER 'paola'@'localhost',

'paola'@'caporale.com';

Prior to version 5.0.2 of MySQL, the DROP USER statement won’t delete a user that has any privileges set to 'Y'. To eliminate the user account’s privileges, issue the REVOKE statement before using DROP USER:

REVOKE ALL ON *.* FROM 'paola'@'localhost';

DROP USER 'paola'@'localhost';

The ALL option is used to ensure revocation of all privileges. The *.* covers all tables in all databases. Prior to version 4.1.1 of MySQL, you would have to issue the following instead of a DROP USER statement:

DELETE FROM mysql.user

WHERE User='paola' AND Host='localhost';

FLUSH PRIVILEGES;

Notice that the FLUSH PRIVILEGES statement is necessary for the preceding DELETE statement to take effect immediately. It’s not necessary after the DROP USER statement, though.

Name

FLUSH

Synopsis

FLUSH [LOCAL|NO_WRITE_TO_BINLOG] option[, ...]

Options:

DES_KEY_FILE, HOSTS, LOGS, MASTER, PRIVILEGES, QUERY_CACHE,

STATUS, TABLE, TABLES, TABLES WITHOUT READ LOCK, USER_RESOURCES

Use this statement to clear and reload temporary