Allowing all users to access a specified database

Hi,

I have a database server with multiple users and multiple databases. I
have a situation where I want to allow any user to connect to a
specified database.

Unfortunately, the documentation has this to say:

"MySQL does not support wildcards in user names."

Which means, that, although I can use this syntax to grant access for a
user to all databases:

GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'someuser'@'%';

I can't do something like this to grant all users access to a database:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';

So, my question is this: How can I allow any user to use a specific
database, without individually granting them all access?

The reason I want to be able to do this is that the server hosts a large
number of individual ecommerce sites running on the same core software.
Each site has its own database for products, customer data, etc, which
is only accessible to that site. But each site also needs to be able to
access a single, server-wide database which both stores configuration
information for the software that the sites run on and store usage
statistics for each site which can be accessed by the server admins
without needing to have access to each site's own database.

Clues, anyone?

Mark
--
Sent from my Babbage Difference Engine
http://mark.goodge.co.uk
http://www.ratemyairport.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Allowing all users to access a specified database

--001517570b2a5b2b8804a4cc2944
Content-Type: text/plain; charset=ISO-8859-1

The reason

*GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';*
*
*
does not work is because that command would be suicidal in terms of
security.

If you are hosting a large number of ecommerce sites and granting any user
access to those databases then you would want security to be far tighter.
Allowing that sort of access is about as secure as publishing the data on
facebook.

What version of MySQL are you using?


On 3 June 2011 11:03, Mark Goodge wrote:

> Hi,
>
> I have a database server with multiple users and multiple databases. I have
> a situation where I want to allow any user to connect to a specified
> database.
>
> Unfortunately, the documentation has this to say:
>
> "MySQL does not support wildcards in user names."
>
> Which means, that, although I can use this syntax to grant access for a
> user to all databases:
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'someuser'@'%';
>
> I can't do something like this to grant all users access to a database:
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';
>
> So, my question is this: How can I allow any user to use a specific
> database, without individually granting them all access?
>
> The reason I want to be able to do this is that the server hosts a large
> number of individual ecommerce sites running on the same core software. Each
> site has its own database for products, customer data, etc, which is only
> accessible to that site. But each site also needs to be able to access a
> single, server-wide database which both stores configuration information for
> the software that the sites run on and store usage statistics for each site
> which can be accessed by the server admins without needing to have access to
> each site's own database.
>
> Clues, anyone?
>
> Mark
> --
> Sent from my Babbage Difference Engine
> http://mark.goodge.co.uk
> http://www.ratemyairport.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=john.daisley@butterflysys tems.co.uk
>
>


--
John Daisley
Butterfly Information Systems

Microsoft SQL Server Database Administrator
Certified MySQL 5 Database Administrator & Developer
Cognos BI Developer \ Administrator

Available for short & long term contracts

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--001517570b2a5b2b8804a4cc2944--

Re: Allowing all users to access a specified database

While it does not support wildcards, it does support an empty username field. A subtle distinction :-) Simply grant access to ''@'%'.

----- Original Message -----
> From: "Mark Goodge"
> To: mysql@lists.mysql.com
> Sent: Friday, 3 June, 2011 12:03:56 PM
> Subject: Allowing all users to access a specified database
>
> Hi,
>
> I have a database server with multiple users and multiple databases.
> I
> have a situation where I want to allow any user to connect to a
> specified database.
>
> Unfortunately, the documentation has this to say:
>
> "MySQL does not support wildcards in user names."
>
> Which means, that, although I can use this syntax to grant access for
> a
> user to all databases:
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'someuser'@'%';
>
> I can't do something like this to grant all users access to a
> database:
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';
>
> So, my question is this: How can I allow any user to use a specific
> database, without individually granting them all access?
>
> The reason I want to be able to do this is that the server hosts a
> large
> number of individual ecommerce sites running on the same core
> software.
> Each site has its own database for products, customer data, etc,
> which
> is only accessible to that site. But each site also needs to be able
> to
> access a single, server-wide database which both stores configuration
> information for the software that the sites run on and store usage
> statistics for each site which can be accessed by the server admins
> without needing to have access to each site's own database.
>
> Clues, anyone?
>
> Mark

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Allowing all users to access a specified database

On 03/06/2011 11:24, John Daisley wrote:
> The reason
>
> *GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';*
> *
> *
> does not work is because that command would be suicidal in terms of
> security.
>
> If you are hosting a large number of ecommerce sites and granting any user
> access to those databases then you would want security to be far tighter.
> Allowing that sort of access is about as secure as publishing the data on
> facebook.

That's not a problem in this case - the data is *intended* to be shared
between all users of the system. It's data required by the software that
the sites run on - which is simple, non-confidential stuff like basic
settings as well as data which each site deliberately exports for
copying by the others. The end users are not different organisations,
they are different trading divisions within the same organisation.

> What version of MySQL are you using?

5.0.7

Mark
--
Sent from my Babbage Difference Engine
http://mark.goodge.co.uk
http://www.ratemyairport.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Allowing all users to access a specified database

--000e0cd25cae33152e04a4cd1c42
Content-Type: text/plain; charset=ISO-8859-1

You could easily write a script which queries the mysql grant tables and
grants the required permissions to every user on your system. Granting
access the way you were suggesting gives the whole world access not just
users of the system.

That said as that is a shared database which all users are dependent upon I
would consider using stored procedures to do the tasks your users need to
carry out on the shared database. Then you don't have to grant users access
at all.

To use stored procedures create a `stored procedure` user and grant only
that user appropriate access permissions to the database. Then create stored
procedures to do the tasks your users would normally do and within the
stored procedure set DEFINER to the `stored procedure` user you created and
specify SQL SECURITY DEFINER. Finally grant your other users EXECUTE
permissions for the stored procedures.

Users get the access they need and security is a little tighter :)

You should also find Stored Procedures execute a little faster :)


On 3 June 2011 11:47, Mark Goodge wrote:

> On 03/06/2011 11:24, John Daisley wrote:
>
>> The reason
>>
>> *GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';*
>> *
>> *
>> does not work is because that command would be suicidal in terms of
>> security.
>>
>> If you are hosting a large number of ecommerce sites and granting any user
>> access to those databases then you would want security to be far tighter.
>> Allowing that sort of access is about as secure as publishing the data on
>> facebook.
>>
>
> That's not a problem in this case - the data is *intended* to be shared
> between all users of the system. It's data required by the software that the
> sites run on - which is simple, non-confidential stuff like basic settings
> as well as data which each site deliberately exports for copying by the
> others. The end users are not different organisations, they are different
> trading divisions within the same organisation.
>
>
> What version of MySQL are you using?
>>
>
> 5.0.7
>
>
> Mark
> --
> Sent from my Babbage Difference Engine
> http://mark.goodge.co.uk
> http://www.ratemyairport.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=john.daisley@butterflysys tems.co.uk
>
>


--
John Daisley
Butterfly Information Systems

Microsoft SQL Server Database Administrator
Certified MySQL 5 Database Administrator & Developer
Cognos BI Developer \ Administrator

Available for short & long term contracts

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--000e0cd25cae33152e04a4cd1c42--

Re: Allowing all users to access a specified database

Granting to ''@'%' and putting a password on that 'user' works fine - the password stops just anyone from getting in; but the emtpy username in the db table should get picked up fine, as authentication and authorisation are disctinct in MySQL.


----- Original Message -----
> From: "John Daisley"
> To: "mysql"
> Sent: Friday, 3 June, 2011 1:32:35 PM
> Subject: Re: Allowing all users to access a specified database
>
> You could easily write a script which queries the mysql grant tables
> and
> grants the required permissions to every user on your system.
> Granting
> access the way you were suggesting gives the whole world access not
> just
> users of the system.
>
> That said as that is a shared database which all users are dependent
> upon I
> would consider using stored procedures to do the tasks your users
> need to
> carry out on the shared database. Then you don't have to grant users
> access
> at all.
>
> To use stored procedures create a `stored procedure` user and grant
> only
> that user appropriate access permissions to the database. Then create
> stored
> procedures to do the tasks your users would normally do and within
> the
> stored procedure set DEFINER to the `stored procedure` user you
> created and
> specify SQL SECURITY DEFINER. Finally grant your other users EXECUTE
> permissions for the stored procedures.
>
> Users get the access they need and security is a little tighter :)
>
> You should also find Stored Procedures execute a little faster :)
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org