-------------------------------------------------------------------
$Id: howto-mysql-common-tasks.txt,v 1.2 2002/09/14 16:23:13 cvsjoko Exp $

$Log: howto-mysql-common-tasks.txt,v $
Revision 1.2  2002/09/14 16:23:13  cvsjoko
no message

Revision 1.1  2002/02/14 00:21:11  cvsjoko
+ new

-------------------------------------------------------------------


=========================================================
    establish basic safety privileges
=========================================================

- we assume a running mysql-daemon, 
   else start it via "safe_mysqld" or (newer versions) with "mysqld_safe" in the mysql/bin/ - directory
- run mysql-client: [root@host]# mysql (you should be logged in as "root")
- select database: mysql> use mysql;

- deny access for user "root" from the outer space (not localhost)
  mysql> delete from user where user='root' and host='%';
  mysql> flush privileges;
- assure everything is all right and you will be able to re-login again later ;)
  mysql> select * from user;
  --> there should be (as a minimum) an entry like ...
  --- snip ---
  | localhost | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          |
  --- snip ---
  ... which gives access-rights for user "root" at localhost with no password
- test for safety
  mysql> select * from user;
  --> there should be no other "root"-entry in the "user"-table then the one established above
  
  
=========================================================
    add new databases and grant access to them for new mysql-user-accounts
=========================================================

- we assume a running mysql-daemon, 
   else start it via "safe_mysqld" or (newer versions) with "mysqld_safe" in the mysql/bin/ - directory
- run mysql-client: [root@host]# mysql (you should be logged in as "root")

- create database
  mysql> create database <databasename>;
  
- add new user
  mysql> use mysql;
  mysql> insert into user (host, user, password) values ('localhost', '<username>', password('<password>'));

- grant access for local usage (e.g. from php- or cgi-scripts)
  mysql> use mysql;
  mysql> insert into db values ('localhost', '<databasename>', '<username>', 'Y','Y','Y','Y','Y','Y','N','Y','Y','Y');

- apply changes to privileges
  mysql> flush privileges;

- test access to new database
  - logout from mysql in "root"-mode
  - login to mysql-daemon as newly created user using the new database
    mysql> mysql -u<username> -p<password> <databasename>
        or leave "<password>" empty for supplying the password invisible  ;)
  - this should work!
  
- TODO:
  - there should be a (perl-)script (perhaps there is already one coming with mysql) 
     to pass "databasename", "username" and "password" to, 
     which should execute the tasks described above non-interactively  ;)
     - note: use regression-checks
       - don't re-create database
       - if user already exists, just modify password!!!
       - don't accept empty arguments!
     - note: integrate into the gsn-framework (on the long term)
       - user "service" should be able to access the mysqld-daemon with root-privileges from a remote location
         in order to be able to absolve all requests seamlessly without any user interaction
         
    
=========================================================
    add a "root-warrior" account
=========================================================
- this user should be able to to anything from remote side

  mysql> use mysql;
  mysql> insert into user values ('%', 'patman_warrior', password('pw'), 'Y','Y','Y','Y','Y','Y','N','N','N','N','N','N','N','Y');
  mysql> flush privileges;