# Multiple MySQL users :)



## ksv (Mar 4, 2003)

I have MySQL installed and running on an OS X Server, and need to have multiple MySQL users. I don't know that stuff, though 

Anyone?


----------



## michaelsanford (Mar 17, 2003)

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



```
*************************** 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: Y
```
*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


----------



## michaelsanford (Mar 17, 2003)

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.


----------



## macmasta (Mar 23, 2003)

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'


----------



## ksv (Mar 29, 2003)

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?


----------



## michaelsanford (Mar 29, 2003)

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

```
+-----------------+
| 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:

```
*************************** 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: 0
```
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?


----------



## michaelsanford (Mar 29, 2003)

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_)


----------



## ksv (Mar 29, 2003)

Thanks a lot 
I think it's 3.23, installed using the neat little app called "MySQL Manager" in Mac OS X Server.
Yep, I've got a database I'd like to keep. How exactly do I backup and restore it again after reinstalling?


----------



## michaelsanford (Mar 29, 2003)

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


----------

