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.
But is there any way that I can set a role to a DB user using the LISA JDBC Step?
Thanks in advance
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 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.
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.
I guess I will stick with Java script step, else my test case is gonna have numerous jdbc steps.