Database single sign-on and ALTER SESSION

[Sanmargar]

What is the best way to manage database connections to ensure that all operations are performed within the same user session and are isolated from other application users? I also need to execute

ALTER SESSION [here, configuring a database parameter that varies depending on the user logged into the application]

after logging into the database. It would also be ideal to have control over how long the connection is maintained.

What is the best way to implement single sign-on for both the application and the database (such as Oracle), meaning to log into the database with the same user credentials as those used to log into the application?

I am aware that this is not efficient for web applications. However, this is the business requirement, and I am not going to argue with it.

[Isomorphic]

We would strongly suggest that you take this requirement back to the business types and point out the major issues here. Just for starters:

1) every ordinary user has credentials to log into the database, so if any ordinary user leaks his credentials or has a terrible password, anyone with those credentials can get into the DB from any network where login is possible

2) you need to set up a way to add new logins to the database just to register a new user

There are a lot of other issues, we would recommend asking an AI such as ChatGPT: "what are the main reasons why, for web applications, credentials for users that log into the web application are not also used as credentials to log directly into the database?"

That all said, it is achievable if your company feels like it has too many feet and would like to blow one off. In outline:

1) use the pattern shown here to issue your ALTER SESSION command:

https://smartclient.com/smartgwtee/s...jdbcOperations

You will want to set up a custom subclass of SQLDataSource, and use it pervasively, and have an override, probably of SQLDataSource.execute(), so that you can be sure you always issue an ALTER SESSION before any other command is used

2) you will need a separate SQL config for every user, and then arrange for a dynamic value of SQLDataSource.dbName, so each user is effectively using their own copy of the core DataSource.

This is somewhat similar to a multi-tenant situation.

You can dynamically add new SQL configs using the Config server-side class.

You can effectively generate a distinct DataSource for every set of credentials via the DynamicDSGenerator. The docs for this start in the QuickStart Guide.

--

As we've covered, this is generally considered an anti-pattern, so we can't help you further with on the forums w/o support (partly because it might encourage others to go in this direction as well). However, we can help you with it on a Consulting basis - go here:

https://smartclient.com/services/#consulting

In particular, the requirement here is extremely similar to our multi-tenant approach (however that is of course done with distinct databases/schema, not just distinct credentials), which is not yet publicly documented and available, but our Consulting team can access it, and via Feature Sponsorship you can make it officially supported so that your usage is supported.

[claudiobosticco]

Hello, I'm just chiming in to ask if you plan to release this multi-tenant feature and in which release eventually?

[Isomorphic]

It's slate for 14.0, however, we're going to be doing an unusual thing this time and releasing 13.1 and 14.0 pretty much back-to-back.

If you wanted to get involved as a beta tester for this feature in particular, it's likely to land in around a month.