Suggestion for Authentication

sjerinic · 2014-03-14 09:08:15

Hi AB!

In one our project we have SQLServer table with username and password data which users use for connect to our server via TSQLRestServerFullMemory. New users are added from the Web app which is independent of Mormot and for this reason I have added to timer every 120 seconds Mormot server reads sqlserver table and update existing user data and add new users like

EnterCriticalSection
try
   ReadUserTable
   LoopFromRecordSet and if UserID is exists Update it, else Add
finally
  UpdateToFile all users and groups
  LeaveCriticalSection
end

Does this concept is good or you have a suggestion for a better solution because we have a problem with the loggin process. With 200+ users in the database, some of them can not connect to Mormot server although the data is good.

ab · 2014-03-14 09:31:01

Why not just link directly to the external MSSQLServer table via ORM?
I suspect this is because the corresponding MSSQLServer tables are a bit complex...
You may add a trigger in your SQLServer database to populate two dedicated tables, with the TSQLAuthUSer/TSQLAuthGroup tables.
Then you could link to the User/Group tables with a cache...

In all cases, updating every 120 seconds should not harm the performance.
Are you sure this is the bottleneck?
Or is it because 120 second is too big an interval, so just-modified data is not replicated to the mORMot's tables?

sjerinic · 2014-03-14 10:00:33

I don't know is it bottleneck or some my mistake. Such a user can not connect to after a few hours. Then I have to reset the server.

My idea is that TSQLRestServerAuthentication.ClientSetUser function read directly from the SQLServer User table when a user wants to connect, but I don't know how to do it. Is this possible?
Is that what you mean when you say: "just link directly to the external MSSQLServer table via ORM"? Do you have any example?

ab · 2014-03-14 10:30:38

What I meant by "just link directly to the external MSSQLServer table via ORM" was to call VirtualTableExternalRegister() to access the table on the remote MS SQL server, not in-memory.

AFAIK ClientSetUser() is for the client-side, so I do not understand well your proposal here.

What you can do is override the following method for you own authentication scheme:

  /// abstract class used to implement server-side authentication in TSQLRestServer
  // - inherit from this class to implement expected authentication scheme
  TSQLRestServerAuthentication = class
  protected
   (....)
    /// retrieve an User instance from its logon name
    // - should return nil if not found
    // - this default implementation will retrieve it from ORM
    // - you can override this method and return an on-the-fly created value
    // as a TSQLRestServer.SQLAuthUserClass instance (i.e. not persisted
    // in database nor retrieved by ORM), but the resulting TSQLAuthUser
    // must have its ID and LogonName properties set with unique values (which
    // will be used to identify it for a later call and session owner
    // identification), and its GroupRights property must contain a REAL
    // TSQLAuthGroup instance for fast retrieval in TSQLRestServer.URI 
    function GetUser(Ctxt: TSQLRestServerURIContext;
      const aUserName: RawUTF8): TSQLAuthUser; virtual;
   (....)

tech · 2014-03-14 10:37:28

Hi,

this is an example for connecting to MSSQL via OLE and virtual table :

uses SynOleDB, ....;
...
var aPropsMS : TOleDBMSSQL2008ConnectionProperties;
   Model : TSQLModel;
...
aPropsMS := TOleDBMSSQL2008ConnectionProperties.Create(server, database, user, pwd);
Model := TSQLModel.Create([TSQLCLASS]);

VirtualTableExternalRegister(Model, TSQLCLASS, aPropsMS, 'MS_TABLE_NAME');

CreateMissingTables(0);

You can explore the code of 15 - External DB performance.

Ooops I did not saw the post of AB.

Last edited by tech (2014-03-14 10:38:52)

sjerinic · 2014-03-14 11:52:40

If I understand it, the solution is:
- make in MSSQLSERVER tables TSQLAuthUser and TSQLAuthGroup
- make trigger on our table Users which update TSQLAuthUser table
- in server use VirtualTableExternalRegister
- change TSQLRestServerFullMemory into TSQLRestServerDB?

ab · 2014-03-14 14:31:55

Or... override the TSQLRestServerAuthentication.GetUser() method with your own implementation.
See the remarks above.
http://synopse.info/forum/viewtopic.php?pid=9978#p9978

sjerinic · 2014-03-15 20:29:19

Thanks AB!

I would like to try to override GetUser, but there do not see the password. My idea was to receive a username and password and check in MSSQLServer is user valid or not.

Also I found the problem with update TSQLAuthUser table.
For example, if the user decides to change its username (yes, users have that options...), update changes the data in the TSQLAuthUser table (UpdateToFile shows me the changed data), but he can not connect with new or old username. If he decides to return back the old username then he can connect with the old username. In same time, if user decides to change password, everything is OK.

ab · 2014-03-15 20:39:21

Ensure you read the declaration and documentation (as comments in the interface) of TSQLRestServerAuthentication class and all its inherited variants.

In addition to GetUser(), you have the Auth() method will do most of the work.

See for instance how TSQLRestServerAuthenticationDefault.Auth() is implemented.
You can override GetUser() and CheckPassword() to customize this default authentication mode with you custom password check content.

sjerinic · 2014-03-17 12:19:12

Done and works perfectly.

Thanks AB!

mORMot Open Source

#1 2014-03-14 09:08:15

Suggestion for Authentication

#2 2014-03-14 09:31:01

Re: Suggestion for Authentication

#3 2014-03-14 10:00:33

Re: Suggestion for Authentication

#4 2014-03-14 10:30:38

Re: Suggestion for Authentication

#5 2014-03-14 10:37:28

Re: Suggestion for Authentication

#6 2014-03-14 11:52:40

Re: Suggestion for Authentication

#7 2014-03-14 14:31:55

Re: Suggestion for Authentication

#8 2014-03-15 20:29:19

Re: Suggestion for Authentication

#9 2014-03-15 20:39:21

Re: Suggestion for Authentication

#10 2014-03-17 12:19:12

Re: Suggestion for Authentication

Board footer