Database Protector

• 1: MSSQL Database Protector
• 1.1: Understanding the Architecture
• 1.2: System Requirements
• 1.3: Preparing the Environment
• 1.3.1: Extracting the Installation Package
• 1.3.2: Installing the Log Forwarder
• 1.3.3: Installing the RPAgent
• 1.4: Installing the MSSQL Database Protector
• 1.4.1: Installing the Policy Enforcement Point (PEP)
• 1.4.2: Managing Certificate Based Login
• 1.4.2.1: Creating the certificate
• 1.4.2.2: Upgrading the certificate
• 1.4.3: Creating the User Defined Functions
• 1.5: Configuring the MSSQL Database Protector
• 1.5.1: Configuring MSSQL User Access Permissions
• 1.5.2: Impersonating a User
• 1.6: Upgrading the Database Protector
• 1.6.1: Preparing the System for Upgrade
• 1.6.2: Dropping the UDFs
• 1.6.3: Upgrading the Database Protector
• 1.6.4: Recreating the UDFs
• 1.6.5: Restarting the RPAgent
• 1.7: Uninstalling the MSSQL Database Protector
• 1.7.1: Dropping the Database Protector UDFs
• 1.7.2: Uninstalling the RPAgent
• 1.7.3: Uninstalling the Log Forwarder
• 1.7.4: Removing the Database Protector
• 2: Oracle Database Protector
• 2.1: Understanding the Architecture
• 2.2: System Requirements
• 2.3: Preparing the Environment
• 2.3.1: Extracting the Installation Package
• 2.3.2: Installing the Log Forwarder
• 2.3.3: Installing the RPAgent
• 2.4: Installing the Oracle Database Protector
• 2.4.1: Installing the Policy Enforcement Point (PEP)
• 2.4.2: Creating the User Defined Functions (UDFs)
• 2.5: Configuring the Oracle Database Protector
• 2.5.1: User Impersonation
• 2.5.2: Enterprise User Security (EUS) in the Oracle Database
• 2.6: Uninstalling the Oracle Database Protector
• 3: User Defined Functions and APIs
• 3.1: Oracle User Defined Functions and APIs
• 3.1.1: General UDFs
• 3.1.2: Access Check Procedures
• 3.1.3: Insert Encryption UDFs
• 3.1.4: Insert No-Encryption, Token, and FPE UDFs
• 3.1.5: Multiple Insert Encryption Procedures
• 3.1.6: Select Decryption UDFs
• 3.1.7: Select No-Encryption, Token, and FPE UDFs
• 3.1.8: Update Encryption UDFs
• 3.1.9: Update No-Encryption, Token, and FPE UDFs
• 3.1.10: Multiple Update Encryption Procedures
• 3.1.11: Hash UDFs
• 3.1.12: Blob UDFs
• 3.1.13: Clob UDFs
• 3.1.14: Bulk UDFs
• 3.1.15: Oracle Input Datatype to UDF Mapping
• 3.2: MSSQL User Defined Functions and APIs
• 3.2.1: General Functions
• 3.2.2: Insert Procedures
• 3.2.3: UPDATE Extended Stored Procedures
• 3.2.4: Access Check Procedures
• 3.2.5: Select Functions and Procedures
• 3.2.6: VARCHAR UDFs
• 3.2.7: NVARCHAR UDFs
• 3.2.8: Integer UDFs
• 3.2.9: BLOB UDFs
• 3.2.10: CLOB UDFs

1 - MSSQL Database Protector

Protegrity MSSQL Database Protector v10.0 is a database‑native security solution that provides column‑level data protection for Microsoft SQL Server environments. The protector integrates directly with the SQL Server execution layer, enabling secure protect, unprotect, and reprotect operations on sensitive data while minimizing runtime overhead. Managed centrally through the Protegrity Enterprise Security Administrator (ESA), it enforces standardized security policies and key management without requiring changes to existing applications or database schemas.

1.1 - Understanding the Architecture

The following diagram illustrates the architecture of the MSSQL Database Protector:

1.2 - System Requirements

This section describes the System Requirements including the hardware, software, and network requirements for installing the MSSQL database protector.

The following are the prerequisites required for installing the MSSQL Database Protector:

• The ESA v10.0 appliance is installed, configured, and running.
• The IP address or host name of the ESA is noted.
• The administrator rights for the operating system are granted.
• The DBA rights to the MSSQL database are granted.
• Before installing the protector, ensure that the Policy Information Management(PIM) is initialized, if you are installing the ESA for the first time. This prerequisite holds true for versions 7.2.0 and later releases.

Note: For more information about initializing the PIM, refer to the section Initializing the Policy Management.

1.3 - Preparing the Environment

The steps to prepare the environment to install the MSSQL Database Protector are explained in the sub-sections mentioned below.

1.3.1 - Extracting the Installation Package

1. Download the DatabaseProtector_WIN-ALL-64_x86-64_MSSQL-ALL-64_<version>.zip installation package made available by Protegrity.

2. Create a directory to install the protector.

   Note: In case of a failure to create the installation directory, the script will use C:\Program Files\Protegrity\ as the default installation directory.

3. To obtain the signature files, extract the contents of the DatabaseProtector_WIN-ALL-64_x86-64_MSSQL-ALL-64_<version>.zip file.

   • DatabaseProtector_WIN-ALL-64_x86-64_MSSQL-ALL-64_<version>.zip
   • signatures\
   • DatabaseProtector_WIN-ALL-64_x86-64_MSSQL-ALL-64_<version>.zip_10.0.sig

4. To obtain the installation files from the package, extract the DatabaseProtector_WIN-ALL-64_x86-64_MSSQL-ALL-64_<version>.zip file.

   The following files are available in the installation package:

   • LogforwarderSetup_Windows_x64_<version>.exe
   • PepSQLServerSetup_Windows_x64_<version>.exe
   • RPAgentSetup_Windows_x64_<version>.exe
   • U.S.Patent.No.6,321,201.Legend.txt

1.3.2 - Installing the Log Forwarder

1. Log in to the database server as the user that has the permissions to install the Log Forwarder. Usually, this tends to be the instance owner.

2. Navigate to the directory containing the installation files.

3. To install the Log Forwarder, double-click the LogforwarderSetup_Windows_x64_<version>.exe file.

   The Welcome to the Log Forwarder Setup Wizard dialog box appears.

   Caution: It is mandatory to install the Log Forwarder before installing the RPAgent to ensure that the MSSQL Database Protector is configured correctly.

4. Click Next.

   The Audit Store Connectivity Information dialog box appears.

5. In the Audit Store Connectivity Information dialog box, select the number of audit stores.

   Note: The minimum and maximum Audit Store endpoints supported are 1 and 3 respectively.

6. In the Endpoint 1 box, enter the IP address for the audit store.

   Note: The default port number is 9200.

7. Click Next.

   The Select Destination location dialog box appears.

8. To change the default installation directory for the Log Forwarder:

   1. Click Browse.

   2. Select the required installation directory to install the Log Forwarder.

   3. Click OK.

   Note: The default installation directory is C:\Program Files\Protegrity\logforwarder.

9. Click Next.

   The Ready to Install dialog box appears.

10. Click Install.

    The installer completes the installation and the Completing the Log Forwarder Setup dialog box appears.

11. To exit the installer, click Finish.

    Note: After the Log Forwarder is installed successfully, to ensure that the Logforwarder sends logs to the ESA, verify that the Log Forwarder service is in the Running state.

12. To check the logforwarder service state, navigate to Start > Control Panel > System and Security > Administrative Tools > Services.

    The Services window appears.

13. Verify that the Protegrity Log Forwarder service is in the Running state.

1.3.3 - Installing the RPAgent

Before you begin:

Before proceeding with the RPA installation in secure mode, ensure that the required CA certificate is available and trusted on the system.

• For ESA

  Download the certificate from ESA.

  For more information about downloading certificates from ESA, refer to Manage Certificates.

After obtaining the certificate, configure the system environment variable:

Ensure the ESA hostname or IP is present in the ESA TLS certificate (SAN or CN) and it is resolvable from the RPAgent host.

After the CA certificate is available, proceed with the RPA installation.

To install the RPAgent:

1. Navigate to the directory containing the installation files.

2. To install the RPAgent, double-click the RPAgentSetup_Windows_x64_<version>.exe file.

   The Welcome to Resilient Package Agent Setup Wizard dialog box appears.

3. Click Next.

   The Upstream Connectivity Information dialog box appears.

4. In the Address box, enter the host name or the IP address of the ESA.

   Caution: Ensure that the ESA is up and running with the HubController service. The HubController service must be in the Running state to enable automatic download of certificates.

5. In the Port box, type the ESA port number.

   Note: The default port for ESA is 8443.

6. In the Certificate Download User box, type the ESA username.

7. In the Certificate Download Password box, type the ESA password.

8. Click Next.

   The Select Destination Location dialog box appears.

9. To change the default installation directory for the RPAgent:

   1. Click Browse.

   2. Select the required installation directory to install the RPAgent.

   3. Click OK.

   Note: The default directory is C:\Program Files\Protegrity\rpagent where the RPAgent is installed.

10. Click Next.

    The Ready to Install dialog box appears.

11. Click Install.

    The installer completes the installation and the Completing the Protegrity RPAgent Setup Wizard dialog box appears.

12. To exit the wizard, click Finish.

13. To check the Protegrity RPAgent service status, navigate to Start > Control Panel > System and Security > Administrative Tools > Services. The Services window appears.

14. Verify that the Protegrity RPAgent service is in the Running state.

1.4 - Installing the MSSQL Database Protector

This section describes the procedure to install the MSSQL Database Protector and create the required User‑Defined Functions (UDFs). The process includes instructions to install the Policy Enforcement Point (PEP) for Microsoft SQL Server, optionally managing certificate‑based logins, and creating the UDFs.

Note: Certificate-based login management is only required in case of certificate creation. Depending on the specific requirements, create the User-defined functions (UDFs) with or without the certificates.

1.4.1 - Installing the Policy Enforcement Point (PEP)

The Policy Enforcement Point (PEP) integrates with SQL Server to enforce data protection and security policies at runtime, ensuring controlled access to sensitive data. This section outlines the installation procedure, and key considerations to ensure a successful installation.

To install the PEP:

1. Navigate to the directory containing the installation files.

2. To install the PEP for MSSQL server, double-click the PepSQLServerSetup_Windows_x64_<version>.exe file.

   The Select Destination Location dialog box appears.

3. To change the default installation directory:

   1. Click Browse.

   2. Select the required installation directory to install the PEP for MSSQL Server.

   3. Click OK.

   Note: The default directory is C:\Program Files\Protegrity\Database Protector.

4. Click Next.

   The Ready to Install dialog box appears.

5. Click Next.

   The Configure SQL Server Database Name dialog box appears.

   Note: The default database name is set to master.

6. Enter the database name in the box.

7. Click Install.

   The installer completes the installation successfully and the Completing the Protegrity Data Security Platform - SQL Server UDF Setup Wizard dialog box appears.

8. Click Finish.

   Note: After creating the UDFs, Protegrity recommends to restart the MSSQL service. For more information, refer to Restarting the SQL server.

Note: After installing Database Protector, configure Certificate-based login or continue with UDF installation without certificate-based authentication.

1.4.2 - Managing Certificate Based Login

Certificate‑based login securely authorizes Protegrity assemblies in SQL Server by using a certificate created from the Protegrity‑signed dll.

This section provides information about creating and upgrading the certificate based login for the MSSQL database protector.

1.4.2.1 - Creating the certificate

Certificate‑based login securely authorizes Protegrity assemblies in SQL Server by using a certificate created from the Protegrity‑signed dll. The certificate is mapped to a login with the UNSAFE ASSEMBLY permission. This permission enables the trusted execution of assemblies and UDFs without enabling the database‑wide TRUSTWORTHY setting.

To create a certificate-based login:

1. Login to SQL Server Management Studio.

2. To create a database certificate using a signed dll. execute the following query.

   CREATE CERTIFICATE MSSQL_90009_cert
   FROM EXECUTABLE FILE = 'C:\Program Files\Protegrity\Database Protector\sqlserver\DNPepConnector.dll'
   GO 
   

3. To create a certificate‑based login and grant the UNSAFE ASSEMBLY permissions, execute the following query.

   CREATE LOGIN John
   FROM CERTIFICATE MSSQL_90009_cert
   GO
   
   GRANT UNSAFE ASSEMBLY TO <John>;
   GO
   

4. To verify the certificate, navigate to System Database > Security > Certificates.

5. Select the appropriate database type to install the UDFs.

6. To create an assembly:

   1. Verify whether any assembly exists.
   2. If the assembly exists, then drop the existing assembly.
   3. Open the createassembly.sql script.
   4. Set the value of the TRUSTWORTHY parameter to OFF.
   5. Save the changes to the createassembly.sql script.
   6. Execute the createassembly.sql script.

   A sample script is given below.
   

   USE [master]
   GO
   
   sp_configure 'clr enable',1
   RECONFIGURE
   GO
   
   alter database [master] set trustworthy OFF
   GO
   
   if exists(SELECT name FROM sys.assemblies WHERE name = 'DNPepConnector')
   DROP ASSEMBLY [DNPepConnector]
   GO
   
   CREATE ASSEMBLY [DNPepConnector]
   AUTHORIZATION [dbo]
   FROM 'C:\Program Files\Protegrity\Database Protector\sqlserver\DNPepConnector.dll'
   WITH PERMISSION_SET = UNSAFE
   GO
   

7. To verify the user, navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver.

8. To install the database objects, execute the CreateObjects.sql script.

9. Execute the protect/unprotect operations.

1.4.2.2 - Upgrading the certificate

This section describes the instructions to upgrade certificates for certificate‑based logins. Certificate upgrades are required only when replacing or renewing an existing certificate and are not part of the initial installation. Perform this procedure only if certificate‑based login is already configured.

To upgrade the certificate:

1. Login to SQL Server Management Studio.

2. To identify the login mapped to the certificate, execute the following query:

   SELECT name
   FROM sys.server_principals
   WHERE sid IN (
      SELECT sid
      FROM sys.certificates
      WHERE name = '<certificate_name>'
   );
   

   Note: Replace the placeholder with actual names.

   This query returns the login_name associated with the existing certificate.

3. To drop the login name, execute the following query:

   DROP LOGIN [login_name];
   

   Note: Replace the placeholder with actual names.

   This command removes the login_name associated with the existing certificate.

4. To drop the associated objects, execute the following script:

   DropObjects.sql
   

5. To drop the user, execute the following query:

   DROP USER [user_name];
   

   Note: Replace the placeholder with actual names.

   This command removes the user_name associated with the existing certificate.

6. To drop the certificate, execute the following query:

   DROP CERTIFICATE <certificate_name>;
   

7. To re-create the certificate, follow the steps mentioned in Creating a Certificate-Based Login.

1.4.3 - Creating the User Defined Functions

This section describes the instructions install the User Defined Functions (UDFs). Create the UDFs with or without a certificate-based login. For more information about certificate-based login, refer to Managing Certificate-Based Login.

Note: If the TRUSTWORTHY database property is OFF, create a certificate-based login from the signed .dll file provided by Protegrity. Create the certificate-based login before creating the UDFs. If the TRUSTWORTHY database property is ON, create the UDFs without creating a certificate-based login.

To install the user-defined functions:

Note: In MSSQL Server, the sa login is disabled by default. Enable the sa login to connect to the database using the sa user. By default, the database name is master.

1. To connect to the database, login as the privileged user with the CREATE ASSEMBLY permissions.

2. To run the scripts, navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\sqlscripts directory.

3. To execute the registered assembly, execute the CreateAssembly.sql script.

4. Open the CreateFunctions.sql file.

5. Replace the default database name with the database to install the UDFs.

6. Save the changes to the CreateFunctions.sql script.

7. To create the UDFs, execute the CreateFunctions.sql script.

   Note: After creating the UDFs, Protegrity recommends to restart the MSSQL service. For more information, refer to Restarting the SQL server.

1.5 - Configuring the MSSQL Database Protector

The installer script implements the required configurations automatically while installing the MSSQL Database Protector. These settings are done automatically by the installation process and do not require manual intervention. The following table describes these settings.

Note: Truncating the user names could lead to a security vulnerability and could result in user names, without the domain names, being treated as duplicate.

Note: It is recommended not to truncate the domain name as it is insecure. If the SQL Server instance is configured to perform windows authentication, then the mixed mode authentication should be disabled. A Windows authenticated user must provide the user name with the domain or host name prepended.

Configuring the TRUSTWORTHY Database Property

It is necessary to secure the connection between any client application and a SQL Server instance. The TRUSTWORTHY property for the MSSQL database is used to indicate whether the SQL Server instance trusts the database and its contents.

Earlier, while running the CreateAssembly.sql script during installation of the MSSQL Database Protector, the TRUSTWORTHY property was set to ON in the ALTER DATABASE statement. Keeping the TRUSTWORTHY property set to ON, increases security risk. It is recommended to keep the TRUSTWORTHY property set to OFF to avoid malicious threats when the database is connected to the server. However, if the TRUSTWORTHY database property is set to OFF while running the CreateAssembly.sql script, then the installation fails with the following error:

CREATE ASSEMBLY for assembly 'DNPepConnector' failed because assembly 'DNPepConnector' is not authorized for PERMISSION_SET = UNSAFE. 
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Note: It is recommended to avoid changing the TRUSTWORTHY property setting. An alternative method to mitigate this issue is that a certificate can be created for the MSSQL database using the signed dll from Protegrity. From this certificate a certificate-based login can be created for the database. An authorized certificate signed by a trusted source can validate the secured connection between the SQL Server instance and the database. A login is created with the certificate to connect the database securely with the server.

For more information about how to create a certificate-based login for the MSSQL database using the signed dll from Protegrity, refer to the section Managing Certificate-Based Login.

For more information about configuring the TRUSTWORTHY** property and creating a certificate, refer to the sections TRUSTWORTHY Database Property and Create a certificate for package signing respectively, in Microsoft’s website.

Updating Parameters in the config.ini File

The MSSQL Database Protector provides the following files that contain different parameters to control the protector behavior:

• config.ini - provides parameters to control the protector behavior.
• rpagent.cfg - provides parameters to control the RPAgent behavior.

To update paramenters in the config.ini file follow the steps below:

1. Log in to the node.

2. Navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\data directory.

3. To open the config.ini file, run the following command:

   vi config.ini
   

4. Press ENTER.

   The command opens the config.ini file.

   ###############################################################################
   # Protector configuration
   ###############################################################################
   [protector]
   
   # Cadence determines how often the protector connects with ESA / proxy to fetch the policy updates in background.
   # Default is 60 seconds. So by default, every 60 seconds protector tries to fetch the policy updates.
   # If the cadence is set to "0", then the protector will get the policy only once.
   #
   # Default 60.
   cadence = 60
   
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. Update the parameters, as per the description in the table.

   Parameter	Description
   cadence	Specifies the frequency at which the protector retrieves the policy. The default value is 60 seconds. If the cadence is set to “0”, then the protector will get the policy only once.
   mode	Specifies the approach of handling logs when the connection to the Log Forwarder is lost.

6. Save the changes to the config.ini file.

Updating the parameters in the rpagent.cfg file

1. Log in to the required node.

2. Navigate to the C:\Program Files\Protegrity\rpagent\data directory.

3. To open the rpagent.cfg file, run the following command:

   vi rpagent.cfg
   

4. Press ENTER.

   The command opens the rpagent.cfg file.

   ###############################################################################
   # Resilient Package Sync Config
   ###############################################################################
   [sync]
   
   # Protocol to use when communicating with the service providing Resilient Packages.
   # Use 'https' for ESA or 'shmem' for local shared memory.
   protocol = https
   
   # Host/IP to the service providing Resilient Packages
   host = <IP_address>
   port = 8443
   
   # Path to CA certificate
   ca = /opt/protegrity/rpagent/data/CA.pem
   
   # Path to client certificate
   cert = /opt/protegrity/rpagent/data/cert.pem
   
   # Path to client certificate key
   key = /opt/protegrity/rpagent/data/cert.key
   
   # Path to a secret file that is used to decrypt the client certificate key.
   # When using a custom certificate bundle, the 'secretcommand' can instead be
   # used to execute an external command that obtains the secret.
   secretfile = /opt/protegrity/rpagent/data/secret.txt
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. Update the parameters, as per the description in the table.

   Parameter	Description
   interval	Specifies the frequency at which the RPAgent retrieves the policy. The minimum value is 1 second and the maximum value is 86400 seconds. This is an optional parameter and must be included in the Sync section of the rpagent.cfg file.
   protocol	Specifies the protocol to use when communicating with the service providing Resilient Packages.
   host	Specifies the hostname to the service providing the Resilient packages.
   port	Specifies the port to the service providing the Resilient packages.
   ca	Specifies the path to the CA certificate.
   cert	Specifies the path to the client certificate.
   key	Specifies the path to the client certificate key.
   secretfile	Specifies the path to the secret file that is used to decrypt the client certificate key.
   mode	Specifies the approach of handling logs when the connection to the Log Forwarder is lost.
   host	Specifies the hostname or the IP address to where the Log Forwarder will forward the audit logs from the protector.

6. Save the changes to the rpagent.cfg file.

Restarting SQL server to apply configuration changes

Note: After the initial installation using the default path, restart the MSSQLSERVER service before executing any queries. If the installation path is modified in later installations, the MSSQLSERVER service must be restarted again prior to query execution.

If the protector is already installed, restart the SQL Server to apply the config.ini changes.
To restart the SQL Server follow the steps below:

1. Open SQL Server Management Studio > SQL Server Configuration Manager.
2. In the left pane, select SQL Server Services.
3. Identify the SQL Server instance to restart:
   • SQL Server MSSQLSERVER for the default instance, or
   • SQL Server <instance_name> for a named instance.
4. Right-click the selected SQL Server instance.
5. Click Restart.
   The SQL Server service restarts and the configuration changes are implemented.

1.5.1 - Configuring MSSQL User Access Permissions

A user must be granted access and permissions to the certain tables such that they can query the database for members and groups.

Grant the following privilege rights to the user, defined in the member source configuration on the ESA, for executing the queries:

• Select access to MASTER.SYSLOGINS
• Select access to SYSUSERS

1.5.2 - Impersonating a User

User impersonation is a security feature that allows one user to temporarily act under the permissions of another user (usually a privileged user) within the same session. This enables controlled execution of privileged actions without permanently granting elevated access, and all actions are audited as performed by the impersonated user.

To impersonate a user:

1. Login to the server as the proxy user, USER1.

2. To change the user execution to privileged user, USER2, run the following command:

   EXECUTE AS USER = 'USER2'
   

3. Perform the protect/unprotect functionality on the data using a data element.

4. Disconnect the current session and initiate the session as USER1.

   Similarly, the EXEC AS statement enables impersonation of a privileged user to perform operations according to the user’s assigned privileges.

   Note:

   • User impersonation settings apply only to the current session.
   • If a proxy user impersonates a privileged user and performs any operation, then entries in the Audit logs are displayed as performed by the privileged user, and not the proxy user.

1.6 - Upgrading the Database Protector

To upgrade the MSSQL Database Protector:

1. Drop the existing UDFs.

2. Uninstall the existing version of the protector.

3. Install the new version of the protector.

4. Start the RPAgent.

5. Create the new user-defined functions.

1.6.1 - Preparing the System for Upgrade

Before proceeding with an upgrade, stop the required services and create a backup of all the configuration files.

To prepare the system for an upgrade:

1. Log in to the database server as a user with the required permissions.

2. In the Windows search box, type Run

3. Press ENTER.

   The Run dialog box appears.

4. Type services.msc.

5. Press ENTER.

   The Services window appears.

6. To stop the RPAgent, right-click the Resilient Package Agent service and select Stop.

7. To stop the Log Forwarder, right-click the Logforwarder service and select Stop.

Note: To preserve the current configuration, copy the config.ini file to the required location. The config.ini file is available in the C:\Program Files\Protegrity\Database Protector\sqlserver\data directory.

1.6.2 - Dropping the UDFs

After stopping the services and creating a backup of the configuration files, drop the existing user defined functions.

To delete the UDFs:

1. Log in to the database server as the user that has the permissions.

2. Navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\sqlscripts folder.

3. Execute the DropObjects.sql file.

4. Create a backup of the Database Protector directory.

1.6.3 - Upgrading the Database Protector

Note: Starting with version 7.2.0, for first‑time ESA installations, initialize Policy Management before installing the protector. For more information about initializing the Policy Management, refer to the section Initializing the Policy Management.

To upgrade the Database Protector, install the newer version of the Database protector. For more information, refer Installing the MSSQL Database Protector.

1.6.4 - Recreating the UDFs

After the upgrade, recreate the required UDFs to restore the Database Protector functionality.

To re-create the UDFs:

1. Log in to the database server as the user that has the permissions.

2. Navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\sqlscripts directory.

3. To register the assembly, execute the CreateAssembly.sql script.

4. To create the UDFs, execute the CreateFunctions.sql script.

1.6.5 - Restarting the RPAgent

After the upgrade, restart the required services and deploy policies to restore Database Protector operations.

To restart the RPAgent service:

1. In the Windows search box, type Run.

2. Press ENTER.

   The Run dialog box appears.

3. Type services.msc.

4. Press ENTER.

   The Services window appears.

5. To restart the RPAgent service, right-click Resilient Package Agent service and select Restart.

6. To restart SQL Server, right-click on the SQL Server service and select Restart.

7. Deploy the policies from the ESA to the Database Protector.

1.7 - Uninstalling the MSSQL Database Protector

The section describes the procedures to uninstall the MSSQL Database Protector and the components.

Before proceeding to uninstall the MSSQL Database protector, stop the MSSQL Service.

To stop the MSSQL Service

1. Navigate to Start > Control Panel > System and Security > Administrative Tools > Services.

   Note: Alternatively, navigate to the Windows > Start > Run. In the Run window, type services.msc and then, click OK.

   The Services window appears.

2. Select MSSQL Server.

3. To stop the MSSQL Service, select Action > Stop.

1.7.1 - Dropping the Database Protector UDFs

1. Log in to the MSSQL database with a username that owns the UDF functions.

   Note: The DropObjects.sql script must be executed either before or after uninstalling all MSSQL Server Database Protector components.

2. Navigate to the C:\Program Files\Protegrity\Database Protector\sqlserver\sqlscripts directory.

3. Open the DropObjects.sql file.

4. Replace the default database name with the database name to uninstall the UDFs.

5. Save the changes to the DropObjects.sql file.

6. To uninstall the UDFs, execute the DropObjects.sql script.

1.7.2 - Uninstalling the RPAgent

1. Navigate to Start > Control Panel > System and Security > Administrative Tools > Services.

   Note: Alternatively, navigate to the Windows > Start > Run. In the Run window, type services.msc and then, click OK.

   The Services window appears.

2. Select Protegrity Resilient Package Agent.

3. To stop the RPAgent server, select Action > Stop.

4. To remove the RPAgent component:

   1. From the Windows menu, navigate to Start > Control Panel > Programs > Programs and Features.

   2. From the list of programs, under the Name column, select Protegrity Resilient Package Agent.

   3. Click Uninstall.

      Note: Alternatively, go to the C:\Program Files\Protegrity\rpagent directory. To uninstall the RPAgent, select unins000 file and then double-click it.

      The RPAgent installer deletes the rpagent directory.

1.7.3 - Uninstalling the Log Forwarder

1. Navigate to Start > Control Panel > System and Security > Administrative Tools > Services.

   Note: Alternatively, navigate to the Windows > Start > Run. In the Run window, type services.msc and then, click OK.

   The Services window appears.

2. Select Logforwarder.

3. To stop the Log Forwarder, select Action > Stop.

4. To remove the Log Forwarder component:

   1. From the Windows menu, navigate to Start > Control Panel > Programs > Programs and Features.

   2. From the list of programs, under the Name column, select Logforwarder.

   3. Click Uninstall.

   Note: Alternatively, go to the C:\Program Files\Protegrity\logforwarder directory. To uninstall the Log Forwarder, select unins000 file and then double-click it.

   The installer removes the Log Forwarder and deletes the Log Forwarder directory.

1.7.4 - Removing the Database Protector

The section describes the procedures to uninstall the MSSQL Database Protector and the components.

1. From the Windows menu, navigate to Start > Control Panel > Programs > Programs and Features.

   The Programs and Features window appears.

2. From the list of programs, under the Name column, select Protegrity Data security platform - SQL Server UDF.

3. Click Uninstall.

   Note: Alternatively, go to the C:\Program Files\Protegrity\Database Protector directory. To uninstall the Protegrity Data security platform - SQL Server UDF, select unins000 file and then double-click it.

   The Protegrity Data security platform - SQL Server UDF is uninstalled and the Database Protector directory is deleted.

2 - Oracle Database Protector

The Oracle Database Protector can be installed by the user with sudoer permissions and the Oracle admin user. This section discusses the installation with a user having the sudoer permissions. Wherever possible, the oracle commands for Oracle admin user would be provided.

To use the Oracle Database Protector, update the environment variables in Oracle.

User Privileges

The Oracle Database Protector installation can be broadly divided into installing the RPAgent and installing the UDFs. The RPAgent installation establishes the connection between the ESA and the Database Protector, while the UDFs use the policies to enforce protection on the data.

User for retrieving users from Oracle Database

For policies to be defined in the ESA, users can be imported from any of the multiple sources such as Active Directory (AD), file, or an Oracle database. To pull users from an Oracle database, a membersource must be created. The following information applies if the users must be pulled from an Oracle database.

To retrieve users from the Member Source Server:

1. Either create a functional database user with create session permissions
   or
   Use an existing user with create session permissions
2. Grant the following two specific grants:
   • Grant select on sys.dba_roles to protegrity
   • Grant select on sys.dba_role_privs to protegrity

Where, protegrity is the functional user created.

User for installing UDFs

After the RPAgent is installed, the UDFs can be installed on the Oracle Database server. Create a functional database user with the following privilege rights:

• Grant unlimited tablespace to USER1
• Grant create session to USER1
• Grant select any table to USER1
• Grant create library to USER1
• Grant create procedure to USER1
• Grant drop public synonym to USER1
• Grant create public synonym to USER1
• Grant create table to USER1
• Grant create view to USER1

Where, USER1 is the functional user created.

2.1 - Understanding the Architecture

The architecture for the Oracle Database Protector is depicted in the image below.

2.2 - System Requirements

Ensure that the following prerequisites are met:

• The Oracle Database is installed and configured.

• The Enterprise Security Administrator (ESA) is installed, configured, and running.

• The IP address or host name of the ESA is noted.

• Ensure that Policy Management (PIM) has been initialized on the ESA. The initialization of PIM ensures that cryptographic keys for protecting data and the policy repository have been created.

• Download and save the Oracle Database Protector, DatabaseProtector_<operating_system>-<arch>_<Oracle_version>-64_<version>.tgz, made available by Protegrity.

• Even if it is not mandatory, create a backup of the database where the Oracle Database Protector and the UDFs will be installed.

• Access to the server, as the oracle instance owner or the user created specifically for Protegrity, is available.

• Access to the Oracle database as the sysdba superuser, should be available.

2.3 - Preparing the Environment

The steps to prepare the environment to install the Oracle Databsae Protector are explained in the sub-sections mentioned below.

2.3.1 - Extracting the Installation Package

1. Log in to the Oracle database server with an account having the required privileges.
2. Save the Oracle database protector installation package, DatabaseProtector_<operating_system>-<arch>_<Oracle_distribution>-64_<version>.tgz, made available by Protegrity, in any sample directory. For example, /opt/protegrity/
3. Navigate to the /opt/Protegrity/ directory.
4. To extract the contents, run the following command:

   tar -xvf DatabaseProtector_Linux-ALL-64_x86-64_Oracle-ALL-64_<DBP_version>.tgz
   

5. Press ENTER. The command extracts the package and the signature files.

   DatabaseProtector_Linux-ALL-64_x86-64_Oracle-ALL-64_<DBP_version>.tgz
   signatures/
   signatures/DatabaseProtector_Linux-ALL-64_x86-64_Oracle-ALL-64_<DBP_version>.tgz_10.0.sig
   

6. To extract the contents of the installation package, run the following command:

   tar -xvf DatabaseProtector_Linux-ALL-64_x86-64_Oracle-ALL-64_<DBP_version>.tgz
   

7. Press ENTER. The command extracts the files from the package.

   LogforwarderSetup_Linux_x64_<DBP_version>.sh
   RPAgentSetup_Linux_x64_<DBP_version>.sh
   PepOracleSetup_Linux_x64_<DBP_version>.sh
   U.S.Patent.No.6,321,201.Legend.txt
   

2.3.2 - Installing the Log Forwarder

1. Log in to the database server as the user that has the permissions to install the Log Forwarder. Usually, this tends to be the instance owner.
2. Navigate to the directory where the installation files are extracted.
3. To install the Log Forwarder, run the following command:

   ./LogforwarderSetup_Linux_x64_<DBP_version>.sh
   

4. Press ENTER. The prompt to enter the audit store endpoint appears.

   Enter the audit store endpoint (host), alternative (host:port) to use another port than the default port 9200 :
   

5. Enter the IP address of the audit store.
6. Press ENTER. The prompt to enter additional endpoint appears.

   Audit store endpoints: <Audit_store_IP_address>:9200
   Do you want to add another audit store endpoint? [y/n]:
   

7. To skip adding additional endpoints, type no.
8. Press ENTER. The prompt to continue the installation appears.

   These audit store endpoints will be added:
   <Audit_store_IP_address>:9200
   
   Type 'y' to accept or 'n' to abort installation:
   

9. To continue the installation, type yes.
10. Press ENTER. The script extracts the files and installs the Log Forwarder.

    Unpacking...
    Extracting files...
    Protegrity Log Forwarder installed in /opt/protegrity/logforwarder.
    

2.3.3 - Installing the RPAgent

1. Log in to the database server as the user that has permissions to install the RPAgent.
2. Navigate to the directory where the installation files are extracted.
3. To install the RPAgent, run the following command:

   ./RPAgentSetup_Linux_x64_<DBP_version>.sh
   

4. Press ENTER. The prompt to enter ESA host name or IP address appears.

   Please enter upstream host name or IP address[]:
   

5. Enter the IP address of the ESA.
6. Press ENTER. The prompt to enter the username to download the certificates appears.

   Please enter the user name for downloading certificates[]:
   

7. Enter the username to download the certificates from ESA.
8. Press ENTER. The prompt to enter the password to download the certificates appears.

   Please enter the password for downloading certificates[]:
   

9. Enter the password.
10. Press ENTER. The script connects to the ESA, retrives the JWT token, extracts the certificates, and installs the RPAgent.

    Unpacking...
    Extracting files...
    Obtaining token from <ESA_IP_Address>:25400...
    Downloading certificates from <ESA_IP_Address>:25400...
    % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                    Dload  Upload   Total   Spent    Left  Speed
    100 11264  100 11264    0     0   136k      0 --:--:-- --:--:-- --:--:--  137k
    
    Extracting certificates...
    Certificates successfully downloaded and stored in /opt/protegrity/rpagent/data
    
    Protegrity RPAgent installed in /opt/protegrity/rpagent.
    

2.4 - Installing the Oracle Database Protector

2.4.1 - Installing the Policy Enforcement Point (PEP)

1. Log in to the node where the installation files are extracted.
2. To install the Oracle objects, run the following command:

   ./PepOracleSetup_Linux_x64_<DBP_version>.sh
   

3. Press ENTER. The prompt to continue appears.

   *****************************************************
   Welcome to the Database Protector Setup Wizard
   *****************************************************
   
   This will install the oracle objects on your computer
   Do you want to continue? [yes or no]
   

4. To continue, type yes.
5. Press ENTER. The prompt to enter the installation directory appears.

   Enter installation directory.
   A new directory will be created in the installation directory.
   [/opt/protegrity]:
   

6. Enter the location to install the Oracle objects.
7. Press ENTER. The command extracts the files and installs the objects.

   Unpacking...
   Extracting files...
   oracle objects installed in /opt/protegrity/databaseprotector/oracle.
   

2.4.2 - Creating the User Defined Functions (UDFs)

The Oracle Database Protector provides the createobjects.sql script to create or install the UDFs. Before executing the createobjects.sql script, configure the listener.ora, tnsnames.ora, and the extproc.ora configuration files, depending on the version of the Oracle database.

To install UDFs for the Oracle Database Protector:

1. Connect to the database as the oracle user with the database owner credentials.

2. Navigate to the /opt/protegrity/databaseprotector/oracle/sqlscripts/ directory.

3. To install the UDFs, run the following command:

   sqlplus User1/Password1 @createobjects.sql
   

   where, User1 and Password1 are the credentials of the database owner. The symbol \ is used for Windows and / for UNIX environments.

4. To view the list of all the installed UDFs, run the following command:

   select PROCEDURE_NAME from user_procedures order by 1;
   

5. To verify the successful installation of the UDFs, execute any one of the following queries:

   select pty.whoami() from dual;
   

   The function returns the name of the user that is logged in to the database.

   select pty.getversion() from dual;
   

   The function returns the protector version.

2.5 - Configuring the Oracle Database Protector

The Oracle Database Protector provides the following files that contain different parameters to control the protector behavior:

• config.ini - provides parameters to control the protector behavior.
• rpagent.cfg - provides parameters to control the RPAgent behavior.

Updating the parameters in the config.ini file:

1. Log in to the node.

2. Navigate to the /opt/protegrity/databaseprotector/oracle/data directory.

3. To open the config.ini file, run the following command:

   vi config.ini
   

4. Press ENTER.

   The command opens the config.ini file.

   ###############################################################################
   # Protector configuration
   ###############################################################################
   [protector]
   
   # Cadence determines how often the protector connects with ESA / proxy to fetch the policy updates in background.
   # Default is 60 seconds. So by default, every 60 seconds protector tries to fetch the policy updates.
   # If the cadence is set to "0", then the protector will get the policy only once.
   #
   # Default 60.
   cadence = 60
   
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. Update the parameters, as per the description in the table.

   Parameter	Description
   cadence	Specifies the frequency at which the protector retrieves the policy. The default value is 60 seconds. If the cadence is set to “0”, then the protector will get the policy only once.
   mode	Specifies the approach of handling logs when the connection to the Log Forwarder is lost.

6. Save the changes to the config.ini file.

Updating the parameters in the rpagent.cfg file:

1. Log in to the required node.

2. Navigate to the /opt/protegrity/rpagent/data directory.

3. To open the rpagent.cfg file, run the following command:

   vi rpagent.cfg
   

4. Press ENTER.

   The command opens the rpagent.cfg file.

   ###############################################################################
   # Resilient Package Sync Config
   ###############################################################################
   [sync]
   
   # Protocol to use when communicating with the service providing Resilient Packages.
   # Use 'https' for ESA or 'shmem' for local shared memory.
   protocol = https
   
   # Host/IP to the service providing Resilient Packages
   host = <IP_address>
   port = 8443
   
   # Path to CA certificate
   ca = /opt/protegrity/rpagent/data/CA.pem
   
   # Path to client certificate
   cert = /opt/protegrity/rpagent/data/cert.pem
   
   # Path to client certificate key
   key = /opt/protegrity/rpagent/data/cert.key
   
   # Path to a secret file that is used to decrypt the client certificate key.
   # When using a custom certificate bundle, the 'secretcommand' can instead be
   # used to execute an external command that obtains the secret.
   secretfile = /opt/protegrity/rpagent/data/secret.txt
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. Update the parameters, as per the description in the table.

   Parameter	Description
   interval	Specifies the frequency at which the RPAgent retrieves the policy. The minimum value is 1 second and the maximum value is 86400 seconds. This is an optional parameter and must be included in the Sync section of the rpagent.cfg file.
   protocol	Specifies the protocol to use when communicating with the service providing Resilient Packages.
   host	Specifies the hostname to the service providing the Resilient packages.
   port	Specifies the port to the service providing the Resilient packages.
   ca	Specifies the path to the CA certificate.
   cert	Specifies the path to the client certificate.
   key	Specifies the path to the client certificate key.
   secretfile	Specifies the path to the secret file that is used to decrypt the client certificate key.
   mode	Specifies the approach of handling logs when the connection to the Log Forwarder is lost.
   host	Specifies the hostname or the IP address to where the Log Forwarder will forward the audit logs from the protector.

6. Save the changes to the rpagent.cfg file.

2.5.1 - User Impersonation

This section describes how to impersonate a user in the Oracle database protector. The user impersonation feature enables you to perform operations and access resources on behalf of another user. Service users leverage this feature to impersonate individual users. However, to supply user context to execute a query, upper applications provide the CLIENT_IDENTIFIER. Set the impersonation parameter to YES in the config.ini file, to use the CLIENT_IDENTIFIER parameter of the inbuilt USERENV application context SYS_CONTEXT provided by the Oracle database.

To impersonate a user:

1. Log in to the node where the Oracle database is installed.

2. Navigate to the /opt/protegrity/databaseprotector/oracle/data/ directory.

3. To open the config.ini file, run the following command:

   vi config.ini
   

4. Press ENTER.

   The command opens the config.ini file.

   ###############################################################################
   # Protector configuration
   ###############################################################################
   [protector]
   
   # Cadence determines how often the protector connects with ESA / proxy to fetch the policy updates in background.
   # Default is 60 seconds. So by default, every 60 seconds protector tries to fetch the policy updates.
   # If the cadence is set to "0", then the protector will get the policy only once.
   #
   # Default 60.
   cadence = 60
   
   
   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   #
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   

5. To include the impersonation parameter and set the value to YES, add the following code:

   [userimpersonation]
   impersonation = yes/no or YES/NO
   

   The default value of the impersonation parameter is set to NO or no.

6. Assign 644 permissions to the config.ini file. This is required only tf the ownership of the config.ini file is not set to the oracle user and the oinstall group.

7. Connect to the database session using the service account. For example, USER1.

8. To set the CLIENT_IDENTIFIER, execute the following query:

   EXEC DBMS_SESSION.SET_IDENTIFIER ('USER2');
   

9. Press ENTER. The query returns the name of the user for whom you set the CLIENT_IDENTIFIER parameter.

   USER2
   

10. To verify the value that is set for the CLIENT_IDENTIFIER parameter, execute the following query: SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') from dual; SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')

11. Press ENTER. The query returns the name of the user for whom you set the CLIENT_IDENTIFIER parameter.

    USER2
    

    Warning: When you set the value of the impersonation parameter to yes/YES, then set a value for the the CLIENT_IDENTIFIER parameter. The protect/unprotect UDFs will run only after the value for the CLIENT_IDENTIFIER parameter is set. If you set the value of the impersonation parameter to yes/YES, and fail to set the value for the CLIENT_IDENTIFIER parameter, then the PTY.WHOAMI() UDF will return the username as <no_user>. This will cause the protect/unprotect operations to fail with the Failed to retrieve user error message.

12. To verify the user who is logged into the database session, execute the following query:

    select pty.whoami() from dual;
    

13. Press ENTER. The query returns the name of the user that is logged into the current database session.

    USER2
    

14. To clear the value set for the CLIENT_IDENTIFIER parameter, execute the following query:

    EXEC DBMS_SESSION.CLEAR_IDENTIFIER;
    

2.5.2 - Enterprise User Security (EUS) in the Oracle Database

Enterprise User Security (EUS) is an important component of the Oracle database that allows you to centrally manage the database users across the enterprise. Enterprise users are the users that are defined and managed in a directory. Every enterprise user has a unique identity across the enterprise. The Enterprise User Security relies on the Oracle Identity Management infrastructure, which uses an LDAP-compliant directory service to centrally store and manage the users.

Protegrity supports the following authentication methods:

• Password-based authentication
• SSL-based authentication
• Kerberos-based authentication

In the following list, the type of user is followed by the value returned:

• Password-authenticated enterprise user: nickname (same as the login name)
• Password-authenticated database user: the database username (same as the schema name)
• SSL-authenticated enterprise user: the DN in the user’s PKI certificate
• SSL-authenticated external user: the DN in the user’s PKI certificate
• Kerberos-authenticated enterprise user: Kerberos principal name
• Kerberos-authenticated external user: Kerberos principal name, which is the same as the schema name

The Oracle database protector supports the retrieval of the user information using the AUTHENTICATED_IDENTITY parameter that returns the identity used for the authentication.

Using the EUS Feature

The instructions and examples provided in the section use Kerberos-based authentication.

Note:

• Ensure that the username in the ESA policy contains only the username and does not include the domain name. For example, USER1.
• Currently, only one domain name is supported.

To use the EUS feature:

1. To create a Kerberos ticket for the enterprise user, run the following command:

   okinit <username>
   

2. Press ENTER. The command prompts for the password of the enterprise user.

   [oracle@db ~]$ okinit USER1
   Kerberos Utilities for Linux: Version 18.0.0.0.0 - Production on 15-DEC-2021 06:07:06
   Copyright (c) 1996, 2017 Oracle. All rights reserved.
   Configuration file : /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/kerberos/krb5.conf.
   Password for USER1@TESTLAB.COM:
   

3. Enter the password.
4. Press ENTER.
5. To verify whether the authentication ticket is generated successfully, run the following command:

   oklist
   

6. Press ENTER. The command displays the authentication ticket details.

   [oracle@db ~]$ oklist
   Kerberos Utilities for Linux: Version 18.0.0.0.0 - Production on 15-DEC-2021 06:07:37
   Copyright (c) 1996, 2017 Oracle. All rights reserved.
   Configuration file : /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/kerberos/
   krb5.conf.
   Ticket cache: FILE:/tmp/krb5cc_54321
   Default principal: USER1@TESTLAB.COM
   Valid starting Expires Service principal
   12/15/21 06:07:06 12/15/21 16:07:06 krbtgt/TESTLAB.COM@TESTLAB.COM
   renew until 12/16/21 06:07:06
   [oracle@db ~]$
   

7. To login to the Oracle database, run the following command:

   sqlplus /@<database_name>
   

8. Press ENTER. The SQL prompt appears.
9. To verify the authentication method, run the following command:

   select sys_context('USERENV','AUTHENTICATION_METHOD') from dual;
   

10. Press ENTER. The command displays the authentication method.

    select sys_context('USERENV','AUTHENTICATION_METHOD') from dual;
    SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
    --------------------------------------------------------------------------------
    KERBEROS
    

11. To view the user, run the following command:

    select user from dual;
    

12. Execute a sample protect operation.

    SQL> select pty.ins_encrypt('AES128', 'Original data', 0) from dual;
    PTY.INS_ENCRYPT('AES128','ORIGINALDATA',0)
    --------------------------------------------------------------------------------
    3713D5C1E058701568115B28885707CA
    SQL>
    

13. Execute a sample unprotect operation.

    SQL> select pty.sel_decrypt('AES128', pty.ins_encrypt('AES128', 'Protegrity', 0) , 0) from dual;
    PTY.SEL_DECRYPT('AES128',PTY.INS_ENCRYPT('AES128','PROTEGRITY',0),0)
    --------------------------------------------------------------------------------
    Protegrity
    SQL>
    

Retrieving User Information

This section describes how to use the AUTHENTICATED_IDENTITY parameter to retrieve the information of an enterprise user.

To fetch the information of an enterprise user, run the following query:

select sys_context( 'userenv', 'AUTHENTICATED_IDENTITY' ) from dual;

Note: The AUTHENTICATED_IDENTITY parameter contains the information of the enterprise user and returns the identity that is used in the authentication.

2.6 - Uninstalling the Oracle Database Protector

The process to uninstall the Oracle Database Protector involves the following steps:

1. Dropping the User Defined Functions.
2. Uninstalling the RPAgent.
3. Uninstalling the Log Forwarder.

Dropping the User Defined Functions

1. Log in to the Oracle Database server using the same account used to create the UDFs.
2. Navigate to the /opt/protegrity/databaseprotector/oracle/sqlscripts/ directory.
3. To drop the UDFs, run the following command:

   sqlplus USER1/Password1 @dropobjects.sql
   

Uninstalling the RPAgent

Before uninstalling the RPAgent, Protegrity recommends creating a backup.

1. Log in to the Oracle Database server.
2. Navigate to the /opt/protegrity/rpagent/data directory.
3. To stop the RPAgent, run the following command:

   rpagentctrl stop
   

4. Delete the rpagent directory.

Uninstalling the Log Forwarder

Before uninstalling the Log Forwarder, Protegrity recommends creating a backup.

1. Log in to the Oracle Database server.
2. Navigate to the /opt/protegrity/logforwarder/data directory.
3. To stop the RPAgent, run the following command:

   logforwarderctrl stop
   

4. Delete the logforwarder directory.

3 - User Defined Functions and APIs

3.1 - Oracle User Defined Functions and APIs

3.1.1 - General UDFs

This section includes the general UDFs that can be used to retrieve the Oracle Protector version and the current user.

pty.whoami

The UDF returns the name of the user who is currently logged in to the database.

Signature:

pty.whoami()

Parameters:
None

Returns:
This UDF returns the name of the user as the VARCHAR2 string.

Exception:
None

Example:

select pty.whoami() ”Test of WhoAmI” from dual;
Test of WhoAmI
---
USER1

pty.getversion

This UDF returns the version of the protector.

Signature:

pty.getversion()

Parameters:
None

Returns:
This UDF returns the version of the protector as the VARCHAR2 string.

Example:

select pty.getversion() ”Test of GetVersion” from dual;

Test of GetVersion
---
x.x.x.x

3.1.2 - Access Check Procedures

The procedures listed here check whether the user is granted access permissions to the data element. The procedures will pass if the user has access. Otherwise, it casts an exception with the reason for failure.

The permissions for protect, unprotect, and reprotect are defined based on the roles assigned to the user. For more information about how to grant these permissions and assign roles, refer to Policy Management.

pty.ins_check

This procedure determines if the user has insert(protect) access to the data element.

Signature:

pty.ins_check(dataelement VARCHAR)

Parameters:

Returns:
The procedure returns the value as Success, if the user can insert data.

Example:

declare
begin
  dbms_output.put_line('Test of INSERT check procedure');
  dbms_output.put_line('------------------------------');
  pty.ins_check('DE_AES256');
end;

pty.sel_check

The procedure determines whether the user has select(unprotect) access to a data element.

Signature:

pty.sel_check(dataelement VARCHAR)

Parameters:

Returns:
The procedure returns the value as success, if the user has access.

Example:

declare
begin
  dbms_output.put_line('Test of SELECT check procedure');
  dbms_output.put_line('------------------------------');
  pty.sel_check('DE_AES256');
end;

pty.upd_check

This procedure determines if the user has update(reprotect) access to the data element.

Signature:

pty.upd_check(dataelement VARCHAR)

Parameters:

Returns:
The procedure returns the value as Success, if the user has update permissions.

Example:

declare
begin
  dbms_output.put_line('Test of UPDATE check procedure');
  dbms_output.put_line('------------------------------');
  pty.upd_check('DE_AES256');
end;

3.1.3 - Insert Encryption UDFs

These UDFs encrypt the data.

Note: The permissions for protect, unprotect, and reprotect are defined based on the roles assigned to the user. For more information about how to grant these permissions and assign roles, refer to Policy Management.

pty.ins_encrypt

This UDF encrypts data with a data element for encryption.

Signature:

pty.ins_encrypt (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt('DE_AES256', 'Original data', 0) "Test of INSERT encrypt func" from dual;

pty.ins_encrypt_char

This UDF encrypts the CHAR data with a data element for encryption.

Signature:

pty.ins_encrypt_char (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_char('DE_AES256', 'Original data', 0) "Test of INSERT enc CHAR func" from dual;

pty.ins_encrypt_varchar2

This UDF encrypts the VARCHAR2 data with a data element for encryption.

Signature:

pty.ins_encrypt_varchar2(dataelement CHAR, inval VARCHAR2, scid1 BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the LONG RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_varchar2('DE_AES256', 'Original data', 0) "Test INSERT enc VARCHAR2 func" from dual;

pty.ins_encrypt_date

This UDF encrypts the DATE data with a data element for encryption.

Note: To protect the Oracle input data type DATE, use the UDFs as described in Oracle Input Data Type to UDF Mapping to identify the appropriate UDF as per requirements.

Signature:

pty.ins_encrypt_date(dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_date('DE_AES256', '23-OCT-14', 0) "Test of INSERT enc DATE func" from dual;

pty.ins_encrypt_integer

This UDF encrypts the INTEGER data with a data element for encryption.

Signature:

pty.ins_encrypt_integer (dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_encrypt_integer('DE_AES256', 12345, 0) "Test of INSERT enc INT func" from dual;

pty.ins_encrypt_real

This UDF encrypts the REAL data with a data element for encryption.

Signature:

pty.ins_encrypt_real (dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_real('DE_AES256', 1234.1234, 0) "Test of INSERT enc REAL func" from dual;

pty.ins_encrypt_float

This UDF encrypts the FLOAT data with a data element for encryption.

Signature:

pty.ins_encrypt_float (dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_encrypt_float('DE_AES256', 1234.1234, 0) "Test of INSERT enc FLOAT func" from dual;

pty.ins_encrypt_number

This UDF encrypts the NUMBER data with a data element for encryption.

Signature:

pty.ins_encrypt_number (dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_encrypt_number('DE_AES256', 12345, 0) "Test of INSERT enc NUMBER func" from dual;

pty.ins_encrypt_raw

This UDF encrypts the RAW data, which is variable length binary data of maximum size 2000 bytes, with a data element for encryption.

Signature:

pty.ins_encrypt_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_encrypt_raw('DE_AES256', 'FFDD12345', 0) "Test of INSERT enc RAW func" from dual;

3.1.4 - Insert No-Encryption, Token, and FPE UDFs

These UDFs are used with Tokenization, Format Preserving Encryption (FPE) and, No Encryption data elements.

pty.ins_char

This UDF protects the CHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.ins_char (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the CHAR data type.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_char('DE_CHAR', 'Original data', 0) "Test of INSERT CHAR func" from dual;

pty.ins_varchar2

This UDF protects the VARCHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

CAUTION: For Date type of data elements, the pty.ins_varchar2 UDF returns an invalid date format error if the input value falls between the non-existent date range from 05-OCT-1582 to 14-OCT-1582 of the Gregorian Calendar. For more information about the tokenization and de-tokenization of the cutover dates of the Proleptic Gregorian Calendar, refer to the section Date Tokenization for cutover Dates of the Proleptic Gregorian Calendar in Protection Methods Reference.

Signature:

pty.ins_varchar2 (dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the VARCHAR2 data type.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_varchar2('DE_VARCHAR2', 'Original data', 0) "Test of INSERT VARCHAR2 func" from dual; 

pty.ins_unicodenvarchar2

This UDF encrypts data with a data element.

Note: This UDF does not support masking.

Signature:

pty.ins_unicodenvarchar2 (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the NVARCHAR2 datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message that explains what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful protection without returning any error, but corruption of data can occur.

Example:

select pty.ins_unicodenvarchar2('fpe_unicode', 'Original data', 0) "Test of INSERT encrypt func" from dual;

pty.ins_unicodevarchar2_tok

This UDF protects the VARCHAR2 data with a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.ins_unicodevarchar2_tok(dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the VARCHAR2 datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message that explains what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful protection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:

```
select pty.ins_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0) from dual;
```

```
select pty.ins_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0) from dual;
```

pty.ins_unicodenvarchar2_tok

This UDF protects the NVARCHAR2 data with a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.ins_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the NVARCHAR2 data type.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message that explains what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful protection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:
select pty.ins_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0) from dual;

```
select pty.ins_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0) from dual;
```

pty.ins_date

This UDF protects the DATE data with a tokenization and No Encryption data element.

Signature:

pty.ins_date (dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected DATE value, when No Encryption data element is used.
  
• This UDF returns the protected DATE value, when a tokenization data element is used and if the data element date format and the NLS_DATE_FORMAT environment variable for an Oracle session is the same as mentioned in the note above.

Exception:

• No Encryption Date Element: If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.
• Tokenization Date Element: Tokenization fails and the UDF terminates with an error message explaining what went wrong.

Example for No Encryption:

select PTY.ins_date('DE_NoEnc', '10-23-2014', 0) "Test of INSERT DATE func" from dual;

Example for Tokenization:

select PTY.ins_date('DE_DATE', '10-23-2014', 0) "Test of INSERT DATE func" from dual;

pty.ins_integer

This UDF protects the INTEGER data with a tokenization and No Encryption data element.

Signature:

pty.ins_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the INTEGER datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.ins_integer('DE_Integer', 12345, 0) "Test of INSERT INT func" from dual;

pty.ins_real

This UDF protects the REAL data with a No Encryption data element.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the REAL datatype.

Signature:
pty.ins_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as the REAL datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_real('DE_NoEnc', 1234.1234, 0) "Test of INSERT REAL func" from dual;

pty.ins_float

This UDF protects the FLOAT data with a No Encryption data element.

Signature:

pty.ins_float (dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as the FLOAT datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_float('DE_NoEnc', 1234.1234, 0) "Test of INSERT FLOAT func" from dual;

pty.ins_number

This UDF protects the NUMBER data with tokenization and No Encryption data elements.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the NUMBER datatype.

Signature:

pty.ins_number (dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as the NUMBER datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_number('DE_Integer', 12345, 0) "Test of INSERT NUMBER func" from dual;

pty.ins_raw

This UDF protects the RAW data with a No Encryption data element.

Signature:

pty.ins_raw (dataelement CHAR, inval RAW, scid BINARY_INTEGER\)

Parameters:

Returns:
This UDF returns the unprotected value as RAW data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_raw('DE_NoEnc', 'FFDD12345', 0) "Test of INSERT RAW func" from dual;

3.1.5 - Multiple Insert Encryption Procedures

These procedures encrypt one to four values of data with one procedure call. The user must be granted Protect access for the data element that will be used to execute these procedures. You can use the ins_check procedure to check whether the user has Protect access.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.encInsert

This procedure encrypts one value of VARCHAR2 data with one data element for encryption.

Signature:

pty.encInsert(dataelement VARCHAR2, cdata VARCHAR2, rdata RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

declare 
  raw_out raw(2000); 
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 1 
    COLUMN');
  dbms_output.put_line('----------------------------------------------');
  pty.encInsert('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data: ' || raw_out);
end;

pty.ins_encryptx2

This procedure encrypts two values of VARCHAR2 data with two data elements for encryption.

Signature:

pty.ins_encryptx2 (dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted values as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

Encrypted values are the output parameters 
declare 
  raw_out1 raw(2000);
  raw_out2 raw(2000);
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 2 
    COLUMNS');
  dbms_output.put_line('---------------------------------------------');
  pty.ins_encryptx2('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
end;

pty.ins_encryptx3

This procedure encrypts three values of VARCHAR2 data with three data elements for encryption.

Signature:

pty.ins_encryptx3(dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted values as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

declare 
  raw_out1 raw(2000);
  raw_out2 raw(2000);
  raw_out3 raw(2000);
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 3 
    COLUMNS');
  dbms_output.put_line('---------------------------------------------');
  pty.ins_encryptx3('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199', 
    raw_out3, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
end;

pty.ins_encryptx4

This procedure encrypts four values of VARCHAR2 data with four data elements for encryption.

Signature:

pty.ins_encryptx4(dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER, dataelement4 VARCHAR2, cdata4 VARCHAR2, rdata4 RAW, scid4 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

declare 
  raw_out1 raw(2000);
  raw_out2 raw(2000);
  raw_out3 raw(2000);
  raw_out4 raw(2000);
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 4 
    COLUMNS');
  dbms_output.put_line('---------------------------------------------');
  pty.ins_encryptx4('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199', 
    raw_out3, 0, 'DE_AES256', 'fhgdADGHSJddeg', raw_out4, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out4);
end;

3.1.6 - Select Decryption UDFs

The UDFs in this section decrypt the encrypted data. Unprotect access is required to use these procedures.

pty.sel_decrypt

This UDF decrypts the RAW data with an encryption data element.

Signature:

pty.sel_decrypt(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt('DE_AES256', PTY.ins_encrypt('DE_AES256', 'Original data', 0),0) "Test of SELECT dec func" from dual;

pty.sel_decrypt_char

This UDF decrypts the CHAR data with an encryption data element.

Signature:

pty.sel_decrypt_char(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_char('AES256', PTY.ins_encrypt_char('AES256', 'Original data', 0),0) "Test of SELECT dec CHAR func" from dual; 

pty.sel_decrypt_varchar2

This UDF decrypts the VARCHAR2 data with an encryption data element.

Signature:

pty.sel_decrypt_varchar2(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the VARCHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_varchar2('AES256', PTY.ins_encrypt_varchar2('AES256','Original data', 0),0) "Test of SELECT dec VARCHAR2 func" from dual;

pty.sel_decrypt_date

This UDF decrypts the DATE data with an encryption data element.

Signature:

pty.sel_decrypt_date(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the DATE datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_date('DE_AES256', PTY.ins_encrypt_date('DE_AES256', '23-OCT-14', 0),0) "Test of SELECT dec DATE func" from dual;

pty.sel_decrypt_integer

This UDF decrypts the INTEGER data with an encryption data element.

Signature:

pty.sel_decrypt_integer(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the INTEGER datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_integer('DE_AES256', PTY.ins_encrypt_integer('DE_AES256', 12345, 0),0) "Test of SELECT dec INT func" from dual;

pty.sel_decrypt_real

This UDF decrypts the REAL data with an encryption data element.

Signature:

pty.sel_decrypt_real(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the REAL datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_real('AES256', PTY.ins_encrypt_real('AES256',1234.1234,0),0) “Test of SELECT dec REAL func” from dual;

pty.sel_decrypt_float

This UDF decrypts the FLOAT data with an encryption data element.

Signature:

pty.sel_decrypt_float(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the FLOAT datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_float('DE_AES256', PTY.ins_encrypt_float('DE_AES256', 1234.1234, 0),0) "Test of SELECT dec FLOAT func" from dual;

pty.sel_decrypt_number

This UDF decrypts the NUMBER data with an encryption data element.

Signature:

pty.sel_decrypt_number(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NUMBER datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_number('DE_AES256', PTY.ins_encrypt_number('DE_AES256', 12345, 0),0) "Test of SELECT dec NUMBER func" from dual;

pty.sel_decrypt_raw

This UDF decrypts the RAW data with an encryption data element.

Signature:

pty.sel_decrypt_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the RAW data.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_decrypt_raw('AES256', PTY.ins_encrypt_raw('AES256', 'FFDD12345', 0),0) "Test of SELECT dec RAW func" from dual;

3.1.7 - Select No-Encryption, Token, and FPE UDFs

These UDFs unprotect the protected data. Unprotect access is required to use these UDFs.

pty.sel_char

This UDF unprotects the CHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.sel_char(dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_char('DE_DTP2_AES256_AN', PTY.ins_char('DE_DTP2_AES256_AN', 'Original data', 0),0) "Test of SELECT CHAR func" from dual;

pty.sel_varchar2

This UDF unprotects the VARCHAR2 data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.sel_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the VARCHAR2 datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_varchar2('DE_DTP2_AES256_AN', PTY.ins_varchar2('DE_DTP2_AES256_AN', 'Original data', 0),0) "Test of SELECT VARCHAR2 func" from dual;

pty.sel_unicodenvarchar2

This UDF unprotects the protected NVARCHAR data.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodenvarchar2(dataelement CHAR, inval NVARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NVARCHAR2 datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example:

select pty.sel_unicodenvarchar2('fpe_unicode', PTY.ins_unicodenvarchar2('fpe_unicode', 'Original data', 0),0) "Test of SELECT NVARCHAR2 func" from dual;

pty.sel_unicodevarchar2_tok

This UDF unprotects the VARCHAR2 data protected by a Unicode Base64 and Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodevarchar2_tok(dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as VARCHAR2.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example for Unicode Base64:

select pty.sel_unicodevarchar2_tok('TE_UNICODE_BASE64_SLT13_ASTYES', pty.ins_unicodevarchar2_tok('TE_UNICODE_BASE64_SLT13_ASTYES', 'Protegrity123',0),0) from dual;

Example for Unicode Gen2:
select pty.sel_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',pty.ins_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0),0) from dual; select pty.sel_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',pty.ins_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0),0) from dual;

pty.sel_unicodenvarchar2_tok

This UDF unprotects the NVARCHAR2 data protected by a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as NVARCHAR2.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:
select pty.sel_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',pty.ins_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0),0) from dual;

```
select 
pty.sel_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',pty.ins_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0),0) from dual;
```

pty.sel_date

This UDF unprotects the DATE data with a No Encryption data element.

Signature:

pty.sel_date(dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the DATE datatype.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_date('DE_NoEnc', PTY.ins_date('DE_NoEnc', '23-OCT-14', 0),0) "Test of SELECT DATE func" from dual;

pty.sel_integer

This UDF unprotects the INTEGER data with tokenization and No Encryption data elements.

Signature:

pty.sel_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the INTEGER datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.sel_integer('Integer4',PTY.ins_integer('integer',12344567,0),0) “Test of SELECT INT func” from dual;

pty.sel_real

This UDF unprotects the REAL data with a No Encryption data element.

Signature:

pty.sel_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the REAL datatype.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_real('DE_NoEnc', PTY.ins_real('DE_NoEnc', 1234.1234, 0),0) "Test of SELECT REAL func" from dual;

pty.sel_float

This UDF unprotects the FLOAT data with a No Encryption data element.

Signature:

pty.sel_float(dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the FLOAT datatype.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_float('DE_NoEnc', PTY.ins_float('DE_NoEnc', 1234.1234, 0),0) "Test of SELECT FLOAT func" from dual; 

pty.sel_number

This UDF unprotects the NUMBER data with tokenization and No Encryption data elements.

Signature:

pty.sel_number(dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NUMBER datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_number('DE_Integer', PTY.ins_number('DE_Integer', 123455667, 0),0) "Test of SELECT NUMBER func" from dual; 

pty.sel_raw

This UDF unprotects the RAW data with a No Encryption data element.

Signature:

pty.sel_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the RAW data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_raw('DE_NoEnc', PTY.ins_raw('DE_NoEnc', 'FFDD12345', 0),0) "Test of SELECT RAW func" from dual;

3.1.8 - Update Encryption UDFs

These UDFs update the data. Protect access is required to use these functions.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.encUpdate

This procedure updates and encrypts one value of the VARCHAR2 data with one data element for encryption.

Signature:

pty.encUpdate(dataelement VARCHAR2, cdata VARCHAR2, rdata RAW, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

declare 
  raw_out raw(2000); 
begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 1
    COLUMN');
  dbms_output.put_line('------------------------------------------------
    ------');
  pty.encUpdate('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data: ' || raw_out);
end;

pty.upd_encrypt_char

This UDF re-encrypts the CHAR protected data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_char(dataelement CHAR, inval CHAR, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_char('DE_AES256', 'Original data', 0) "Test of UPDATE enc CHAR func" from dual;

pty.upd_encrypt_varchar2

This UDF re-encrypts the VARCHAR2 data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_varchar2(dataelement CHAR, inval VARCHAR2, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_varchar2('DE_AES256', 'Original data', 0) "Test of UPDATE enc VARCHAR2 func" from dual;

pty.upd_encrypt_date

This UDF re-encrypts the DATE data that has been updated, with a data element for encryption.

Note: When you use the pty.ins_encrypt_date UDF to protect date, the data is not protected. If you want to protect the Oracle input data type DATE, you must use the UDFs as described in Oracle Input Data Type to UDF Mapping to identify the appropriate UDF as per your requirement.

Signature:

pty.upd_encrypt_date(dataelement CHAR, inval DATE, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_date('DE_AES256', '23-OCT-14', 0) "Test of UPDATE enc DATE func" from dual;

pty.upd_encrypt_integer

This UDF re-encrypts the INTEGER data that has been updated, with a data element for encryption.

Signature:
pty.upd_encrypt_integer(dataelement CHAR, inval INTEGER, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_integer('DE_AES256', 12345, 0) "Test of UPDATE enc INT func" from dual;

pty.upd_encrypt_real

This UDF re-encrypts the REAL data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_real(dataelement CHAR, inval REAL, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_real('DE_AES256', 1234.1234, 0) "Test of UPDATE enc REAL func" from dual;

pty.upd_encrypt_float

This UDF re-encrypts the FLOAT data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_float(dataelement CHAR, inval FLOAT, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_float('DE_AES256', 1234.1234, 0) "Test of UPDATE enc FLOAT func" from dual;

pty.upd_encrypt_number

This UDF re-encrypts the NUMBER data that has been updated, with a data element in encryption.

Signature:

pty.upd_encrypt_number(dataelement CHAR, inval NUMBER, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_number('DE_AES256', 12345, 0) "Test of UPDATE enc NUMBER func" from dual;

pty.upd_encrypt_raw

This UDF re-encrypts the RAW data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_raw(dataelement CHAR, inval RAW, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_encrypt_raw('DE_AES256', 'FFDD12345', 0) "Test of UPDATE enc RAW func" from dual; 

3.1.9 - Update No-Encryption, Token, and FPE UDFs

These UDFs are used to update the data for tokenization and Format Preserving Encryption (FPE). The user must have Protect access to use these procedures.

Note: For reprotect operations, the Audit logs are generated as Protect Logs instead of Reprotect Logs.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.upd_char

This UDF re-protects the CHAR data with tokenization and No Encryption data elements.

Signature:

pty.upd_char(dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the output value as the CHAR datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_char('DE_DTP2_AES256_AN', 'Original data', 0) "Test of UPDATE CHAR func" from dual;

pty.upd_varchar2

This UDF reprotects the VARCHAR2 data with tokenization and No Encryption data elements.

Signature:

pty.upd_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the output value as the VARCHAR2 datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_varchar2('DE_DTP2_AES256_AN', 'Original data', 0) "Test of UPDATE VARCHAR2 func" from dual;

pty.upd_unicodenvarchar2

This UDF re-encrypts the NVARCHAR2 data that has been updated, with a data element.

Signature:

pty.upd_unicodenvarchar2(dataelement CHAR, inval NVARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as the NVARCHAR2 data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful reprotection without returning any error, but corruption of data can occur.

Example:

select PTY.upd_unicodenvarchar2('fpe_unicode', 'Original data', 0) "Test of UPDATE encrypt NVARCHAR2 func" from dual;

pty.upd_unicodevarchar2_tok

This UDF re-encrypts the VARCHAR2 data that has been updated with a Unicode Base64 and Unicode Gen2 data element.

Signature:

pty.upd_unicodevarchar2_tok (dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as VARCHAR2 data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful reprotection without returning any error, but corruption of data can occur.

Example:

select pty.upd_unicodevarchar2_tok('TE_UG2_S13_PL_N_BASCYR_AN_UTF8','‎защита данных‎',0) from dual;

pty.upd_unicodenvarchar2_tok

This UDF re-encrypts the NVARCHAR2 data that has been updated with a Unicode Base64 and Unicode Gen2 data element.

Signature:
pty.upd_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns an encrypted value as NVARCHAR2 data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.
>Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful reprotection without returning any error, but corruption of data can occur.

Example:

select pty.upd_unicodenvarchar2_tok('TE_UG2_S13_PL_N_BASCYR_AN_UTF8','‎защита данных‎',0) from dual;

pty.upd_date

This UDF reprotects the DATE data with a No Encryption data element.

Note: When you use the pty.ins_encrypt_date UDF to protect date, the data is not protected. If you want to protect the Oracle input data type DATE, you must use the UDFs as described in Oracle Input Data Type to UDF Mapping to identify the appropriate UDF as per your requirement.

Signature:

pty.upd_date (dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:
The UDF returns the original value as DATE.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_date('DE_NoEnc', '23-OCT-14', 0) "Test of UPDATE DATE func" from dual; 

pty.upd_integer

This UDF re-protects the INTEGER data with tokenization and No Encryption data elements.

Signature:

pty.upd_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the INTEGER datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY.upd_integer('DE_Integer', 12345, 0) "Test of UPDATE INT func" from dual;

pty.upd_real

This UDF reprotects the REAL data with a No Encryption data element.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the REAL datatype.

Signature:

pty.upd_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the REAL datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_real('DE_NoEnc', 1234.1234, 0) "Test of UPDATE REAL func" from dual;

pty.upd_float

This UDF reprotects the FLOAT data with a No Encryption data element.

Signature:

pty.upd_float(dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the FLOAT datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure that you use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_float('DE_NoEnc', 1234.1234, 0) "Test of UPDATE FLOAT func" from dual;

pty.upd_number

This UDF reprotects the NUMBER data with tokenization and No Encryption data elements.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the NUMBER datatype.

Signature:

pty.upd_number(dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the NUMBER datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure that you use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_number('DE_Integer', 12345, 0) "Test of UPDATE NUMBER func" from dual;

pty.upd_raw

This UDF re-protects the RAW data with a No Encryption data element.

Signature:

pty.upd_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_raw('DE_NoEnc', 'FFDD12345', 0) "Test of UPDATE RAW func" from dual;

3.1.10 - Multiple Update Encryption Procedures

These procedures encrypt one to four values of data with one procedure call. The user must be granted Protect access for the data element that will be used to execute these procedures. You can use the upd_check procedure to check whether the user has Protect access.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.encUpdate

This procedure updates and encrypts one value of the VARCHAR2 data with one data element for encryption.

Signature:

pty.encUpdate (dataelement VARCHAR2, cdata VARCHAR2, rdata RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

declare 
  raw_out raw(2000); 
begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 1
    COLUMN');
  dbms_output.put_line('------------------------------------------------
    ------');
  pty.encUpdate('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data: ' || raw_out);
end;

pty.upd_encryptx2

This procedure updates and encrypts two values of VARCHAR2 data with two data elements for encryption.

Signature:

pty.upd_encryptx2(dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 2 
    COLUMNS');
  dbms_output.put_line('------------------------------------------------
    -------');
  pty.upd_encryptx2('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
end;

pty.upd_encryptx3

This procedure updates and encrypts three values of VARCHAR2 data with three data elements for encryption.

Signature:

pty.upd_encryptx3 (dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 3 
    COLUMNS');
  dbms_output.put_line('-----------------------------------------------
    --------');
  pty.upd_encryptx3('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0,
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199',
    raw_out3, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
end;

pty.upd_encryptx4

This procedure updates and encrypts four values of VARCHAR2 data with four data elements for encryption.

Signature:

pty.upd_encryptx4 (dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER, dataelement4 VARCHAR2, cdata4 VARCHAR2, rdata4 RAW, scid4 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

Exception:
If you configure an exception in the policy and the user does not have Protect access rights in the policy, then the procedure terminates with an error message explaining what went wrong.

Example:

begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 4 
    COLUMNS');
  dbms_output.put_line('------------------------------------------------
    -------');
  pty.upd_encryptx4('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199',
    raw_out3, 0 , 'DE_AES256', ' ASFGFGghg5577fFFyu; AblnQEWsw0129NGku; 
    BINKUcrc8749lLLnx; CAESYwiw0098mMMns; FEORLkjk2323kKKmn; 
    LAENILmcm6677kBBop; MOIRNAzlz9876lMMyu;  MUBMIARAR6087kUUmn; 
   NIASAlziz2398hTTuv; PATRHXuru9898hFFns; ROYNESgog7802gMMus;
   SIRSHAuna9049kKKjn; TOTALSlol7843mWWqa; TUSFAVopo8080tTTnx; 
   TUHSRAknk8108mKKdw; VAENSAJJBJ6712fFFGH; VEPSIMdsd9898kSDnm; 
   URDPLAghg7676LLyu; UNBAKERkik2233lLLmu; YANMRAlsl9090fFFyu; 
   YASTURhom0123hHHmn; XAOILDghg0987fFFmn; ZABCDEmom5577bHHyy; 
  ZOHRASghg5297nNNcd ', raw_out4, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
  DBMS_OUTPUT.PUT_LINE('Encrypted data4: ' || raw_out4);
end;

3.1.11 - Hash UDFs

These UDFs protect the data as a hash value.

pty.ins_hash_varchar2

This UDF uses the hash function to protect the VARCHAR data with a data element for hashing to return a protected value.

Signature:

pty.ins_hash_varchar2(dataelement CHAR, cdata VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the Hash value as the RAW data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT PTY.ins_hash_varchar2('DE_Hash', ' ASertcv2013; CUxdcs3675; ccNNddfF9084; hjMjCS0123',0) "Test of INSERT HASH function" from dual; 

pty.upd_hash_varchar2

This UDF uses the hash function to protect the VARCHAR data with a data element for hashing to return a protected value.

Signature:

pty.upd_hash_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the Hash value as the RAW data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT PTY.upd_hash_varchar2('DE_Hash', 'ASertcv2013; CUxdcs3675; ccNNddfF9084; hjMjCS0123;',0) "Test of UPDATE HASH function" from dual; 

3.1.12 - Blob UDFs

These UDFs can be used to encrypt and decrypt the data stored in the BLOB data type.

pty.ins_encrypt_blob

This function is used to encrypt the data stored in a BLOB with an encryption data element.

Signature:

pty.ins_encrypt_blob(dataelement CHAR, input_data BLOB , scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as the BLOB data. > Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

Exception:
If the user does not have protect privileges in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_blob('AES256',TO_BLOB('691F89CD2BCBF055EFD4F3B51470AEF6'),0) from dual;

Caution: A maximum of 1.5 GB of input data can be protected using the pty.ins_encrypt_blob UDF. The pty.ins_encrypt_blob UDF will return an unexpected behaviour if you exceed the maximum input data limit of 1.5 GB. For example: ORA-28579: network error during callback from external procedure agent.

pty.sel_decrypt_blob

This function is used to decrypt the encrypted data stored in a BLOB with an encryption data element.

Signature:

pty.sel_decrypt_blob (dataelement CHAR, input_data BLOB, scid INTEGER)

Parameters:

Returns:

• This UDF returns the decrypted value as the BLOB data.
• This UDF returns the decrypted value as an EMPTY_BLOB, when the user has no access to the database.

Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

Exception:
If the user does not have unprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.sel_decrypt_blob('AES256',pty.ins_encrypt_blob('AES256',TO_BLOB('691F89CD2BCBF055EFD4F3B51470AEF6'),0),0) from dual;

3.1.13 - Clob UDFs

These UDFs can be used to encrypt and decrypt the data stored in the CLOB data type.

pty.ins_encrypt_clob

This function is used to encrypt the data stored in a CLOB with an encryption data element.

Signature:

pty.ins_encrypt_clob(dataelement CHAR, input_data CLOB, scid INTEGER)

CAUTION: Ensure that the input data stored in the CLOB data type does not contain multibyte characters. If you pass data containing multibyte characters to the CLOB UDF, then an unexpected behaviour is observed.
For example: An error 'ORA-28579: network error during callback from external procedure agent' is returned or the input data is corrupted. For more information about CLOB data type, refer to the Oracle Help Center.

Parameters:

Returns:
This UDF returns the encrypted value as the CLOB data. >Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_clob('AES256','John',0) from dual;

Note: A maximum of 500 MB of input data can be protected using the pty.ins_encrypt_clob UDF.

pty.sel_decrypt_clob

This function is used to decrypt the encrypted data stored in a BLOB with an encryption data element.

Signature:

pty.sel_decrypt_clob(dataelement CHAR, input_data BLOB, scid INTEGER)

Parameters:

Returns:

• This UDF returns the decrypted value as the CLOB data.
• This UDF returns the decrypted value as an EMPTY_CLOB, when the user has no access to the database.

  Note: If you perform a unprotect operation with the input data as null or empty, then the output will be an EMPTY_CLOB.

Exception:
If the user does not have unprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.sel_decrypt_clob('AES256',pty.ins_encrypt_clob('AES256','John',0),0) from dual;

3.1.14 - Bulk UDFs