Thursday, February 17, 2005
1. Sort out the password for the root user
Well I've been all over several forums last night and today trying to understand what this is all about. Basically the installation of mySQL has a "root user", i.e. all powerful user that can do anything in mySQL, and, by default, the root user doesn't have a password. This means that if nasty people gain access to the server that is hosting mySQL then they can mess things up quite badly.
It is easy to get diverted into a discussion about securing the mySQL server but that is a whole other world of pain and additional complication. For the purposes of working through TT380 the smart money is saying that you don't need to worry about it too much (no warranty is implied in that statement!).
So let's stop prevaricating and get on with it.
How do you set a password for the root user?
Okay, most of us (Windows) users will be going into mysql monitor from the DOS window.
It goes something like this:
C:\Documents and Settings\Dell>cd c:\mysql\bin
C:\mysql\bin>mysql -u root -p
Enter password:
COMMENT: if you haven't already set up a password for 'root' then hit enter at the password prompt and you're in
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
COMMENT: this is the code that sets your root password
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('A1234567');
Query OK, 0 rows affected (0.03 sec)
COMMENT: after you've changed the password FLUSH PRIVILEGES just to make sure
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
COMMENT: I tried logging in without a user name and got straight in. I don't know what type of user you login as as default but right now I don't care. I'm assuming it's not the 'root' user
C:\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye
COMMENT: Then tried logging in as 'root' without a password and got bounced...
C:\mysql\bin>mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
COMMENT: Then tried logging in as 'root' with my new password and was let in.
C:\mysql\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Overall, fairly easy to do once you've got the code sorted...what's next...
Next to create some users so I can mimic what is going on in mySQL in 24hrs.
Why am I going to create a new user, why not just use the root user?
Well I'm keeping in mind some tips from Alistair Thomas, just seems using the root user is bad form and something to be avoided. Why that should be will no doubt become clearer as we become more familiar with mySQL:
"Having become familiar with Linux this kind of stuff is fairly similar, i.e. the command line and different user accounts. I have a few guidelines for myself:
- Make sure the root account is secure, i.e. it has a password atleast!
COMMENT: we've just done that!
- Don't use the root account. This is a golden rule which is only broken when:
- Break things down into simple testable steps. e.g. if you create a table type do a show table command to see if it has done what you want. After a while you will become more familiar with the set up and won't need the test step as often, but it always seems to help me to know how to check what I've done.
Thanks Alistair.
Alistair is saying above, amongst other things, that only the root user can be used to create and drop databases. I'm just wondering if that is strictly true as i successfully logged in as "default" and I've been able to create and drop tables there before. we'll see...
2. create a new user to mimic the settings on SSH / OU server
It is easy to get diverted into a discussion about securing the mySQL server but that is a whole other world of pain and additional complication. For the purposes of working through TT380 the smart money is saying that you don't need to worry about it too much (no warranty is implied in that statement!).
So let's stop prevaricating and get on with it.
How do you set a password for the root user?
Okay, most of us (Windows) users will be going into mysql monitor from the DOS window.
It goes something like this:
C:\Documents and Settings\Dell>cd c:\mysql\bin
C:\mysql\bin>mysql -u root -p
Enter password:
COMMENT: if you haven't already set up a password for 'root' then hit enter at the password prompt and you're in
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
COMMENT: this is the code that sets your root password
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('A1234567');
Query OK, 0 rows affected (0.03 sec)
COMMENT: after you've changed the password FLUSH PRIVILEGES just to make sure
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
COMMENT: I tried logging in without a user name and got straight in. I don't know what type of user you login as as default but right now I don't care. I'm assuming it's not the 'root' user
C:\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye
COMMENT: Then tried logging in as 'root' without a password and got bounced...
C:\mysql\bin>mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
COMMENT: Then tried logging in as 'root' with my new password and was let in.
C:\mysql\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Overall, fairly easy to do once you've got the code sorted...what's next...
Next to create some users so I can mimic what is going on in mySQL in 24hrs.
Why am I going to create a new user, why not just use the root user?
Well I'm keeping in mind some tips from Alistair Thomas, just seems using the root user is bad form and something to be avoided. Why that should be will no doubt become clearer as we become more familiar with mySQL:
"Having become familiar with Linux this kind of stuff is fairly similar, i.e. the command line and different user accounts. I have a few guidelines for myself:
- Make sure the root account is secure, i.e. it has a password atleast!
COMMENT: we've just done that!
- Don't use the root account. This is a golden rule which is only broken when:
- you need to add or delete a user,
- change their privileges,
- create or drop a database.
- Break things down into simple testable steps. e.g. if you create a table type do a show table command to see if it has done what you want. After a while you will become more familiar with the set up and won't need the test step as often, but it always seems to help me to know how to check what I've done.
Thanks Alistair.
Alistair is saying above, amongst other things, that only the root user can be used to create and drop databases. I'm just wondering if that is strictly true as i successfully logged in as "default" and I've been able to create and drop tables there before. we'll see...
2. create a new user to mimic the settings on SSH / OU server
Comments:
<< Home
As a 'root' user you're the one in control. So if you want to create another couple of root users with different names that's fine. You just grant them all privileges on all databases. These new super users can also create databases, drop other people's databases, create their own super users, etc.
As you can imagine giving someone this much authority requires you really trust them, but then why not just give them access to the root account? However you don't want to give a new user this much access, just in case they decide, accidentally or on purpose, to drop one of your other user's key databases, etc.
If you want your default account to create new databases then fine, but do you really want that account to also drop databases. Particularly other people's databases.
As you can imagine giving someone this much authority requires you really trust them, but then why not just give them access to the root account? However you don't want to give a new user this much access, just in case they decide, accidentally or on purpose, to drop one of your other user's key databases, etc.
If you want your default account to create new databases then fine, but do you really want that account to also drop databases. Particularly other people's databases.
# posted by 
: 3:09 pm
: 3:09 pm
An interesting point and one that makes me raise a question. Is it possible to track what a particular user has done? Similiar to the way one can track users on both windows and unix system.
Because if you are able to track users, it would be wise to create these superuser for each of the db admin staff, thus allowing you to track what each of them have done. The root user account would not then need to be used and tracking could be setup so that if the root was used it raised alarm bells.
Something to look into I think - just thought I would comment on it here as I felt it relevant.
Because if you are able to track users, it would be wise to create these superuser for each of the db admin staff, thus allowing you to track what each of them have done. The root user account would not then need to be used and tracking could be setup so that if the root was used it raised alarm bells.
Something to look into I think - just thought I would comment on it here as I felt it relevant.
like tumbler and tipsy days hopefully we will remain in high spirits. well, good day
Post a Comment
# posted by : 11:29 pm
: 11:29 pm<< Home
