Transcript GRANT ALL PRIVILEGES ON alik.* TO `novak`

Mass user creation
On our servers is used the convention, that each of user has only
one database, which has the same name, as the user itself. This
method is very popular.
It can be done by following method:
MySQL ver. 4
1) create a text file with many lines similar as the next example:
CREATE DATABASE alik character set = cp1250 ;
GRANT ALL PRIVILEGES ON alik.* TO 'novak’
IDENTIFIED BY 'autobus' ;
The first line will create database itself.
The second line will grant rights to this database to specified user.
Rights should be separated by comma:
SELECT, INSERT, ALTER, CREATE, DELETE
... or you can use "ALL PRIVILEGES" and then use "REVOKE"
Note: if the user doesn’t exist, it will be created.
As a result of the IDENTIFIED BY keyword, password of this user will
be changed.
MySQL from version 5.0
From this version, you have to use the password function to change
user’s password. As a result, you need three commands per user:
1) create database:
CREATE DATABASE alik character set = cp1250 ;
2) grant rights to non-existing user will create him/her:
GRANT ALL PRIVILEGES ON alik.* TO 'novak’ ;
3) set a password for this user:
SET PASSWORD FOR novak = password('autobus') ;
Instead of ALL PRIVILEGES can be (again) list of rights.
For specific user, you can grant the SELECT right only.
Even for version 4, passwords have been coded, but this has been
done without asking.
Note: uppercase and lowercase can be mixed without any problem.
The next file has been created using Excel.
If each part of command is in a cell, in text export it will be
separated by tabs (for MySQL, it is O.K.).
Example of the file:
MySQL ver. 4
For import of this file, lunch (execute) the
MySQL Command Line Client.
In this console, script can be imported
by use of the "source" command:
mysql> source c:\any_name.txt
This is important to prepare this file in such directory, that
the name including path can be easy written (the desktop
address is very complicated to be written).
Note: in the Windows XP command line prompt, you
cannot use the Ctrl+C and Ctrl+V commands.
Using Linux and the “putty” program, you can use older
Ctrl+Insert and Shift+Insert instead of this.
The result of executing file with three pairs:
If there are demand for supervising, we can simply create similar file,
but the rights to the database will be granted to specific user
(in this case, we don't like to change the user's password):
GRANT ALL PRIVILEGES ON alik.* TO 'kral' ;
GRANT ALL PRIVILEGES ON bart.* TO 'kral' ;
GRANT ALL PRIVILEGES ON haryk.* TO 'kral' ;
GRANT ALL PRIVILEGES ON rex.* TO 'kral' ;
For this purpose, the "SELECT" right will be enough.
Example of the file created by Excel:
After executing, we can try to use the selected
account ('kral') to explore databases...
Don't forget to leave the "Database" empty
when logging into, in other cases you can
explore just this database:
You can use the Micka client for maintaining the database,
if you use this client locally (the address should be
"localhost") with the "root" as user and the password
for the command line client when login (use of the "root"
user is typically limited to use on the localhost connection;
can be allowed in the system configuration files).
From the "user" table you can get a list of users by:
SELECT user.User FROM user;
or from the database list (the "db" table):
SELECT User FROM db;
(assuming database with the name “MySQL” has been
selected; in the command line, type “use mysql” .)
Micka after root by localhost connection:
All the maintenance can be done from the command line
client. Note the last two commands
("use" will open the database, then we can use "select"):
The result is little unreadable (passwords have been masked):
Setting of the user password:
Password is coded, so you have to use the "password" function.
The first line will connect (open) the database containing the user
definition, the second command will change the password.
Note, that passwords are in the cache – to change this, you have to
free all caches to force them to reload (the last line):
USE mysql;
UPDATE user SET Password =
PASSWORD('newpwd')
WHERE User = 'kral';
FLUSH PRIVILEGES;