Can we set role to DB user using JDBC Step?

[Sanjupy06]

Hi All,

 

I have a DB user with read access. I need to set a role as provided by my DBA to enable this user to do update operations.

I can do this using custom Script in JavaScript step.

But is there any way that I can set a role to a DB user using the LISA JDBC Step?

 

Thanks in advance

[Srikanth Gajawada]

Hi Sanju,

    You can write a query in JDBC step which will set role to database user. All you need to configure in JDBC step is, you need to add database driver, DB connection URL , user-id and password (this user should have admin privileges to update user role).

 

Thanks

Srikanth

[Sanjupy06]

Thanks Srikanth for the reply.

 

But I guess my question was not clear enough.

I'm not trying to create a role here. DBA has already done that for me and has shared the password. What I need is to assign that role to a DB user

We have a very restricted DB, so I need to assign that role, do whatever update operations I need and set the role back to none in the current session itself.

 

I'm currently able to do that using a java script step as below and it works fine

 

rs = stmt.executeQuery("set role rolename identified by rolepwd");

rs = stmt.executeQuery("Update Query here");

rs = stmt.executeQuery("set role none");

 

But I was wondering if  I can do the same using the built in JDBC step in LISA.

i.e set role > Do Operations > set role to none

 

Using Connection pool in JDBC Step might help, but i don't want the connection to remain open in VSE and others being able to reuse the role.

I'm not very clear about how connection pool works, but I have had an issue in past where the connection remained open even after removing the model from VSE.

 

Thanks!

[Srikanth Gajawada]

Yes, you can do this functionality using 3 individual JDBC steps. You can not add all these 3 operations in a single JDBC step.

And make sure "Return Result set" check box is unchecked.

[Sanjupy06]

Thanks Srikanth.

I guess I will stick with Java script step, else my test case is gonna have numerous jdbc steps.