Board index » off-topic » Login and security on 3-tier DB app
Stéphane
Delphi Developer |
Stéphane
Delphi Developer |
Login and security on 3-tier DB app2005-07-23 12:17:11 AM off-topic12 In a 3-tier DB application, how can I respect the user security implemented in the DB? For example: - how can I use individual passwords since the app server's DB connection is shared? - is there a way of executing SQL statements using a user's given DB role? TIA, Stephane |
Craig Stuntz [TeamB]
Delphi Developer |
2005-07-23 01:30:54 AM
Re:Login and security on 3-tier DB app
Stiphane wrote:
Quote- how can I use individual passwords since the app server's DB other scheme, such as a session ID. -- Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz All the great TeamB service you've come to expect plus (New!) Irish Tin Whistle tips: learningtowhistle.blogspot.com |
Stéphane
Delphi Developer |
2005-07-23 03:07:18 AM
Re:Login and security on 3-tier DB app
Thanks for your reply Graig.
Impersonation sounds promissing. However, I've been programmting client/server on Interbase/Firebird for years and have never heard of this feature. A search of the documentation and web hasn't turned up anything either. So I'd appreciate any info or lead you can give me. By 'impersonation', you mean an SQL can be executed using different db roles without changing the connection to the DB? This 3-tier project will use dbExpress on a LAN. The RDBMS will be Firebird in some installations, MS-SQL Server in others. Thanks again, Stephane "Craig Stuntz [TeamB]" < XXXX@XXXXX.COM [a.k.a. acm.org]>wrote in message news:42e12d4e$ XXXX@XXXXX.COM ... QuoteStiphane wrote: {smallsort} |
Joe Bain
Delphi Developer |
2005-07-23 03:10:32 AM
Re:Login and security on 3-tier DB app
Stiphane wrote:
QuoteIn a 3-tier DB application, how can I respect the user security Delete, and Insert you can use the BeforeUpdateRecord event on the provider. Just raise an exception in it and that change does not goto the DB. -------------- Joe Bain www.iegsoftware.com |
Craig Stuntz [TeamB]
Delphi Developer |
2005-07-23 03:39:41 AM
Re:Login and security on 3-tier DB app
Stiphane wrote:
QuoteImpersonation sounds promissing. However, I've been programmting QuoteBy 'impersonation', you mean an SQL can be executed using different connections, depending on your needs. -- Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz Useful articles about InterBase development: blogs.teamb.com/craigstuntz/category/21.aspx |
Stéphane
Delphi Developer |
2005-07-23 05:37:41 AM
Re:Login and security on 3-tier DB appQuoteFor IB I'd suggest using the session ID scheme |
Stéphane
Delphi Developer |
2005-07-23 05:40:49 AM
Re:Login and security on 3-tier DB app
Does that means you need the client to pass the user's id with every call in
order for the server to checkhis/her security rights? QuoteI like to roll my own security in my Datasnap apps. I use the the |
Joe Bain
Delphi Developer |
2005-07-23 05:45:44 AM
Re:Login and security on 3-tier DB app
Stiphane wrote:
QuoteDoes that means you need the client to pass the user's id with every Joe Bain www.iegsoftware.com |
Craig Stuntz [TeamB]
Delphi Developer |
2005-07-25 08:42:08 PM
Re:Login and security on 3-tier DB app
Stiphane wrote:
Quote>For IB I'd suggest using the session ID scheme lingwithUserLogins Basically you use a session table in lieu of IB user accounts. This is advantageous for systems like online banking where the number of user accounts would become impractical anyway. You write select / update / insert / delete procs which accept a session ID as an argument and return / edit the correct records based on user access. You'll need to pass the session ID with each request you make to the app server. The ID is generated based on the username/password, which don't correspond to an IB user account. -- Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz Please read and follow Borland's rules for the user of their news server: info.borland.com/newsgroups/guide.html |
Syd Bee
Delphi Developer |
2005-07-26 08:09:19 AM
Re:Login and security on 3-tier DB app
Hi Stephane,
As Craig Stuntz has mentioned - one of the options is to avoid using connection pooling. I use BCB6, but I believe that it's similar in Delphi: open the generated 'impl' header file, then set regPooled to false, eg: static HRESULT WINAPI UpdateRegistry(BOOL bRegister) { TRemoteDataModuleRegistrar regObj( GetObjectCLSID(), GetProgID(), GetDescription() ); // Disable these flags in order to disable use by socket // or web connections. Also set other flags to configure // the behavior of your application server. // For more information, see atlmod.h and atlvcl.cpp. regObj.Singleton = false; regObj.EnableWeb = true; regObj.EnableSocket = true; // Connection Pooling is set by default. Add the // following line to avoid connection pooling: regObj.RegisterPooled = false; return regObj.UpdateRegistry(bRegister); } Then open your type library editor and add a user logon method, eg: //--------------------------------------------------------------------------- // Method: UserLogon // Client must call this to set the ADOConnection1::ConnectionString. // This will be used throughout the client's session. // It is imperative that the AppServer has // regObj.RegisterPooled = FALSE for this to work. //--------------------------------------------------------------------------- STDMETHODIMP TrdmSomeAppServerImpl::UserLogon( BSTR Username, BSTR Password, BSTR Workstation, BSTR Application, BSTR Version, BSTR ClientMAC) { // Code here to return E_FAIL if Application/Version not compatible // or client MAC address not in list of valid MAC addresses. AnsiString s = "Some ADOConnection String using Username/Password"; try { m_DataModule->ADOConnection1->Connected = false; m_DataModule->ADOConnection1->ConnectionString = s; m_DataModule->ADOConnection1->Connected = true; } catch (Exception &e) { // Log the error and return E_FAIL... return E_FAIL; } return S_OK; } From your client you can then use the AppServer property of your connection component. This differs a lot between Delphi and BCB, but I believe in Delphi you can just do a: WebConnection1.AppServer.UserLogin(...); Regards, Sydney Delieu Stéphane wrote: QuoteIn a 3-tier DB application, how can I respect the user security implemented |
Craig Stuntz [TeamB]
Delphi Developer |
2005-07-26 08:25:02 PM
Re:Login and security on 3-tier DB app
Syd Bee wrote:
QuoteAs Craig Stuntz has mentioned - one of the options is to avoid using -- Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz Useful articles about InterBase development: blogs.teamb.com/craigstuntz/category/21.aspx |
Stéphane
Delphi Developer |
2005-07-26 11:13:54 PM
Re:Login and security on 3-tier DB app
Thanks so much for taking the time to give a complete answer Sydney.
I'm using BCB6 also :-) The documentation says the RDMs are not pooled by default. So is calling your UpdateRegistry() really needed? And what about the RDMs that contain the queries accessing the DB connection, can they be pooled? "Syd Bee" < XXXX@XXXXX.COM >wrote in message QuoteHi Stephane, |
Stéphane
Delphi Developer |
2005-07-26 11:15:35 PM
Re:Login and security on 3-tier DB app
VERY helpful link.
Thx "Craig Stuntz [TeamB]" < XXXX@XXXXX.COM [a.k.a. acm.org]>wrote in message news:42e4de20$ XXXX@XXXXX.COM ... QuoteStiphane wrote: |
Craig Stuntz [TeamB]
Delphi Developer |
2005-07-26 11:40:33 PM
Re:Login and security on 3-tier DB app
Stiphane wrote:
QuoteThe documentation says the RDMs are not pooled by default. -- Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt your DBs! Open Edition users, get 6.0.1.6 from mers.com |
Stéphane
Delphi Developer |
2005-07-27 04:01:10 AM
Re:Login and security on 3-tier DB app
In BCB6, the remote data module wizard doesn't have an 'Instancing' option
like Delphi6. How I programmatically set an RDM's Instancing to Internal in BCB6 to prevent an external COM client from creating it? "Stéphane" < XXXX@XXXXX.COM >wrote in message QuoteVERY helpful link. |