Creating MySQL Users
MySQL users can be created only by the MySQL administrator i.e. the root user.
Open a DOS Window as described earlier and login into MySQL as root with the appropriate password. As soon as the system prompt changes to mysql> you are ready to create other MySQL users as shown in diagram 1.
Diagram 1: Logging to MySQL as root
Normally, at creation time, a user can be bound to a specific MySQL database. Access to any other MySQL database other than the one to which the user was bound at creation time is prevented by the MySQL db engine. This is for over all security purposes.
NOTE: In order to grant all permissions to a user on single database, you have to login as root and select the database to activate it, by using the USE <databasename> command.
NOTE: If it is so required a single user can be granted permission to access and use the resources contained in multiple MySQL databases as well. We will take a look at this shortly.
The following is the syntax for activate a database i.e. get the MySQL db engine to focus its attention on the database.
The syntax for creating a user with a password, then binding that user to a specific MySQL database is described in the lesson.
The following is the syntax for to create the new user with the password.
GRANT ALL ON <databasename> to <username>@localhost identified by “password”;
GRANT ALL ON SMS to prerna@localhost identified by “@prerna12#“;
Diagram 2: Using the database and then granting permission to the User to access database
NOTE: It’s absolutely important to remember to enclose the password within single quotes as described above.
The above command creates a user named: prerna
(NOTE: user names are case sensitive).
The user prerna is identified by the password: @prerna12#
(NOTE: Using special characters and numbers in a password is recommended to make the password strong).
The user prerna can only log into the MySQL database: SMS
The user prerna must be on the local computer when attempting to log into the SMS database.
This is enforced by the attribute prerna@localhost. If the user prerna attempts to log into the SMS database from any remote computer on the network, the MySQL db engine will prevent this login.
A lot of this is largely used for security purposes to help prevent the MySQL data from being compromised.