Granting SELECT access to all

[MichaelHooker]

I have a website which needs to display photos to site visitors based on their selection from a table in my MySQL database, via a PHP form. I do not want them to have to provide a username and password to do this, the complication of a registration system is totally unnecessary. All the guidance I've read is based on setting up access privileges for named individuals and I can find no example of what I want to do.

Can someone please tell me if what follows is the right approach? The only option I can see is to grant select privileges to a generic user, say "Webuser", at the hostname where my database is located by my website provider with a generic password, say "Password":

GRANT SELECT ON db_name.table_name TO Webuser@host_name IDENTIFIED BY 'Password';

The PHP form then feeds these details in when the query is submitted so that a connection for the user is established and his query processed.

Will this work OK if two users with apparently the same ID send a query at the same time?

I've programmed in Delphi for a long time, used dBase and other engines, but all my database work has been single-user only and I've never had to worry about permissions before, so this is new to me and I'm having trouble adjusting. My website currently works with a csv file which an Activex built-in to Internet Explorer processes on the user's PC with DHTML - but of course that only works in Internet Explorer, and the csv has grown to the point where the download to the user's PC takes too long for comfort.

Thanks in advance

Michael Hooker

 

[elsenorjose]

Give username select access to your table from any hostname:

grant select on db_name.table_name to 'username' @ '%' identified by 'password';

If you do it your way, you'd have to define every host from which people could connect. Using the wildcard will allow anyone with the username/password combination to connect from any host.

As far as I know, there is no limit to how many people can connect to your DB using the same credentials. Just be sure you set your max_connections variable in your my.ini or my.cnf config file high enough so no one gets denied access due to too many connections.

 

[MichaelHooker]

Ah. Thank you very much. Shows how little I understand this internet business. I thought the hostname was the hostname of the system on which the php script was running, ie the webspace provider's SQL server. I know that doesn't make much sense given the construction "username@hostname" but this is certainly the hostname <I> have to use when accessing the db from a PHP script, or PHPMyAdmin.

However, it's reassuring that I managed to work out the right approach. I must be getting there, albeit slowly.

I've managed to set up the table structure, and after many attempts have persuaded PHPMyAdmin to import my data into it from the csv file I mentioned earlier, so now I'm ready to set the access permissions and try it out as an anonymous user. I had to write a small Delphi program to re-configure the csv file, as after I'd used Excel to "normalise" some of the entries, the Excel export mysteriously lost some trailing commas where the last field was blank. Took me a while to work out why PHPMyAdmin was telling me there weren't enough fields for some entries!

Thanks again, that's very helpful.

Michael Hooker

 

[MichaelHooker]

Well, that didn't work: it seems my website/MySQL provider hasn't given me permission to GRANT access to my own data! I'm waiting for their comments, but the alternative seems to be that everyone has to use my connection details, which means that my details have to be included somewhere in the public website directory, which is hardly secure.

That rather takes the query out of the scope of this Forum, but thanks anyway.

Michael Hooker

 

[elsenorjose]

You're welcome. Sorry it didn't work out.