How to do integrate oracle database with servicenow.

[chiranjeevi thi]

There is a database access-removal catalog form, Once the ticket is created from catalog form, the user’s access should be automatically removed from the Oracle SQL database using the user ID. How can we achieve this requirement. We already have the SQL username, password, and host details. how to do through integration in flow designer.

[Jennifer Metz · Accepted solution]

Hi @chiranjeevi thi,

I am happy to help out. I haven't done this setup with Oracle specifically, but this should point you in the right direction.

1. Create JDBC Connection & Credential Alias for the Oracle database using a mid server.

2. Build a flow that triggers on the catalog request or after any approvals

3. Lookup the catalog variables and grab the user id and any other information you need.

4. Use the JDBC step in flow designer to call a stored procedure or execute an SQL statement to revoke access. You will need an integration hub subscription for this step.

5. If successful, add validation to confirm the users access was revoked.

6. If failure, create a task to a specific team for investigation or manual adjustment.

7. Update ritm based on outcome.

If you don't have integration hub, let me know and I can try and come up with an alternative method.

I hope this helps!

[chiranjeevi thi]

Hi@Jenifer Metz

We have completed the JDBC and MID Server setup successfully and are able to fetch data from the database. However, when we attempt to delete a database user using the SQL statement DROP USER 'f274983', ServiceNow throws the error “Given SQL statement is not allowed to be executed at this time.”

This is expected behavior because ServiceNow JDBC steps block destructive DDL statements such as DROP USER for security reasons. To delete a database user, the supported approach is to use a database side stored procedure and invoke it from ServiceNow via the JDBC step.