Connecting to Oracle database

Overview

Cayosoft Administrator can connect, read, and write to Oracle databases. This capability makes it easy for administrators to connect to databases for provisioning, updating, and deprovisioning user accounts and for updating records in the database. This article describes the process to set up a connection to the Oracle database.

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 the machine where Cayosoft Administrator is running.

To install Oracle Data Provider perform the following steps:

1. Run PowerShell console as Administrator.

2. Run the scripts below:

   Step 1:

   Copy

   [Net.ServicePointManager]::SecurityProtocol=[Net.SecurityProtocolType]::Tls12
   Install-PackageProvider -Name NuGet -Force  -ErrorAction Stop

   Step 2:

   Copy

   Install-Package 'Oracle.ManagedDataAccess.Core' -SkipDependencies -Force -RequiredVersion 2.19.180 -Source "nuget.org" -Scope AllUsers

3. After that, the Oracle.ManagedDataAccess.dll will be installed in C:\Program Files\PackageManagement\NuGet\Packages\Oracle.ManagedDataAccess.Core.2.19.180\lib.

Network Connection Requirements

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

Permissions required for Cayosoft Oracle Database User Account

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

CONNECT

RESOURCE

SELECT privilege on the database with the data.

Creating a Cayosoft Oracle Database User Account

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

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

NOTE: You need to replace the <username> and <password> by desired name and password values.

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 <username> identified by <password>; grant connect to <username>;

• grant resource to <username>;

• grant SELECT on ["schema name".]"HR_DATABASE" to <username>.

Setup Cayosoft Administrator Oracle Connection

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

1. In the Cayosoft Administrator Console, navigate to the Utils extension settings.

2. Expand the Oracle section.

3. To the right of the Credentials field, click the ... button.

4. Enter the Oracle user account name and password.

5. Click OK.

6. In the Hostname field enter the Oracle server hostname (or its IP address) to which Cayosoft Administrator will connect.

7. In the Service Name field enter the Oracle database service name.

8. In the Port field, enter your Oracle SQL*Net Listener port number, the default is 1521.

9. In the Oracle Data Access Components Path, specify the path where you installed the ODP.NET provider, by default it is

   C:\Program Files\PackageManagement\NuGet\Packages\Oracle.ManagedDataAccess.Core.2.19.180\lib\netstandard2.0

10. Click Save Changes.

Troubleshooting Oracle connection

1. In the PowerShell, run the Oracle connection test script and check the result.

2. In the fullserver variable specify your hostname and port.
   

3. In the servicename variable specify your service name.

$dllPath="c:\Oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
$fullserver="server_name:port"
$servicename="service_name"
$TimeOut=900
$tail = '';
$cred=Get-Credential
$user = $cred.UserName.Trim();
$pwd =  $cred.GetNetworkCredential().Password
if($user.ToUpper() -eq"SYS"-or$user.ToUpper() -eq"SYSDBA"){ $tail = 'DBA Privilege=SYSDBA;'}
$connectionStr = [string]::Format("Data Source={0}/{1};User Id={2};Password={3};Connect Timeout={4};{5}", $fullserver.Trim(), $servicename.Trim(), $user.Trim(), $pwd, $TimeOut, $tail)
Add-Type -Path $dllPath
try
{            
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionStr)
$connection.Open()  
Write-Host"Server: $($connection.ServerVersion)"
}
finally
{
$connection.Close()
}  
##
$error[0]
$error[0].Exception
$error[0].Exception.InnerException
#