This documentation was used during the planning phase. See also, newer documentation on the User Audit Log
User auditing is auditing what users (or processes) do to the registry at a high level. e.g. Sue added a group on a certain day. It might not record that since that group was created, some privileges were created, the "base" type was associated with that group, etc.
We should have two tables. An audit table, and an audit meta data table. The audit table has user info, timestamp, audit_type, audit_action, etc, and 10 misc cols for various things. The metadata table describes what those 10 cols mean (e.g. for a group insert, col1 means group_uuid, col2 means group_name, etc) for a certain type and action (this table only has a few dozen rows). Then in the API we code in where the auditing occurs in variuos places. A loader job will delete audit information that is too old (not implemented yet). In the UI or WS we can query this information (probably by audit type and action), and a table of data with headers can be returned (headers are from metadata).
We also need a context_id on all tables, and in the user audit table, and PIT tables. In Java we will have an inverse of control which sets a threadlocal context id if it is not there. If there is a query without a context id, then it should throw an exception (since something is not coded completely). The code where the context is set, is probably where the user auditing should occur. Maybe only if the context id is new (not nested from somewhere else).
There is a view "grouper_audit_entry_v" which is the best DB resource for browsing audits, since it puts the category, action, and misc labels next to the audit entry record...
Audit type table holds (not all fields described here):
- id: foreign key to audit entry
- auditCategory: to organize the audits, e.g. "group"
- actionName: type of action in a category, e.g. "add_group"
- labelString01-08: labels of the audit entry string fields 01-08, e.g. group_id, group_name, member_id, etc
- labelInt01-05: labels of audit entry int fields 01-05, e.g. number_of_inserts, etc
Audit entry table holds (not all fields described here):
- id: unique id of audit entry
- contextId: ties together all the actions inside the one high level action
- auditTypeId: foreign key to the audit type table
- loggedInMemberId: member id logged in to e.g. UI or WS
- actAsMemberId: if the member is acting as someone else, this is that id
- createdOn: timestamp when entry was created
- description: description in sentence form of the action
- envLabel: from grouper.properties, e.g. PROD
- grouperEngine: e.g. UI, WS, GSH, loader, etc
- grouperVersion: e.g. v1.4.0
- serverHost: e.g. myServerName
- userIpAddress: IP address of user e.g. in WS or UI 1.2.3.4
- string01-08: misc string fields, each must have label in auditType
- int01-08: misc int fields, each must have label in auditType
GSH queries
All results in short form:
gsh 1% new UserAuditQuery().executeReport() Results 1 - 10 of 35 ordered by: lastUpdatedDb desc 2009-04-15 07:42:03.179 membership - addMembership ( 261ms, 18 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) (actAs: jdbc - test.subject.1 - description.test.subject.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: grouperWS, host: AIT100229, user: mchyzer 2009-04-15 07:41:56.554 membership - deleteMembership ( 376ms, 11 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: grouperWS, host: AIT100229, user: mchyzer 2009-04-15 07:26:10.495 membership - addMembership ( 532ms, 20 queries) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: null, host: AIT100229, user: mchyzer 2009-04-15 07:10:55.061 membership - addMembership ( 302ms, 13 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.5, field: members Server: grouperUI, host: AIT100229, user: mchyzer 2009-04-15 06:40:52.351 membership - addMembership ( 281ms, 13 queries) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.6, field: members Server: null, host: AIT100229, user: mchyzer 2009-04-15 06:26:45.965 groupType - addGroupType ( 33ms, 2 queries) Description: Added group type: requireActiveStudent Server: grouperShell, host: AIT100229, user: mchyzer 2009-04-15 06:26:45.902 groupField - addGroupField ( 346ms, 3 queries) Description: Added group field: requireActiveEmployee, id: 1dc48fed-b1ca-4099-a16c-f04375d6e145, type: attribute, groupType: requireInGroups Server: grouperShell, host: AIT100229, user: mchyzer 2009-04-15 06:24:52.760 membership - addMembership ( 67ms, 18 queries) Description: Added membership: group: aStem:activeEmployee, subject: jdbc.test.subject.1, field: members Server: grouperShell, host: AIT100229, user: mchyzer 2009-04-15 06:22:02.883 membership - addMembership ( 3856ms, 18 queries) Description: Added membership: group: aStem:activeEmployee, subject: jdbc.test.subject.0, field: members Server: grouperShell, host: AIT100229, user: mchyzer 2009-04-15 06:21:08.070 groupField - addGroupField ( 290ms, 2 queries) Description: Added group field: requireAlsoInGroups, id: 5f4bb1f3-117a-4008-bbbf-91c2697b58b8, type: attribute, groupType: requireInGroups Server: grouperShell, host: AIT100229, user: mchyzer
All results in long form:
gsh 5% new UserAuditQuery().executeReportExtended(); Results 1 - 10 of 35 ordered by: lastUpdatedDb desc 2009-04-15 07:42:03.179 membership - addMembership ( 261ms, 18 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) (actAs: jdbc - test.subject.1 - description.te st.subject.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: grouperWS, host: AIT100229, user: mchyzer Id: 1b9de977-d3d7-4832-b107-64fe89bac52a FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: 7d0c3b88-733e-4bc7-8a44-84751503ebd1 MembershipType: immediate OwnerType: group OwnerId: 197d1379-ac1a-4c0f-a5d0-80254d128212 OwnerName: aStem:activeStudent 2009-04-15 07:41:56.554 membership - deleteMembership ( 376ms, 11 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: grouperWS, host: AIT100229, user: mchyzer Id: 5eda0781-4d36-4f63-857d-22e099cde428 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: 7d0c3b88-733e-4bc7-8a44-84751503ebd1 MembershipType: immediate OwnerType: group OwnerId: 197d1379-ac1a-4c0f-a5d0-80254d128212 OwnerName: aStem:activeStudent 2009-04-15 07:26:10.495 membership - addMembership ( 532ms, 20 queries) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: null, host: AIT100229, user: mchyzer Id: 5eda0781-4d36-4f63-857d-22e099cde428 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: 7d0c3b88-733e-4bc7-8a44-84751503ebd1 MembershipType: immediate OwnerType: group OwnerId: 197d1379-ac1a-4c0f-a5d0-80254d128212 OwnerName: aStem:activeStudent 2009-04-15 07:10:55.061 membership - addMembership ( 302ms, 13 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.5, field: members Server: grouperUI, host: AIT100229, user: mchyzer Id: fc5019d7-95e3-4a58-8695-dba7216307b3 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: 7caa1de6-41fd-4a25-8115-7cc0c896ac5c MembershipType: immediate OwnerType: group OwnerId: 197d1379-ac1a-4c0f-a5d0-80254d128212 OwnerName: aStem:activeStudent 2009-04-15 06:40:52.351 membership - addMembership ( 281ms, 13 queries) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.6, field: members Server: null, host: AIT100229, user: mchyzer Id: fd44d176-abc8-44a6-8fef-f22f397bf4a4 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: 4296d1c8-a311-4e30-b429-887135132464 MembershipType: immediate OwnerType: group OwnerId: 197d1379-ac1a-4c0f-a5d0-80254d128212 OwnerName: aStem:activeStudent 2009-04-15 06:26:45.965 groupType - addGroupType ( 33ms, 2 queries) Description: Added group type: requireActiveStudent Server: grouperShell, host: AIT100229, user: mchyzer Id: 5acacc8b-c15b-4aab-b7a3-961d90d7c290 Name: requireActiveStudent 2009-04-15 06:26:45.902 groupField - addGroupField ( 346ms, 3 queries) Description: Added group field: requireActiveEmployee, id: 1dc48fed-b1ca-4099-a16c-f04375d6e145, type: attribut e, groupType: requireInGroups Server: grouperShell, host: AIT100229, user: mchyzer Id: 1dc48fed-b1ca-4099-a16c-f04375d6e145 Name: requireActiveEmployee GroupTypeId: a0d01b9b-1b1b-4791-863f-2fe42200f4b9 GroupTypeName: requireInGroups Type: attribute 2009-04-15 06:24:52.760 membership - addMembership ( 67ms, 18 queries) Description: Added membership: group: aStem:activeEmployee, subject: jdbc.test.subject.1, field: members Server: grouperShell, host: AIT100229, user: mchyzer Id: ffea4d28-1335-4d0c-ad37-987fbe0e9ca5 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: e5c1a993-ecf9-4d12-b561-a80b09738cd8 MembershipType: immediate OwnerType: group OwnerId: c99afbc4-9138-4d42-8ff9-dd77d2369262 OwnerName: aStem:activeEmployee 2009-04-15 06:22:02.883 membership - addMembership ( 3856ms, 18 queries) Description: Added membership: group: aStem:activeEmployee, subject: jdbc.test.subject.0, field: members Server: grouperShell, host: AIT100229, user: mchyzer Id: 1b16f549-7408-4035-bd8b-2fd5c7dd7af4 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: 154ca2b1-6306-48ac-be74-a10966ecb427 MembershipType: immediate OwnerType: group OwnerId: c99afbc4-9138-4d42-8ff9-dd77d2369262 OwnerName: aStem:activeEmployee 2009-04-15 06:21:08.070 groupField - addGroupField ( 290ms, 2 queries) Description: Added group field: requireAlsoInGroups, id: 5f4bb1f3-117a-4008-bbbf-91c2697b58b8, type: attribute, groupType: requireInGroups Server: grouperShell, host: AIT100229, user: mchyzer Id: 5f4bb1f3-117a-4008-bbbf-91c2697b58b8 Name: requireAlsoInGroups GroupTypeId: a0d01b9b-1b1b-4791-863f-2fe42200f4b9 GroupTypeName: requireInGroups Type: attribute
Records by user mchyzer
gsh 10% grouperSession = GrouperSession.startRootSession(false); edu.internet2.middleware.grouper.GrouperSession: 35a9ab9f-c630-4671-8a33-18fd2f29477d,'GrouperSystem','application' gsh 11% subject = SubjectFinder.findByIdOrIdentifier("mchyzer", true); subject: id='10021368' type='person' source='pennperson' name='Chris Hyzer' gsh 12% member = MemberFinder.findBySubject(grouperSession,subject, true); member: id='10021368' type='person' source='pennperson' uuid='ad020c13-15d3-4386-9517-821b727155ea' gsh 13% new UserAuditQuery().loggedInMember(member).executeReport() Results 1 - 3 of 3 ordered by: lastUpdatedDb desc 2009-04-15 07:42:03.179 membership - addMembership ( 261ms, 18 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) (actAs: jdbc - test.subject.1 - description.test.subject.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: grouperWS, host: AIT100229, user: mchyzer 2009-04-15 07:41:56.554 membership - deleteMembership ( 376ms, 11 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.7, field: members Server: grouperWS, host: AIT100229, user: mchyzer 2009-04-15 07:10:55.061 membership - addMembership ( 302ms, 13 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: aStem:activeStudent, subject: jdbc.test.subject.5, field: members Server: grouperUI, host: AIT100229, user: mchyzer
Developers
How developers add user auditing to their code:
- There should be a HibernateSession callback wrapper around all queries.
- Note: this relates all contextIds to this audit record, it makes the operation transactional, and it allows the auditing system to time the operation, and count how many queries it requires (helpful for performance debugging)
- Pass in to the callback that you will audit
HibernateSession.callbackHibernateSession( GrouperTransactionType.READ_WRITE_OR_USE_EXISTING, AuditControl.WILL_AUDIT, new HibernateHandler() {
- Use an existing, or create a new AuditTypeBuiltin (this will auto create a row in grouper_audit_type). Make sure to put all column headings in there for the string and/or int cols
- Make a unit test like AuditTest
Todo
- add audit on an export that an export was done
- add page auditing, as opposed to smaller contexts
- add delete rules in grouper.properties
- add audit rules in grouper.properties (e.g. be able to turn off auditing)
- grouper.properties require context id in saves
- add login to audits
- page audits? (delete in a week)?
- web service hit audits? (delete in a week)? keep input and output from servlet?
- which cols for indexes?
- unit test in same transaction (not needed right?)
- should we move grouper loader log to audit log? (might be able to make a view which is similar)
- Oh yeah, I need to update uuid's in memberChangeSubject calls potentially when merging subjects
- if object didnt change, should we save it? should we audit it?
- can we get rid of event log?
- turn off auditing during xmlimport, so it is the same?
- change api of audit entry to take name of field to set (e.g. for string or int)
- pass through the WS client name, and passthrough user IP address
Demo
Here is a demo of user auditing (movie)
First, clear out database:
gsh -registry -reset
Add a stem, and a type, and a group, and associate
gsh 0% typeAdd("testType"); type: 'testType' gsh 1% addRootStem("newStem", "new stem"); stem: name='newStem' displayName='new stem' uuid='82b8cd54-9a69-4754-b6da-649dc87670b6' gsh 2% addGroup("newStem", "aGroup", "a group"); group: name='newStem:aGroup' displayName='new stem:a group' uuid='913f36a9-c842-4fa1-911e-062a256028b2' gsh 3% groupAddType("newStem:aGroup", "testType"); true gsh 4%
Assign a privilege with web services
C:\temp\client>java -jar grouperClient.jar --operation=assignGrouperPrivilegesLiteWs --groupName=newStem:aGroup --subjectIdentifier=mchyzer --privilegeName=admin --allowed=true Success: T: code: SUCCESS_ALLOWED: group: newStem:aGroup: subject: 10021368: access: admin
Query the audits
gsh 10% new UserAuditQuery().loggedInMember(member).executeReport() Results 1 - 4 of 4 ordered by: lastUpdatedDb desc 2009-04-26 21:37:29.522 privilege - addPrivilege ( 597ms, 14 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added privilege: group: newStem:aGroup, subject: pennperson.10021368, privilege: admin Server: grouperWS, host: AIT100229, user: mchyzer 2009-04-26 21:28:49.284 membership - addMembership ( 78ms, 14 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: newStem:aGroup, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer 2009-04-26 21:28:22.847 group - updateGroup ( 9ms, 1 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Updated group: newStem:aGroup, Fields changed: description.description: FROM: 'null', TO: 'some group' Server: grouperUI, host: AIT100229, user: mchyzer 2009-04-26 21:28:22.800 group - updateGroup ( 17ms, 1 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Updated group: newStem:aGroup, Fields changed: none Server: grouperUI, host: AIT100229, user: mchyzer gsh 11% new UserAuditQuery().executeReportExtended() Results 1 - 10 of 25 ordered by: lastUpdatedDb desc 2009-04-26 21:37:29.522 privilege - addPrivilege ( 597ms, 14 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added privilege: group: newStem:aGroup, subject: pennperson.10021368, privilege: admin Server: grouperWS, host: AIT100229, user: mchyzer PrivilegeName: admin MemberId: d03585aa-b2e7-405a-b9bc-91f73413c60b PrivilegeType: access OwnerType: group OwnerId: 913f36a9-c842-4fa1-911e-062a256028b2 OwnerName: newStem:aGroup 2009-04-26 21:28:49.284 membership - addMembership ( 78ms, 14 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: newStem:aGroup, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer Id: 5c1db55a-7d3c-4010-869f-a7e013cac7b5 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: d03585aa-b2e7-405a-b9bc-91f73413c60b MembershipType: immediate OwnerType: group OwnerId: 913f36a9-c842-4fa1-911e-062a256028b2 OwnerName: newStem:aGroup 2009-04-26 21:28:22.847 group - updateGroup ( 9ms, 1 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Updated group: newStem:aGroup, Fields changed: description.description: FROM: 'null', TO: 'some group' Server: grouperUI, host: AIT100229, user: mchyzer Id: 913f36a9-c842-4fa1-911e-062a256028b2 Name: newStem:aGroup ParentStemId: 82b8cd54-9a69-4754-b6da-649dc87670b6 DisplayName: new stem:a group Description: some group 2009-04-26 21:28:22.800 group - updateGroup ( 17ms, 1 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Updated group: newStem:aGroup, Fields changed: none Server: grouperUI, host: AIT100229, user: mchyzer Id: 913f36a9-c842-4fa1-911e-062a256028b2 Name: newStem:aGroup ParentStemId: 82b8cd54-9a69-4754-b6da-649dc87670b6 DisplayName: new stem:a group 2009-04-26 21:27:44.206 membership - addMembership ( 53ms, 13 queries) Description: Added membership: group: penn:etc:webServiceClientUsers, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer Id: 21e2bd30-3c09-4a20-98cb-133f33fa8e56 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: d03585aa-b2e7-405a-b9bc-91f73413c60b MembershipType: immediate OwnerType: group OwnerId: e7b8f0c4-a6f3-4259-8fe0-2c0b232a5602 OwnerName: penn:etc:webServiceClientUsers 2009-04-26 21:27:44.143 membership - addMembership ( 47ms, 13 queries) Description: Added membership: group: penn:etc:webServiceActAsGroup, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer Id: aa146747-7856-4b8a-b854-70bd86b3c1b2 FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: d03585aa-b2e7-405a-b9bc-91f73413c60b MembershipType: immediate OwnerType: group OwnerId: ffd6c90b-3b39-49be-8393-4e79884cb8cd OwnerName: penn:etc:webServiceActAsGroup 2009-04-26 21:27:44.050 membership - addMembership ( 46ms, 13 queries) Description: Added membership: group: penn:etc:userInterfaceUsers, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer Id: 0086d53a-5507-4bb4-8850-deda7118b6ce FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: d03585aa-b2e7-405a-b9bc-91f73413c60b MembershipType: immediate OwnerType: group OwnerId: 2e3f6b80-537b-4d2b-85d8-4e182c5d0c9e OwnerName: penn:etc:userInterfaceUsers 2009-04-26 21:27:43.987 membership - addMembership ( 276ms, 21 queries) Description: Added membership: group: penn:etc:sysAdminGroup, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer Id: b0981d60-d404-4ca7-90da-184fee9a57bb FieldId: f015c16f-a784-45e3-95aa-d6479a190e59 FieldName: members MemberId: d03585aa-b2e7-405a-b9bc-91f73413c60b MembershipType: immediate OwnerType: group OwnerId: 74190a41-7da3-48e0-a1a3-c5c4a26454fe OwnerName: penn:etc:sysAdminGroup 2009-04-26 21:22:00.672 groupTypeAssignment - assignGroupType ( 569ms, 7 queries) Description: Assigned group type: newStem:aGroup, typeId: f7cb86b1-dc75-45d7-ba37-8f7c809d30e2, to group: newStem:aGroup, groupId: 913f36a9-c842-4fa1-911e-062a256028b2 Server: grouperShell, host: AIT100229, user: mchyzer Id: 02db5bdb20e5260e0120e52a11510006 GroupId: 913f36a9-c842-4fa1-911e-062a256028b2 GroupName: newStem:aGroup TypeId: f7cb86b1-dc75-45d7-ba37-8f7c809d30e2 TypeName: testType 2009-04-26 21:21:16.922 group - addGroup ( 283ms, 22 queries) Description: Added group: newStem:aGroup Server: grouperShell, host: AIT100229, user: mchyzer Id: 913f36a9-c842-4fa1-911e-062a256028b2 Name: newStem:aGroup ParentStemId: 82b8cd54-9a69-4754-b6da-649dc87670b6 DisplayName: new stem:a group gsh 13% fromDate = edu.internet2.middleware.grouper.util.GrouperUtil.toTimestamp("2009/04/26 21:28:30"); java.sql.Timestamp: 2009-04-26 21:28:30.0 gsh 39% new UserAuditQuery().loggedInMember(member).setFromDate(fromDate).executeReport() Results 1 - 2 of 2 ordered by: lastUpdatedDb desc 2009-04-26 21:37:29.522 privilege - addPrivilege ( 597ms, 14 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added privilege: group: newStem:aGroup, subject: pennperson.10021368, privilege: admin Server: grouperWS, host: AIT100229, user: mchyzer 2009-04-26 21:28:49.284 membership - addMembership ( 78ms, 14 queries) Logged in user: pennperson - 10021368 - Chris Hyzer (ip: 127.0.0.1) Description: Added membership: group: newStem:aGroup, subject: pennperson.10021368, field: members Server: grouperUI, host: AIT100229, user: mchyzer