Multiple MySQL users :)
- Thread starter ksv
- Start date
michaelsanford
Translator, Web Developer
I'm sure there's a way to do this with mysqladmin, but I just do it manually (I have tonnes of extra users):
[helios:~]% mysql -uroot -p mysql
Start MySQL engine as root user, open into the mysql table. If your user has privileges to write to the mysql table, you don't have to start as MySQL's root user.
mysql> SELECT * FROM user\G
Show the current user table, and all the necessary fields you'll have to fill (printed vertically - \G). Here's my default user (password/username removed for security).
mysql> INSERT INTO user VALUES ('localhost', 'username', password("password_string"), 'Y', 'Y', etc...);
Insert a new record into the table. If you want the user not to have to enter a password, just use a null field "", otherwise use password("string") for that field. The rest of the fields are enumerated Y/N privileges for doing things; the field names are pretty intuitive.
mysql> quit
Exit MySQL.
[helios:~]% mysqladmin reload
Reload the MySQL grant tables for the new user to take affect. You'll have to do this after any change you make to the table, not just adding a user, but changing a user's privileges as well.
Then you can use GRANT within MySQL to change the user's preferences for certain tables.
GRANT privilege [(column, ...)] [, privilege] [(column)] ON {table} TO user [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password'] ...] [WITH GRANT OPTION] (That's from O'Reilly's MySQL & mSQL; Yarger, Reese, & King).
That's it, I promise it's less complicated than it looks
[helios:~]% mysql -uroot -p mysql
Start MySQL engine as root user, open into the mysql table. If your user has privileges to write to the mysql table, you don't have to start as MySQL's root user.
mysql> SELECT * FROM user\G
Show the current user table, and all the necessary fields you'll have to fill (printed vertically - \G). Here's my default user (password/username removed for security).
Code:
*************************** 3. row ***************************
Host: localhost
User: name
Password: pass
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: YInsert a new record into the table. If you want the user not to have to enter a password, just use a null field "", otherwise use password("string") for that field. The rest of the fields are enumerated Y/N privileges for doing things; the field names are pretty intuitive.
mysql> quit
Exit MySQL.
[helios:~]% mysqladmin reload
Reload the MySQL grant tables for the new user to take affect. You'll have to do this after any change you make to the table, not just adding a user, but changing a user's privileges as well.
Then you can use GRANT within MySQL to change the user's preferences for certain tables.
GRANT privilege [(column, ...)] [, privilege] [(column)] ON {table} TO user [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password'] ...] [WITH GRANT OPTION] (That's from O'Reilly's MySQL & mSQL; Yarger, Reese, & King).
That's it, I promise it's less complicated than it looks
michaelsanford
Translator, Web Developer
PS I think to let them access from any host you can use either a "*" or a "" in the Host: field, not sure which one though.
They'd only need access from another host if you want to allow them to use a GUI client to access their DBs remotely, otherwise if they're only running scripts on your server, leave it at localhost.
They'd only need access from another host if you want to allow them to use a GUI client to access their DBs remotely, otherwise if they're only running scripts on your server, leave it at localhost.
macmasta
Reiswaffel
phpMyAdmin would be the easier way. its also pretty easy to install...
but you need php installed for that which you can also install easily with the following instructions:
http://www.entropy.ch/software/macosx/php/
once php installed & running, grab phpMyAdmin
http://prdownloads.sourceforge.net/phpmyadmin/phpMyAdmin-2.4.0-php.tar.gz
and follow the provided instructions..
have 'fun'
but you need php installed for that which you can also install easily with the following instructions:
http://www.entropy.ch/software/macosx/php/
once php installed & running, grab phpMyAdmin
http://prdownloads.sourceforge.net/phpmyadmin/phpMyAdmin-2.4.0-php.tar.gz
and follow the provided instructions..
have 'fun'
ksv
web developer
You're a god, michael
I've got a problem, though. The mysql database didn't seem to exist (no idea why it was not there), so I recreated it. How do I recreate the required tables in the database to make this work?
Also, can I create databases that are only editable by their owners?
I've got a problem, though. The mysql database didn't seem to exist (no idea why it was not there), so I recreated it. How do I recreate the required tables in the database to make this work?
Also, can I create databases that are only editable by their owners?
michaelsanford
Translator, Web Developer
Hehe no problem ksv
Issues:
1) That information is for MySQL build 3.23 and is not applicable to MySQL 4 (the user's table has some added coumns).
2) You don't have a database called mysql? That's very strange, because that's where all the users (like root) are kept. It should look like this
If you don't have that database, I would suggest re-installing MySQL--something may have gone wrong during installation. If you've got lots of data already (somehow) in databases, just mysqldump them.
And yes you can create tables and grant whatever kind of access you want to whatever user you want.
Example:
mysql> GRANT ALL ON mysql TO ksv;
Will give you unrestricted acess to the mysql table.
mysql> GRANT USAGE ON mytable TO ksvs_little_sister;
Will grant your little sister no access at all to mytable. (I don't think "usage" is aptly named for a privilege that implies no usage at all... ).
This is the new user table definition:
This is my account, and is identical to root. Now that I've just shown you this, I still think it would be a good idea to re-install MySQL because if you don't have that database you're going to have problems later. For all I know the mysql databse might have a special hidden privilege that allows it to read user's information (preventing you spoofing it to another DB) for example.
Also what version and installer are you using?
Issues:
1) That information is for MySQL build 3.23 and is not applicable to MySQL 4 (the user's table has some added coumns).
2) You don't have a database called mysql? That's very strange, because that's where all the users (like root) are kept. It should look like this
Code:
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)If you don't have that database, I would suggest re-installing MySQL--something may have gone wrong during installation. If you've got lots of data already (somehow) in databases, just mysqldump them.
And yes you can create tables and grant whatever kind of access you want to whatever user you want.
Example:
mysql> GRANT ALL ON mysql TO ksv;
Will give you unrestricted acess to the mysql table.
mysql> GRANT USAGE ON mytable TO ksvs_little_sister;
Will grant your little sister no access at all to mytable. (I don't think "usage" is aptly named for a privilege that implies no usage at all...
).This is the new user table definition:
Code:
*************************** 4. row ***************************
Host: localhost
User: my_darwin_username
Password: my_password
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0Also what version and installer are you using?
michaelsanford
Translator, Web Developer
PS about grant, databases and tables aren't like files in linux, they don't record an owner.
So if you wanted to ungrant yourself access from your own database, you could.
A few more flags:
mysql> GRANT ALL ON xxx TO ksv;
Where xxx is:
1) table_name (table table_name in current database)
2) * (all tables in the current database)
3) *.* (all tables in all databases)
4) database_name.* (all tables in database database_name)
So if you wanted to ungrant yourself access from your own database, you could.
A few more flags:
mysql> GRANT ALL ON xxx TO ksv;
Where xxx is:
1) table_name (table table_name in current database)
2) * (all tables in the current database)
3) *.* (all tables in all databases)
4) database_name.* (all tables in database database_name)
michaelsanford
Translator, Web Developer
You can back up your tables in two ways (I suggest using both, just in case).
First Way (the standard way)
At the command prompt type:
[amras:~/Desktop]% sudo mysqldump -c -v --all-databases > dump.sql
... or ...
[amras:~/Desktop]% sudo mysqldump -c -v database_name [table_names] > dump.sql
Without the redirection operator it will dump it to stdout--the screen. Now pico dump.sql and have a look through it to make sure everything you need is there. You should see some CREATE DATABASE, CREATE TABLE, and INSERT INTO table VALUES statements, to compeletely re-create your database structure and data.
Then to restore the data:
[amras:~/Desktop]% mysql -u root -p < dump.sql
You will need to use the mysql root user to reload data unless you have already configured your local user to have privileges to create databases and stuff...
Also, make sure that if you did a dump with --all-databases you didn't also dump a blank "mysql" database which will overwrite your new properly configured one
Second Way (not 100% sure about this one)
Note: You'll have to "sudo -s" to get access to these folders.
Your databases are stored in /usr/local/mysql/data/database_name/tables[.MYD][.MYI][.frm].
Just copy the files out of the data/ folder to somewhere else, and then copy them back. I'm 90% sure this way will work, the 10% discrepancy is because I'm not certain that there isn't some file that has to be updated to point to the database files, or if MySQL just reads all the appropriate files from that folder.
NOTE: If the first way works properly to re-create your data, don't restore with the second method!
You might also consider upgrading to MySQL 4, thay've now released native binaries for OS X/Darwin (no more entropy.ch):
http://www.mysql.com/downloads/mysql-4.0.html
First Way (the standard way)
At the command prompt type:
[amras:~/Desktop]% sudo mysqldump -c -v --all-databases > dump.sql
... or ...
[amras:~/Desktop]% sudo mysqldump -c -v database_name [table_names] > dump.sql
Without the redirection operator it will dump it to stdout--the screen. Now pico dump.sql and have a look through it to make sure everything you need is there. You should see some CREATE DATABASE, CREATE TABLE, and INSERT INTO table VALUES statements, to compeletely re-create your database structure and data.
Then to restore the data:
[amras:~/Desktop]% mysql -u root -p < dump.sql
You will need to use the mysql root user to reload data unless you have already configured your local user to have privileges to create databases and stuff...
Also, make sure that if you did a dump with --all-databases you didn't also dump a blank "mysql" database which will overwrite your new properly configured one
Second Way (not 100% sure about this one)
Note: You'll have to "sudo -s" to get access to these folders.
Your databases are stored in /usr/local/mysql/data/database_name/tables[.MYD][.MYI][.frm].
Just copy the files out of the data/ folder to somewhere else, and then copy them back. I'm 90% sure this way will work, the 10% discrepancy is because I'm not certain that there isn't some file that has to be updated to point to the database files, or if MySQL just reads all the appropriate files from that folder.
NOTE: If the first way works properly to re-create your data, don't restore with the second method!
You might also consider upgrading to MySQL 4, thay've now released native binaries for OS X/Darwin (no more entropy.ch):
http://www.mysql.com/downloads/mysql-4.0.html