Integrate with Database Application Tables (MSSQL)
Prerequisites
Before you install and configure a Database Application Tables (MSSQL) Orchestrated System, you should consider the following prerequisites and tasks.
- Your Database Application Tables (MSSQL) system is certified with Oracle Access Governance. Refer to Database Application Tables Components Certified for Integration with Oracle Access Governance for details of the versions supported.
Configure
You can establish a connection between customer databases and Oracle Access Governance by entering connection details. To achieve this, use the orchestrated systems functionality available in the Oracle Access Governance Console.
Navigate to the Orchestrated Systems Page
- From the Oracle Access Governance navigation menu icon
, select Service Administration → Orchestrated Systems.
- Click the Add an orchestrated system button to start the workflow.
Select system
On the Select system step of the workflow, you can specify which type of system you would like to onboard. You can search for the required system by name using the Search field.
- Select Database Application Table (MSSQL DB).
- Click Next.
Add details
- Enter a name for the system you want to connect to in the Name field.
- Enter a description for the system in the Description field.
- Determine if this orchestrated system is an authoritative source, and if Oracle Access Governance can manage permissions by setting the following checkboxes.
- This is the authoritative source for my identities
- I want to manage permissions for this system
- Click Next.
Add Owners
Note:
When setting up the first Orchestrated System for your service instance, you can assign owners only after you enable the identities from the Manage Identities section.- Select an Oracle Access Governance active user as the primary owner in the Who is the primary owner? field.
- Select one or more additional owners in the Who else owns it? list. You can add up to 20 additional owners for the resource.
Account settings
- Select to allow Oracle Access Governance to create new accounts when a permission is requested, if the account does not already exist. By default this option is selected meaning that an account will be created if it does not exist, when a permission is requested. If the option is unselected then permissions can only be provisioned where the account already exists in the orchestrated system. If permission is requested where no user exists then the provisioning operation will fail.
-
Select where and who to send notification emails when an account is created. The default setting is User. You can select one, both, or none of these options. If you select no options then notifications will not be sent when an account is created.
- User
- User manager
- When an identity leaves your enterprise you should remove access to
their accounts. You can select what to do with the account when this happens.
Select one of the following options:
- Delete
- Disable
- No action
Note:
The options above are only displayed if supported in the orchestrated system type being configured. For example, if Delete is not supported, then you will only see the Disable and No action options. - When all permissions for an account are removed, for example when
moving from one department to another, you may need to adjust what accounts the
identity has access to. You can select what to do with the account when this
happens. Select one of the following options:
- Delete
- Disable
- No action
Note:
The options above are only displayed if supported in the orchestrated system type being configured. For example, if Delete is not supported, then you will only see the Disable and No action options. - If you want Oracle Access Governance to manage accounts created directly in the orchestrated system you can select the Manage accounts that are not created by Access Governance option. This will reconcile accounts created in the managed system and will allow you to manage them from Oracle Access Governance.
Note:
If you do not configure your system as a managed system then this step in the workflow will display but is not enabled. In this case you proceed directly to the Integration settings step of the workflow.Note:
If your orchestrated system requires dynamic schema discovery, as with the Generic REST and Database Application Tables integrations, then only the notification email destination can be set (User, Usermanager) when creating the orchestrated system. You cannot set the disable/delete rules for movers and leavers. To do this you need to create the orchestrated system, and then update the account settings as described in Configure Orchestrated System Account Settings.Integration settings
On the Integration settings step of the workflow, enter the details required to allow Oracle Access Governance to connect to your customer database.
Table - Integration settings
Parameter Name | Mandatory? | Description |
---|---|---|
Easy connect URL for Microsoft SQL Server database |
Yes |
URL of the server hosting the customer database system you want to integrate with. Use the format host/port/database/encrypt/trustServerCertificate, for example jdbc:sqlserver://[host]:[port];[databaseName];[encrypt];[trustServerCertificate]. For further details refer to the MSSQL JDBC documentation for your version. |
User name |
Yes |
The username required to connect to the customer database system to perform data reconciliation and provisioning. |
Password?/Confirm password |
Yes |
The password that authenticates the user you are connecting to the customer database system with. |
Database name | Yes | The customer database to which you need to connect (such as master database). |
Custom jar details |
Yes | MSSQL Server database driver jar. Please refer to
the MSSQL JDBC documentation for your version for details. The
jar name and checksum should be in the format of
<jarName>::<jarChecksum> .Calculate
the checksum using SHA-512. Details of how this is used by the
agent can be found in Custom Jar
Support.
|
User account table name |
Yes |
The name of the table containing your user accounts. Note: For a key column which is non auto increment, Create Account provisioning will be supported only with custom script. For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy.Note: Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_PERSON else you will see errors. User name is passed as a separate parameter as detailed in this table. |
Permission tables |
Add the names of your permission tables in a comma-separated list. This parameter only applies if your orchestrated system is configured in managed system mode. Note: Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_PERMISSION else you will see errors. User name is passed as a separate parameter as detailed in this table. |
|
Account permission tables |
If you have account data resident in parent and child tables, then provide a comma-separated list of the child tables names. Note: Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_ACCOUNTPERMISSION else you will see errors. User name is passed as a separate parameter as detailed in this table. |
|
Lookup tables |
Comma-separated list of lookup tables for attributes such as country. Note: Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_LOOKUP else you will see errors. User name is passed as a separate parameter as detailed in this table. |
|
Key column mappings |
Yes |
Comma-separated list of key column mappings.
These mappings should be entered in the format
Table:KeyColumn .
Note: This parameter is applicable for ACCOUNT, ENTITLEMENT, and LOOKUP tables only. |
Name column mappings |
Yes |
Comma-separated list of name column mappings.
These mappings should be entered in the format
Table:NameColumn .
Note: This parameter is applicable for ACCOUNT, ENTITLEMENT, and LOOKUP tables only. |
User account table password column mapping |
Password column mapping for user account table
in the format |
|
User account table status column mapping |
Yes |
Status column mapping for the user account table
in the format |
User account enabled status value |
This value will be used as the enable value if the status column is configured, and it is a String type. If no value is provided for this parameter, then it defaults to 'ACTIVE'. |
|
User account disabled status value |
This value will be used as the disable value if the status column is configured, and it is a String type. If no value is provided for this parameter, then it defaults to 'INACTIVE'. |
|
User account filter condition |
A WHERE clause which defines the subset of user account records that you want to bring from your customer database into Oracle Access Governance. For example: COUNTRY in ('IN','US'). |
|
Create script |
Custom script to use custom stored procedures or
SQL statements rather than the default SQL statements for
performing provisioning operations. Enter the file URL of
the Groovy script created for the create user account
provisioning operation. You must enter the file URL in the
following format: Sample value:
For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy. |
|
Update script |
Custom script to use custom stored procedures or
SQL statements rather than the default SQL statements for
performing provisioning operations. Enter the file URL of
the Groovy script created for the update user account
provisioning operation. This script is called when you
update the account attribute form, enable or disable the
user account. You must enter the file URL in the following
format: Sample value:
For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy. |
|
Delete script |
Custom script to use custom
stored procedures or SQL statements rather than the default
SQL statements for performing provisioning operations. Enter
the file URL of the Groovy script created for the delete
user account provisioning operation. This script is called
when you revoke or delete an account. You must enter the
file URL in the following format:
Sample value:
For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy . |
|
Dataload script |
Custom script to use custom stored procedures or SQL
statements rather than the default SQL statements for
performing provisioning operations. Enter the file URL of
the Groovy script created for reconciliation. The connector
delegates the data load operation to the Groovy script,
which is responsible for passing the information (connector
object) to the callback handler. This script is called while
performing an account search (operations such as full data
load). You must enter the file URL in the following format:
Sample value:
For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy. |
|
Add relationship data script |
Custom script to use custom stored procedures or SQL
statements rather than the default SQL statements for
performing provisioning operations. Enter the file URL of
the Groovy script created for the add multivalued attribute
(including permissions for account) provisioning operation.
This script is called when you add multivalued child
attributes. You must enter the file URL in the following
format: Sample value:
For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy. |
|
Remove relationship data script |
Custom script to use custom stored procedures or SQL
statements rather than the default SQL statements for
performing provisioning operations. Enter the file URL of
the Groovy script created for the remove multivalued
attribute (including permissions for account) provisioning
operation. This script is called while removing multivalued
child attributes. You must enter the file URL in the
following format:
Sample value:
For further details on scripting with the Database Application Tables integration, see Develop Custom Scripts for Database Application Tables (MSSQL) Using Groovy. |
- Click Add to create the orchestrated system.
Finish Up
The final step of the workflow is Finish Up where you are prompted to download the agent for your Orchestrated System. Once you have downloaded the agent, you can install and configure the agent in your environment using the instructions in Manage Oracle Access Governance Agent for Indirect Integrations.
- Customize before enabling the system for data loads
- Activate and prepare the data load with the provided defaults
Post Configuration
Update Intermediate Schema JSON File
schema.json
is created on the agent host. This
file maps the tables in the integrated database with the schema which is represented
on Oracle Access Governance. The initial schema JSON file is created with basic attributes enabled for data
load, UID, NAME, STATUS and PASSWORD (if configured by user). The full data load
operation can execute with this initial schema JSON file, loading data for only
these basic attributes. You can then further modify the schema JSON file to include
more attributes for the next data load operations.
Note:
Ensure that you have granted read/write permissions on the schema JSON file for the operating system user that will be running the agent.For full details on the structure and options available when editing the
schema.json
, refer to Schema JSON File Reference.
Fetch Latest Custom Attributes
You should perform a schema discovery operation which will fetch the latest custom attribute information. For details on how to perform this task, see Fetch Latest Custom Attributes.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customer access to and use of Oracle support services will be pursuant to the terms and conditions specified in their Oracle order for the applicable services.