Introduction to Row Level Security

First available in TMWSuite: 2011.10_08.0208

Row Level Security (RLS) lets you secure records. For example, security can be applied to records belonging to specific companies, divisions, or terminals. It limits access so that only users or groups assigned to them retrieve their records.

The Row Level Security feature is an add-on that is sold separately and requires a special license. Contact Trimble Sales for more information.

Impact on the base system

Row Level Security applies to the TMWSuite and TMW.Suite applications. This document gives a high-level overview of how it affects key areas of the system.

User profiles

You can link a user with specific RevType1 values in their profile. The user can access all records having RevType1 values that match the ones to which they are linked. You must specify a default RevType1 value. It will be used to auto-populate the RevType1 field on any company profile or billing rate the user creates.

However, to link a user with a specific value for one of the other security basis fields, you must use the Row Level Security Assignment window.

  1. For a list of fields on which you can base security, see Understanding the security basis.

  2. For more information on linking users with specific field values, see Assigning security to a user’s profile.

Profiles

You can set up security for:

  • Companies

  • Drivers

  • Tractors

  • Trailers

  • Carriers

  • Pay Tos

  • Third Parties

  • Average Fuel Price tables

A user may open these records only when their value for fields on which security is based match the values associated with the user.

  1. For a complete list of the fields available for each of these records, see Understanding the security basis.

  2. If you set security for company or asset records only by the RevType1 or Terminal fields, any records having a value of UNKNOWN in these fields will be accessible by all users. However, the profiles of such records may be edited only by users who have a RevType1 or Terminal value of UNKNOWN in their user profiles.

  3. The Booking Terminal field is available only when your system is set up for the branch tracking feature. The feature is activated when the TrackBranch General Info Table setting has a String1 value of Y. For more information, see Branch File Maintenance in the File Maintenance online help.

Order Entry

You can set up security for orders based on RevType1 - RevType4 field values. If you are using the branch tracking feature, you can also set security on the order’s Booking Terminal and Executing Terminal values.

Users may open an order only when the order’s value for these fields match those specified for them. The user’s values are set up in the Row Level Security Assignment window.

You can have the system set up to default the RevType1 value on an order based on the user’s login. This requires that you:

If you are set up this way, be sure to specify the same RevType1 value relative to the orders table for the user in the Row Level Security Assignment window. Otherwise, the user cannot open orders they create.

Dispatch

In the Trip Folder, a user may open an order only when the order’s RevType1 - RevType4 field values match those set for the user in the Row Level Security Assignment window.

You can have the system set up to default the RevType1 value on an order based on the user’s login. This requires that you:

  • Have [Order]SetRevType1DefaultFrom=L in your TTS50.

  • Have identified a default RevType1 value for the user in their profile.

If you are set up this way, you should be sure to specify the same RevType1 value relative to the orders table for the user in the Row Level Security Assignment window. Otherwise, the user will not be able to open orders they create.

In the Planning Worksheet, Available Trips and Available Power views can be restricted by many of the fields on which you can base security. Users can be assigned to views having the security field values associated with them in the Row Level Security Assignment window to limit the trips and resources they can retrieve.

In the Asset Assignment window and the Trip Folder Stop Detail grid, these fields will show only assets that have profile values that match those assigned to the user:

  • Driver1

  • Driver2

  • Tractor

  • Trailer

  • Carrier

You make the user’s assignments in the Row Level Security Assignment window.

In the Dispatch Worksheet, views can be restricted by Terminal values. Users see only trips and assets assigned to terminals matching their RevType1 value(s).

The Dispatch Worksheet is available when you have the system set up to use the Intermodal feature. For more information, see the Intermodal guide.

Scroll windows

RLS affects records retrieved in scroll windows. They will be restricted based on values set in the Row Level Security Setup window. The restrictions apply to these scroll windows:

  • Scroll Company

  • Orders Scroll

  • Invoice Scroll

  • Driver Scroll

  • Tractor Scroll

  • Trailer Scroll

Rate Schedules

In the Edit Billing Rate Schedules window, users may open only rates having RevType1 values matching theirs. Rates cannot be saved if the RevType1 index restriction field is left as UNKNOWN. When rates are retrieved in the Billing Rate Schedules scroll window, only those having RevType1 values matching the user’s will be displayed.

In the Edit Settlement Rate Schedules window, users may open only rates having at least one Terminal field value matching their value. Rates cannot be saved if the Terminal index restriction field is left as UNKNOWN. When rates are retrieved in the Settlement Rate Schedules scroll window, only those having Terminal values matching the user’s RevType1 value(s) will be displayed. The system performs a hierarchal search for a matching Terminal value specified for asset types. It looks at:

  1. Driver

  2. Tractor

  3. Trailer

Invoicing

When security is set up for the invoice table, users may open records only when the order’s RevType1 value matches any of the user’s. This is true whether the order or invoice number is:

  • Entered manually

  • Retrieved using the Invoicing queues

    • Orders Ready to Prepare

    • Print Invoices

    • Invoices On Hold

    • Invoices On Hold for Audit

    • Batch Rating and Rebilling

Settlements

In the Trip Settlements Folder, users may open records only when the order’s RevType1 value matches any of the user’s. This is true whether the order number is:

  • Entered manually

  • Retrieved using the Settlement queues (Trips Ready to Settle, Trips On Hold).

  1. If there is more than one order on a trip, as long as one of them has a RevType1 value matching the user’s, the trip can be retrieved.

  2. In the case of a trip that is an empty move, the tractor on the trip must have a Terminal value matching the user’s for the trip to be retrieved.

In the Final Settlements Folder, users may open settlement records only when the resource’s Terminal value matches any of the user’s RevType1 values. This is true whether the asset ID is:

  • Entered manually

  • Retrieved using the Settlement queues

    • Trips Ready to Settle

    • Assets Collect

    • Settlements Close

    • Scroll Settlement Items

Understanding the security basis

The basis of Row Level Security is classifications applied to certain fields in a selected group of tables in the TMWSuite database. You can assign users and groups to the user-defined values that the fields may contain.

When assigning security, remember that:

  • Many fields appear in multiple tables. A user may be associated with one value for a field in one table and a different value for the same field in a different table.

  • This table gives the default field names used throughout the system. Your company may have modified the displayed names for these fields in the labels used to define their options. The modified names will display in the Row Level Security Assignment and Row Level Security Setup windows.

  • If you are using the track branch feature, the TrackBranch setting’s String2 and String3 fields to customize the displayed names for the Booking Terminal and Executing Terminal fields. The Row Level Security Assignment and Row Level Security Setup windows show the default names. They do not show the modified names.

This table lists the tables, and their fields, you can use to set up security.

* Requires that you are using the track branch feature. The feature is in effect when the TrackBranch General Info Table setting’s String1 field has a value of Y.

Table name Field name Table column name

Average Fuel Price

RevType1

afp_revtype1

Carriers

Branch *

CarType1

CarType2

CarType3

CarType4

car_branch *

car_type1

car_type2

car_type3

car_type4

Cash Card Codes

RevType1

ccc_revtype1

Companies

Branch *

Other Type 1

Other Type 2

RevType1

RevType2

RevType3

RevType4

cmp_bookingterminal *

cmp_othertype1

cmp_othertype2

cmp_revtype1

cmp_revtype2

cmp_revtype3

cmp_revtype4

CRM Work Companies

Branch *

Other Type 1

Other Type 2

RevType1

RevType2

RevType3

RevType4

mp_bookingterminal *

cmp_othertype1

cmp_othertype2

cmp_revtype1

cmp_revtype2

cmp_revtype3

cmp_revtype4

Drivers

Branch *

Company

Division

Domicile

DrvType1

DrvType2

DrvType3

DrvType4

FLEET

Team Leader

Terminal

mpp_branch *

mpp_company

mpp_division

mpp_domicile

mpp_Type1

mpp_Type2

mpp_Type3

mpp_Type4

mpp_fleet

mpp_teamlead

mpp_terminal

Invoices

RevType1

RevType2

RevType3

RevType4

ivh_revtype1

ivh_revtype2

ivh_revtype3

ivh_revtype4

Orders

Booked By

Booking Terminal

Executing Terminal

RevType1

RevType2

RevType3

RevType4

ord_bookedby

ord_booked_revtype1 *

ord_broker

ord_revtype1

ord_revtype2

ord_revtype3

ord_revtype4

Payto

RevType1

RevType2

RevType3

RevType4

pto_type1

pto_type2

pto_type3

pto_ype4

Tariffs

RevType1

trk_rowsec_revtype1

Third Parties

Branch *

RevType1

RevType2

RevType3

RevType4

tpr_branch *

tpr_revtype1

tpr_revtype2

tpr_revtype3

tpr_revtype4

Tractors

Branch *

Company

Division

FLEET

Terminal

TrcType1

TrcType2

TrcType3

TrcType4

trc_branch *

trc_company

trc_division

trc_fleet

trc_terminal

trc_Type1

trc_Type2

trc_Type3

trc_Type4

Trailers

Branch *

Company

Division

FLEET

Terminal

TrlType1

TrlType2

TrlType3

TrlType4

trl_branch *

trl_company

trc_division

trl_fleet

trl_terminal

trl_Type1

trl_Type2

trl_Type3

trl_Type4

Trip/Regions

RevType1

cmp_revtype1