PHP —

Adding and Deleting Users

We have not yet discussed adding and deleting users from the MySQL DBMS. Our rationale in leaving this topic until this final section is that DBMS users aren't as important in a web database application as in other applications. Because access to the database and DBMS is generally controlled in the application logic of the middle tier, usually only one or two DBMS users are needed.

A user, Alexa, who has full control over all aspects of the DBMS and can access the DBMS from the machine that hosts the DBMS, can be created with the statement:

GRANT ALL PRIVILEGES ON *.* TO Alexa@localhost
  IDENTIFIED BY 'password' WITH GRANT OPTION;

Allowing access over a network can be added with:

GRANT ALL PRIVILEGES ON *.* TO Alexa@"%"
  IDENTIFIED BY 'password' WITH GRANT OPTION;

There is no need to allow network access for a web database application if the middle-tier components-the web server and scripting engine-are installed on the same machine as the DBMS.

This user can then connect to the database from the shell with the command:

% mysql -ppassword -uAlexa

The user information is stored in the mysql database in the user table, which can be explored with:

USE mysql;
SELECT * FROM user;

The mysql database and the user table can be managed in the same way as any other database. For example, you can update the password of the new user with the UPDATE statement:

UPDATE user
  SET password=password('newpwd')
  WHERE user='Alexa';

Note the use of the password( ) function we described earlier to encrypt the password for storage in the user table.