Install and Configure

The Sparx Systems Pro Cloud Server product is installed using a standard Microsoft Windows Installer package that includes a number of optional components. One of these components is Visibility Levels, which is installed by default into the C:\Program Files (x86)\Sparx Systems\Pro Cloud Server\VisibilityLevels\ folder (assuming a 64bit machine is being used). If necessary, you can change the install location of the Visibility Levels files.

Requirements

In order to use Visibility Levels you must have:

A licensed Pro Cloud Server
An Oracle 8+ or SQL Server 2016+ based Enterprise Architect model/repository that uses the EA1220 schema definition
An SQL based management console tool that supports the execution of SQL scripts
Enterprise Architect build 14.0 or later
Enterprise Architect Ultimate Edition for assignment of Visibility Level on a Package

What is in the Package?

The Visibility Levels folder consists of a number of SQL script files that will either apply or remove the required database changes to an Enterprise Architect model so that it can support the DBMS's native Row Level Security feature.

The implementation of Row Level Security in Oracle is completely different to the implementation in SQL Server. While the end result behaves the same way, the physical objects that are needed by each DBMS are sufficiently different for each to need its own creation script.

Task	Script	See also
Apply Visibility Level changes to Oracle model	EASchema_VL_Updates_Oracle_1220.sql - Oracle SQL script that will update an existing Enterprise Architect Oracle based repository so that it supports Visibility Levels, by creating a set of functions, procedures, policies, triggers and columns.
Apply Visibility Level changes to SQL Server model	EASchema_VL_Updates_SQLServer_1220.sql - SQLServer specific SQL script that will update an existing Enterprise Architect MS SQL Server based repository so that it supports Visibility Levels, by creating a set of functions, procedures, policies, triggers, roles and columns.
Remove Visibility Level changes from Oracle model	EASchema_VL_Removal_Oracle_1220.sql - Oracle SQL script that will remove the functions, procedures, policies, triggers, roles and additional columns that were added to support Enterprise Architect's Visibility Levels. Note: All basic Enterprise Architect modeling data will remain unchanged by the removal script.
Remove Visibility Level changes from SQL Server model	EASchema_VL_Removal_SQLServer_1220.sql - SQLServer SQL script that will remove the functions, policies, triggers, roles and additional columns that were added to support Enterprise Architect's Visibility Levels. Note: All basic Enterprise Architect modeling data will remain unchanged by the removal script.

How to execute

In order for an Enterprise Architect model to support Visibility Levels, the appropriate update script must be executed on that model's database. The script is standard SQL, so any tool can be used; however, you must connect using an ID with owner privileges because updates will be made to the physical schema.

Script Execution for Oracle

Identify the Enterprise Architect model database to which to apply Visibility Levels.
Ask all users to log out of the model.
(Optional step) Physically stop any further user access to the database; for example, disconnect the server from the network.
Perform a complete backup of the database.
Open your SQL console of choice and connect to the database as the owner.
Load the Oracle script ('EASchema_RLS_Updates_Oracle_1220.sql') into the console.
Review the comments at the top of the script, in particular the section that outlines the tasks that need to occur before running the script.
Make the needed adjustments to the script for your environment/preferences.
Execute the script.
Follow the set of instructions documented at the top of the script in the 'Do the following AFTER running this script' section
Perform a second complete backup of the database, as a restore point.
Define the Database Managers in the Pro Cloud Server (refer to the Defining Visibility Level Aware Database Managers table in the Visibility Levels User Guide).
(Optional step) If Step 3 was performed, re-enable regular user access to the database.
Open Enterprise Architect Ultimate edition, connect to the Pro Cloud model with the highest Visibility Level (as defined in step 12) and set the Visibility Level on all Packages to their required values.

Script Execution for MS SQL Server

Identify the Enterprise Architect model database to which to apply Visibility Levels.
Ask all users to log out of the model.
(Optional step) Physically stop any further user access to the database; for example, disconnect the server from the network.
Perform a complete backup of the database.
Open your SQL console of choice and connect to the database as the owner.
Load the SQL script ('EASchema_RLS_Updates_SQLServer_1220.sql').
Review the comments at the top of the script, in particular the section that outlines the tasks that need to occur before running the script.
Make the needed adjustments to the script for your environment/preferences.
Execute the script.
Follow the set of instructions documented at the top of the script in the 'Do the following AFTER running this script' section
Perform a second complete backup of the database, as a restore point.
Define the Database Managers in the Pro Cloud Server (refer to the Defining Visibility aware Database Managers table in the Visibility Levels User Guide).
(Optional step) If Step 3 was performed, re-enable regular user access to the database.
Open Enterprise Architect Ultimate edition, connect to the Pro Cloud model with highest Visibility Level (as defined in step 12) and set the Visibility Level on all Packages to their required values.