Jason
There are 5 layers to access control in MySQL that are checked to see if a
query can be executed. Starting from the most generic these layers are:
User --> Db (and Host) --> Tables_priv --> Columns_priv
The first layer that is found to have adequate privileges will cause the
query to execute.
'User' is the most generic. It provides access checks for a user to all
Db's on this server.
'Db' allows access to specific Db's. I can restrict access to Db's based on
'Host' (where the user is accessing the Db from) as well. You might for
example allow general access (both remote & local) to a Db, but might want
to restrict admin type access to only local users.
'Tables_priv' and 'Columns_priv' allow access to sub-sets of tables &
columns respectively.
The above order is significant. If I have 'user' access, for example, the
query is executed, without checking the subsequent layers. So to restrict a
user to a specific database, I need to add a user with no privileges, then
add specific access to the database.
I'd also recommend using 'GRANT' & 'REVOKE' instead of manipulating the
tables directly (if you can, this does require 3.22.11 or better).
So, to do what you want to do, you'd need something like:
GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypass';
This creates a user with no privileges. If you do a SELECT on mysql.user
you should see 'N' across all the privileges. At this stage there should not
be anything in the mysql.db table specifically for this user. If there is
it's probably left over from before. Then...
GRANT SELECT, INSERT, UPDATE, DELETE ON database1.* TO 'myuser'@'%';
This grants specific access to the database1 for this user. This adds an
entry to the mysql.db table. Because this user doesn't have privileges
beyond database1, if they try to access mysql they will get an 'access
denied' error.
And don't forget to:
FLUSH PRIVILEGES;
And to
SHOW GRANTS FOR myuser;
To check everything is as you'd expect it.
HTH
Trevor
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Trevor Harris NuSphere Corp.
Instructor/Consultant 14 Oak Park
Bedford, MA 01730 USA
E-mail: [EMAIL PROTECTED] http://www.nusphere.com
Tel 781-280-4243
Fax 781-280-4901
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-----Original Message-----
From: Jason Frisvold [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 3:15 PM
To: [EMAIL PROTECTED]
Subject: Database Permissions
Greetings,
I'm having a little trouble understanding how the permissions work
within mySQL. I have 3 current databases. The first is the default mysql
database, and the other 2 are ones that I have created. I want to be able
to grant access to each table individually based on username. Hostname
isn't really a big deal to me because of the firewalls. Basically, if they
can get to the database server, then they belong there. But, I want to
limit what they can get to via username.
So, I tried the following :
insert into user (host, db, user, select_priv, insert_priv, update_priv,
delete_priv) values ('%', 'myuser', password('mypass'), 'Y', 'Y', 'Y', 'Y');
insert into db (host, db, user, select_priv, insert_priv, update_priv,
delete_priv) values ('%', 'database1', 'myuser', 'Y', 'Y', 'Y', 'Y');
insert into db (host, db, user, select_priv, insert_priv, update_priv,
delete_priv) values ('%', 'mysql', 'myuser', 'N', 'N', 'N', 'N');
flush privileges;
I *thought* this would allow access to database1, and not mysql, but
apparently I was wrong... I can still access the mysql database, make
changes, etc. without any restrictions.
What am I doing wrong???!!!???
Also.. Is there a way to allow a user to connect to the mySQL server, not
specifying a database (this is via Perl and DBI), and get a list of
databases that they have permissions to? (What I'd like to do is let them
list all db's except those they have no permissions on...) I have it
working so that they can list all the databases, but it will list everything
on the server.. (This may be a moot point when I get actual permissions to
work...)
---------------------------
Jason H. Frisvold
Senior ATM Engineer
Engineering Dept.
Penteledata
CCNA Certified - CSCO10151622
[EMAIL PROTECTED]
---------------------------
"We should take care not to make the intellect our god; it has, of course,
powerful muscles, but no personality." - Albert Einstein (1879-1955)
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
