Good food for thought, Colm. When the week starts in earnest, I'll take a closer look at what you've pointed out there.
The nature of the site (and its 'administrator') precludes restriction on the basis of IP address, so the option I've proposed would seem to have the least problems; though about 300 queries are all going to have to have username and password added to them in any case - there weren't ANY, can you believe? Cheers Terry ----- Original Message ----- > 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/mu > ltip > 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]
