MySQL - basic query

[Ahimsa]

Hello
I am both the sole user and root on my machine. When creating a new database with mysql, do I have to start a new database as root to grant permissions to myself as user to use the db? That appears to be how it is laid out in the manual, but I could have misunderstood/misread something.

[furrycat]

It would be interesting to know what you mean by "do I have to?" Is that "do I have to?" as in "Aw shucks do I have to go to bed without supper?" or "Will it not work at all if I don't?"

The answer to the second question is "no," you can start the server running and connect to it straightaway but you certainly should grant only the privileges you need to do whatever it is you want to do. If your question was "Aw shucks MUST I?" I would strongly recommend you think about the implications of always connecting to the database with elevated privileges. In other words "yes" you do have to.

The system root user and the MySQL root user are separate entities. Anyone can connect to the MySQL database as root if they have the database root password and even if they don't have the system root password. Conversely you can have the system root password and not be able to connect to the MySQL database as root if you don't have the password. Admittedly this is kind of unlikely to happen since the database server is usually installed by the root user :-) Not to mention the fact that you can just reset the database root password...

You should practise the same security procedures on your MySQL database as you do on your system: connect as an unprivileged user except when you really do need root access. apache.org was hacked a few months ago because the attacker was able to exploit a root login to the database. Since the database server runs under the system root uid, the whole server was compromised. This particular hack would have been impossible if the apache.org folks had configured their MySQL database correctly.

I think you already know this, however, since you mentioned you are the sole user as well as having r00t on your machine. Sorry for lecturing you in that case!

[Ahimsa]

Don't apologise for the 'lecture' FurryCat: I'm here to learn, and repetition is a good method of drumming knowledge into reticent skulls :).

OK - then the next question is this: I found some help in the man pages regarding the instruction '--skip-grant-tables', which allowed me to get in and create a practice db. However, I don't want to keep doing that, nor do I want to su as r00t, to run the db. All I am really looking for is a way of granting myself the necessary privileges to create and access dbs as a standard user without exposing r00t, and without opening all privileges to anyone who might be lurking. I'm not too concerned about that because as a single user machine I dial-up to the Net when I want, and don't run any clients off of my machine.
Hope my query makes sense.

[element-x]

Create a Database (http://www.mysql.com/doc/C/R/CREATE_DATABASE.html)

Add a new user (http://www.mysql.com/doc/A/d/Adding_users.html)

And you're off to the races :)

Hope that this helps a bit.

[Ahimsa]

hmmm ... well no exactly off to the races: that's the problem. I keep getting a 1044 or a 1064 (can't remember which right now) error that I - as a user - am not authorised to create a new database. That's what my confusion was. Since I'm the only one here that's a little strange - unless it was a Multiple Personality of mine that emerged and decided to rule the roost without my knowledge!! :)
Hence my query ... I was thinking that there may be some central place to fix permissions, but even looking at the permissions it seems that the user has permission to r,w,x so I'm at a loss.

[furrycat]

Your choices are two: grant your user the ability to create databases or connect as the root user, create the database and then grant all necessary privileges on the new db to your user.

[bdl]

I just wanted to mention, as furrycat did, that the root user and root mysql user are two seperate entities. You don't have to su into root to do anything, just login to mysql as root whenever you need to create a db like so:

prompt@regularuser $ mysql -u root -p

Hope this clears it up some. I should also mention, that it's very important once you have your mysql server up and running, to set a root mysql password. At the point of install, the password is blank, anyone can potentially login to the mysql server and create / drop dbs and tables.

[Ahimsa]

... aaaahhh .... <sees a light dimly dawning> :)
OK - I'll try that out this w/end. Much obliged folks.:o