{"id":6633,"date":"2013-10-01T15:19:12","date_gmt":"2013-10-01T20:19:12","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/docs\/?page_id=6633"},"modified":"2021-12-03T08:39:28","modified_gmt":"2021-12-03T14:39:28","slug":"row-level-security-template","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/docs\/knowledge-base\/row-level-security-template","title":{"rendered":"Row Level Security Template"},"content":{"rendered":"\n<p>Row Level Security is a highly flexible technique for controlling which records are returned based on the current user. Implementing row level security is a three step process. First, you must create a table that contains security credentials. Second, you need to create a retrieval over this table, using the Row Level Security template. Lastly, you must integrate the security retrieval with the application you wish to secure.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h1 class=\"wp-block-heading\"><strong><a id=\"video\" class=\"anchor\"><\/a><\/strong>Video Walkthrough<\/h1>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"Implement Security in your Web Application\" width=\"643\" height=\"362\" src=\"https:\/\/www.youtube.com\/embed\/Ad-cD6E2_Ao?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n<\/div><\/div>\n\n\n\n<h1 class=\"wp-block-heading\"><strong><a id=\"table\" class=\"anchor\"><\/a><\/strong>Creating the Security Table<\/h1>\n\n\n\n<p>The table must contain three fields. One field will hold user profile information, another will contain values, and a third will contain data regarding the relationship between the user and the values. When naming your fields, do not name the user profile field USER. This word is reserved within Java. Valid relationship values are:<\/p>\n\n\n\n<div class=\"leftMargin\">\n<ul>EQ Equal to<\/ul>\n<br>\n<ul>GE Greater than or equal to<\/ul>\n<br>\n<ul>GT Greater than<\/ul>\n<br>\n<ul>LE Less than or equal to<\/ul>\n<br>\n<ul>LS Is in the list<\/ul>\n<br>\n<ul>NS Not in the List<\/ul>\n<br>\n<ul>LT Less than<\/ul>\n<br>\n<ul>NE Not equal to<\/ul>\n<br>\n<ul>NG Not greater than<\/ul>\n<br>\n<ul>NL Not less than<\/ul>\n<br>\n<ul>RG Is in the range<\/ul>\n<br>\n<ul>SW Starts with<\/ul>\n<\/div>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-info\">You most likely will want to build a maintainer over this table.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"\/docs\/vue-images\/row_level_1.png\"><img decoding=\"async\" src=\"\/docs\/vue-images\/row_level_1.png\" alt=\"\"\/><\/a><\/figure>\n\n\n\n<p>This table has been set up so that user Kong has the credentials to view all values in the list of IL, NY, and FL. Conversely, user Hurckes can only view values related to IL and CO. Once your security table has been created, you are ready to create the Row Level Security application.<\/p>\n\n\n\n<p><a href=\"#index\">Back to the Top<\/a><\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><strong><a id=\"app\" class=\"anchor\"><\/a><\/strong>Creating the Security Application<\/h1>\n\n\n\n<p>In order to implement Row Level Security, you must create a retrieval over the table you just created. This retrieval must be created using the Row Level Security template. Applications created with this template are expected to run behind the scenes, and not in the browser.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Application Specifications<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"applicationSettings\" class=\"anchorx\"><\/a>Application Settings<\/h3>\n\n\n\n<div class=\"leftMargin\"><strong>Description<\/strong>: Allow developers to specify their application&#8217;s description\/name.\n<p>&nbsp;<\/p>\n<p><strong>Program Name<\/strong>: This will be the application&#8217;s number. By default, m-Power uses the letter I (Inquiry) followed by a five digit number.<\/p>\n<p><strong>Select Only Matching Records<\/strong>: Select either &#8216;No&#8217;, for a left outer join, or &#8216;Yes&#8217; for an inner join. A left outer join will return all records from the primary table along with matching records from the secondary tables. An inner join will return records from the primary and secondary tables only where matching records are found.<\/p>\n<p><strong>Template<\/strong>: You can select a template by scrolling through the available templates. The templates define the general layout and functionality of the resulting application. This section will list all the available maintenance templates; you will see a small screenshot of what that template looks like at runtime. Learn <a href=\"\/docs\/m-power-build-process\/select-a-template\" target=\"_blank\" rel=\"noopener noreferrer\">more<\/a> about templates.<\/p>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"dataSelection\" class=\"anchorx\"><\/a>Data Selection<\/h3>\n\n\n\n<p>Here you will see\/modify the table you have selected for your application. Here you can create or modify joins to other tables as well.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"sequencing\" class=\"anchorx\"><\/a>Sequencing<\/h3>\n\n\n\n<p>Sequence your application by the field containing the user values.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"fieldSettings\" class=\"anchorx\"><\/a>Field Settings<\/h3>\n\n\n\n<p>Here you will find the settings for all the fields in the application. The options are:<\/p>\n\n\n\n<div class=\"leftMargin\"><strong>Note<\/strong>: When creating the security application, please ensure that the third field in your application is the field you wish to secure against.\n<p>&nbsp;<\/p>\n<p><strong>Delete<\/strong>: You can delete fields by clicking the checkbox and clicking Accept. If a field has a red circle with a cross line, that means that the field is being used somewhere else in the application (sequence, calculation, etc.), and, due to this dependency, is not available for deletion.<br>NOTE: deleting a field will remove the field from the application only, the table will be unchanged.<\/p>\n<p><strong>Field<\/strong>: This is the same name the field has in the table.<\/p>\n<p><strong>Table<\/strong>: The name of the table where that field exists.<\/p>\n<p><strong>Field Description<\/strong>: This is the text that will appear in the column header for that field, you can modify this field to suit your needs. This field will populate with the field description from the table by default, but it can be customized at the application level.<\/p>\n<p><strong>Display<\/strong>: A radio button allows developers to display or hide the field from the output page. There may be situations where a field is needed for calculation purposes, but the field does not need to be displayed at runtime.<\/p>\n<p><strong>Length<\/strong>: Here you can modify the length of the field. m-Power will only allow developers to shorten the length of the field, shortening a fields length will truncate its data. For example, if a field is 10 alpha and it is changed to 5 alpha, now only the first 5 characters will be displayed. The same principle applies to a numeric field, if a numeric field is 8 digits long; changing it to 4 digits long will only display the first 4 digits, and leave out the rest of digits.<\/p>\n<p><strong>Decimals<\/strong>: For numeric fields, you can modify the amount of decimal digits. A numeric field will have the option of changing its decimal length, alpha fields does not have a decimal option, developers can use this as an indicator to check if a field is a numeric or character type.<\/p>\n<p><strong>Numeric Format Code<\/strong>: For numeric fields, you can modify the way the numbers will display, this includes displaying decimals or not, how to display negative numbers, etc. Multiple formats are built in for developers to use, shall you need a different format code, m-Power allows developers to create their own User Defined Format codes; these codes will add logic for common types of fields such as: Currency, Time, or Dates. Accessing the User Defined Format Codes from the Admin section will also allow developers to modify current codes. Learn <a href=\"\/docs\/general\/edit-codes-in-servlets\" target=\"_blank\" rel=\"noopener noreferrer\">more<\/a>.<\/p>\n<p><strong>Note:<\/strong> Do not confuse the User Defined Format Code with the <em>User Defined Functions<\/em> (UDF). The latter is a feature that allows developers to create or incorporate programing functions into m-Power. Learn <a href=\"\/docs\/external-objects-udf\/introduction-to-user-defined-functions\" target=\"_blank\" rel=\"noopener noreferrer\">more<\/a> about UDFs.<\/p>\n<p><strong>User def<\/strong>: This feature has been deprecated.<\/p>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"recordSelections\" class=\"anchorx\"><\/a>Record Selections<\/h3>\n\n\n\n<p>Record Selections can be created over any database fields. These are the options:<\/p>\n\n\n\n<div class=\"leftMargin\"><strong>Field<\/strong>: A drop down allows you to select the field you want to filter on.\n<p>&nbsp;<\/p>\n<p><strong>Relation<\/strong>: A drop down allows you to select a relationship for the filter.<\/p>\n<p><strong>Value<\/strong>: This is the value to compare against. The options are:<\/p>\n<p><strong>Constant Value<\/strong>: A constant value allows you to hard-code any given value into a selection. This value cannot be modified by the end-user at run\u00e2??time.<\/p>\n<p><strong>Application Field Value<\/strong>: Developers have the option of comparing a value from one field to a value from another field within the same record.<\/p>\n<p><strong>And\/Or<\/strong>: When creating multiple record selections, you have the option to set them as and or or Example: selection A and selection B will display only records that match both selections. Selection A or selection B will display records that match one or both selections. Learn <a href=\"\/docs\/general\/working-with-record-selections\" target=\"_blank\" rel=\"noopener noreferrer\">more<\/a>.<\/p>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><strong><a id=\"calculations\" class=\"anchorx\"><\/a>Calculations<\/strong><\/h3>\n\n\n\n<p>Calculations are a very powerful feature of m-Power; with calculations, developers can create logical fields that will apply to the current application only. This can be used to include SQL code in a field, such as cast a numeric field as character, create date conversions, inserting the current date and time, and much more. Learn <a href=\"\/docs\/calcs\/m-power-calculation-screen\" target=\"_blank\" rel=\"noopener noreferrer\">more<\/a>. In Row Level Security applications, calculations are often used to create the relationship field, without needing to alter the table.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong><a id=\"externalObjects\" class=\"anchorx\"><\/a>External Objects<\/strong><\/h3>\n\n\n\n<p>External Objects are not supported in the Row Level Security Template.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong><a id=\"smartLinks\" class=\"anchorx\"><\/a>SmartLinks<\/strong><\/h3>\n\n\n\n<p>SmartLinks are not supported in the Row Level Security Template.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong><a id=\"properties\" class=\"anchorx\"><\/a>Application Properties<\/strong><\/h2>\n\n\n\n<p>When accessing the Application Properties, a popup window will open with multiple tabs; let&#8217;s go through each of the tabs:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Program Options<\/strong><\/h3>\n\n\n\n<div class=\"leftMargin\"><strong>debug:<\/strong> Selecting Yes will display the SQL generated by m-Power at runtime, at the bottom of the page. This allows developers to debug the application to see exactly what is being queried to the database. The default setting is No.\n<p>&nbsp;<\/p>\n<p><strong>edit_type<\/strong>: This property specifies whether this application can be edited through m-Painter (WYSIWYG and text editing), or through text editing only.<\/p>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>SQL Statement<\/strong><\/h3>\n\n\n\n<div class=\"leftMargin\"><strong>caseSensitive<\/strong>: When using record selections or creating filters via the Selection window, if no matches are found but you know matching records exist, m-Power offers three options for case sensitivity. Change the search value to uppercase is the default, most databases use uppercase when writing data to the table, so this selection will uppercase the values typed in the input field by the end user. Selecting No case conversion will not uppercase the uses input, use this option if know the data in the database are lowercase. The third option is Change both the search value and the DB field to uppercase using the UPPER keyword this will uppercase both the database and the users input, this option is the slowest because the data needs to be read from the table and changed to uppercase, this is done for every record in the table. <a href=\"\/docs\/m-painter-app-prop\/case-sensitivity-within-applications\">More Information<\/a>\n<p>&nbsp;<\/p>\n<p><strong>secureby<\/strong>: m-Power applications can be secured against username or session ID. Click <a href=\"\/docs\/security\/secure-by-user-or-session-id\">here<\/a> to learn more about this feature.<\/p>\n<p><strong>sql_statement<\/strong>: Developers can specify an SQL statement with this property to override the default SQL statement created by the application.<\/p>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Override Properties<\/strong><\/h3>\n\n\n\n<p>This section allow developers to override the default error messages , by selecting the error message you wish to customize, and then simply modifying the Value section to whatever you would like your message to say at runtime in place of the default message. <a href=\"\/docs\/m-painter-app-prop\/how-to-customize-error-messages-in-your-web-applications\">More Information<\/a><\/p>\n\n\n\n<p><a href=\"#index\">Back to the Top<\/a><\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><strong><a id=\"implement\" class=\"anchor\"><\/a>Implementing Row Level Security<\/strong><\/h1>\n\n\n\n<p>Once you&#8217;ve created your security table and retrieval, all that is left to do is to integrate the security retrieval with the application you wish to secure. To do so, open the application and navigate to the Record Selections specification.<\/p>\n\n\n\n<p>For the field value, select the field in your application that contains user information. Select &#8216;Row Level Security&#8217; from the relation dropdown. The value dropdown list will populate with all row level security templates that have been created in this dictionary. Select your security application and click accept.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"\/docs\/vue-images\/row_level_2.png\"><img decoding=\"async\" src=\"\/docs\/vue-images\/row_level_2.png\" alt=\"\"\/><\/a><\/figure>\n\n\n\n<p>Once you have added your record selection, you will need to recompile your application. However, you do not need to overwrite the HTML or Application Properties. Your application should now be set to use Row Level Security!<\/p>\n\n\n\n<p><a href=\"#index\">Back to the Top<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Row Level Security is a highly flexible technique for controlling which records are returned based on the current user. Implementing row level security is a three step process. First, you must create a table that contains security credentials. Second, you need to create a retrieval over this table, using the&#8230;<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[249],"ht-kb-tag":[],"class_list":["post-6633","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-retrieval"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/6633","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/comments?post=6633"}],"version-history":[{"count":37,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/6633\/revisions"}],"predecessor-version":[{"id":13156,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/6633\/revisions\/13156"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/media?parent=6633"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-category?post=6633"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-tag?post=6633"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}