Projects/Reporting-friendly KDB dump format - Revision history

TomYu at 20:11, 14 September 2015

2015-09-14T20:11:17Z

TomYu at 02:47, 9 September 2015

2015-09-09T02:47:49Z

TomYu at 22:15, 8 September 2015

2015-09-08T22:15:50Z

TomYu at 21:12, 4 September 2015

2015-09-04T21:12:14Z

TomYu at 22:38, 2 September 2015

2015-09-02T22:38:31Z

TomYu at 21:55, 22 July 2015

2015-07-22T21:55:00Z

TomYu at 21:41, 6 July 2015

2015-07-06T21:41:46Z

TomYu at 18:48, 17 March 2015

2015-03-17T18:48:04Z

TomYu: New page: {{project-early}} This is split from Projects/KDB reporting and bulk operations. ==Background== Operators often want to perform reporting operations on KDB data, but the dump format ...

2015-03-02T22:06:24Z

New page: {{project-early}} This is split from Projects/KDB reporting and bulk operations. ==Background== Operators often want to perform reporting operations on KDB data, but the dump format ...

New page

{{project-early}}
This is split from [[Projects/KDB reporting and bulk operations]].

==Background==

Operators often want to perform reporting operations on KDB data, but the dump format is optimized for loading by <code>kdb5_util</code>, not human reading or reporting using simple scripts. For example, the number of columns on each principal dump line depends on the number of keydata entries associated with that principal. Also, some useful metadata such as modification date are only present in a human-unfriendly hexadecimal format, as an artifact of being stored in the tl_data of the principal.

==Format choice==

Tab-separated value formats are probably acceptable to the largest variety of tools. These tools range from simple awk scripts to SQL databases.

There are several conceptual database tables, each of which will have a different set of columns. To allow combining these in a single dump file, each dump line will have a first column that indicates the conceptual table to which it belongs. Another option is to provide command line options to select an individual conceptual table to dump.

==Conceptual tables==

This is an attempt to split KDB data fields into a somewhat more normalized schema that is somewhat easier to manipulate for reporting purposes.

===Principal metadata===

* Principal name
* Last modified by (principal name)
* Last modification date

===Principal keys===

There will generally be multiple dump key data dump lines per principal. The order is significant (though typically it's only important within the active kvno), so there will need to be a key index number in case the user imports the dump into a data store that doesn't preserve the ordering of input lines (such as most relational databases).

Some open questions include whether to use numeric values or string representations for enctype or salt type. Maybe this can be a runtime option?

* Principal name
* Key index
* Key version number (kvno)
* Enctype
* Salt type
* Salt data as hex string (might be "-1" to denote no salt or normal/default salt)

A sample implementation of a "keyinfo" dump format is at https://github.com/tlyu/krb5/tree/keyinfo

===Per-principal policy data===

* Principal name
* Principal expiration date
* Password expiration date
* Max ticket lifetime
* Max renewable ticket lifetime
* Password policy name

===Per-principal lockout data===

These are the per-KDC (non-replicated) data that track failed logins due to incorrect passwords.

* Principal name
* Last success
* Last failure
* Count of failed attempts

===Principal boolean attributes===

This is currently an boolean flag word; it's probably best to make it a set of strings. This is a bit tricky because there are some flags that are of the form disallow_*.

* Principal name
* Attribute name if set (string form)

===Principal string attributes===

* Principal name
* Attribute name
* Attribute value

===Password policy===

* Policy name
* Min password life
* Max password life
* Min password length
* Min password character classes
* Password history length

===Lockout policy===

* Policy name
* Max failures
* Failure count reset interval
* Lockout duration

===Ticket policy===

* Policy name
* Max ticket lifetime
* Max renewable ticket lifetime

===Policy boolean attributes===

As for principal boolean attributes

===Policy allowed keysalts===

(Is this an ordered list?)

* Policy name
* Enctype
* Salt type

@@ Line 1: / Line 1: @@
-{{project-early}}
+{{project-rel|1.14}}
 ==Background==
@@ Line 111: / Line 111: @@
 ;key: Attribute name
 ;value: Attribute value

@@ Line 38: / Line 38: @@
 ===Symbolic names===
-Some values such as enctypes and salttypes can be printed numerically or using symbolic names.
+Some values such as enctypes and salttypes can be printed numerically or using symbolic names.  The default will be for symbolic names.
 ===Date/time===
-Time stamps can be printed as decimal POSIX time_t values.  Later options could include ISO 8601 formats.
+Time stamps print as ISO 8601 UTC date and time.  Numeric output is decimal POSIX time_t values.
-Durations can be printed as decimal seconds.  Later options could include ISO 8601 durations (although technically ISO 8601 duration representations are only supposed to designate intervals by context, and not pure durations).
+Durations can print as decimal seconds.  Later options could include ISO 8601 durations (although technically ISO 8601 duration representations are only supposed to designate intervals by context, and not pure durations).
 ==Conceptual tables==
-This is an attempt to split KDB data fields into a somewhat more normalized schema that is somewhat easier to manipulate for reporting purposes.
+This is an attempt to split KDB data fields into a somewhat more normalized schema that is somewhat easier to manipulate for reporting purposes.  The rectype is the string used to request a specific conceptual table.  Initially, only one table can print at a time.  Eventually, there will be a combined output format where each line is prefixed with the rectype.  Additional possibilities include outputting a directory with one file per table.
 ===Principal metadata===
-;rectype: princmeta
+;rectype: princ_meta
 ;name: Principal name (krb5_kdb_entry)
 ;modby: Last modified by (principal name) (KRB5_TL_MOD_PRINC)
@@ Line 64: / Line 64: @@
 There will generally be multiple dump key data dump lines per principal.  The order is significant (though typically it's only important within the active kvno), so there will need to be a key index number in case the user imports the dump into a data store that doesn't preserve the ordering of input lines (such as most relational databases).
 ;rectype: keyinfo (or keydata)
@@ Line 99: / Line 97: @@
 ===Principal boolean attributes===
 Feedback from operators indicates that having a row for each attribute, regardless of whether or not it's set, can be useful to satisfy auditors.
-Unknown attributes can probably print as hexadecimal numbers, or optionally ignored.
+Unknown attributes print as hexadecimal numbers, if set.  If the user requests numeric output, output all flag settings, including unset flags, as hexadecimal.
 ===Principal string attributes===
-* Attribute name
+;name: Principal name
-* Attribute value
+;key: Attribute name
 ===Principal key history===
@@ Line 167: / Line 164: @@
 ;len:
 ;mask: (not encoded?)
-;attributes: princflags
+;attributes: princ_flags
 ;max_life: princ_tktpolicy
 ;max_renewable_life: princ_tktpolicy
 ;expiration: princ_tktpolicy
 ;pw_expiration: princ_tktpolicy
-;last_success: princlockout
+;last_success: princ_lockout
-;last_failed: princlockout
+;last_failed: princ_lockout
-;fail_auth_count: princlockout
+;fail_auth_count: princ_lockout
 ;n_tl_data: (tl_data)
 ;n_key_data: keyinfo/keydata
 ;e_length: (implicit)
 ;e_data: princ_edata
 ;tl_data: (tl_data)
 ;key_data: keyinfo/keydata
@@ Line 186: / Line 183: @@
 ;version:
-;policy: princmeta
+;policy: princ_meta
 ;aux_attributes:
 ;old_key_len: (implicit in oldkeyinfo/oldkeydata)
 ;old_key_next: (implicit in oldkeyinfo/oldkeydata)
 ;old_keys: oldkeyinfo/oldkeydata
-;admin_history_kvno: princmeta
+;admin_history_kvno: princ_meta
 ==tl_data cross reference==
-;KRB5_TL_LAST_PWD_CHANGE: princmeta
+;KRB5_TL_LAST_PWD_CHANGE: princ_meta
-;KRB5_TL_MOD_PRINC: princmeta
+;KRB5_TL_MOD_PRINC: princ_meta
 ;KRB5_TL_KADM_DATA: (see osa_princ_ent_rec)
-;KRB5_TL_MKVNO: princmeta
+;KRB5_TL_MKVNO: princ_meta

@@ Line 55: / Line 55: @@
 ;name: Principal name (krb5_kdb_entry)
 ;modby: Last modified by (principal name) (KRB5_TL_MOD_PRINC)
-;moddate: Last modification date (KRB5_TL_MOD_PRINC)
+;modtime: Last modification date (KRB5_TL_MOD_PRINC)
 ;lastpwd: Last password change (KRB5_TL_LAST_PWD_CHANGE)
 ;policy: Policy object name (osa_princ_ent_rec)
 ;mkvno: Master key version used for this principal's key data (KRB5_TL_MKVNO)
 ===Principal keys===
@@ Line 71: / Line 72: @@
 ;kvno: Key version number
 ;enctype: Enctype
 ;salttype: Salt type
 ;salt: Salt data as hex string (might be "-1" to denote no salt or normal/default salt)
@@ Line 78: / Line 80: @@
 ===Per-principal policy data===
-;rectype: princpolicy
+;rectype: princ_tktpolicy
 ;name: Principal name
 ;expiration: Principal expiration date
@@ Line 89: / Line 91: @@
 These are the per-KDC (non-replicated) data that track failed logins due to incorrect passwords.
 ===Principal boolean attributes===
-This is currently an boolean flag word; it's probably best to make it a set of strings.  This is a bit tricky because there are some flags that are of the form disallow_*.
+The KDB currently stores this as a boolean flag word; it's probably best to make it a set of strings.  This is a bit tricky because there are some flags that are of the form disallow_*.
 * Principal name
-* Attribute name if set (string form)
+* Attribute name (string form)
 * Boolean value (true/false or 1/0)
@@ Line 111: / Line 114: @@
 * Attribute name
 * Attribute value
 ===Principal key history===
@@ Line 172: / Line 168: @@
 ;mask: (not encoded?)
 ;attributes: princflags
-;max_life: princpolicy
+;max_life: princ_tktpolicy
-;max_renewable_life: princpolicy
+;max_renewable_life: princ_tktpolicy
-;expiration: princpolicy
+;expiration: princ_tktpolicy
-;pw_expiration: princpolicy
+;pw_expiration: princ_tktpolicy
 ;last_success: princlockout
 ;last_failed: princlockout
@@ Line 192: / Line 188: @@
 ;policy: princmeta
 ;aux_attributes:
-;old_key_len: oldkeymeta
+;old_key_len: (implicit in oldkeyinfo/oldkeydata)
-;old_key_next: oldkeymeta
+;old_key_next: (implicit in oldkeyinfo/oldkeydata)
 ;old_keys: oldkeyinfo/oldkeydata
-;admin_history_kvno: oldkeymeta
+;admin_history_kvno: princmeta
 ==tl_data cross reference==

@@ Line 33: / Line 33: @@
 bar@EXAMPLE.COM	1	1	des-cbc-crc	normal	-1
 </pre>
 ==Conceptual tables==

@@ Line 10: / Line 10: @@
 ==Format choice==
-Tab-separated value formats are probably acceptable to the largest variety of tools.  These tools range from simple awk scripts to SQL databases.
+Tab-separated value formats are probably acceptable to the largest variety of tools.  These tools range from simple awk scripts to SQL databases.  Comma-separated values are easy to add.  Various quoting and escaping options exist in various dialects of CSV format.  Opinions vary on whether tab-separated formats can use quoting, but in any case, most of the fields will not require quoting if tab-separated.
-There are several conceptual database tables, each of which will have a different set of columns.  To allow combining these in a single dump file, each dump line will have a first column that indicates the conceptual table to which it belongs.  Another option is to provide command line options to select an individual conceptual table to dump.
+There are several conceptual database tables, each of which will have a different set of columns.  To allow a single combined dump format, each dump line will have a first column that indicates the conceptual table to which it belongs.  Another option is to provide command line options to select an individual conceptual table to dump, in which case the table name prefix would be omitted from each line.  Headers should be optional, because some tools work better without them.
 ==Examples==
@@ Line 22: / Line 22: @@
 name	keyindex	kvno	enctype	salttype	salt
 foo@EXAMPLE.COM	0	1	aes128-cts-hmac-sha1-96	normal	-1
 bar@EXAMPLE.COM	1	1	des-cbc-crc	normal	-1
 $ sqlite3
@@ Line 29: / Line 30: @@
 bar@EXAMPLE.COM	1	1	des-cbc-crc	normal	-1
 sqlite> .quit
 $ awk -F'\t' '$4 ~ /des-cbc-/ { print }' keyinfo.txt
 bar@EXAMPLE.COM	1	1	des-cbc-crc	normal	-1
 </pre>
@@ Line 42: / Line 43: @@
 * Last modified by (principal name)
 * Last modification date
 ===Principal keys===
@@ Line 65: / Line 69: @@
 * Max ticket lifetime
 * Max renewable ticket lifetime
 ===Per-principal lockout data===
@@ Line 93: / Line 96: @@
 * Attribute name
 * Attribute value
 ===Password policy===
@@ Line 127: / Line 148: @@
 * Enctype
 * Salt type

@@ Line 40: / Line 40: @@
 ===Principal metadata===
 ===Principal keys===
@@ Line 53: / Line 54: @@
 Some open questions include whether to use numeric values or string representations for enctype or salt type.  Maybe this can be a runtime option?
 A sample implementation of a "keyinfo" dump format is at https://github.com/tlyu/krb5/tree/keyinfo
@@ Line 64: / Line 66: @@
 ===Per-principal policy data===
-* Principal expiration date
+;name: Principal name
-* Password expiration date
+;expiration: Principal expiration date
-* Max ticket lifetime
+;pw_expiration: Password expiration date
-* Max renewable ticket lifetime
+;max_life: Max ticket lifetime
 ===Per-principal lockout data===

← Older revision		Revision as of 18:48, 17 March 2015
Line 1:		Line 1:
	{{project-early}}		{{project-early}}
		+	{{project-target\|1.14}}
		+
	This is split from [[Projects/KDB reporting and bulk operations]].		This is split from [[Projects/KDB reporting and bulk operations]].