List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 17 1999 8:02pm
Subject:Re: tables... user, columns_priv, and tables_priv
View as plain text  
"Roy F. Cabaniss" wrote:
< ... > 
> so I wonder.... which of the tables listed in the subject line do I need to have
> the insert into statement for?
> when I create the user... do I HAVE to grant select privledges for all
> databases?  Or can the denial in the user table, be overridden in the
> tables_priv table.  Since the user tabel does not specify a table to work on or
> a database to work on... It seems to me that I am safer denying at that point.
> if I DO put a database into the db table am I correct in assuming that every
> table in it follows the parameters that I set in the db table.
> how do I get the dialup host from many different folks on the web.. or should I
> just assume that they are all on "localhost" or "%".  And since folks change
> isp's so often, won't that result in their being locked out of the database when
> they are actually already in it.  It seems to me that about the only solution to
> that is to use % as the host value.
> maybe something like...
> in the user.... no one gets any privledges
> in the host.... no one gets any privledges
> in the tables_priv ... all users get select and update on certain tables. (Is
> this necessary since the columns_priv also has a table entry.
> in the columns_priv .... all users get select and update on certain columns
> and the format for the set statement in the columns_priv is?
> and when done a nice mysqladmin reload to top it off with.
> I know that I really rambled here... and I DO wish that the O'Reily book on
> mysql was released.
> --
> Dr. Roy F. Cabaniss   Ph.D. in Marketing

Hi Roy

Why don't you use the GRANT syntax?

You need an entry in the mysql.user table, to allow the person to connect to this mysql
server with a name and password from the specified machines. 
A person not inserted here can't use this mysql server.

These person shall not have priviledges on all DB's, so you should not give her any
priviledges in the mysql.user table. 
You can't deny priviledges given here in another priviledge table.
You only can give additional priviledges.

If you want to give her priviledges on everything in a database, then you have to insert
an entry in the mysql.db table.

If you want to give her additional priviledges, if she connects from a special machine,
then insert a row in the table.

If you want to give her priviledges on one table, then insert a row into

If you want to give her priviledges only to one field in a table, then add a row to the
mysql.columns_priv table.

If all people only have access to this database via scripts on the server, then they will
all connect from the same machine.
If the webserver and the mysql server are on the same machine, then your script connects
from 'localhost'.

If some of these people have direct access to the mysql server, then you have to give 
them a wildcard host name ('%').

After changing the priviledge tables you don't need to use the mysqladmin tool to flush
Just issue an:


tables... user, columns_priv, and tables_privRoy F. Cabaniss17 Jun
  • Re: tables... user, columns_priv, and tables_privChristian Mack18 Jun