This topic is discussed in the Advanced Topics training video.
Grouper's views can be granted to schemas to make it possible to access Grouper data from SQL. Note, if you do this you should keep track of what you grant to do some sanity tests on Grouper upgrades. Note, some views are for reporting reasons and are not intended for real time data. Check the views that you use to see the performance. The SQL interface is useful for large loads that might overwhelm or not be performant or not be possible in web services. This SQL interface is for SELECT only, not insert/update/delete, and is not used with the Grouper API, just SQL queries in whatever programming language you would like. Ask the Grouper team for help if you are not sure how to use the tables
Best practices to using the SQL interface
- Do not use the Grouper schema for accesses to the Grouper SQL interface, make another schema which is readonly and only has access to the required data (where clause)
- Make a view on top of the Grouper tables/views and grant that to the other schema. This is because when Grouper is upgraded the views are dropped (including grants), so they would be lost or need to be recreated
The main views to use are:
- grouper_membership_lw_v: This view can be used for memberships or Grouper group privileges (e.g. if someone can ADMIN a group). Note: make sure you have the proper where clause which selects the proper list (generally "members")
- grouper_perms_all_v: This view can be used for Grouper permissions. Note, DISALLOW is exposed through the SQL interface, and is not calculated. So if DISALLOW is assigned in the data being used, you should either not use the SQL interface, or you would need to calculate the DISALLOWs after selecting the data (note, this is a complicated algorithm though it is documented on the wiki with examples).
- There are some useful tables to join to: grouper_groups, grouper_members, grouper_stems, etc