Terry, You don't HAVE to use the extra layer of security of mySQL to restrict access on the basis of ip address as Tim said you can restrict usage via username/password on the queries as you do. But suppose exclusive writes on the one dsn are being made with cflock on the queries instead of read-only multiple requests on the one dsn, is not your app going to have traffic jams with multiple users waiting for exclusive access? There isn't much written about this but I`m guessing multiple dsn's for each database would avoid this problem as well? Instead of a-b-c-d-dsn queue to update on the one dsn, you could have simultaneous updates a-dsn1,b-dsn2,c-dsn3,d-dsn4 or maybe to limit the possible damage, just have 2 dsn's? Or even mix the 2 approaches. Also another approach would be to use multiple instances of the application server if using MX and set up different users on a different application instance.http://www.macromedia.com/devnet/mx/coldfusion/j2ee/articles/multip le.html.
Basically as well as being able to restrict users by ip you give yourself for example the option of low permission users only requiring read-only access given one dsn and high permission exclusive access using another dsn cheers Colm 'food for thought, get a plate, it's hot,'(Victor, Big Brother:) -----Original Message----- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: 23 July 2004 18:10 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] CF and MySQL premissions Actually, guys, in order to restrict permissions, it is only necessary to have the DSN set as the root (all permissions) user - or whatever you want top level access to be - and restrict usage via username/password on the queries. I've just tried this out operating the username/password as session variables set at login (or not, as in the case of the basic 'select-only' user), and it works! Now I'm starting to wonder why I went through that exercise in the first place.... Cheers Terry ----- Original Message ----- > Thanks for that, Tim > > > > In CFAdmin, when the database is registered, you give a username > > > and password, and the only one which seems to work here is the > > > root user and password (ie superuser) set in MySQL, which is fair > > > enough if you want to grant all users on your site access to all > > > db functions. But naturally, one doesn't want to do that. > > > > I'm not entirely clear what you're trying to do -- are you trying to > > create separate MySQL users with limited permissions? If that's the > > case then it certainly is possible, you just need to create the user > > and > > assign the appropriate access rights to the database. I go one step > > further and limit access via IP address. > > > > If you're trying to use different access permissions depending on > > which > > user is logged in to the front end (CF) app then you're going to have > > to > > set up multiple DSNs - one for each "security level". > > This is what I was actually trying to acheive, but even after creating > the required permissions for the other (limited) users in MySQL, I seem > unable to create separate DSNs in CFAdmin - if I try to create others, > using a different DSN, but the same database, I get bounced with a 'Is > there really a server on 3306?' message. > > The permissions work. If I log into my MySQL GUI (I'm using SQLyog) > with the 'webuser' and his password, I can't insert (as expected), but > can select. > > > > > Create the three users in MySQL with the appropriate access > > permissions, > > then create three separate DSNs in the CFAdmin. Then you'll somehow > > have to switch between DSNs depending on which user is required to > > access the system. > > > > Tim. > > Cheers > Terry -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED] --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0430-3, 23/07/2004 Tested on: 25/07/2004 17:15:25 avast! is copyright (c) 2000-2003 ALWIL Software. http://www.avast.com -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
