Issues setting privileges in createDatabase.py

[organisciak]

I'm not entirely clear why bookworm/CreateDatabase.py tries to GRANT SELECT in grantPrivileges(): shouldn't users have already set the proper privileges before running the scripts? This could be unexpected.

When I was setting up the example, it also tried to use the admin account to GRANT SELECT on itself, which would cause a bug. I didn't diagnose too closely, just routed around the whole method and everything worked.

[bmschmidt]

Will look into it. Proper behavior is for admin to have all privileges, but then a web only user to be granted select only at the end. Since we're not transactional n queries, the idea is provide another bulwark against SQL injection and human error by having two users.

On Monday, January 19, 2015, Peter Organisciak notifications@github.com wrote:

I'm not entirely clear why bookworm/CreateDatabase.py tries to GRANT SELECT in grantPrivileges(): shouldn't users have already set the proper privileges before running the scripts? This could be unexpected.

When I was setting up the example, it also tried to use the admin account to GRANT SELECT on itself, which would cause a bug. I didn't diagnose too closely, just routed around the whole method and everything worked.

— Reply to this email directly or view it on GitHub https://github.com/Bookworm-project/BookwormDB/issues/46.

[organisciak]

I created an admin user in ~/.my.cnf and a web-only user in /etc/mysql/my.cnf; line 94 of CreateDatabase.py tried to GRANT SELECT on the admin user. My other point was that I already set GRANT SELECT when creating the web user.

[bmschmidt]

I know I should just look this up--but what do you mean "I already set GRANT SELECT when creating the web user." You grant select on what? Goal is for the program to do it automatically so you don't have to.

On Mon, Jan 19, 2015 at 12:31 PM, Peter Organisciak < notifications@github.com> wrote:

I created an admin user in ~/.my.cnf and a web-only user in /etc/mysql/my.cnf; line 94 of CreateDatabase.py tried to GRANT SELECT on the admin user. My other point was that I already set GRANT SELECT when creating the web user.

— Reply to this email directly or view it on GitHub https://github.com/Bookworm-project/BookwormDB/issues/46#issuecomment-70531162 .

[organisciak]

As suggested in the README,

GRANT SELECT ON *.* TO 'www-data'@'localhost';
FLUSH PRIVILEGES;

I was working with a fresh EC2 instance and apache install, without a 'www' user, so I first created one ('www-bookworm'), set the SELECT privilege, and added it to the global mysql config file.

I see, you need to GRANT SELECT on the new database after creating it, since it didn't exist during the earlier GRANT SELECT ON *.*. In that case, the problem seems to be that my install used the admin user exclusively: how does Bookworm differentiate between the admin and non-admin user? My understanding was that global my.cnf => web-only user, while user my.cnf => admin user.

[bmschmidt]

Ah.

Your understanding of the distinction is correct: global should web only, ~/.my.cnf is admin.

A problem is that we have two different strategies for solving the client access problem clinking around in the docs still. This www-data@localhost approach was something Matt cooked up for a tutorial to solve various problems on machines with no [client] statement in the global my.cnf at all. I’ve been trying to bury the Congress tutorial a bit just because it uses some nonstandard approaches.

The current grantPrivileges clause is looking for a read-only username and password in the following places.

config.read(["~/.my.cnf","/etc/my.cnf","/etc/mysql/my.cnf","bookworm.cnf"])

If your global mysql file isn’t in one of those places, privileges won’t be properly assigned. This list may need to be extended.

The last one it hits on that list with a client user and a client password is the one that it assigns select privileges to.

Possibly there needs to be a new entry in there: /root/my.cnf or something?

Or maybe in your case the username wasn’t defined within a “client” block? ​

On Mon, Jan 19, 2015 at 1:00 PM, Peter Organisciak <notifications@github.com

wrote:

As suggested in the README https://github.com/Bookworm-project/BookwormDB#required-mysql-database,

GRANT SELECT ON . TO 'www-data'@'localhost'; FLUSH PRIVILEGES;

I was working with a fresh EC2 instance and apache install, without a 'www' user, so I first created one ('www-bookworm'), set the SELECT privilege, and added it to the global mysql config file.

I see, you need to GRANT SELECT on the new database after creating it, since it didn't exist during the earlier GRANT SELECT ON .. In that case, the problem seems to be that my install used the admin user exclusively: how does Bookworm differentiate between the admin and non-admin user? My understanding was that global my.cnf => web-only user, while user my.cnf => admin user.

— Reply to this email directly or view it on GitHub https://github.com/Bookworm-project/BookwormDB/issues/46#issuecomment-70535356 .