List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:June 3 2011 11:32am
Subject:Re: Allowing all users to access a specified database
View as plain text  
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 <mark@stripped> 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=1
>
>


-- 
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@stripped

Thread
Allowing all users to access a specified databaseMark Goodge3 Jun
  • Re: Allowing all users to access a specified databaseJohn Daisley3 Jun
    • Re: Allowing all users to access a specified databaseMark Goodge3 Jun
      • Re: Allowing all users to access a specified databaseJohn Daisley3 Jun
        • Re: Allowing all users to access a specified databaseJohan De Meersman3 Jun
  • Re: Allowing all users to access a specified databaseJohan De Meersman3 Jun