hi,
[EMAIL PROTECTED] wrote:
I rarely need to add users to a running setup but I always seem to run
into the same sort of problem: I grant privs to a user but they refuse
to show up. This user will be the one a web application goes through to
access the DB.
As the root user:
GRANT SELECT, INSERT, UPDATE ON the_database.*
-> TO [EMAIL PROTECTED] IDENTIFIED BY 'the_password';
Query OK, 0 rows affected (0.00 sec)
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
That statement does (at least) two things to mysql.user:
1) if there's no row in mysql.user for [EMAIL PROTECTED], it creates it
2) if there's an existing row, it updates its password.
IT DOES NOT AFFECT ANY OTHER COLUMN IN mysql.user.
But it does insert or update a row in mysql.db. Check there. You are
confused on the difference between global privileges, which are stored
in mysql.user, and database-level privileges, which are stored in
mysql.db and which you are changing with this statement.
"0 rows affected" on the GRANT suggests that not much was accomplished.
That is normal. It will never say anything else.
SELECT * FROM user WHERE User = 'the_user'\G
*************************** 1. row ***************************
Host: localhost
User: the_user
Password: *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)
I then tried:
UPDATE user SET
Insert_priv = 'Y',
Update_priv = 'Y',
Select_priv = 'Y'
WHERE User = 'the_user';
FLUSH PRIVILEGES;
Which gives me what I expected:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
But I still cannot update any tables as this user.
Probably because you have a database-level or table-level privilege that
overrides the global privileges you are granting.
I know that I've resolved this issue before but it's been ages since
I've run into this (I generally use Postgres but I've been using MySQL
since 3.23, so this is a little embarassing).
I *can* connect to the DB as this user, and select from tables. I just
can't update them:
UPDATE command denied to user 'the_user'@'localhost'
for table 'customer'
Where the heck am i going wrong?
--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
