This is lurching in the direction of RBAC - role-based access control. You might also wonder about whether to use LBAC - label-based access control. And, depending on your DBMS, there could be other ways to achieve it (consider Oracle VPD - virtual private database - for example). All of this is either rather or very DBMS specific - different solutions for different DBMS.
You seem to be talking about control at the row level. That is, one row in the contact table may be accessible to everybody, while another is accessible only to one set of departments, another is only accessible to one group of people, and so on.
Remember that relational DBMS work best with sets. A single group is a set of groups with one member group; a single user is a set of groups with one member user. This means we have fewer cases to deal with.
If you want to implement it in Standard SQL, then I think you are going to need to use a combination of views exploiting joins with control tables, etc. The hard parts with such a system are populating the control tables and restraining the administrative users (actually, restraining administrators is always one of the hard parts).
The basic technique would be:
- Create the base table with an appropriate column to identify the privilege set that applies to each row in the table.
- Revoke all public access to the table.
- Create a view on the base table which shows all the columns from the base table that are permitted. It will be a join view with a control table, to be defined momentarily. The view query conditions will also be conditioned by the current user.
- Grant appropriate access to the view.
- Create appropriate INSTEAD OF triggers on the view to handle insert, delete and update operations on the view, relaying the changes to the base table.
- Create the control table to join with the base table.
- Populate it with appropriate data.
- Light blue touch paper and stand well back.
Now, about that joining column and the control table...
Someone has to specify what permissions apply to newly inserted rows in the table - what is the default access provided. And someone has to define how the default access can be overridden. Both of those could be messy.
There are several ways to structure the control table:
One mechanism relies on a each row in the base table having a unique ID (which might be an automatic generated ID or just the value of the primary key). The control table then includes a copy of that unique ID, and defines which users or groups can access it. This means that there may be multiple entries in the control table for a given row, one for each user or group that can access the row. In this scheme, the control table has a foreign key that references the base table.
Another mechanism embeds an ID number into the base table that is a foreign key to the control table(s). It basically identifies a set of privileges, and the reference in the base table means that the row has the access permission associated with the access control ID. The structure behind the control table might be that the ID 0 has no access for anyone (via the view), ID 1 has access for everyone, and other values designate combinations of users and groups - each different combination has a different ID. With this, there might be several tables in the set of control tables - and we're also discussing having a set of these control tables for each table that is protected.
Clearly, access to the control tables is strictly limited - but also crucial to the management of who can see what.
Both of these are administrative nightmares - which is why you probably end up with a DBMS-provided access control mechanism rather than a generic SQL solution.