Creating User Accounts with GRANT and REVOKE


Creating User Accounts with GRANT and REVOKE

User privileges are given with the GRANT statement and are taken away with the REVOKE statement. These are standard SQL statements that you can execute like any other statement in this book. All MySQL user information and user privileges are eventually stored in a MySQL database, just like your own applications.

To run the statements in this section, you will yourself need to have a certain level of privilege. If you installed MySQL, you will have access to the root account and, therefore, will have the appropriate level of privilege. If you are using MySQL on a machine controlled by somebody else (such as at work or at an ISP), you may not have the appropriate privilege level to run these queries. If you do not, you will receive an error message along these lines:

 
 ERROR 1045: Access denied for user: 'laura@127.0.0.1' (Using password: YES) 

Granting Privileges

We'll begin by looking at the GRANT statement. This statement is used to create user accounts and give users access to databases, tables, and functions. Let's look at a simple example first, as follows :

 
 grant usage on * to luke@localhost identified by 'password'; 

This statement creates an account for the user called luke when he is trying to log in from localhost . It sets up a password for him (the highly secure password password ”obviously, you should use something better!). The word usage indicates the privilege we are giving to luke . It means that he can log in, but not do anything else. The ON clause is used to specify what things we are granting privileges on. Because we are only granting the ability to log in in this case, the ON clause is not really relevant here.

The general form of the GRANT statement from the MySQL manual is

 
 GRANT  priv_type  [(  column_list)]  [,  priv_type  [(  column_list  )] ...] ON {  tbl_name  *  *.*  db_name  .*} TO user_name [IDENTIFIED BY [PASSWORD] '  password  ']     [,  user_name  [IDENTIFIED BY '  password  '] ...] [REQUIRE     NONE      [{SSL X509}]     [CIPHER  cipher  [AND]]     [ISSUER  issuer  [AND]]     [SUBJECT  subject  ]] [WITH [GRANT OPTION  MAX_QUERIES_PER_HOUR  #  MAX_UPDATES_PER_HOUR  #  MAX_CONNECTIONS_PER_HOUR  #  ]] 

The GRANT clause lists the privileges we are granting. We will look at what these are in the next section. Some privileges are global (that is, they apply across all databases), and some apply only to certain items (databases, tables, or columns ).

The ON clause specifies the items we are granting privileges on. This can be a named table or a named database with all its tables ( dbname.* ). We can also specify *.* , which means all databases and all tables. If we specify * , the privileges are granted on the currently selected database. If no database is selected, the privileges are granted as if we had specified *.* in this clause.

The TO clause is used to specify the user we are granting privileges to. If this user already has an account, the new privileges will be added to it. If not, an account will be created for the user. We can specify more than one user in this clause. We can also specify the hosts they can log in from, for example, fred@localhost . If you are having trouble logging in as a newly created user, try adding the hostname you are logging in from to the GRANT statement. The MySQL username need not be the same as a user's operating system username. Usernames can be up to 16 characters long.

The IDENTIFIED BY clause sets the password for a new user or resets it for an existing user.

Users can change their passwords by typing

 
 set password = password('newpassword'); 

You can change a user's password by typing, for example,

 
 set password for fred@localhost = password('newpassword'); 

You need to have access to the database called mysql to do this.

The clause WITH GRANT OPTION is a special privilege that allows the user to grant privileges. If you found that you could not grant any privileges to users, this is the privilege you yourself were missing. Also, you may not grant a privilege to other users that you do not have yourself.

The WITH clause can also be used to limit the number of queries, updates, or connections that a user can make in an hour . The default value for these is zero, meaning no limitation.

The REQUIRE clause allows you specify that a user must connect using a secure connection. To use this, you will need to configure MySQL appropriately. We will discuss this further in Chapter 15, "Securing Your MySQL Installation."



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net