This continues our video blog series about new features, that are implemented in the new version of SQL Server Migration Assistant 7.0. And now we will talk about some important security issues. Row-Level Security (RLS) enables customers to control access to rows in a database table based on the characteristics of the user executing a query. The feature was introduced in SQL Server 2016. Starting from the 7.0 version the SSMA tool can handle Oracle VPD Policy and DB2 access controls, correctly converting it into Row-Level Security in SQL Server 2016 and Azure SQL Database.
Background on SQL Server Row-Level Security and Oracle Virtual Private Database
Row-Level Security simplifies the design and coding of security in your application. For example, it ensures that employees can access only those data rows, that refer to their department, or restricts customer’s data access to only the data, which is relevant to their company.
The key moment about RLS is that the access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier. This makes your security system more reliable and robust by reducing the surface area of your security system.
For example, in Oracle (since 11g version) the Virtual Private Database (VPD) technology is used to implement this feature. It is one of the most popular security features in the database.
So, during database migration process, we often face the VPD policies, which are used for security features in Oracle source database. SSMA 7.0 brings the support of Oracle Virtual Private Database Policy conversion into SQL Server Row-Level Security. Please note that the previous versions of SSMA didn’t support Row-Level security, so, users had to manually edit the security issues while migrating Oracle to SQL Server.
In this video we’ll show you how convert Oracle database with VPD policies into SQL Server database with RLS functions support.
Need help getting started? Check out our SSMA Jumpstart Offer!
Thanks for watching our video.
Now you can download the SSMA 7.0 for Oracle.
Known issues migrating Oracle Virtual Private Database
Please note that by default Row-level Security Policies in the target database are turned off after the conversion from Oracle VPD Policies source, so you still have to do a bit of work and turn them on manually.
Also remember that you have to convert all objects, related to the source VPD Policy function.
Currently Oracle provides a richer set of options with VPD compared to SQL Server RLS, so that we don’t have direct analogs to convert to and thus don’t support the following features:
- Grouped policies (they do not exist in SQL Server RLS);
- Policy context (different policies can be activated depending on application context, it should be implemented manually in SQL Server);
- Column-level VPD (RLS-enforcement for queries referencing security-relevant columns, there’s no analogue in SQL Server);
- VPD for synonyms (only tables and views are supported by SQL Server RLS as of now);
- Performance-optimization parameters of VPD such as Static, Context-Sensitive are ignored.
Oracle uses dynamic WHERE clause in VPD filtering function but SQL Server’s RLS implementation requires inline TVF filtering function. In order to emulate Oracle’s approach in SQL Server we have to use dynamic SQL executed with extended stored procedure inside scalar UDF, which in its turn is called from inline TVF. This leads to the following restrictions:
- This approach is not supported in migrations to Azure SQL, so in these cases we create empty VPD function without dynamic SQL converted from Oracle;
- Dynamic SQL may fail when executed on SQL Server (if it uses some non-ANSI or noncompatible features, specific to Oracle). This is because SSMA is not able to analyze and convert dynamic SQL statements – they are taken as-is, so it is up to user to review or possibly make modifications to the dynamic SQL code;
- The performance can be unacceptable on large sets of rows because essentially we make SQL Server execute scalar UDF + extended stored procedure per row.
Useful references for using SSMA for Oracle to SQL Server Migration, Oracle VPD Technology and SQL Server 2016 Row-Level Security
- Oracle Virtual Private Database Policy: http://docs.oracle.com/database/121/DBSEG/vpd.htm.
- Row-Level Security support in SQL Server 2016: https://msdn.microsoft.com/en-us/library/dn765131.aspx.
Stay tuned to our blog for more information about the new features in the SQL Server Migration Assistant (SSMA) 7.0.
