Page 1 of 1

Alternative login credentials for Connection Wizard

PostPosted: Mon Mar 23, 2020 9:34 am
by pricerc
Is there a special reason why we *can't* specify alternate credentials for a SQL connection in the Connection Wizard, when we *can* in the Database Wizard?

Re: Alternative login credentials for Connection Wizard

PostPosted: Mon Mar 23, 2020 9:37 am
by Scott.Pearce
It's just the way security has been engineered.

The initial connection to a Jiwa database is *always* made with a known set of SQL credentials JiwaLogin. JiwaLogin *only* has access to the HR_Staff table. It connects and obtains the *real* SQL credentials for the user logging in from HR_Staff.

Re: Alternative login credentials for Connection Wizard

PostPosted: Mon Mar 23, 2020 10:21 am
by SBarnes
Hi

The attached will update an existing database based upon the current database, basically you can create a demo database and then update an existing the database, the only thing it won't do is add different users to the database if you use others than JiwaUser and JiwaReports.

The idea cam from scripts Scott recently posted on the forums

Re: Alternative login credentials for Connection Wizard

PostPosted: Fri May 15, 2020 11:41 pm
by Mike.Sheen
pricerc wrote:Is there a special reason why we *can't* specify alternate credentials for a SQL connection in the Connection Wizard, when we *can* in the Database Wizard?


I'm late to the conversation, but yes, there is a very good reason - but it's not overly special, just sensible.

When using the database wizard, you need credentials which have schema level credentials - like creating tables and the like. This is not something you want any old user to know. So we ask them to provide credentials to use for creating a database, tables, and so on when doing database operations.

For creating a connection, we don't want the user to need to know SQL credentials at all. We assume the JiwaLogin user is already created and present, and has access only to the tables needed (HR_Staff read only access) in the databases deemed appropriate to that SQL login and user.

This design was forged from experience, it wasn't an unintentional limitation to not have the ability to provide SQL credentials in the connection wizard.

Is there a reason why you want to supply SQL credentials within the connection wizard?

Re: Alternative login credentials for Connection Wizard

PostPosted: Sat May 16, 2020 6:50 pm
by pricerc
Mike.Sheen wrote:Is there a reason why you want to supply SQL credentials within the connection wizard?


I guess with all the SQL databases I've logged into over the years, it's just weird for me to see a SQL login dialog with no username/password option (unless it's using integrated authentication, which it's not, but that's a different discussion).

Then there's the fact that you can't tell from the connection wizard what username it's trying to use. It's like I'm expected to have read the manual before running the program.

But mostly it's because I don't want to use a default password. That's really bad for security.

I also think it would be better for security if you could choose a different username. Just like you can for JiwaUser/JiwaReports. I realise the credentials would need to be stored somewhere, but I think having an encrypted password in the registry, or in an obscure config file, would be preferable to a well-known password.

I'd put money on there being many customers whose JiwaLogin user has waaaay more privileges on the SQL server than they're supposed to (unless you've got code that's changing it!), which means a well known user with a well known password is a dangerous thing.

This also would provide better security silos for primitive 'multi-tenant' scenarios - here I'm thinking of the potential for a type of hosted environment for really small clients (one or two users) where the cost of a SQL Standard instance can be shared instead of having multiple SQL Express or localdb instances.

Re: Alternative login credentials for Connection Wizard

PostPosted: Mon May 18, 2020 10:24 am
by Mike.Sheen
pricerc wrote:But mostly it's because I don't want to use a default password. That's really bad for security.


Agreed, that is the philosophy one should adopt, but this is a little different.

We used to not have a JiwaLogon login/user and in fact did have the option to specify SQL Credentials when creating a connection. But this was all stored in a local file - which is bad for security. Not storing it an prompting the user all the time we were told was unacceptable.

So we came up with the design we have. It's simple and robust.

pricerc wrote:I'd put money on there being many customers whose JiwaLogin user has waaaay more privileges on the SQL server than they're supposed to (unless you've got code that's changing it!), which means a well known user with a well known password is a dangerous thing.


I don't think so - at least not with what I've been exposed to. Everyone tends to use their JiwaUser or JiwaReports SQL credentials for 3rd party integrations, I've never seen anyone use JiwaLogin for anything so I doubt anyone is going to the effort to mess with the privileges for that login.

pricerc wrote:This also would provide better security silos for primitive 'multi-tenant' scenarios - here I'm thinking of the potential for a type of hosted environment for really small clients (one or two users) where the cost of a SQL Standard instance can be shared instead of having multiple SQL Express or localdb instances.


Contained database authentication in 7.3 of Jiwa will achieve this. No more server-wide credentials, just users in the database.

Re: Alternative login credentials for Connection Wizard

PostPosted: Mon May 18, 2020 12:11 pm
by pricerc
Mike.Sheen wrote:But this was all stored in a local file - which is bad for security. Not storing it an prompting the user all the time we were told was unacceptable.

Hmmm. Tricky. I agree that storing an unencrypted password in a file is a bad thing, especially if it's a well-known file that is easy to find. But I think storing it encrypted in the registry, or in %AppData% somewhere would be acceptable, and preferable to the 'well-known username and password' we have currently.

Mike.Sheen wrote:I don't think so - at least not with what I've been exposed to. Everyone tends to use their JiwaUser or JiwaReports SQL credentials for 3rd party integrations, I've never seen anyone use JiwaLogin for anything so I doubt anyone is going to the effort to mess with the privileges for that login.

Ok, fair enough, I just did a quick check after creating a new demo database. I see it only has access to a few tables - I'm sure I saw one with more access than that.

But that said, I'm not sure that you're thinking nefariously enough. If I know that a customer is using Jiwa, I can use the JiwaLogin account to connect to the database from anywhere on the network and get lists of all the users and API keys. Although to be fair, in my experience, if I can get that far, there's probably easier ways of getting the information on the network than trying to guess users' Jiwa passwords.

Mike.Sheen wrote:Contained database authentication in 7.3 of Jiwa will achieve this. No more server-wide credentials, just users in the database.

Another reason to look forward to seeing 7.3.