Topic
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 SQLServer2016+  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 1330 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 a Entereprise Architect model so that it can support Visibility Levels.

The implementation of Row level security in the two different DBMSs (Oracle and SQL Server) is completely different to each other.  While the end results behaves the same way the physical objects that are needed by each DBMS are so different that each need their own creation script.

Task

Script

See also

Apply RLS changes to Oracle model

EASchema_RLS_Updates_Oracle_1220.sql - Oracle SQL script that will create the required functions, procedures, policies, triggers, roles and additional columns to implement Enterprise Architect's Visibility Levels.

Apply RLS changes to SQL Server model

EASchema_RLS_Updates_SQLServer_1220.sql - SQLServer specific SQL script that will create the required functions, triggers, roles and additional columns to implement Enterprise Architect's Visibility Levels.

Remove RLS 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.

Remove RLS 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.

How to execute

In order for a 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_SQLServer_1220.sql') into the console.
  7. Review the comments at the top of the script, particular in regard to the10 replacements that are needed - 5 user account names and a password for each account. 
  8. Make the needed replacements for the accounts to suit your environment/preferences.
  9. Execute the script.
  10. Perform a second complete backup of the database, as a restore point.
  11. Define the 5 different Database Managers in the Pro Cloud Server (refer to the Defining Visibility aware Database Managers topic).
  12. (Optional step) If Step 3 was performed, re-enable regular user access to the database.
  13. 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 appropriate SQL script in the SQL console; for example, for MS SQL Server open 'EASchema_RLS_Updates_SQLServer_1220.sql'.
  7. Review the comments at the top of the script to understand how the security is assigned.
  8. Execute the script.
  9. Perform a second complete backup of the database, as a restore point.
  10. Depending on the method your DBMS uses to access the database, and the method the database users use, you might need to grant all database users the appropriate Visibility Level.
  11. Define the 5 different Database Managers in the Pro Cloud Server (refer to the Defining Visibility aware Database Managers topic).
  12. (Optional step) If Step 3 was performed, re-enable regular user access to the database.
  13. 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.