by Jordan » Sat Nov 26, 2011 3:29 am
Use different user to recreate stored procedures:-
I have the following setup:
• a dev database where changes are applied as they are needed
• a beta (nightly builds) that houses the current (collective) state of the db for the public to test
• a production db that houses the actual db that is used on the real site
Now, the current deployment works as follows
1. Code is created on dev as it is needed.
2. Stored procedures (and Alter commands) are generated in a file and uploaded to the server that houses the beta db.
3. The server then executes an svn update and executes the sql-files in batch mode via cronjob
4. Done! Enjoy your nightly build
5. After enough development is done, the production db is updated manually. Step 5 is irrelevant for the actual problem so I won't mention it further.
Now this all worked well until I added the stored procedures update routine. My specific problem is the DEFINER part of the stored procedure.
What I did on the MySQL side was the following. Since I am aware of security and stuff, I added a special user cron that is only granted DDL Rights (e.g. CREATE, ALTER, DROP) and cannot read the actual content of the db. However, once the script is done, it added DEFINER=cron@localhost to the stored procedure. Consequently, no page is served because cron is not allowed to view anything. I then changed the DEFINER manually to DEFINER=actual_user@localhost to try something out, but then MySQL tells me that cron needs SUPER privileges to do that. After consulting the manual about the SUPER privilege, I am unsure that I actually want to give cron that privilege since it surely does not need that much power.
Another alternative is to let actual_user execute the batch-file but that user is only allowed to view data and edit data. Additionally, I think that a user who only works with content really doesn't need any DDL rights.
It would be awesome if I could just give cron the right to change the definer, but I guess it won't be that easy. Any alternatives to my problem?