mORMot Open Source

Dear List
  I have a requirement to allow potential Customers to register in order to gain User access to the mORMot Server.
  The Server uses the Service Oriented Architecture and SQLite.
  I thought of doing this as follows.
      1. Create a TSQLApplicant table that contains Customer Contact information for the "Applicant" (the person wishing to register).
      2. Allow the Applicant to submit their details from the "Client" using a SQLAuthUser login "Applicant".
      3. Alter the SQLAuthGroup to ensure that user "Applicant" only has WRITE Access to the TSQLApplicant table - and no other tables. (The Admin staff will view the Applicant records on a daily basis and  "register" (create a User login) for those Applicants who are approved.
  My test Server defines "RSvrDB.ServiceDefine(TServiceApplicant, [IUsrApplicant], sicShared);", which only contains one method - that being to create a new TSQLApplicant record.
  Security is enabled via "TSQLRestServerAuthenticationDefault".
  I've used the following code to update the TSQLAuthGroup table :
 
  ...
  begin
  tblIndex :=  Model.GetTableIndex(TSQLApplicant);
  AuthGrp := TSQLAuthGroup.Create(RSvrDB, 'Ident = ?', ['Applicant']);
    C := True; R := False; U := False; D := False; //Only Create enabled
    AuthGrp.Ident := 'Applicant';
    AuthGrp.SessionTimeout := 60;
    rights := AuthGrp.SQLAccessRights;
    rights.Edit(tblIndex, C, R, U, D);
    rights.AllowRemoteExecute := [reService];
    AuthGrp.SQLAccessRights := rights;
    grpID := RSvrDB.AddOrUpdate(AuthGrp, True);   //   .Add(AuthGrp);
    ...
    goes on to register the AuthUser "Applicant" login.
   
The AuthGroup now contains the value "2,0,3,0,0,0" for AccessRights.
(GetTableIndex(TSQLApplicant) returns an Index of 3).
And, the AuthUser entry for "Applicant" has the value 5 in the GroupRights column, which is the TID value for the "Applicant" AuthGroup entry.

But, when I log in to the Client as "Applicant", I'm not prevented from performing ALL the CRUD operations on all the tables, and I can perform all CRUD operations on Applicant (not just "Create", so the new AuthGroup setting seems to have no effect).
I've spent of lot of time reading the SAD 1.18 PDF and searching the Forum, but I just can't figure out what I'm doing wrong (going nuts).

So, I guess I have two questions :
  1. Is my approach to this requirement appropriate?
and
  2. If Yes, could someone point me to an example of how to perform this task? (None of the mORMot examples seem to do this)
 
Many thanks All. ANY advice would be deeply appreciated.

Horbs

I need to store some user variables for the life of a Server User Session.
These vars will be used to populate some records in Interface Service requests throughout the user session life cycle.
What I'd like to achieve is to identify some kind of event that gets triggered by mORMot after a successful authentication, and be able to hook that event from my code. I'd then want to store that info (User TID and AuthGroup etc) for the life of that User Session in a way that's accessible to Service Requests (I'm thinking of SessionGetUser to retrieve the info, but knowing how to and what to hook has me stumped).

Can anyone suggest a way to automatically capture the result of the authentication process so that I can fire off a method to store the required user info?

Many thanks

Horbs

I have a requirement that asks me to provide a user interface which allows (non technical) administrator staff to administer the User and Group tables (using SOA + SQLite). Ideally, I'd like to return the AccessRights in a grid with human readable values for the users to digest.
Is there any way I can return the bitfield results from GetSQLValues or GetAccessRights in a format where I can populate fields for each of the AccessRights values into columns (POSTSQL|SELECTSQL|Service|AuthR|AuthW|TablesR|TablesW) - perhaps as boolean values to populate check boxes, or Yes|No values?

And a couple of minor things :
1. The mormot Framework SAD describes security group layout as POST SQL SELECT SQL Auth R Auth W Tables R Tables W Services
Notes in mORMot.pas show                                                         POSTSQL SELECTSQL Service AuthR AuthW TablesR TablesW
** Note the position of "Service" (I'm assuming the mORMot.pas sequence is correct).
2. For "Admin" we have "VALUES ('Admin',10,'47,1-256,0,1-256,0,1-256,0,1-256,0')". I'm confused by the ",0," values. Are they just field separators ?

I'm currently implimenting Buisness Administration functionality to an almost completed Booking System project using mORMot and SQLite.

The Client uses a web javascript front end, and the Admin functions will be provided by a Delphi VCL Client (doing that now). I've just started to impliment User Management, and I'm confused regarding errors with the TSQLAuthGroup, TSQLAuthUser tables with respect to VirtualTableExternal and CreateMissingTables.

The server side code is :

  {1}  DBProps := TSQLDBSQLite3ConnectionProperties.Create('PopitBookings.DB3','','','');
  {2}  Model := CreateSampleModel;
  {3}  VirtualTableExternalRegisterAll(Model, DBProps);
  {4}  PopitDB := TSQLRestServerDB.Create(Model, 'PopitBookings.db3', False);
  {5}  PopitDB.CreateMissingTables; // error here
  ...

Prior to implementing the Auth tables, I could delete the Bookings.db3 file and, on running the server, a new database was created as expected.

After adding the Auth Tables - Model Creation is now :

  TSQLModel.Create([TSQLAuthGroup, TSQLAuthUser, and the other 7 tables ... ]);

And now I get an exception after Line 5 (CreateMissingTables) that tells me the Database is busy :
  "Error SQLITE_BUSY using 3.28.0 - database is locked ...".

If I omitt line 3, the Auth tables are created as part of the database, but not populated wit Auth records.

I've read the SAD over and over, but have had no success in figuring out how to resolve this issue, or understanding why my approach is wrong.

Any help/advice would be greatly appreciated (mORMot version : 1.18.5232).

Just been working on my first mORMot / Elevate Web Builder application - started a few weeks ago and I'm having all kinds of problems.

My first question is for Arnaud, I'll post the second question as a seperate request.

I've noticed that the mORMot source hosts 2 wrappers (Mustache and Smart Mobile Studio) for accessing mORMot servers as part of the mORMot library, and I'm wondering if we could add one for EWB (Elevate Web Builder).

Erick Engelke has opensourced his mORMot wrapper (as sample code provided with his two EWB/mORMot books), and that would be a really good place to start in terms of collaboration to make best use of these two excellent frameworks.

Arnaud, can you advise whether you think this would be a worthwhile innitiative? If yes, I'll check with Erick to identify the most suitable version of his source code to include as a part of mORMot.

Horbs

Dear All

This is a quick question on using "select by value" statements for the SynRestDataset.CommandText statements.

I've used ...mORMot\SQLite3\Samples\ThirdPartyDemos\EMartin\TSynRestDataset\FishFactSyn for this example.

If I modify
    'http://LocalHost:8080/root/BioLife?select=Species_No, ... ,Notes,Som&sort=Species_No,
by appending
    &where=Species_No=90110'
then the correct result set is returned.

But when I use
    &where=Category=Shark'
in place of the above I get ...

Error Not Found from ... where=Category=Shark.

Is there some special search formating / string literal that I'm missing?

Many thanks

Horbs