Once MySQL has been successfully installed, you should immediately set a password for the root user. Until you have done so, anyone can access your databases and have administrative-level privileges. Windows users who followed the steps earlier for installing MySQL can skip this step, since the root user was created when the MySQL configuration wizard was run. Once you've established the root user's password, you can begin establishing the users who will regularly access the database (for example, from PHP scripts). It is very insecure to use the root user for general purposes, so everyone should create some new MySQL users for regular use. Setting the root user passwordThe mysqladmin utility, as the name might imply, is used to perform administrative-level tasks on your database. These include stopping MySQL, setting the root user's password, and more. (Some of the things you can do with mysqladmin can also be accomplished more directly within the mysql client, though.) One of the first uses of mysqladmin is to assign a password to the root user. When MySQL is installed, there is no such value established. This is certainly a security risk that ought to be remedied before you begin to use the server. Just to clarify, your databases can have several users, just as your operating system might. The MySQL users are different from the operating system users, even if they share a common name. Therefore, the MySQL root user is a different entity than the operating system's root user, having different powers and even different passwords (preferably but not necessarily). Most important, understand that the MySQL server must be running for you to use mysqladmin. Again, if you ran the MySQL configuration wizard on Windows, you have already established a root user's password and can skip ahead to the next sequence of steps. To assign a password to the root user
Creating users and privilegesAfter you have MySQL successfully up and running, and after you've established a password for the root user, it's time to begin adding other users. To improve the security of your applications, you should always create new users for accessing your databases, rather than continue to use the root user at all times. The MySQL privileges system was designed to restrict access to only certain commands on specific databases by individual users. This technology is how a Web host, for example, can securely have several users accessing several databases, without concern. Each user within the MySQL system can have specific capabilities on specific databases from specific hosts (computers). The root userthe MySQL root user, not the system'shas the most power and is used for creating subusers, although subusers can be given rootlike powers (inadvisably so). When a user attempts to do something with the MySQL server, MySQL will first check to see if the user has the permission to connect to the server at all (based upon the username, the user's password, and the information in the user table of the mysql database). Second, MySQL will check to see if the user has the permission to run the specific SQL statement on the specific databasesfor example, to select data, insert data, or create a new table. To determine this, MySQL uses the db, host, user, tables_priv, and columns_priv tables, again from the mysql database. Table A.1 lists the various privileges that can be set on a user-by-user basis.
There are a handful of ways to set users and privileges within MySQL, but I prefer to do it manually, using the mysql client and the GRANT command. The syntax goes like this: GRANT privileges ON database.* TO password' For the privileges aspect of this statement, you can list specific privileges from the list in Table A.1, or you can allow for all of them using ALL (which is not prudent). The database.* part of the statement specifies which database and tables the user can work on. You can name specific tables using the database.tablename syntax or allow for every database with *.* (again, not prudent). Finally, you can specify the username and a password. The username has a maximum length of 16 characters. When creating a username, be sure to avoid spaces (use the underscore instead) and note that usernames are case-sensitive. The password has no length limit but is also case-sensitive. The passwords will be encrypted within the mysql database, meaning they cannot be recovered in a plain text format. Omitting the IDENTIFIED BY 'password' clause results in that user not being required to enter a password (which, once again, should be avoided). Finally, there is the option of limiting users to particular hostnames. The hostname is either the name of the computer on which the MySQL server is running (localhost being the most common value here) or the name of the computer from which the user will be accessing the server. This can even be an IP address, should you choose. To specify a particular host, change your statement to GRANT privileges ON database.* TO hostname IDENTIFIED BY 'To allow for any host, use the hostname wildcard character (%).GRANT privileges ON database.* TO password' |