News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Mysql CREATE & INSERT commands problem.

Started by vizion, September 12, 2005, 04:55:01 PM

Previous topic - Next topic

vizion

Freebsd 5.3

Apache2

dns1# pkg_info | grep mysql
mysql-client-5.0.11 Multithreaded SQL database (client)
mysql-server-5.0.11 Multithreaded SQL database (server)
php4-mysql-4.4.0    The mysql shared extension for php


Owner can login from shell:
Shell test of mysql:

dns1# mysql -h localhost -u OrgKatrina01 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.11-beta-log

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




However after completing the first page next page is::
--------------------------------
Line #21: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_attachments'
Line #39: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_ban_groups'
Line #62: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_ban_items'
Line #74: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_board_permissions'
Line #150: INSERT command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_board_permissions'
Line #179: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_boards'
Line #187: INSERT command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_boards'
Line #206: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_calendar'
Line #218: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_calendar_holidays'
Line #335: INSERT command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_calendar_holidays'
Line #395: INSERT command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_calendar_holidays'
Line #408: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_categories'
Line #415: INSERT command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_categories'

etc
------------------------------------------------------------------------

I hope this does not mean that I need to downgrade mysql as well!!!

David

Oldiesmann

Login to MySQL as root, then issue this command:

GRANT ALL ON *.* TO 'OrgKatrina01'@'localhost' IDENTIFIED BY {password};
Michael Eshom
Christian Metal Fans

[Unknown]


vizion

Thanks for the replies -- just got to see them..

I am missing something or failed to explain something clearly...

My first posting included the dialogue that showed OrgKatrina01 can login to mysql. I did not also post the dialogue that showed all privileges had been granted even though they had.

Are your responses based on the assumption that grant all had not bee given to the user. If so I apologize for not makeing that clear. Here are the dialogues. What am I missing?


Here is the login dialogue:
dns1# mysql -h localhost -u OrgKatrina01 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 5.0.11-beta-log

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



And the Grants:
mysql> show grants
    -> ;
+-------------------------------------------------------------------------------------------------------------+
| Grants for OrgKatrina01@%                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'OrgKatrina01'@'%' IDENTIFIED BY PASSWORD '*203FCF75AB22F295DB851635B1492321252D5FF6' |
| GRANT ALL PRIVILEGES ON `OrgKatrina`.`OrgKatrina` TO 'OrgKatrina01'@'%' WITH GRANT OPTION                   |
+-------------------------------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

But I get:
Line #21: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_attachments'
Line #39: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_ban_groups'
Line #62: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_ban_items'
Line #74: CREATE command denied to user 'OrgKatrina01'@'localhost' for table 'OrgKat_board_permissions'


Thanks in advance

David


[Unknown]

The output of that is odd, seems to differ from mine, but I guess it's right.

Have you restarted MySQL or run a FLUSH PRIVILEGES since making that change?

If, from the shell, you enter this:

CREATE TABLE test (
   column varchar(80) NOT NULL default ''
) TYPE=MyISAM;

What happens?

-[Unknown]

vizion

OK just got back from lunch ;D

I hope you can make sense of this --  we seem to have a syntax error: :o

mysql> CREATE TABLE test
    -> column varchar(80) NOT NULL default ''
    -> ) TYPE=MyISAM;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column varchar(80) NOT NULL default ''
) TYPE=MyISAM' at line 2


I am just going to look up but in the meantime here is the version info:
mysql-client-5.0.11 Multithreaded SQL database (client)
mysql-server-5.0.11 Multithreaded SQL database (server)
php4-mysql-4.4.0    The mysql shared extension for php


[Unknown]

Not:

CREATE TABLE test
   column varchar(80) NOT NULL default ''
) TYPE=MyISAM;

You're missing the (.  You need:

CREATE TABLE test (
   column varchar(80) NOT NULL default ''
) TYPE=MyISAM;

-[Unknown]

vizion

My first one had a typo (left out the open bracket after (
But same result!1

mysql> CREATE TABLE test (
    -> column varchar(80) NOT NULL default ''
    -> ) TYPE=MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column varchar(80) NOT NULL default ''
) TYPE=MyISAM' at line 2

vizion

We cross postED .. BUT STILL AT SAME POINT  :(

[Unknown]

Try col instead of column, then... it thought it wasn't be a reserved word, though.... MySQL has so few.

CREATE TABLE test (
   col varchar(80) NOT NULL default ''
) TYPE=MyISAM;

-[Unknown]

vizion

I do not know what to make of this

Do you?

David  ???

dns1# mysql -h localhost -u OrgKatrina -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.11-beta-log

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

mysql> show grants
    -> ;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for OrgKatrina@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'OrgKatrina'@'localhost' IDENTIFIED BY PASSWORD '*203FCF75AB22F295DB851635B1492321252D5FF6' |
| GRANT ALL PRIVILEGES ON `OrgKatrina`.`OrgKatrina` TO 'OrgKatrina'@'localhost' WITH GRANT OPTION                   |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show tables
    -> ;
ERROR 1046 (3D000): No database selected
mysql> use OrgKatrina
Database changed
mysql> show tables
    -> ;
Empty set (0.01 sec)

mysql> CREATE TABLE test (
    -> col varchar(80) NOT NULL default ''
    -> ) TYPE=MyISAM;

ERROR 1142 (42000): CREATE command denied to user 'OrgKatrina'@'localhost' for table 'test'
mysql>


vizion

I made some errors in that one -- used OrgKatrina as user instead of OrgKatrina 01

I will come back to this later -- hotta do some other work right now

Thanks guys

David

Oldiesmann

The information you posted above shows this:

If you login with a password, you don't get any privileges (USAGE = create account but don't give any privileges to it)
If you login without a password, you get all standard privileges plus the ability to grant privileges to other users (WITH GRANT OPTION), but only for the table OrgKatrina on the database OrgKatrina.

You need to do it this way:

Login as "root" (not OrgKatrina, but the actual "root" account that you created when you set up MySQL)
Issue the following command:

GRANT ALL PRIVILEGES ON *.* TO 'OrgKatrina'@'localhost' IDENTIFIED BY 'password';

That will give the OrgKatrina user full privileges when logging in with a password.
Michael Eshom
Christian Metal Fans

[Unknown]

Actually, apparently that's how MySQL shows it.  I think he has it set correctly.

-[Unknown]

vizion

OK

I found the problem

nothing to do with the sql commands -- it was system wide permission setting on my mysql config!!

I have just got it working
\
Thanks guys

Advertisement: