tables_priv not working
I can't get table-specific grants to work.
(FIRST: I log in as an administrator and run the following command:)
mysql GRANT SELECT, INSERT, UPDATE, DELETE ON myDataBase.watch TO '%';
Query OK, 0 rows affected (0.00 sec)
mysql SELECT * FROM tables_priv;
+--++--++---++-+-+
| Host | Db | User | Table_name | Grantor | Timestamp |
|Table_priv | Column_priv |
+--++--++---++-+-+
| %| myDataBase | %| watch | nhamlin@localhost | 20020116151451 |
|Select,Insert,Update,Delete | |
+--++--++---++-+-+
1 row in set (0.00 sec)
mysql select Host, Db, User, Select_priv as SEL, Insert_priv as INS, Update_priv as
UPDT, Delete_priv as DEL FROM db WHERE user = 'dummy';
+--++---+-+-+--+-+
| Host | Db | User | SEL | INS | UPDT | DEL |
+--++---+-+-+--+-+
| %| myDataBase | dummy | Y | N | N| N |
+--++---+-+-+--+-+
1 row in set (0.00 sec)
(SECOND: I log out and log back in using another user with the following credentials:)
mysql select user, Select_priv as SEL, Insert_priv as INS, Update_priv as UPDT,
Delete_priv as DEL FROM user WHERE user = 'dummy';
+---+-+-+--+-+
| user | SEL | INS | UPDT | DEL |
+---+-+-+--+-+
| dummy | N | N | N| N |
+---+-+-+--+-+
1 row in set (0.00 sec)
(THIRD: I see if I can access the table.)
mysql select * from watch;
+-+-+-+-+
| user| w_table | w_value | create_date |
+-+-+-+-+
| nhamlin | fo | 6075| 2002-01-03 13:46:22 |
| nhamlin | fo | 7584| 2002-01-04 14:51:56 |
| nhamlin | p_q | 3350| 2002-01-10 12:23:48 |
| nhamlin | cpr | 4101| 2002-01-10 19:24:56 |
| nhamlin | cpr | 4094| 2002-01-11 17:38:46 |
| nhamlin | fo | 7 | 2002-01-15 10:10:34 |
| qa568 | fo | 10409 | 2002-01-16 12:46:38 |
+-+-+-+-+
7 rows in set (0.00 sec)
(FOURTH: I then try to add a value to the table. This is where it fails.)
mysql insert into watch values ('dummy','fo','6075',NOW());
ERROR 1142: insert command denied to user: 'dummy@localhost' for table 'watch'
What am I doing wrong? I have about 9 tables inside of MyDataBase, and I only want
two tables, 'watch' and 'user', to be updatable by anyone with an existing account who
can log in from anywhere (more specifically, the web).
Nicholas
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: tables_priv not working
On Wednesday 16 Jan 2002 8:35 pm, Hamlin Nicholas-qa568 wrote: I can't get table-specific grants to work. snip Have you flush privileges ? Yes. That didn't change it. Nicholas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird problem with mysql database
I have added some extra fields to the mysql.user table, namely:
full_name VARCHAR(255)
job VARCHAR(255)
location VARCHAR(255)
phone VARCHAR(255)
reminder VARCHAR(255) NOT NULL
ip_addr VARCHAR(255) NOT NULL
I want to store these values for each person that creates a new account on my database.
The database is still in production test mode, so I was adding some dummy users.
The statement to create the account is:
INSERT into user (
Host,
User,
Password,
full_name,
job,
location,
phone,
reminder,
coreID,
ip_addr
)
VALUES ('%','yessir',PASSWORD('yesyesyes'),'Nathan
Lanier','Yes-man','FL-SE','123-1234','yesyesyes','161.161.1.131')
I kept getting Access Denied errors for this user. I scoured the newsgroup archives
for this problem, and tried every bit of advice on MySQL.com's documentation under
4.2.10, and nothing helped. I restarted the mysqld, and nothing helped. I needed to
make sure that I could still create new users, so, I tried:
INSERT into user (
Host,
User,
Password,
full_name,
job,
location,
phone,
reminder,
coreID,
ip_addr
)
VALUES ('%','newUser',PASSWORD('yesyesyes'),'Nathan
Lanier','','','','yesyesyes','161.161.1.131')
This one works perfectly. I narrowed the problem down to the job column, so I deleted
the value for user 'yessir'. It worked! In order to then figure out why some
accounts with values in the job column work and user 'yessir' didn't, I tried various
values for the job column. Thinking it was the hyphen, I put in Yesman and it
worked! But, I have another user with 'Heave-ho' as it's job and that one works fine.
So, it's not the hyphen. Very strange.
I think that the Yes- is throwing it off. But, if that was so, why can I change the
reminder column, on the same account, to be Yes-yesyes and the account still
works? Is there some limitation to using the keyword job? The column full_name
resides before job in the table, so I don't think that it's conflicting with the
order of the priviledge columns.
Any ideas would be appreciated so that I can make sure that some unsuspecting user
doesn't throw some value in their entry that will cause the system not to work for
them. Or maybe there's a better way of going about this?
Nicholas Hamlin
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Weird problem with mysql database
Sinisa Milivojevic writes: ALTERing will completely break security and permission systems and will even prevent MySQL from proper functioning. Can you give me an example? I'm having no problems with security even after adding these columns. Users only have access to the dbs and tables that I assign in mysql.db and mysql.tables_priv, since all users are initially created with only usage privileges. How does adding non-privilege, information only columns to the end of mysql.user jeopardize security? And you can only access them via GRANT / REVOKE command. I don't understand this statement. I can access them via GRANT/REVOKE, or also by directly updating the information using UPDATE, INSERT, and DELETE (with the subsequent FLUSH PRIVILEGES). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
