Database user logons/session - expanded thoughts/suggestion

Hi there,

In one of my earlier posts I raised the issue of the need for individual user logons to the back end SQL database in an application rather than the current single generic logon by the host for all sessions. This is required in many, perhaps even the majority, of SQL based applications in the field for a number of reasons including triggers, procedures and views that rely on the SQL logon/session to determine business logic in existing apps, security of tables, columns, rows etc., again utilizing the SQL logon/session, also from an audit point of view both from traceability aspects and in some sites in fact the use of generic logons are forbidden entirely particularly in the government and security forces area for instance.

The initial answer to this issue was the suggestion to create a Custom Data Connector to get around it whereby the developer would write a connector to duplicate the functionality of the in-built sync database transactions but wrap them within a user logon specific connection. But there are a number of problems with this which whilst not insurmountable do beg the question of whether there's a better solution - for example :

1) The Custom Data Connector for say MS-SQL would end up being written over and over by different customers/developers as it's going to be needed a lot.
2) Inherently any Custom Data Connector is going to be less efficient than the native access provided as standard as its code calling code in some way and although this may well be insignificant it's still worth noting.
3) More importantly than 2), there's definite scope for possibilities of something 'breaking' with a Custom Data Connector written outside of DragonRAD's code or the interaction between it and the native code and that means there's something else that could wrong which is always best avoided.
4) Regardless of the fact it would be written by and the responsibility of the customer inevitably there will be an impact on support calls and time required etc., if and when things aren't working in anyway either in or between the Customer Data Connector and the main package.
5) Many customers may well not have the resources to develop the Custom Data Connector as one of DragonRAD's selling points is the ease of use of creating apps without the need for specialist technical folks, something which writing a Custom Data Connector would definitely need.
6) A lot of companies might also be adverse to buying a product which doesn't provide what might be considered a vital part of it and finding they have to effectively write part of it themselves - from their view point that is!

There are probably other reasons as well why perhaps the Custom Data Connector is not ideal so what's the answer? ..

Well from what I can see and past experience it should be relatively simple to implement the ability to have user rather than generic session in the core product as an optional choice when creating a project. So not replacing the current generic method of database access but merely adding in a flag or switch that the developer chooses when in the project to say they want use logins to the database.

The choice could be added say in the "LoginMode" if a "CustomApp" has been chosen as an additional choice within that list to say this app should use user login to the backend. Putting it here would also ensure that the app is a user logon form app as well so hence the user information is available which is already coded for as this info is passed to the sync routines already in this case.

If the project is designated as being user login rather than generic, then rather than just using the existing SQL connection the update/select sync transaction would be wrapped in a new user connection each time which, depending on the internal current code structure may well be just some "IF USERLOGIN, OPEN CONNECTION ELSE USE GENERIC ONE etc" modifications to get it working.

In terms of the multiple connection/sessions in SQL this is standard and expected anyway in a multiple user SQL app and .NET's automatic connection pooling is pretty efficient anyway so no problems there as long as the developer and SQL DBA are aware of it.

Obviously I don't know the way the current internal modules hang together or exactly how the logic has been coded but it should be a lot simpler to add this option internally as opposed to the external developer(s) having to write separate Custom Data Connectors themselves.

Depending on the current structure of the code internally it maybe that this change could be done in such a way as it covers all of the main db's (MS-SQL, Oracle, MySQL, etc) along with ODBC in one 'hit' so to speak or it might need to be done for each of them .. as another thought just come to mind perhaps the database type list itself could be used to dictate if user or generic by having one for each type maybe rather than using a project level flag? Keeping the user specific connection option within different database types.

I understand that it's not a 5 minute task to implement this option/feature but I think if it was done then DragonRAD having the ability to use either generic single logon or user specific multiple logons built into it as native would be a big plus from a sales perspective both from catching those sales it might miss without it and making it easier to get marginal sales where the Custom Data Connector development may be an issue in one of the above ways.

From my point of view I can say that the last 3 major projects I've worked on in the last two years using a similar SQL to BlackBerry solution just wouldn't have been possible without user logon to SQL for business/app logic and security reasons so I do believe it'd be a benefit to DragonRAD to have this.

Obviously well up for further discussion and brainstorming of any aspect of this so do feel free to get in touch to cover more and keen to hear your views on whether you think you will look at this?

Best regards,

Derek