Control table visibility through AD security groups

Question

I'm not sure if this would even be possible, but it would be awesome to be able to restrict the visibility of tables to different Active Directory security/user groups. For example;

Main user group - can only view "Products" and "Clients" tables.

Super user group - can view "Products, "Clients", "Staff" and "Payroll" tables.

Accounts user group - can only view "Payroll" and "Staff" tables.

The current option of hiding certain table's relationships is fantastic, as well as the option of assigning a different group for "Admin" level access and "User" level access, but having that level of control over specific tables as well would be great.

Answer 1

Answer #1 - Separate Role Specific Applications

There are two solutions depending on the reason for managing the table visibility. Based on the question, it seems that the intention is to manage the table access based on different user roles.

In that case the best answer would be to create multiple applications to satisfy the different user roles. Within dbFront that is fairly straightforward and simple. This would involve some duplication of effort, but it would make sense since they are different roles with significantly different needs. The different roles likely need more than just additional/less table access. They likely need different reports, exports, access to differ procedures or workflows, visibility of different fields (more or less) etc...

To setup a new application, create a new database connection, again limited to the specific tables you need and proceed from there.

It is possible to copy the configuration from one database/application to another:

1. Open the original connection and export the configuration (optional table selection),
2. Open the new connection and import the configuration.

This can be done repeatedly, and it will ignore additional or missing tables. dbFront also allows you to easily restore a previous configuration so there is little risk.

Comments

Hello,
I can see the benefit of both ConnorK's original request and Anthony's suggestion that separate applications are a possible solution. I think that the separate applications are cleaner but will, necessarily, involve more work. A possible way to circumvent some of the additional work would be to allow the export and import of individual table metadata to cut down on the re-keying and re-design.
If you imported this table metadata into a Repository application you've got the beginnings of a data dictionary which you could use as building blocks for other applications.
Best Regards
Colin

---

We will be using SSO (OKTA), with that we pass in our DBFront AD groups that the user belongs to.  These AD Groups are used for general access to the application.  It would be great if these AD Groups could held as a fixed variable like %ADGroups% that could be used in the Row Security queries.  This would allow me to put ('DBFront_PayrollAdmins' in %ADGroups%) in the Can Update section of Row Security.  We manage our AD Groups very closely so I can count on them being correct.

With this suggestion the %ADGroups% would contain a comma separated list of the AD groups that our SSO provider would pass to DBFront.
Cheers!

---

@tblaser, I was about to implement your suggestion, since it seemed very logical and straightforward, but then ran into the empty list problem.   A SQL IN () clause can't be empty.  There are work arounds but they are non trivial and would result in brittle code (SQL in this case).  This in combination with the fact that some group lists can be HUGE, means that the best solution is a function expression such as {inGroup(groupName)}

---

@Colin, The dbFront export process does allow you to select the tables and configuration to export.

Answer 2

Answer #2 - New [Can See Table] security expression

As of dbFront 1.4.2.0660 you have access to a new [Can See Table] security expression. The security expressions also support a new function named {IsGroupMember(groupname)}.

With these two new features you can easily adjust the table visibility as needed based on the security groups that a user is a member of, or any other need.

The [Can See Table] expression is found on the [Security] tab in the table preferences. Security Tab

Comments

Cool, what is the syntax supposed to be? {inUserGroup('Administrators')} is giving "The field 'Can See Table' is invalid. The group ['Administrators'] was not found!"
Also note the expression in dbFront is named InUserGroup, vs. IsGroupMember here and the release notes.

---

Good catch and good testing.   No quotes required.  I updated dbFront to automatically handle quotes if provided.  IsGroupMember() is better then InUserGroup() so I renamed it.