No one probably wants to go through the trouble to code this solution.... but it is possible to use MySQL Proxy to filter the SQL statements and results.
MySQL Proxy sits in between MySQL Server and the MySQL Client. It can read queries, modify them, send queries to the server or deny them all together, and even read results and modify them as well, or deny the results to be sent back to the client. Perhaps if you can resolve to a less complicated set up, but still lean towards the VPD idea, MySQL Proxy might work for you. I just wanted to throw this solution out in case it was useful. -RG Eric Frazier wrote: > Mohammad wrk wrote: >> Hi Eric, >> >> In the case of a "yes" answer to the second question below, can't we >> still use something like VPD (Virtual Private Database) in MySQL? >> >> Thanks, >> >> Mohammad >> > > Hi, > > I don't know much about Oracle, but I looked this up. MySQL can't do it, > that I know of. Here is the final example of such a setup that I found > at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php > > CONNECT user1/[EMAIL PROTECTED]; > INSERT INTO schemaowner.user_data (column1, user_id) > VALUES('User1', 1); > INSERT INTO schemaowner.user_data (column1, user_id) > VALUES('User2',2); > COMMIT; > > CONNECT user2/[EMAIL PROTECTED] > INSERT INTO schemaowner.user_data (column1, user_id) > VALUES ('User 1', 1); > INSERT INTO schemaowner.user_data (column1, user_id) > VALUES ('User 2', 2); > COMMIT; > > CONNECT schemaowner/[EMAIL PROTECTED] > SELECT * FROM schemaowner.user_data; > CONNECT user1/[EMAIL PROTECTED]; > SELECT * FROM schemaowner.user_data; > CONNECT user2/[EMAIL PROTECTED] > SELECT * FROM schemaowner.user_data; > > Notice that: > > * When connected to USER1, only the first insert will work. > * When connected to USER2, only the second insert will work. > * The failing inserts produce the error: > ORA-28115: policy with check option violation > > > You can setup column level privileges on MySQL, but I wonder if it would > be buggy considering I have never heard of anyone doing this before. > Plus from what I understand the above example is a lot more than column > privileges. user1 can only insert data if the insert statement's data > sets user_id to 1, for example. Pretty cool, but scary in a way. I find > this much logic in the DB to be scary(esp if not well documented), but > then I use MySQL :) > > So as to which way you should go is most defiantly a matter of opinion I > think. But, going back to my opinion(which is not at all informed as to > all the details), if question 2 is a YES, then I would tend to go with > separate DBs. > > Thanks for the Oracle lesson :) > > Thanks, > > Eric > > > > > > > > >> ----- Original Message ---- >> From: Eric Frazier <[EMAIL PROTECTED]> >> To: Mohammad wrk <[EMAIL PROTECTED]> >> Cc: mysql@lists.mysql.com >> Sent: Monday, November 19, 2007 7:42:13 AM >> Subject: Re: Giant database vs unlimited databases >> >> Mohammad wrk wrote: >> > Hi, >> > >> > I'm working on a web 2.0 project that targeting small to medium >> size companies for providing business services. Companies simply >> register to the site and then start their business by loading their >> data, sharing and discussing them with others. >> > >> > The design/architectural decision now we are facing from database >> perspective is how we should store companies' specific data? One way >> is to put all of them in a single database and partition them by >> company-id and the other one is to create, on the fly, a new database >> per company . The justification for the latter is that MySQL is not >> powerful enough (compare to Oracle or DB2) to handle large amount of >> data and concurrent users. >> > >> > I'm new to MySQL and don't know that much about it and this is why >> I'd like to discuss this concern here. >> > >> >> Funny, I thought you asked the question, should I separate my customers >> into their own databases, or use one big DB? Not MySQL sucks, Oracle is >> better. :) >> >> Issues I would ask about on this: >> >> 1. Is there a chance that given their separation, these DBs will ever >> diverge in design because of differences between customers? >> 2. Could they ever need to be separated for legal reasons? (like one bad >> query causing customer data be compromised) >> 3. Is there any other reason you may do something vastly different from >> one customer to another? >> >> If you answer yes to any of these, then you might be best off separating >> dbs. But, if you never want to, or expect for any of these things to >> happen, you will just be creating headaches for yourself. Backup, >> replication, and the need for cross DB queries, will all be a pain in >> comparison to a single DB. >> >> I am sure there is more to consider, but these are the points that come >> to my mind right away. >> >> Thanks, >> >> Eric >> >> >> ------------------------------------------------------------------------ >> Instant message from any web browser! Try the new * Yahoo! Canada >> Messenger for the Web BETA* >> <http://ca.messenger.yahoo.com/webmessengerpromo.php> > > > > ------------------------------------------------------------------------ > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
