programming4us
programming4us
DATABASE

MySQL for Python : Creating Users and Granting Access - GRANT access in MySQL

5/8/2013 9:08:40 PM

After creating a user account, one still needs to tell MySQL what kind of privileges to assign to it. MySQL supports a wide range of privileges (see the table of privileges on page 9). A user can only grant any privilege that they have themselves.

As with creating a user, granting access can be done by modifying the mysql tables directly. However, this method is error-prone and dangerous to the stability of the system and is, therefore, not recommended.

Important dynamics of GRANTing access

Where CREATE USER causes MySQL to add a user account, it does not specify that user's privileges. In order to grant a user privileges, the account of the user granting the privileges must meet two conditions:

  • Be able to exercise those privileges in their account

  • Have the GRANT OPTION privilege on their account

Therefore, it is not just users who have a particular privilege or only users with the GRANT OPTION privilege who can authorize a particular privilege for a user, but only users who meet both requirements.

Further, privileges that are granted do not take effect until the user's first login after the command is issued. Therefore, if the user is logged into the server at the time you grant access, the changes will not take effect immediately.

The GRANT statement in MySQL

The syntax of a GRANT statement is as follows:

GRANT <privileges> ON <database>.<table>
TO '<userid>'@'<hostname>';

Proceeding from the end of the statement, the userid and hostname follow the same pattern as with the CREATE USER statement. Therefore, if a user is created with a hostname specified as localhost and you grant access to that user with a hostname of '%', they will encounter a 1044 error stating access is denied.

The database and table values must be specified individually or collectively. This allows us to customize access to individual tables as necessary. For example, to specify access to the city table of the world database, we would use world.city.

In many instances, however, you are likely to grant the same access to a user for all tables of a database. To do this, we use the universal quantifier ('*'). So to specify all tables in the world database, we would use world.*.

We can apply the asterisk to the database field as well. To specify all databases and all tables, we can use *.*. MySQL also recognizes the shorthand * for this.

Finally, the privileges can be singular or a series of comma-separated values. If, for example, you want a user to only be able to read from a database, you would grant them only the SELECT privilege. For many users and applications, reading and writing is necessary but no ability to modify the database structure is warranted. In such cases, we can grant the user account both SELECT and INSERT privileges with SELECT, INSERT.

To learn which privileges have been granted to the user account you are using, use the statement SHOW GRANTS FOR <user>@hostname>;.


With this in mind, if we wanted to grant a user tempo all access to all tables in the music database but only when accessing the server locally, we would use this statement:

GRANT ALL PRIVILEGES ON music.* TO 'tempo'@'localhost';

Similarly, if we wanted to restrict access to reading and writing when logging in remotely, we would change the above statement to read:

GRANT SELECT,INSERT ON music.* TO 'tempo'@'%';

If we wanted user conductor to have complete access to everything when logged in locally, we would use:

GRANT ALL PRIVILEGES ON * TO 'conductor'@'localhost';

Building on the second example statement, we can further specify the exact privileges we want on the columns of a table by including the column numbers in parentheses after each privilege. Hence, if we want tempo to be able to read from columns 3 and 4 but only write to column 4 of the sheets table in the music database, we would use this command:

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%';

Note that specifying columnar privileges is only available when specifying a single database table&mdash;use of the asterisk as a universal quantifier is not allowed. Further, this syntax is allowed only for three types of privileges: SELECT, INSERT, and UPDATE.

A list of privileges that are available through MySQL is reflected in the following table:

Privilege Function Context
ALL Grants all privileges to user Databases, tables, or indexes
CREATE Creates database objects Databases, tables, or indexes
DROP Drops database objects Databases or tables
GRANT OPTION Grants privileges to other users Databases, tables, or stored routines
REFERENCES Supported internally but otherwise unused Databases or tables
ALTER Allows use of ALTER TABLE Tables
   
DELETE Allows use of DELETE Tables
INDEX Enables creation and dropping of indexes Tables
INSERT Allows data insertion Tables
SELECT Allows reading from a database Tables
UPDATE Allows use of UPDATE Tables
CREATE TEMPORARY TABLES Allows user to create temporary tables Tables
LOCK TABLES Enables the use of LOCK TABLES for tables on which SELECT has been granted Tables
TRIGGER Allows the automation of certain events in a table under conditions set by the user Tables
CREATE VIEW Enables the creation and deletion of views Views
SHOW VIEW Allows the showing of views Views
ALTER ROUTINE Allows user to alter and delete stored routines Stored routines
CREATE ROUTINE Enables the creation of stored routines Stored routines
EXECUTE Allows the execution of stored routines Stored routines
FILE Enables file access on localhost File access
CREATE USER Enables the creation of users Server administration
PROCESS Enables the user to view all processes with SHOW PROCESSLIST Server administration
RELOAD Enables use of FLUSH Server administration
REPLICATION CLIENT Allows user to query about master and slave servers Server administration
REPLICATION SLAVE Allows slave servers to read binary logs from the master server Server administration
SHOW DATABASES Allows user to view available databases Server administration
SHUTDOWN Enables the use of mysqladmin shutdown Server administration
SUPER Enables the use of several superuser privileges Server administration
ALL [PRIVILEGES] Grants all privileges to the user that are available to the grantor Server administration
USAGE Allows access to the user Server administration

MySQL does not support the standard SQL UNDER privilege and does not support the use of TRIGGER until MySQL 5.1.6.

More information on MySQL privileges can be found at http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html

Using REQUIREments of access

Using GRANT with a REQUIRE clause causes MySQL to use SSL encryption.

The standard used by MySQL for SSL is the X.509 standard of the International Telecommunication Union's (ITU) Standardization Sector (ITU-T). It is a commonly used public-key encryption standard for single sign-on systems. Parts of the standard are no longer in force. You can read about the parts which still apply on the ITU website at http://www.itu.int/rec/T-REC-X.509/en

The REQUIRE clause takes the following arguments with their respective meanings and follows the format of their respective examples:

  • NONE: The user account has no requirement for an SSL connection. This is the default.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%';

  • SSL: The client must use an SSL-encrypted connection to log in. In most MySQL clients, this is satisfied by using the --ssl-ca option at the time of login. Specifying the key or certificate is optional.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE SSL;


					  

  • X509: The client must use SSL to login. Further, the certificate must be verifiable with one of the CA vendors. This option further requires the client to use the --ssl-ca option as well as specifying the key and certificate using --ssl-key and --ssl-cert, respectively.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE X509;


					  

  • CIPHER: Specifies the type and order of ciphers to be used.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE CIPHER 'RSA-EDH-CBC3-DES-SHA';


					  

  • ISSUER: Specifies the issuer from whom the certificate used by the client is to come. The user will not be able to login without a certificate from that issuer.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE ISSUER 'C=ZA, ST=Western Cape,
 L=Cape Town, O=Thawte Consulting cc, OU=Certification Services Division,CN=Thawte Server CA/emailAddress=server-certs@thawte.com';

  • SUBJECT: Specifies the subject contained in the certificate that is valid for that user. The use of a certificate containing any other subject is disallowed.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE SUBJECT 'C=US, ST=California, 
L=Pasadena, O=Indiana Grones, OU=Raiders, CN=www.lostarks.com/emailAddress=indy@lostarks.com';

Using a WITH clause

MySQL's WITH clause is helpful in limiting the resources assigned to a user. WITH takes the following options:

  • GRANT OPTION: Allows the user to provide other users of any privilege that they have been granted

  • MAX_QUERIES_PER_HOUR: Caps the number of queries that the account is allowed to request in one hour

  • MAX_UPDATES_PER_HOUR: Limits how frequently the user is allowed to issue UPDATE statements to the database

  • MAX_CONNECTIONS_PER_HOUR: Limits the number of logins that a user is allowed to make in one hour

  • MAX_USER_CONNECTIONS: Caps the number of simultaneous connections that the user can make at one time

It is important to note that the GRANT OPTION argument to WITH has a timeless aspect. It does not statically apply to the privileges that the user has just at the time of issuance, but if left in effect, applies to any options the user has at any point in time. So, if the user is granted the GRANT OPTION for a temporary period, but the option is never removed, then the user grows in responsibilities and privileges, that user can grant those privileges to any other user. Therefore, one must remove the GRANT OPTION when it is not longer appropriate.

Note also that if a user with access to a particular MySQL database has the ALTER privilege and is then granted the GRANT OPTION privilege, that user can then grant ALTER privileges to a user who has access to the mysql database, thus circumventing the administrative privileges otherwise needed.


The WITH clause follows all other options given in a GRANT statement. So, to grant user tempo the GRANT OPTION, we would use the following statement:

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' WITH GRANT OPTION;


					  

If we want to limit the number of queries that the user can have in one hour to five, as well, we simply add to the argument of the single WITH statement. We do not need to use WITH a second time.

GRANT SELECT,INSERT ON music.sheets TO 'tempo'@'%' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 5;					  
Other  
 
Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer
Video
programming4us
 
 
programming4us