Changes between Version 72 and Version 73 of ServerIntro

Timestamp:

Feb 21, 2013, 2:08:53 AM (11 years ago)

Author:

Christian Beer

Comment:

change mysql permission scheme

ServerIntro

@@ -143 +143 @@
 === MySQL notes === #MySQL-notes
  * After installing and running the server,
-   grant permissions for your own account and for the account under
-   which Apache runs ('nobody' in the following;
-   may be different on your machine).
+   grant permissions for the 'db_user' account (usually boincadm).
+   The 'db_name' is usually the short name of the project.
+   The credentials (db_user, db_name, db_passwd) are stored in the project's [ProjectOptions#db config.xml].
    All MySQL accounts should be password protected, especially `root`.
 {{{
 mysql -u root
-grant all on *.* to yourname@localhost identified by 'password';
-grant all on *.* to yourname identified by 'password';
-grant all on *.* to nobody@localhost identified by 'password';
-grant all on *.* to nobody identified by 'password';
-}}}
-
-{{{
-#!comment
-This is a more secure approach but will not work for new projects because make_project tries to create and drop the database on its own. Existing projects should change the permissions accordingly.
-
+CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'db_passwd';
+GRANT CREATE,DROP ON *.* TO 'db_user'@'localhost'
+GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES, LOCK TABLES ON  `db_name`.* TO 'db_user'@'localhost';
+}}}
+Existing projects should change the permissions accordingly by first revoking 
+all rights and grant with the above example. The user 'nobody' means the 
+account under which Apache runs (may be different on your machine) which does 
+not need access anymore.
+{{{
 mysql -u root
-CREATE DATABASE `db_name`;
-CREATE USER 'db_user'@'localhost' IDENTIFIED BY  'db_password';
-GRANT USAGE ON *.* TO 'db_user'@'localhost'
-GRANT ALL PRIVILEGES ON  `db_name`.* TO 'db_user'@'localhost';
-
-mysql -u root
-REVOKE ALL PRIVILEGES ON  *.* FROM  'db_name'@'localhost';
+REVOKE ALL PRIVILEGES ON  *.* FROM  'db_user'@'localhost';
+REVOKE ALL PRIVILEGES ON  *.* FROM  'db_user';
 REVOKE ALL PRIVILEGES ON  *.* FROM  'nobody'@'localhost';
+REVOKE ALL PRIVILEGES ON  *.* FROM  'nobody';
+}}}
+After creating a project you should also revoke the global DROP and CREATE 
+permission for your 'db_user' like this:
+{{{
+REVOKE ALL PRIVILEGES ON *.* FROM 'db_user'@'localhost';
+REVOKE GRANT OPTION ON * . * FROM 'db_user'@'localhost';
+GRANT USAGE ON *.* TO 'db_user'@'localhost';
 }}}
  * Set your `PATH` environment variable to include MySQL programs