MySQL grant permission question

I want to grant a user delete permission to a database but got issue:

Suppose I have a database: sampleDatabase and a table: sampleTable, and username: sampleUser

What I did was:
grant usage on *.* to 'sampleUser'@'12.1.1.22' identified by password 'xxx';
grant select, insert, update, delete on 'sampleDatabase'.* to 'sampleUser'@'12.1.1.22'

But I still got 'delete permission denied for user: sampleUser@12.1.1.22'

When I specially granted permission to table level, it could work:
grant select, insert, update, delete on 'sampleDatabase'.'sampleTable' to 'sampleUser'@'12.1.1.22'

I have many tables in this database, so I can't grant permission one by one. I didn't experience such issue before when I used '*' to grant permission to all tables in a database.

Any hint? Thanks.
heyday2004Asked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
when you're connected to mysql server, what did you see when you issue below command:
show grants for current_user();
0
 
johanntagleConnect With a Mentor Commented:
I cannot replicate the problem, but http://stackoverflow.com/questions/3891855/grant-delete-on-database-table-to-username seems to describe the same situation as yours.
0
 
heyday2004Author Commented:
Thanks. Still wondering if what the issue is.

When I use:
select * from mysql.db where User = 'xxx' and Host='xxx'
I could see the delete_priviledge is "Y". But when I tried to delete record, it still reported "permission denied". I have to grant delete permission to a table level to delete records.

One thing I notice is: select * from mysql.host returns zero. Does it matter if there is no record in mysql.host table?

Thanks for any hint.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
Issue following on mysql prompt:
Flush Privileges;
0
 
heyday2004Author Commented:
I did that, still no change.
0
 
johanntagleCommented:
Thanks but can I request the asker to post how the problem was solved?  More for the benefit of those who will see this thread in the future.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.