 |
|
|
User:goeko > Database/SQL > MySQL > User Managment
User ManagmentSetting the administrator passwordSet the admin password from the root account command line, in linux of course. (replace "my_password", with the password) mysql -u root -e "set password = password('my_password');"
Creating a User by altering the mysql.user tableMySQL database users table (I think this ia a pretty old version, newer versions have more table) +-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
01| Host | char(60) | NO | PRI | NULL | |
02| User | char(16) | NO | PRI | NULL | |
03| Password | char(41) | NO | | NULL | |
04| Select_priv | enum('N','Y') | NO | | N | |
05| Insert_priv | enum('N','Y') | NO | | N | |
06| Update_priv | enum('N','Y') | NO | | N | |
07| Delete_priv | enum('N','Y') | NO | | N | |
08| Create_priv | enum('N','Y') | NO | | N | |
09| Drop_priv | enum('N','Y') | NO | | N | |
10| Reload_priv | enum('N','Y') | NO | | N | |
11| Shutdown_priv | enum('N','Y') | NO | | N | |
12| Process_priv | enum('N','Y') | NO | | N | |
13| File_priv | enum('N','Y') | NO | | N | |
14| Grant_priv | enum('N','Y') | NO | | N | |
15| References_priv | enum('N','Y') | NO | | N | |
16| Index_priv | enum('N','Y') | NO | | N | |
17| Alter_priv | enum('N','Y') | NO | | N | |
18| Show_db_priv | enum('N','Y') | NO | | N | |
19| Super_priv | enum('N','Y') | NO | | N | |
20| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
21| Lock_tables_priv | enum('N','Y') | NO | | N | |
22| Execute_priv | enum('N','Y') | NO | | N | |
23| Repl_slave_priv | enum('N','Y') | NO | | N | |
24| Repl_client_priv | enum('N','Y') | NO | | N | |
25| Create_view_priv | enum('N','Y') | NO | | N | |
26| Show_view_priv | enum('N','Y') | NO | | N | |
27| Create_routine_priv | enum('N','Y') | NO | | N | |
28| Alter_routine_priv | enum('N','Y') | NO | | N | |
29| Create_user_priv | enum('N','Y') | NO | | N | |
30| ssl_type | enum(,'ANY','X509','SPECIFIED') | NO | | NULL | |
31| ssl_cipher | blob | NO | | NULL | |
32| x509_issuer | blob | NO | | NULL | |
33| x509_subject | blob | NO | | NULL | |
34| max_questions | int(11) unsigned | NO | | 0 | |
35| max_updates | int(11) unsigned | NO | | 0 | |
36| max_connections | int(11) unsigned | NO | | 0 | |
37| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
mysql> INSERT INTO user
-> VALUES('%','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','dummy',);mysql> FLUSH PRIVILEGES;
UPDATE user SET password=PASSWORD('something') WHERE user = 'monty';
Grant user Privilegesmysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';
Can use grant command to create user.GRANT ALL PRIVILEGES ON phpbbdb.* TO 'phpbb'@'localhost' IDENTIFIED BY 'Wha7Scrt4';
MySQL 4.1 How to create a user link http://dev.mysql.com/doc/refman/4.1/en/adding-users.html
An example user add. (adding users who can just see values). mysql> insert into user (Host,User,Password) VALUES ( '10.16.10.136', 'metro', 'password '); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> update user set Select_Priv='y' where user='metro'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
*NOTE* with version 5.0 use to the PASSWORD() function when adding a password to a user. To find this out I only saw an error when the database server started, and it put a message in the log. *NOTE* with version 5.0 I also had to use the GRANT command to allow access to the databse. (? Not sure if you must also do this in 4 ? ) Make sure the privileges take effect. flush PRIVILEGES;
Creating a User User the create user command to create the user, then alter the privileges. create user fredsql; update mysql.user set select_priv='Y', insert_priv='y', update_priv='y',delete_priv='y',create_priv='y',alter_priv='y' where user = 'fredsql';
You may also want to set the users password now, use mysql;
update user set password=PASSWORD('ANewSecurePassword') where user='fredsql';
If you have granted all privilages you may want to take these away from a user. revoke CREATE USER, PROCESS, SHUTDOWN, SUPER, GRANT OPTION on *.* from 'fredsql'@'localhost';
Make sure the privileges take effect. flush PRIVILEGES;
Linkshttp://dev.mysql.com/doc/refman/5.0/en/grant.html http://dev.mysql.com/doc/refman/5.0/en/adding-users.html Access Control http://dev.mysql.com/doc/refman/5.0/en/connection-access.html Mysql Grant Syntax http://dev.mysql.com/doc/refman/5.0/en/grant.html Good link about mysql system tables for privlages and users http://www.idevelopment.info/data/MySQL/DBA_tips/Security/SEC_1.shtml Secrets of the MySQL Client Command Line http://www.developer.com/db/article.php/3725901/Secrets-of-the-MySQL-Client-Command-Line.htm Files1
|
||||||||||||||
