1 / 17

Mass user creation

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: 1) create a text file with many lines similar as the next example:

brooks
Télécharger la présentation

Mass user creation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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: 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 ver. 4

  2. 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.

  3. The next file has been created using Excel.If each part of command is in a cell, in text export it will beseparated by tabs (for MySQL, it is O.K.). Example of the file: MySQL ver. 4

  4. For import of this file, lunch (execute) theMySQL Command Line Client. In this console, script can be importedby 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 olderCtrl+Insert and Shift+Insert instead of this.

  5. The result of executing file with three pairs:

  6. 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.

  7. Example of the file created by Excel: After executing, we can try to use the selectedaccount ('kral') to explore databases...

  8. Don't forget to leave the "Database" empty when logging into, in other cases you can explore just this database:

  9. 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 passwordfor 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” .)

  10. Micka after root by localhost connection:

  11. All the maintenance can be done from the command lineclient. Note the last two commands("use" will open the database, then we can use "select"):

  12. The result is little unreadable (passwords have been masked):

  13. 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 userdefinition, the second command will change the password.Note, that passwords are in the cache – to change this, you have tofree all caches to force them to reload (the last line): USE mysql; UPDATE user SET Password = PASSWORD('newpwd') WHERE User = 'kral'; FLUSH PRIVILEGES;

More Related