Topics
Prev Next

Install and Config

The Sparx Systems Pro Cloud Server product is installed using a standard Windows Installer package that includes a number of optional components. One of these components is Visibility Levels, which is installed by default. It is possible to change the install location of Visibility Levels files; however, if the defaults are used the Visibility Levels files will be installed into the C:\Program Files (x86)\Sparx Systems\Cloud Services\RLS\ folder (assuming a 64bit machine is being used).

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 1351 or later

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 Visibility Levels.

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 so different that each needs its own creation script.

Task

Script

See also

Apply Visibility Level changes to Oracle model

EASchema_RLS_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_RLS_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_RLS_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_RLS_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

  1. Identify the Enterprise Architect model database to which to apply Visibility Levels.
  2. Ask all users to log out of the model.
  3. (Optional step) Physically stop any further user access to the database; for example, disconnect the server from the network.
  4. Perform a complete backup of the database.
  5. Open your SQL console of choice and connect to the database as the owner.
  6. Load the Oracle script ('EASchema_RLS_Updates_Oracle_1220.sql') into the console.
  7. 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.
  8. Make the needed adjustments to the script for your environment/preferences.
  9. Execute the script.
  10. Follow the set of instructions documented at the top of the script in the 'Do the following AFTER running this script' section
  11. Perform a second complete backup of the database, as a restore point.
  12. 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).
  13. (Optional step) If Step 3 was performed, re-enable regular user access to the database.
  14. Open Enterprise Architect, connect to the Pro Cloud model with the highest Visibility Level (as defined in step 11) and set the Visibility Level on all Packages to their required values.

Script Execution for MS SQL Server

  1. Identify the Enterprise Architect model database to which to apply Visibility Levels.
  2. Ask all users to log out of the model.
  3. (Optional step) Physically stop any further user access to the database; for example, disconnect the server from the network.
  4. Perform a complete backup of the database.
  5. Open your SQL console of choice and connect to the database as the owner.
  6. Load the SQL script ('EASchema_RLS_Updates_SQLServer_1220.sql').
  7. 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.
  8. Make the needed adjustments to the script for your environment/preferences.
  9. Execute the script.
  10. Follow the set of instructions documented at the top of the script in the 'Do the following AFTER running this script' section
  11. Perform a second complete backup of the database, as a restore point.
  12. Define the Database Managers in the Pro Cloud Server (refer to the Defining Visibility aware Database Managers table in the Visibility Levels User Guide).
  13. (Optional step) If Step 3 was performed, re-enable regular user access to the database.
  14. Open Enterprise Architect, connect to the Pro Cloud model with highest Visibility Level (as defined in step 11) and set the Visibility Level on all Packages to their required values.