Creating New SQL Users






Creating New SQL Users

In Section 1.4, we described the concept of a user. We also mentioned briefly the respective roles of users and applications. A user starts up an application. This application passes SQL statements to the database server that processes them. These SQL statements can be entered "live" (interactive SQL) by a user or can be included in the application code (preprogrammed SQL).

Here, a clear distinction should be made between the real, human user and the username that he uses to log on. To avoid confusion, we call the latter the SQL user.

SQL users can be granted privileges. A privilege is a specification indicating what a certain SQL user can or cannot do. For example, one user might be allowed to create new tables, another might be authorized to update existing tables, and a third might be able to only query tables.

The relationship between human users and SQL users can be one-to-one, but that is not required. A human user is allowed to log on under different SQL usernamesand every time he will have other privileges. Additionally, an entire group of human users is allowed to use the same SQL username. They all have the same privileges. Therefore, the relationship between users and SQL users is a many-to-many relationship. You have to decide how you will arrange all this.

So, to log on, you need to have an SQL user. Several SQL users have already been created during the installation procedure, to prevent the chicken-and-egg problem. Therefore, you do not have to create one. However, if you want to create your own SQL users, you can do that with a special SQL statement.

Imagine that you log on with the SQL user called root. Next, you can use the CREATE USER statement to create your own, new SQL users. With this, you assign a new SQL user a name and a password.

1. Introduce a new SQL user called BOOKSQL with the password BOOKSQLPW.

CREATE USER 'BOOKSQL'@'localhost' IDENTIFIED BY 'BOOKSQLPW'

Explanation With the specification 'BOOKSQL'@'localhost', the name of the new SQL user is created. What localhost exactly means is explained in another chapter. The statement ends with the password, which, in this case, is BOOKSQLPW. Be sure that the username, the term localhost, and the password are placed between single quotation marks.

Portability

The CREATE USER statement is not supported by all SQL products. Some use the GRANT statement to create new users. Additionally, for products that do support the CREATE USER statement, differences in syntax exist.


When an application logs on to a database server with an SQL username, a so-called connection starts. A connection should be seen as a unique link between the application and a specific database for the concerned SQL user. This link consists of two parts. The first part bonds the application to the database server; the second bonds the database server to the database. Therefore, when the application logs on, it is connected to the specified database at one time. And what can be sent over that connection is determined by the privileges of the SQL user.

A new SQL user is allowed to log on, but he does not have any other privileges yet. We have to grant those privileges to BOOKSQL first with the GRANT statement.

The features of the GRANT statement are extensive. This statement and related topics are discussed in detail in Chapter 23, "Users and Data Security." However, to put you on your way, the next example contains the statement by which the new SQL user called BOOKSQL is granted enough privileges to create tables and manipulate them afterward.

2. Give the SQL user BOOKSQL the privileges to create and manipulate tables.

GRANT ALL PRIVILEGES
ON    *.*
TO    'BOOKSQL'@'localhost'
WITH  GRANT OPTION

BOOKSQL can now log on and execute all the statements in the following chapters.

Note

In the rest of the book, we assume that you log on as user BOOKSQL with the password BOOKSQLPW and that this user has sufficient privileges.


Portability

The GRANT statement is supported by most SQL products. However, the syntax to grant users sufficient privileges to create tables looks very different in different products. The GRANT statement just shown, therefore, is specific to MySQL. For example, in DB2, a comparable statement would look like this:

GRANT DBADM
ON    DATABASE
TO    BOOKSQL




 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows