Alternative login credentials for Connection Wizard

Discussions relating to plugin development, and the Jiwa API.

Alternative login credentials for Connection Wizard

Postby pricerc » Mon Mar 23, 2020 9:34 am

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?
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Alternative login credentials for Connection Wizard

Postby Scott.Pearce » Mon Mar 23, 2020 9:37 am

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.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Alternative login credentials for Connection Wizard

Postby SBarnes » Mon Mar 23, 2020 10:21 am

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
Attachments
Plugin Fix Jiwa Users.xml
Fix User
(566.8 KiB) Downloaded 60 times
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Alternative login credentials for Connection Wizard

Postby Mike.Sheen » Fri May 15, 2020 11:41 pm

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?
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Alternative login credentials for Connection Wizard

Postby pricerc » Sat May 16, 2020 6:50 pm

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Alternative login credentials for Connection Wizard

Postby Mike.Sheen » Mon May 18, 2020 10:24 am

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Alternative login credentials for Connection Wizard

Postby pricerc » Mon May 18, 2020 12:11 pm

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Technical and or Programming

Who is online

Users browsing this forum: Google [Bot] and 1 guest

cron