Tech Tip 75176 discussed how to handle user logins via an application's SQL Server as opposed to the 4D Application Server. Whether you are using the On SQL Authentication Database Method or the default 4D Password system, the SQL Server user must end up logged in as a 4D user. In the On SQL Authentication Database Method, the CHANGE CURRENT USER command does that.
By setting up a specific group for the SQL Server connections it is possible to limit table access on a case by case basis. You can limit access to certain groups to read only or read/write, or even full design access. This is done in the SQL pane of the Prefences dialog. An example of that is shown below:
In the example above, dedicated SQL Users are all members of the "SQLUsers" group and 4D users are part of the "4DUsers" group. When connecting via the SQL Server - using the settings above - the 4D users will have full Read/Write access while the dedicated SQL users only have Read access.
You can then log SQL Server users in to limited access groups as shown in Tech Tip 75176.
For more information on limiting database access in general (outside the SQL Server) see the Tech Tip on changes to User Access in 4D v11 SQL.
KNOWLEDGE BASE
Tech Tip: Limit Table Access for SQL Server users
PRODUCT: 4D Server | VERSION: 11.3 | PLATFORM: Mac & Win
Published On: March 18, 2009
Commented by Thomas Fitch on March 30, 2009 at 1:20 PM
In 4D 2004 you can do the same thing in the Structure window. You can manage each table's access rights for different user groups. Obviously there is no SQL Server in 4D 2004, but you can log ODBC connections in as a specific user in a similar way and limit their access. I've done this with users getting data from 4D into Excel via queries to make sure they can only get data and not update data.