On 4/9/01 10:12 AM, "Burke Patrick" <[EMAIL PROTECTED]> wrote:
> Hi Lindsay,
>
> maybe you can help me?
>
> I am trying to give a user SELECT access to just one table in my database.
> If I don't give him SELECT privileges in the mysql.user table or mysql.db
> table, he cannot even login to the database. - Well he can when I do a
> GRANT USAGE statement.
>
> If I give him SELECT privileges in the user table or the db table, he can
> SELECT from all tables in my database.
>
> How can I get around this?
>
> regards
> Patrick
>
Okay, to grant select on one table in one database, use the following
GRANT select ON database.table TO 'username@host' IDENTIFIED BY 'password'
So, if you only want to grant select access to user joeblow on the clothing
table in a merchandise database, and you want that user to be able to log in
from ANY computer in the world, then you have:
GRANT select ON merchandise.clothing to 'joeblow@%' IDENTIFIED BY 'password'
The database.table section uses * as the wildcard,
So to grant select on all tables in one database, you would use:
GRANT select ON merchandise.* to...
To grant select on ALL databases use:
Grant select on *.* to...
The user@host section uses % as a wildcard.
So to grant a specific user, connecting from any machine in a certain
domain:
user@%.domain.com
Or, anyone on a .net domain: user@%.net
Or anyone on the private subnet 192.168.10:
[EMAIL PROTECTED]%
Or any user at any domain %@%
Then of course, you can expand the GRANt statement to include column level
privs.
>From the online manual: http://www.mysql.com/doc/G/R/GRANT.html
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
The problem with using inserts and updates on the mysql databse, is you
might put something in the wrong place,and open your security up and not
even realize it.
For restrictive access, use grant, because you will get it right quicker.
To allow only table level access, then ALL privs in the user table should be
'N'
You really have to read up and understand how mysql 'falls' through the
security tables.
By setting something to Y in the user table, that means that that user has
global right to do that priv on all databases and tables. MySQL stops right
there and says, okay, you are allowed. If it sees an 'N', then it checks
other tables.
Read the security chapter in the books multiple times. Over and over, and it
will start to sink in.
---------------------------------------------------------------------
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
