Published: 31/08/2018

Applies to: Cayosoft Administrator 4.4.6 or later.

Oracle Database version: 12c or later

Summary: Cayosoft Administrator has the ability to connect to and read from various data sources including Oracle Database. This capability makes easy for Administrators to connect to databases for provisioning, updating and deprovisioning user accounts.

This article describes how to setup connection to Oracle Database.

---

In this article: 

Oracle Database Requirements

Software Requirements

1. Oracle Database version 12c or later.
   To determine your version of Oracle Database, follow the instructions on Oracle’s Website here: Identifying Your Oracle Database Software Release
   
2. Oracle Data Provider for .NET installed on machine where Cayosoft Administrator is running.
   You can download ODP.NET from the Oracle site, select the latest version ODAC XCopy 64-bit: https://www.oracle.com/technetwork/topics/dotnet/downloads/odacdeploy-4242173.html
   
   To install ODP.NET you need to download and unzip ODP.NET and run install.bat script from the Windows Command Line tool on the machine where Cayosoft Administrator Service is running.
   
   Note: Oracle recommends running the batch file under the "Run as Administrator" context. Installation details you can find in the readme.htm file that is part of downloaded ODAC XCopy archive.
   
   install.bat all c:\oracle odac 

Network Connection Requirements

Firewall settings on both the Cayosoft Administrator Server and Oracle Database Server must be configured to allow the servers to communicate.  

Permissions required to Cayosoft Oracle Database User Account

Oracle user must be able to have the following roles for correct working with databases and allow Cayosoft Administrator Console users to select fields from the data source using what is known as an “Object Picker” dialog box :

• CONNECT
• RESOURCE
• SELECT privilege on database with the data

 

Creating a Cayosoft Oracle Database User Account

You can use the script below to create a oracle user account with CONNECT and RESOURCE permissions.

The permissions are necessary for the Cayosoft Object Picker and for Cayosoft Administrator to read database tables and views that are granted to the Cayosoft Account.

Note: You can change user name from C##CAYOSOFTADMIN to any other name that starts with C## and replace <password> by desired password value. If your Oracle configuration doesn't require C## prefix for new users, you can use the name without C##.

Note2: Be sure to replace HR_DATABASE in the script with the actual name of the database that contains the data to be accessed. Part ["schema name".] is optional.

create user C##CAYOSOFTADMIN identified by <password>;
grant connect to C##CAYOSOFTADMIN;
grant resource to C##CAYOSOFTADMIN;
grant SELECT on ["schema name".]"HR_DATABASE" to "C##CAYOSOFTADMIN";

Setup Cayosoft Administrator Oracle Connection

After a Oracle user account has been created for Cayosoft Administrator the Oracle Connection can be configured and tested. If issues arise, the most likely cause are firewall issues between the two servers.

1. Open the Cayosoft Console.
2. Navigate to Utils Extension.
3. Expand Oracle section.
4. To the right of the Credentials field, click the ... button.
5. Enter the Oracle user account name (it is C##CAYOSOFTADMIN in the script example above) and password.
   If you choose name different from C##CAYOSOFTADMIN, enter the name you have chosen.
6. Click OK.
7. In the Hostname field enter the Oracle server host name (or its IP address) to which Cayosoft Administrator will connect.
8. In the Service Name field enter the Oracle database service name.
9. In Port enter Oracle SQL*Net Listener port number, the default is 1521.
10. In Oracle Data Access Components Path specify path where you installed ODP.NET provider, by default it is C:\oracle\odp.net\managed\common
11. Save Changes.

 

 

---

Related Articles