How to incorporate Initialisation SQLs for JDBC Adapter

hi,

my integration requires a connection to a Oracle database which requires the logged in session to set the password to the database role.

after logging in, i have to run the following…
“set role <rolename> identified by ‘<password>’;”

I’ve thought of putting the above statement into a startup service of the packages that needs it but is not sure if it would work.

Basically, the concern is with connection pooling.
When the jdbc adapter only up the connection to the database as and when it’s needed. And these “new” sessions will not have executed the above sql statement.

Please help.

Kenny

Set role is session-level, so as you say, connection pooling is a problem.

Looks like a play for a Dynamic SQL adapter service.

For example:
SET ROLE ${role} IDENTIFIED BY ${password}

You will supply role and password as service inputs.

Don’t forget to SET ROLE NONE before finishing because the connection is still shared.

Hi Mark,

thanks for the help.
maybe u can help me further…

how do i set it such that it’ll run after every time the JDBC adapter starts a new connection for the pool?

Kenny

If the role is to remain active through the life of the connection, it would be easier to set up a different login ID with the appropriate permissions set on the default role.

As an alternative, you could create a package startup service that executes the dynamic SQL mentioned above.

yup, having a role with the required permission would be the easier way.

I don’t understand how using a startup service can solve the problem though. The startup service will probably use just one of the connection in the pool (i.e only one session would have the required permissions). When IS allocates another connection from the pool to another adapter service, that integration will fail right?

Good point. I’d stay with the default role approach if you don’t want to explicitly turn the role on and off.

The main use I’ve seen for turning roles on and off is where upgraded permissions are enabled for a given user id only when they are executing sql from within an approved application, which provides the password for the role secretly.

Outside the application, if the user is running an ad-hoc tool, the permissions are more restricted.

Regards