Row and column access control with a system-period temporal table

Row and column access control can be defined on both a system-period temporal table and its associated history table.

Row and column access control (RCAC) is a layer of data security that controls access to a table at the row level, column level, or both. RCAC can be applied to system-period temporal tables and history tables. When RCAC is activated for a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table. This default row permission prevents any direct access to the history table. When the history table is protected by the default row permission, updates and deletes still generate history rows in the history table.

When a temporal query is run against a system-period temporal table, the row permissions and column masks from the system-period temporal table are also applied to the rows returned from the history table. For example, if a row permission is defined for a system-period temporal table and a query with an FOR SYSTEM_TIME AS OF clause is run, both current and historical rows are returned when the current or historical row satisfies both the RCAC rule from the temporal table and was current as of the time specified.

If the history table has only a default permission, you cannot query it directly. However, if a row or column rule other than the default permission is also defined on the history table, that rule is applied when the history table is accessed directly. Therefore, if you need to query the history table directly, you can create a row permission or column mask on the history table that matches the row permission or mask that was created on the system-period temporal table. When the row permission or column mask is created, you are able to query the history table directly while also controlling access to the data.

For more information about RCAC, see Row and column access control (RCAC).