> -----Original Message----- > From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] > Sent: Wednesday, June 16, 2010 2:48 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: RE: opening a server to generalized queries but not "too" far > > Daevid Vincent writes: > > > For the love of God and all that is holy, > > do NOT put the user/pass on the URL like that!!!!!! > What's so unholy (or even unwise) about it?
Oh my goodness, where to begin... Well barring the fact that it'll be in the user's cache and browser history, you are sending this information in plain text across the internet, open to ANYONE on the way who wishes to packet sniff. > > Or use "mod_auth_mysql" to maintain your 'authorized' > users to your page. > Why is this so much better? > In my case it's worse cause I don't want this to be interactive. > I want to install something on a user's machine that will access his > data without him having to do anything. The url is to be used by > a program, not a person on a browser. One has nothing to do with the other. Mod_auth_mysql will serve as the user/password prompt and authentication between the user and the page you're creating. It is just an easy way for YOU to manage the users. If you have their user/password in a database -- presumably you already do, then leverage that. That's all this apache module will do. It's no different than you having to use $_GET['user'] and $_GET['password'] from your URL, querying the DB and granting some data back, except you've removed the horrific thought of forcing the user to expose their vitals in plain text in the URL/GET/POST/whatever. > > And as Adam beat me to, use a VIEW to expose ONLY the > columns and joins you > > want. This is also a good time to normalize the data and > column names so > > that you don't expose what their internal names are. > So far I don't like this solution. Interesting. Perhaps you don't understand it? A view seems to suit your needs very well from what you've explained. It allows a READ ONLY access to your data. It prevents exposing your column/table names. It normalizes your data so your customers get it without having to jump through hoops and knowing intimate details about your schema. Pretty much this is why VIEWs were invented. > > But also has he said, I don't see what you're trying to > accomplish. If > I'm trying to let a large number of users each access his own data > and not everyone else's in a very flexible way, in particular, > allowing selection using functions, aggregations, filters. Right. And for YOU to code all that up in some fancy web GUI is going to be a nightmare and will never be all things to all people that you desire. So you either give people a GUI front end with select boxes and parameters, OR you give them some direct SQL access to a VIEW, OR you give them some XML/JSON/Excel/CVS/whatever output that they can then pull into their tool of choice to manipulate. Why not provide daily SQL dumps of their normalized data to your users and let them run their reports -- if they're trying to run SQL queries themselves? > > someone is technically literate to format SQL statements, > then just give > > them a read-only account to the mysql (or view) directly. > Let them use > > their own GUI tool like SQLYog or whatever -- it will be > far more robust > > than anything you can write yourself. > In this case there may be a lot of users but the queries are likely to > be written by a small number. > > > If you're trying to do some "reports", then just code up > the reports and > > use select boxes for the options you want someone to > choose. Use jQuery and > > table_sorter plugin and you're done. > I can't predict what options will be needed. Well you don't have to. You just make select boxes and such for each field that someone would want to select on and various filters. I bet you can cover 80% of your users with that if not all of them. > And this seems much easier. Oh grasshopper... So Naïve. :) Here's what I think will happen -- and you can tell me "nah nah nah nah" in 2 years when you finish writing this uber-tool that does all this fancy SQL magic manipulation. It will either still fall short of what you wanted it to do b/c it's too complicated to code, OR it will be too complicated to use and nobody will understand it enough to use it. But hey, more power to ya Don if you want to tackle it, go ahead. I would suggest maybe looking at http://www.phpmyadmin.net Daevid. http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
