Syntax for GRANT and REVOKE in MySQL

[MacKenze]

Permissions can be managed in two ways: through the SQL statements GRANT and REVOKE, or with standard SQL statements (INSERT, UPDATE, etc..) On the affected tables. The difference to note is that in the first case the changes are effective immediately, while in the latter case you should use the FLUSH PRIVILEGES command to force MySQL to reload the grant tables into memory. Here are some examples of syntax for GRANT and REVOKE:

GRANT SELECT ON purchases .* TO Suniert @ localhost IDENTIFIED BY 'password' WITH GRANT OPTION

This statement assigns the user the SELECT privilege on the database Suniert @ localhost purchases. If the user did not previously exist, the line is added to the user table and 'password' is your password. If the user already exists, the password is replaced. SELECT permission on the table will registration db, being assigned to the database level. In addition, the user is given permission GRANT, thanks to which will be able to give other users their permission. Attention: With this option you can assign all of its permits to other, not just those received with this statement, but also those who had and those who receive in the future.

GRANT ALL ON TO buying.orders Suniert @ localhost

This statement assigns all permissions on the Orders table of the database user purchases Suniert @ localhost. You have not specified a password, so if the user already exists it will not be changed. If the user did not exist, is created without a password.

[MacKenze]

But beware: option sql-server mode provides the value of several possible NO_AUTO_CREATE_USER. If this value is active, you can not implicitly create a user without a password with a GRANT statement. In this case, the statement will fail, unless of course you did not already exist. In this example we saw that Grant was not awarded a permit, but if you already have it will still be able to reassign those permissions.

REVOKE SELECT on purchases .* FROM lucid @ localhost 
REVOKE ALL PRIVILEGES, GRANT OPTION FROM Suniert @ localhost

With the first statement we remove the SELECT privilege on the user purchases db lucid @ localhost. In the second we take away all privileges on the tables more than GRANT Suniert @ localhost. In this case the user will no privileges, but its use is still not deleted from the user table. It is important to remember that when you delete a database or table, all existing permits remain active. Obviously this is influential in cases where objects were recreated with the same name. Now let's see some other instructions on the management of permissions:

Hunter CREATE USER @ localhost; 
CREATE USER @ localhost IDENTIFIED BY 'password'; 
Hunter DROP USER @ localhost; 
SET PASSWORD = PASSWORD ('pw'); 
SET PASSWORD FOR Suniert @ localhost = PASSWORD ('pw');

The first statement creates a user with no password (in this case it works even if NO_AUTO_CREATE_USER is active), the second creates a user password. Those users must already exist. The third statement removes a user. The fourth set the password 'pw' for the user logged on, the last set the password 'pw' user @ localhost Suniert.

[MacKenze]

The first three instructions require permission CREATE USER, and the fourth is possible for anyone connected as you are not anonymous, the fifth and require permission to UPDATE mysql database. Now let's see what are the key permissions can be assigned to a user on the tables and databases, and instructions that allow:

There are some administrative permits that do not make sense to refer to a database: these permits are in fact only the user table. Let's see some:

SHOW DATABASES can also be used by people who do not have permission on (unless the server has been started with the - skip-show-database). However, these will not see all databases on the server, but only those for which they have rights. In general, however, non-administrative users should never have allowed the latter group. The last argument to be reported in the system of permits is very important on the storage of passwords. The password for each user is stored in the user table Password column: a field of 41 characters of which the first is an asterisk, while the next 40 are the result of hashing algorithm on the password that was set in a GRANT or CREATE USER, or with the PASSWORD () function.

[MacKenze]

The encryption algorithm is one-way, so it is not possible to get the password starting from the encrypted string. When the user tries to log on and enter the password, the client encrypts and sends the encrypted password to the server, which compares with the one stored on the database. An important element, however, is that the encryption algorithm currently used was introduced with version 4.1 of MySQL: Earlier versions used a simple algorithm that produced an encrypted string of only 16 characters. This has created a compatibility problem that occurs when a client like 'old' (ie pre-4.1 version) attempts to connect to a server of type 'new': old clients are not in fact able to support the new algorithm for hashing the password. The consequence of this is that an old client can not connect to a new server if the password the user attempts to connect with the new system is stored. The most typical situation where this occurs is the use of the old mysql extension of the PHP language, often used with MySQL. This extension is in fact a pre-4.1 client, and therefore does not support the new encryption algorithm. The solution is a new type of server can store password is of a type that the other: it is clear that the difference between two types of encryption, the server is able to distinguish easily and can then allow access to both users with the old password with the new ones. The server option - old-passwords is that which determines what types of passwords are created by GRANT, and SET PASSWORD PASSWORD () function. If the server is started with this option, the password will be generated by old type. If the option is not active, type the new password will be generated.

[MacKenze]

In essence, then, to use an older client on a server post-4.1, you must use a user whose password is encrypted with old algorithm.

In summary:

• On the server, users can coexist with the old password and new password with the user. The new type of client can access all the services, like those of old only to users with the old password.
• If the server is started without the option - old-passwords, passwords are generated with the new format. This means that new users created from the old client will not be accessible. In addition there is a risk of changing the password of a user connecting with a former client: the new password will be encrypted with the new algorithm and the user will no longer be able to connect. To generate an older password, you can use the old_password ('password').
• If the server is started with the - old-passwords, all passwords will be generated in the old format. In this way, will never generate long passwords, as well as existing ones, if changed, would return to the old format. In this case, it maintains compatibility with all clients, but their increased security resulting from the new encryption algorithm

Evidently touch the database administrator to determine, based on the needs of its users, whether or not to use the option --old-passwords.