Wednesday, December 14, 2016

Building a New Column to List the Guardium Group Membership of DB Users

by John Haldeman, Enterprise Architect

Sometimes it's useful to know whether some of the data shown in a report is in a Guardium group without having to look up the group data elsewhere or modifying your conditions.

Let's take an example. Say you have three classifications of user:

  1. DBAs or other highly privileged users
  2. Non-DBA, direct-access users: people that connect directly with personal accounts
  3. Service accounts: users that belong to application or batch processes

You want a report showing all connections but a column indicating that the user is in one of those groups or unclassified.


It should be noted that you can do what I'm describing by color coding your reports. That works great in V9 (I have trouble with it in V10), but it doesn't work so well if you frequently export data to CSV files. Also, if you want to reuse the definition you need to recreate the color coding over and over again for each report - not very convenient.

The method this post discusses creates a custom computed column that looks into the GROUP tables of Guardium and then gives you a field in your report showing which groups the users belong to. It looks something like this:
Report Showing Computed Column that Lists DB User Group Membership
If this is something you're interested in doing, you can execute the following grdAPI call from the CLI to add the column:
grdapi create_computed_attribute attributeLabel="User In Groups" entityLabel="Client/Server" expression="(SELECT GROUP_CONCAT(GD.GROUP_DESCRIPTION) FROM GROUP_MEMBER GM, GROUP_DESC GD WHERE GM.GROUP_ID = GD.GROUP_ID AND GM.GROUP_MEMBER = DB_USER)"
Change "User In Groups" above if you want a different column name. Change "DB_USER" in the join at the end of the SQL statement in the "expression" field if you'd like to do this with different columns (for example: CLIENT_IP, SOURCE_PROGRAM, etc.). 
The new column appears in the query builder under the "Client/Server" entity. You can now use this in any report where "Client/Server" is available. I've found this particularly useful when debugging things like whitelists and logging levels, but there are many other uses as well. You can also do similar things with custom tables as well - not just groups - to add additional context to your reports. Custom computed columns are quite powerful.
New Column Shown in the Query Builder - The New Columns Always Appear Last in the Lists



1 comment: