MySQL Privileges

[databasiqs]

I am trying to learn the MySQL privileges system, using the code below. I had intended
to generate an error message by misspelling the password. Whether I use the right password
"johnny" or "johnnie" (wrong), the INSERT goes through -- that is not what I wanted!

Any help correcting my mistake greatly appreciated.

Many thanks in advance.

DROP TABLE IF EXISTS Orders2;
CREATE TABLE Orders2 (
account_id CHAR( 20 ), username CHAR( 20 ), password CHAR( 20 ), apples INT( 4 ),
bananas INT( 4 ), cherries INT( 4 )
) TYPE = InnoDB;


GRANT SELECT, INSERT, UPDATE
ON Orders2
TO 'john'@'localhost'
IDENTIFIED BY 'johnny';
FLUSH PRIVILEGES;

INSERT INTO Orders2 (
account_id, username, password, apples, bananas, cherries
)
VALUES (
'accountid', 'john', 'johnnie', 20, 30, 300
);


SELECT * FROM Orders2;

+------------+----------+----------+--------+---------+----------+
| account_id | username | password | apples | bananas | cherries |
+------------+----------+----------+--------+---------+----------+
| accountid | john | johnnie | 20 | 30 | 300 |
+------------+----------+----------+--------+---------+----------+
1 row in set (0.00 sec)

[fimi]

im assumeing that you are using the command prompt(for windows) and going into mysql/bin folder and then typing mysql.

if you do that...it means that you are logged in as root, in other words you do not have any permission stuff related to it. if you use PHPMyAdmin, and you setup the username and password correctly you would be able to grant those permissions...

as i see in your code,
you can add any username in the table Orders2,
the privileges of mysql means who can insert/create, update, delete records/tables in a database.


<EDITED>Oh, one more thing:

the account_id should not be defined as text, it should increase automatically.
</EDITED>

hope that helped,
fimi

[raf]

This is confusing.

Just to avoid mistakes:
GRANT SELECT, INSERT, UPDATE
ON Orders2
TO 'john'@'localhost'
IDENTIFIED BY 'johnny';
FLUSH PRIVILEGES;
---> this will change the permissions for the mySQL account

INSERT INTO Orders2 (
account_id, username, password, apples, bananas, cherries
)
VALUES (
'accountid', 'john', 'johnnie', 20, 30, 300
);
---> this will simply insert a record in that table, but it has nothing to do with the mySQL accoutn statement aboven. The password in the Orders2 table is not related in any way with the mySQL's permissionsystem or user-table. It is just text. You can just as well enter 'sdqsede'

Whethet the insert goes trough or not, has nothing to do with the values inside the insertstatement. It will depend on which mySQL username and pwd you used when you requested a connection to the mySQL-server to run the query.

So i don't see the relevance between all your order2 code and your question/problem.