Fwd: quick MySQL question about permissions

[Sheeri K. Cabral]

Hi all,

I don't have a quick answer offhand other than YOU SHOULD BE USING GRANT STATEMENTS -- I can research and test, but I figured someone on here might know right away.  Feel free to blog the question and answer if you'd like.....if you don't I likely will, so the answer will get out there (including that the real answer is to use GRANT statements)...

-Sheeri

---------- Forwarded message ----------
From: Peter Olszowka <p.ols...@gmail.com>
Date: Feb 12, 2008 12:13 AM
Subject: quick MySQL question about permissions
To: sheeri kritzer <awf...@gmail.com>


Hi,

I know you from various suspects events and somehow I'm on your MySQL
users group email list.  I hope you don't mind me asking you a quick
MySQL question.

This question is about something where the doc was a bit ambiguous.
On version 4.1.22, I'd like to grant a user permission to lock a table
in a database.  There is a column called Lock_tables_priv in the table
db, but when I set that bit (along with select, etc.) the user did not
have permission to lock tables (though he did have perm to select,
etc.).  When I set Lock_tables_priv in the table user, then the user
could lock tables.

Is this expected behavior? i.e. does db.Lock_tables_priv have no
purpose in 4.1.22?

Thanks,

 --PeterO

--
- Sheeri K. Cabral
née Sheeri Kritzer

Note:  Currently I work for The Pythian Group (http://www.pythian.com/ ), a remote DBA firm, and consulting would constitute a conflict of interest.  However, I do recommend our work, and if you're looking for DBA work (I do MySQL work, but they have great Oracle and SQL Server DBAs too) right now, whether you need a few hours a week, or someone fulltime while you continue a job search.

I have a webpage with tips on how to find a DBA:  http://sheeri.net/how-to-find-a-dba/
I can recommend http://www.toomanyconnections.com as a good site to find MySQL (and Oracle) DBAs.
If you're looking for web design, I recommend the small business my husband works for, http://www.digital-loom.com
I regret that I have no other information for any other type of position, including developer, programmer and systems administrator.

[Jay Pipes]

Possibly, the privilege might have not been flushed. When you alter the
priv tables directly, be sure to issue FLUSH PRIVILEGES afterwards so
that the in-memory privileges are synced.

In addition, the order in which MySQL checks privileges is as follows:

global (user table) privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

So that may have been the cause.

Cheers,

Jay