Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#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

#2 2007-11-09 10:28:46

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

#5 2007-11-09 16:05:46

iblastoff
Plugin Author
From: Toronto
Registered: 2006-06-11
Posts: 1,197
Website

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

#6 2007-11-09 16:08:27

iblastoff
Plugin Author
From: Toronto
Registered: 2006-06-11
Posts: 1,197
Website

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

#8 2007-11-13 22:02:25

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

#11 2008-10-23 11:48:46

Destry
Member
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,909
Website

Re: [wiki] mysql database privileges

/* reminds self to follow-up */

Offline

#12 2008-11-17 21:16:07

Destry
Member
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,909
Website

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

Board footer

Powered by FluxBB