Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Pages: 1
#1 2007-11-09 10:18:58
- AlexStacey
- Member
- Registered: 2007-11-09
- Posts: 22
[wiki] mysql database privileges
Hi,
I have just installed textpattern and am very impressed – props to the developers and contributors!
I was thinking it would be a nice addition to the http://textbook.textpattern.net/wiki/index.php?title=Detailed_Installation_Instructions if the necessary mysql database privileges (with basic instructions about adding a new user) were included for those that wish to create a new database user for textpattern.
In my case, security on the server I’m installing on is of great importance (and one of the reasons I chose textpattern over more popular platforms like wordpress) so I’ve created a user who only has privileges for that database, and ideally I’d like these set to the minimum privileges needed to run textpattern.
Just a thought, and perhaps outside of the scope of these instructions (which is why i didn’t barge in and start editing without bringing it up here first)
Thanks,
Alex =]
Offline
Re: [wiki] mysql database privileges
Good idea. I’d recommend posting the instructions you’d like to add to textbook here on the forum first, so people can comment on it. It’s probably easier to discuss it here than in textbook.
Offline
#3 2007-11-09 15:04:21
- AlexStacey
- Member
- Registered: 2007-11-09
- Posts: 22
Re: [wiki] mysql database privileges
ok, how about something like…
Creating a new database and user for textpattern
If you would like to use a new database for textpattern, you will have to create it. Textpattern will not create it for you. For security reasons, it’s a good idea to create a new user for this database who only has priviliges to modify the ‘txp’ database. To do this, log into MySql as the root user and create the database and new user as follows. (note: the database name ‘txp’, the username ‘txpusr’ and the password ‘passwrd’ can all be replaced with names of your choice)
$ mysql -u root -p
Enter password: ******* (enter the root password here)
mysql> CREATE DATABASE txp;
mysql> GRANT ALL PRIVILEGES ON txp.* TO txpusr@'localhost' IDENTIFIED BY 'passwrd';
You then need MySql to read the newly modified privileges like this:
mysql> FLUSH PRIVILEGES;
You could go a step further and limit the privileges of the user with something like instead of the GRANT ALL PRIVILEGES above:
mysql> GRANT insert,update,delete,index,alter ON txp.* TO txpusr@'localhost' IDENTIFIED BY 'passwrd';
…but I haven’t tested this to see if it will work with all the different SQL statements in textpattern although it seems to be fine for the installation.
[Ruud: edited to make the code show up properly]
Offline
#4 2007-11-09 15:54:43
- AlexStacey
- Member
- Registered: 2007-11-09
- Posts: 22
Re: [wiki] mysql database privileges
hmm…
interestingly, this forum seems to change apostrophes into angled ‘apostrophes’ which don’t work in mysql if you cut and paste from here.
Offline
Re: [wiki] mysql database privileges
AlexStacey wrote:
ok, how about something like…
Creating a new database and user for textpattern
If you would like to use a new database for textpattern, you will have to create it. Textpattern will not create it for you. For security reasons, it’s a good idea to create a new user for this database who only has priviliges to modify the ‘txp’ database. To do this, log into MySql as the root user and create the database and new user as follows. (note: the database name ‘txp’, the username ‘txpusr’ and the password ‘passwrd’ can all be replaced with names of your choice)
$ mysql -u root -p
Enter password: ******* (enter the root password here)
mysql> CREATE DATABASE txp;
mysql> GRANT ALL PRIVILEGES ON txp.* TO txpusr@'localhost' IDENTIFIED BY 'passwrd';
You then need MySql to read the newly modified privileges like this:
mysql> FLUSH PRIVILEGES;
You could go a step further and limit the privileges of the user with something like instead of the GRANT ALL PRIVILEGES above:
mysql> GRANT insert,update,delete,index,alter ON txp.* TO txpusr@'localhost' IDENTIFIED BY 'passwrd';
…but I haven’t tested this to see if it will work with all the different SQL statements in textpattern although it seems to be fine for the installation.
Last edited by iblastoff (2007-11-09 16:06:59)
Offline
Re: [wiki] mysql database privileges
its because this board uses textile. so you’d have to use something like “bc.” to specify block of code
edit: nm looks like ruud took care of it :)
Last edited by iblastoff (2007-11-09 16:09:05)
Offline
#7 2007-11-13 20:08:34
- AlexStacey
- Member
- Registered: 2007-11-09
- Posts: 22
Re: [wiki] mysql database privileges
ok, well this is a bit better. let me know if anyone has any objections, otherwise i’ll go ahead and add it. =]
Creating a new database and user for textpattern
If you would like to use a new database for textpattern, you will have to create it. Textpattern will not create it for you. For security reasons, it’s a good idea to create a new user for this database who only has priviliges to modify the ‘txp’ database, thereby protecting any other databases you have on the same server. To do this, log into MySql as the root user and create the database and new user as follows. (note: the database name ‘txp’, the username ‘txpusr’ and the password ‘passwrd’ can all be replaced with names of your choice)
$ mysql -u root -p
Enter password: ******* (enter the root password here)
mysql> CREATE DATABASE txp;
mysql> GRANT ALL PRIVILEGES ON txp.* TO txpusr@'localhost' IDENTIFIED BY 'passwrd';
You could go a step further and limit the privileges of the user so that they can’t drop (get rid of) tables or databases.
mysql> REVOKE DROP ON txp.* FROM txpusr@'localhost';
Finally, you need MySql to reload the newly modified privileges:
mysql> FLUSH PRIVILEGES;
Offline
Re: [wiki] mysql database privileges
I thought the idea was to find which were the minimum privileges needed to be able to use Textpattern? In that case it’s probable clearer to explicitly list the privileges needed instead of granting everything (which differs per MySQL version) and then revoking.
Offline
#9 2007-11-15 13:02:05
- AlexStacey
- Member
- Registered: 2007-11-09
- Posts: 22
Re: [wiki] mysql database privileges
Good point. I’ll run through the installation and a few basic operations in textpattern when I have some time and log the sql statements so I can see exactly what’s needed. I’m guessing it will need:
INDEX
SELECT
CREATE
INSERT
ALTER
UPDATE
DELETE
but won’t need:
FILE
RELOAD
SHUTDOWN
PROCESS
DROP
USAGE
Offline
#10 2007-11-15 13:37:23
- net-carver
- Archived Plugin Author
- Registered: 2006-03-08
- Posts: 1,648
Re: [wiki] mysql database privileges
AlexStacey
nice idea.
FWIW; some plugins might need other DB privileges — but I would imagine that this would be a small minority of plugins. I myself have one plugin that needs the DROP
and CREATE_TEMPORARY_TABLE
privileges.
— Steve
Offline
Re: [wiki] mysql database privileges
/* reminds self to follow-up */
Offline
Re: [wiki] mysql database privileges
OK, so looking around in the wiki I don’t see that this was ever followed-up with. I would propose instead of adding it into the the already long “detailed” install instructions (which I’m going to refine later anyway), that it becomes it’s own article titled Creating a New Database User, because if I’m not mistaken the intention was not to create a new database, but just a user with restricted access to the existing DB. Note also I omitted “Textpattern” from the title because it’s TextBook after all and the whole wiki is about Textpattern.
Put this at the bottom of the page (all tutorial-like articles will be further organized later on):
<!-- Keep at bottom! -->
[[category:Tutorials]]
Offline
Pages: 1