Data Level Report Security Utilizing User Groups

(5 posts) (2 voices)
  1. rpark, Member

    Hi all,
    I wanted to maintain the functionality of the Groups feature, but instead of using it for report-level security I wanted to use it for data-level security. Luckily all my database records are either tied directly or via foreign key to a single parent record called "Account". I thus modified the Account table to include "group_id" as a foreign key and created a 1:1 relationship between Account and mydbr_groups by creating a Group for each Account.

    All that was left was adding each User to the corresponding Group and then adding a JOIN and WHERE statement between Account and mydbr_groupsusers for each report such as:

    join mydbr_groupsusers mgu on account.group_id = mgu.group_id where mgu.user = inLogin;

    This appears to achieve all of my desired data-level filtering while also keeping the Groups feature working as expected. However, I worry that I am constantly repeating this JOIN and WHERE clause in all my reports (instead of reusing the code) and that there is simply a better way to do this?

    Would appreciate a confirmation that this is the correct approach, or a push in the right direction which allows for better code reuse. Even better would be native support for what I am attempting to do (I don't want to recreate an already existing wheel).

    Thank you!

  2. myDBR Team, Key Master

    a data-level security is dependent on the data structures and business requirements on your database. Data-level security basically identifies the person running the report (you can use inLogin for that) and then determines what data is accessible to the user. There really is no "one right way" or "native way" of doing this as different data structures and business requirements have different needs and offer different possibilities.

    If your 'Account' is enough to determine the access rights for the user, then you can use myDBR groups as part of the logic. That is perfectly fine. You may still reserve some of the groups for report level access control.

    myDBR Team

  3. rpark, Member

    Ok great, then I suppose I am doing a correct approach.

    Last question, the documentation states that there's a "special" use of inLogin which is "inLogin varchar(30)", however, 30 characters is not sufficient for my usernames. I thus am utilizing "inLogin varchar(128)" which is the varchar size in the table "mydbr_groupsusers" column for username.

    Will this present an issue? I am unsure why the documentation specifically references 30 characters?

    Appreciate it!

  4. myDBR Team, Key Master

    The "special" was referring to the automatic parameter "inLogin". It's length can be (up to) 128 characters. Cleared the text now in documentation.

    myDBR Team

  5. rpark, Member

    So 128 length is no issue... Perfect, thanks again


You must log in to post.