Hi Team,
Historically I have used a 'Users' table that contains the end users relevant details including Log in ID, Password & Name, as well as a couple of Integer fields that I use bit management to authorise, or not access to and/or functionality within apps.  e.g. Is user A authorised to use App xyz, is user B authorised to access menu option ZZZ in App xyz, etc.
This works fine and doesn't really need to be changed.  You know, if it ain't broke, don't fix it.
Having said that I thought I would investigate the use of Users & Roles in EDB for the same purpose.
Are there any examples around of using Users/Roles in this manner?

Regards & TIA,
Ian

Ian


My heretical comment is - stick with what you've got.

I faced a similar question  and my solution was to use ElevateDB to restrict access overall for logon but thereafter use what I'd already developed using DBISAM to restrict access to functionality.

YMMV but back when I made the decision I found that ElevateDB didn't have the granularity I needed - in some cases down to field level, or access to a specific form or subsytem or where content and level defined what could be done. Since I needed a mixed system I decided to keep the mix as light as possible. User ID & password are ElevateDB's job everything else is mine. I do have a duplicated user ID in my Staff table.

I also, to save confusing myself, renamed my users table to staff.

Roy Lambert

Hi Roy,
Thanks for the input.
I think I will stick with what works.  It's been working for 30+ years in one form or another so it must be OK.
Like you I have granularity to a low level.
Regards,
Ian

Ian


It is worth moving the actual logon to ElevateDB - you have to do something anyway, and you might want to know who did what at some point. It also means that managing the password encryption is done for you.

Roy Lambert

Roy Lambert wrote:

> Ian
>
>
> It is worth moving the actual logon to ElevateDB - you have to do something anyway, and you might want to know who
> did what at some point. It also means that managing the password encryption is done for you.
>
> Roy Lambert

Hi Roy,

Yay!!  Doing this in the NG.  Back up to 3 'subsciptions' active on NG/NNTP.

Hmmm.  Perhaps.  It's not urgent, just a line of investigation.
Start Beta testing of the ADS=>EDB converted Apps with one of my Customers today.  Fingers crossed..
Still interested in an example of the implementation/use of Users & Roles.

Regards,
Ian

On 21/06/2018 8:39 AM, Ian Branch wrote:
<snip> Still interested in an example of the implementation/use of Users
& Roles.

I'm using User & Roles in a limited fashion. I only have two roles per
database: SuperUser and non-SuperUser, so I am simply using the name of
the UserRole to distinguish between the two.  I could have used the
attributes field just as easily if I had more security needs.

I have this simple bit of code as part of my login procedure:-

S := 'SELECT 1 FROM Configuration.UserRoles UR '
   + 'WHERE UR.Name = '
   +        Engine.QuotedSQLStr('DB' + sClientCode + 'SuperUser')
   + ' AND UR.GrantedTo = ' + Engine.QuotedSQLStr(sUserName);
bSuperUser := (EDBAdminSession.Execute(S) = 1);

Thanks Jeff.

Hi Ian,

For use within your App, I would also say stick with your own custom table, as others have suggested.

Users and Roles can be used to globally limit access to tables, including rights to open, edit, insert, delete etc. This is all part of the SQL standard, so it is built into the product.

In theory this is useful, but in practice I find in an App you quite often need access to something that the User is not granted. ie the App needs "Admin" access while the User needs a lower level of access. If the User has logged the App in at the lower level of access it will not be allowed to "do" the Admin level work.

Of course you can write code to log-in as Admin within the App ... but that feels like a lot of work to me!

Ian,

<< Having said that I thought I would investigate the use of Users & Roles in EDB for the same purpose.
Are there any examples around of using Users/Roles in this manner? >>

You *can* use the attributes of a user/role in ElevateDB to store key=value pairs that define extended "rights" for use in your applications.  These can be queried using this system information table:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Users_Table
https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Roles_Table

However, even if you don't do that, please do yourself a favor and:

1) Make sure to change the default Administrator password.

2) Make sure that all users have some form of restricted access, at the very least just remove their ability to execute CREATE, ALTER, and DROP statements.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> 2) Make sure that all users have some form of restricted access, at the very least just remove their ability to
> execute CREATE, ALTER, and DROP statements.

Hi Tim,
   Ahhh.  Sage advice and not something I had considered.

Regards,
Ian