Re: [Sqlrelay-discussion] Query filter per user
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Query filter per user
|
From: David M. <dav...@fi...> - 2010-04-21 15:42:40
|
Hello Matias, I'm glad you got it working. You can have multiple queries per router instance. For instance, you can add another query tag with a pattern for updates to that same router instance, rather than having to create a separate router instance for updates. But you'd need to create a separate instance for each client. I've recently gotten requests for a variety of enhanced routing features, including several user-based routing features. They probably won't make it into the very next release, 0.42, but are on the list for 0.43. David Muse dav...@fi... On 04/14/2010 10:40 AM, Matias Rollan wrote: > Hola! > > I created the following sqlrelay.conf and so far is working as expected. Only filtering select where client_id=2 for the sqlrelay "clientid2" user. > > <?xml version="1.0"?> > <!DOCTYPE instances SYSTEM "sqlrelay.dtd"> > > <instances> > > <!-- This instance maintains connections to a MySQL database --> > <instance id="database" port="" socket="/tmp/database.socket" dbase="mysql" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1"> > <users> > <user user="mysqluser" password="mysqlpassword"/> > </users> > <connections> > <connection connectionid="db" string="user=mysqluser;password=mysqlpassword;host=localhost;db=database;" metric="1" behindloadbalancer="no"/> > </connections> > </instance> > > > <!-- This instance sends queries to the > mysqldb instance after filtering them --> > <instance id="router" port="9001" socket="/tmp/router.socket" dbase="router" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1"> > <users> > <user user="clientid2" password="clientid2"/> > </users> > <connections> > </connections> > <router> > <!-- send queries to "mysqldb" which match the > specified patterns --> > <route host="" port="" socket="/tmp/database.socket" user="mysqluser" password="mysqlpassword"> > <!-- allow selects with a where clause --> > <query pattern="^\s*select\s+.*\s+from\s+.*\s+where\s+client_id=2"/> > </route> > <!-- all other queries will be filtered out --> > <filter> > <query pattern=".*"/> > </filter> > </router> > </instance> > > > </instances> > > I only found that that the dbase="router" wasn't working because the "router" value wasn't listed in sqlrelay.dtd . I added it to sqlrelay.dtd file and now is working fine. > > The sqlrelay version that I am currently using is: > ii sqlrelay 1:0.39.4-6ubuntu1 Database connection pooling, proxying and lo > > > Now I guess only need to create one entry per "client" to solve the problem I explained in the previous email but I am wondering if there's a better way to achieve this than creating one instance per user and query pattern to solve my problem of filtering each SQLrelay user to a certain query form ? > > Thanks in advance, > > -- > Matias Rollan > <cy...@sa...> > > On Tue, Apr 13, 2010 at 04:49:59PM -0300, Matias Rollan wrote: > >> Hola! >> >> I am new to SQLrelay and I just installed it and connected it to MySQL for testing purposes as I think it may solve my problem. >> >> >> I need to find a way to filter queries that are currently being executed from differents mysql clients so each client can only modify the rows that belong to them and not the others. Right now every client is connecting to a MySQL server with the same user/passwd so I have no control on how to limit each client to certains rows from a table. >> >> An example to try to clarify this: >> >> table: M >> fields: a,b,c,d,client >> >> I need that client 1 can only "UPDATE" the rows from table M WHERE client=1 and so on. >> >> Since MySQL has no permissions per row I think that I can use SQLrelay in the middle, connecting each "client" to SQLrelay to handle the SQL query permissions at the SQLrelay level. >> >> The clients queries are written the following way: >> >> "UPDATE M set field=value WHERE client=$client" >> >> I was thinking that I could create one SQLrelay user per client and only allow the queries with the form written above. Is this possible? Do I have to add one user per client with the query that is allowed for that user? >> >> Any recommendation would be highly appreciated. >> >> Thanks in advance, >> >> -- >> Matias Rollan >> <cy...@sa...> >> >> >> >> ------------------------------------------------------------------------------ >> Download Intel® Parallel Studio Eval >> Try the new software tools for yourself. Speed compiling, find bugs >> proactively, and fine-tune applications for parallel performance. >> See why Intel Parallel Studio got high marks during beta. >> http://p.sf.net/sfu/intel-sw-dev >> _______________________________________________ >> Sqlrelay-discussion mailing list >> Sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion >> > > ------------------------------------------------------------------------------ > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > > > _______________________________________________________ > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting > http://www.doteasy.com > _______________________________________________________ Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting http://www.doteasy.com |