Alpha Video Training
Results 1 to 6 of 6

Thread: Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

  1. #1
    Member rogerrabbit222's Avatar
    Real Name
    Kanishka "KayBee" Basak
    Join Date
    Mar 2009
    Location
    Pune, India
    Posts
    53

    Default Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

    Hey Guys,

    I have a Multi-Tenant Web Application scenario where I need to keep a separated Primary DB which will ONLY store the Alpha Five Framework Security tables and a table that holds the reference connection strings to all my Tenant DBs.

    The rest of the tables needed for the application will be in their respective Tenant databases.

    After a successful login, the websecurity settings will redirect me to an intermediate page which will identify the Tenant DB that the logged in user belongs to (by running a bunch of other queries) and then apply the concept of Dynamic Connection Strings introduced by alpha in V10.5 ( http://wiki.alphasoftware.com/Buildi...+Strings+V10.5 ).

    Now, I have reached the point where I am identifying the User and getting the DB instance for the Tenant and applying the scripts needed to the doing the dynamic connection string change. After that I redirect to a A5W page which holds a simple grid. The grid does not load and the error that is thrown clearly indicates that I am not connected to the target DB and its table.

    Has anyone worked with Dynamic Connection Strings on V12 ?
    Is it doable?
    KayBee

  2. #2
    Member
    Real Name
    Garth McConnochie
    Join Date
    May 2009
    Location
    UK
    Posts
    138

    Default Re: Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

    Hi KayBee

    I do this in an application I use for administering 20 different application all connecting to different databases

    Here I have my web security tables in 1 database. I also have a table the defines the connection strings for all the other databases. When I login I use a defined connection string pointing at the database that contains this data.

    When I select the database to work on I retrieve the connection string to connect to that database. I set Session.__protected__BSAdmin this is the underlying session variable that alpha resolves the DynamicConnection_BSADMIN alphaADO sql named connection to.

    the way you set these up is the BSAdmin part of the string is relates to the same part in the DynamicConnection_BSADMIN

    grids, UX dialogs all resolve this named connector to the session variable. to use this approach in xbasic open the database connection use something like this...

    dim dbconn as SQL::Connection
    IF a5SQLConnectionOpen(dbconn,"::Name::DynamicConnection_BSAdmin",session) = .f. THEN
    errv = "Unable to connect to selected database"
    error_generate( errv)
    END IF

    for further info the help has reasonable detail on the subject.... search for dynamic connection

    good luck
    Garth

  3. #3
    Member rogerrabbit222's Avatar
    Real Name
    Kanishka "KayBee" Basak
    Join Date
    Mar 2009
    Location
    Pune, India
    Posts
    53

    Default Re: Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

    Thanks Garth, I got it to work.

    But here is another scenario -

    Lets say we have a different structure where my Primary DB has a single table that only that holds the reference connection strings to all my Tenant DBs. Each Tenant DBs will have its own set of the Alpha Five Framework Security tables and all other tables needed for the application.

    Each tenant will access the application using a sub domain URL such -
    http://tenant1.mywebapp.com/index.a5w
    http://tenant2.mywebapp.com/index.a5w
    and so on...

    My Index.a5w page will determine hostname URL using Request.Host and this value will be searched in the single table in the Primary DB to find out the Tenant DB. It will then do the change to dynamic connections and then redirect itself to the page holding the Login component. I tried doing this but it didn't work.

    Is it possible to change the target database before the Login component gets loaded ?
    KayBee

  4. #4
    Member
    Real Name
    Garth McConnochie
    Join Date
    May 2009
    Location
    UK
    Posts
    138

    Default Re: Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

    That looks more like you're talking about using some form of re-direction based on the URI you're connecting via

    my understanding is once you've got to the alpha appserver you're going to be running under alpha security if its enabled

    Garth

  5. #5
    Member rogerrabbit222's Avatar
    Real Name
    Kanishka "KayBee" Basak
    Join Date
    Mar 2009
    Location
    Pune, India
    Posts
    53

    Default Re: Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

    Garth,

    No re-directions here. There is a single code base in my A5Webroot.

    All of the URL point to a single Index.a5w in my A5Webroot. This done my defining the sub-domains to point to my A5Webroot folder on the server.
    KayBee

  6. #6
    Member
    Real Name
    Jason Martin
    Join Date
    Jan 2014
    Location
    Australia
    Posts
    21

    Default Re: Multi-Tenant Web Application where the Security Framework SQL tables are separated out - Is it doable?

    Hi Kaybee

    I did some testing of the multi-tenant features recently. I setup my security tables in a SQL database that was accessed by a dynamic connection string. My testing showed that even if you set the dynamic connection string to connect to a different database (one of the tenants) before attempting login, the application would still query the original database defined in the dynamic connection string for security login.

    I believe the reason why the application does not 'see' the change to the dynamic connection string is that the security system is accessing the SQL database indirectly via those dbf link tables that you have to setup connect security to a SQL database, not directly accessing the SQL database from the query string.

    Based on my testing, for a multi-tenant application, all the security tables must be in a single 'master' database but the remainder of your tables can be in a your individual tenant databases.

    Cheers

    Jason

Similar Threads

  1. Software as a Service (Saas) and Multi-tenant Architecture
    By ssmoore1 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 39
    Last Post: 05-07-2013, 01:12 PM
  2. Using SQL tables for security framework
    By Jay Talbott in forum Application Server Version 10 - Web/Browser Applications
    Replies: 7
    Last Post: 06-04-2011, 09:50 PM
  3. Multi-tenant Web app
    By blancolandau in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 12-20-2010, 09:49 PM
  4. Securty Framework with SQL tables
    By Howard G. Cornett in forum Application Server Version 8
    Replies: 3
    Last Post: 12-14-2007, 03:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •