how do I set a password for MySQL?

[raefe77]

Following the Open CMS installation documentation, I need to enter the following commands at the MySQL command line:

use mysql;
insert into user values ('localhost', 'opencmsuser', password('XXXXX'),\
    'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
insert into db values ('localhost', 'opencms', 'opencmsuser',\
    'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges;

But i get this error message: ERROR 1136 (21S01): Column count doesn't match value count at row 1

(Make sure you replace opencmsuser and opencms with the name of your user and database in case you have changed them on the setup wizard.)

I read somewhere that the tables in the database might not correspond to each other. But I did a fresh installation so the documentation will surely be correct!?

I am new to MySql, so please help me with step-by-step instructions on what I shoudl do next please.

 

[KarveR]

depends largely on version, newer versions have alot more fields, so if you do:
mysql> use mysql
mysql> desc user

you should see how many fields you need to actually set.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[raefe77]

Thanks for your response, Karver.

When I type "use mysql" it says "Database changed"(!?)
And then I insert "desc user" only an arrow (->) shows?

What next? How do I get out of it again (exit doesnt work!?)

 

[KarveR]

oops sorry, all commands should be terminated with either \g or ;

mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     |         |       |
| x509_issuer           | blob                              | NO   |     |         |       |
| x509_subject          | blob                              | NO   |     |         |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[raefe77]

Ok that's more like it ;-) Something new i've learned.... ^^

mysql> desc user;
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Field                 | Type                              | Null | Key | Defau
lt | Extra |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Host                  | char(60)                          | NO   | PRI |
   |       |
| User                  | char(16)                          | NO   | PRI |
   |       |
| Password              | char(41)                          | NO   |     |
   |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N
   |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N
   |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N
   |       |
| File_priv             | enum('N','Y')                     | NO   |     | N
   |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| References_priv       | enum('N','Y')                     | NO   |     | N
   |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N
   |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N
   |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N
   |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N
   |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N
   |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N
   |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N
   |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |
   |       |
| ssl_cipher            | blob                              | NO   |     |
   |       |
| x509_issuer           | blob                              | NO   |     |
   |       |
| x509_subject          | blob                              | NO   |     |
   |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0
   |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0
   |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0
   |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0
   |       |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
37 rows in set (0.06 sec)

mysql>

I've counted 26 'N's, so I guess my code should be:

use mysql;
insert into user values ('localhost', 'opencmsuser', password('XXXXX'),\
    'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
insert into db values ('localhost', 'opencms', 'opencmsuser',\
    'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges;

.. but I still get: ERROR 1136 (21S01): Column count doesn't match value count at row 1

Do I really need a password for MySQL database? How dangerous is it concerning access from the internet? Here in my office there is no security danger, as I am the only one who kind of knows what to do. Shall I leave it?

I think the most important is that the OpenCMS login (

http://localhost:8080/opencms/opencms/system/login/)

is secured?

 

[KarveR]

37 rows in set (0.06 sec)

so you need 37 fields in your insert.

you have what, 26 .. add another 11.

The easiest way, if you dont require any permissions is;

insert into user (host,user,password) values ('localhost', 'opencmsuser', password('XXXXX');

but surely you'll need some create,insert,update priviliges or you wont be able to do much anyway.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[raefe77]

Well, I might aswell finish it off the way we started.

I am not sure about the 'opencmsuser' name. Will this be my Windows account name (e.g. MainPC), that I log on with? Or do I not have to change this (opencmsuser), only the password (XXXXX)?

Are the 37 rows all N (user values) or are some Y (
db values)? COULD YOU PLEASE PASTE ME THE CORRECT COMMAND LINE ? (SORRY!)

Gladly I will not have to fiddle around with MySQL settings once I get started (only common commands viw IE browser)

 

[KarveR]

If you are purely accessing the data, end-user style, only the select priv should be required, however if you are setting up the DB, you'll need pretty much everything on.

for super-user privs, insert into user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_pri,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv
) values ('your_host','your_username',password('your_password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[raefe77]

Sorry for the inconvenience, and many thanks for your patience and time!

To give you a jist of what I am doing:
I want to build a website with OpenCMS. Tomcat and Java are successfully installed. MySql now also.
The workplace will be OpenCMS directly, where it uses the other programs as instruments. So I dont think I will need to go too deep into MySql (maybe just throught the browser's admin panel).

So my main objective is only to refresh and polish up my basic website I previously did in Frontpage 97.

 

[lgarner]

My vote:

grant all on opencms.* to opencmsuser@localhost identified by 'XXXXX';

 

[raefe77]

Thanks.. but was is the exact command line I need?

 

[raefe77]

is that the exact command line?

use mysql;
grant all on opencms.* to opencmsuser@localhost identified by 'XXXXX';

(I only have to change the XXXXX with my new password?)

 

[raefe77]

grant all on opencms.* to opencmsuser@localhost identified by 'MYPASSWORD';
Query OK, 0 rows affected (0.05 sec)

Didn't work .....

KarveR (MIS) 10 Aug 06 12:11
If you are purely accessing the data, end-user style, only the select priv should be required

OK, so do I. WHAT IS THE EXACT CODE PLEASE?

 

[johnwm]

Before someone says 'RTFM' try

http://dev.mysql.com/doc/refman/5.1/en/account-management-sql.html

Then read faq222-2244 to see how these forums work

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[lgarner]

You could try a little bit of troubleshooting.

What does "it doesn't work" mean, exactly? Any error message? What's in your user table after adding the opencms user? What happens when you log into MySQL locally using the new userID?

These will help everyone, you most of all, track down the issue.

 

[raefe77]

Query OK, 0 rows affected (0.05 sec)

As stated above, this is what I get. That's why it didnt work, because I still can't log on with my required password (access denied).

Do I really need a password? Internally there is no securtiy leak, it's just a question whether anybody could access it from the web.

What's in your user table after adding the opencms user?

I don't know, if you tell me what to do I can gladly look? I cant get far with troubleshooting because this is all new to me (my intention is OpenCMS)

 

[KarveR]

mysql>use mysql;
mysql>select * from user where user='opencms';

you should have a row of info, provided thats available, you should be able to log in as opencms.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[raefe77]

Empty set (0.05 sec)

Thanks for helping, what next?

 

[raefe77]

I can log in normally btw:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.23-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

 

[lgarner]

Please provide the exact queries that you're entering. It's too hard to troubleshoot with only the results.