Bug 49700 - Support monitoring of last successful LDAP bind to simplify identifying inactive accounts
Support monitoring of last successful LDAP bind to simplify identifying inact...
Status: CLOSED FIXED
Product: UCS
Classification: Unclassified
Component: LDAP
UCS 4.4
Other Linux
: P5 normal with 1 vote (vote)
: UCS 4.4-3-errata
Assigned To: Johannes Keiser
Erik Damrose
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2019-06-20 17:55 CEST by Arvid Requate
Modified: 2020-03-18 12:27 CET (History)
9 users (show)

See Also:
What kind of report is it?: Feature Request
What type of bug is this?: ---
Who will be affected by this bug?: ---
How will those affected feel about the bug?: ---
User Pain:
Enterprise Customer affected?: Yes
School Customer affected?: Yes
ISV affected?:
Waiting Support:
Flags outvoted after Product Owner Review:
Ticket number: 2019062021000216
Bug group (optional):
Max CVSS v3 score:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Arvid Requate univentionstaff 2019-06-20 17:55:51 CEST
This bug is a feature request:

A customer asked for a method to find inactive accounts by monitoring their last successful LDAP bind.


OpenLDAP offers the overlay slapo-lastbind for this, see

http://manpages.ubuntu.com/manpages/bionic/man5/slapo-lastbind.5.html

This would work per server.


Unfortunately the customer cannot simply activate this, because that would lead to two-three problems:

1. When activating this on the master, all replicating LDAP servers will stop working, because the replicated schema contains the new "authTimestamp" attribute, which has a usage flag of "dSAOperation", which OpenLDAP refuses to load. This needs to be filtered out of the schema in replication.py.

2. After that, the replicating LDAP servers will throw a fail.ldif because they don't know the replicated attribute. So replication.py probably also should filter out this attribute when replicating objects.

3. The overlay should get activated on each server.

For these reasons it would be good to adjust replication.py to filter out the attribute. It may also be helpful to make this "lastbind" feature configurable via UCR-Variable per server.
Comment 1 Florian Best univentionstaff 2019-06-20 18:47:42 CEST
Can't we create a LDAP ACL, which disallows read access to the attribute for servers not having the overlay activated? (instead of adjusting replication.py)

access to attrs="authTimestamp"
  by filter="&((objectType=DC_computer)(!(univentionService=slapo_lastbind)))" none stop
  by * +0 break
Comment 2 Arvid Requate univentionstaff 2019-06-21 09:24:10 CEST
Good additional layer of protection, thanks for proposing this. But see point 1: slapd hangs after schema replication if replicaton.py doesn't filter the attrivute out of the schema. What I described in detail in the list is a standard procedure as you can see by checking out e.g. the handling of memberof replication.py. Also I think that each system is repsonsible of protecting itself.
Comment 3 Arvid Requate univentionstaff 2019-07-01 11:43:00 CEST
FYI: If Samba/AD is installed the customer may refer to the lastLogonTimeStamp attribute, see

https://social.technet.microsoft.com/wiki/contents/articles/22461.understanding-the-ad-account-attributes-lastlogon-lastlogontimestamp-and-lastlogondate.aspx

Sites like https://www.epochconverter.com/ldap may bew helpful to convert Windows FILETIME to something readable.
Comment 4 Michael Grandjean univentionstaff 2019-08-19 11:23:28 CEST
Just FYI, the consulting point of view:

Samba/AD "lastLogonTimeStamp" is usually the way to go for customers in PS projects. Unfortunately this only works in environments where all users authenticate primarily against Samba/AD.

In environments without Samba/AD, the most used workaround is to implement password policies where users are forced to renew their password regularly. Users which didn't renew their expired password in a certain time interval are considered inactive.
This is not very practical and the overlay slapo-lastbind sounds like the "correct" approach to solve such requirements.
Comment 5 Arvid Requate univentionstaff 2019-08-20 17:03:30 CEST
It may be a good idea to e.g. create a cli tool then, that can be used to query the max of lastLogonTimeStamp and authTimestamp, if present.
Comment 6 Dirk Ahrnke univentionstaff 2019-11-28 13:58:38 CET
requested by another customer who is using  the S4-based workaround atm.
This customer is using Linux/LDAP-based clients it may happen that there is no lastLogonTimeStamp is available.
PCI-DSS regulations may apply to this customer with the requirement to disable accounts that havent been used for some time.
Comment 7 Johannes Keiser univentionstaff 2020-02-24 15:39:37 CET
7d1ae69d4f Bug #49700: Merge branch 'jkeiser/4.4-3/lastbind' into 4.4-3
d1023a2ddb Bug #49700: yaml
34ddd880e6 Bug #49700: debian changelog
e4868a7e52 Bug #49700: tests
bf9ccce3c7 Bug #49700: univention-lastbind script
a1d8eb4b47 Bug #49700: filter out authTimestamp from ldap schema
0933293824 Bug #49700: slapo-lastbind config

Successful build
Package: univention-directory-replication
Version: 12.0.0-4A~4.4.0.202002241532

Successful build
Package: univention-ldap
Version: 15.0.0-35A~4.4.0.202002241534

Successful build
Package: ucs-test
Version: 9.0.3-158A~4.4.0.202002241534

-----------

Added
ldap/overlay/lastbind
ldap/overlay/lastbind/precision
ucr variables.

If ldap/overlay/lastbind is activated then the timestamp of an successful ldap bind is stored in the 'authTimestamp' attribute on the user (on that ldap server; not replicated).

Added the script /usr/share/univention-ldap/univention_lastbind.py
The script can be executed for one or all users and it will collect all
'authTimestamp' values from all reachable ldap servers and store the youngest of
them into the newly added 'lastbind' extended attribute on the user.
Comment 8 Erik Damrose univentionstaff 2020-02-24 23:45:48 CET
OK: schema extension
OK: UCRv ldap/overlay/lastbind; ldap/overlay/lastbind/precision + templates
OK: authTimestamp gets written on every LDAP bind
OK: authTimestamp is not replicated
OK: /usr/share/univention-ldap/univention_lastbind.py {--user,--allusers} writing to UDM attribute lastbind, ldap Attribute univentionAuthTimestamp

~~: authTimestamp is written for every account that does a bind, e.g. cn=admin, computerobjects, etc. The lastbind overlay has no option to limit the account by a filter. To avoid too many LDAP modifications ldap/overlay/lastbind/precision should be defined - the overlay example configures an example of 1 week (604800).
Comment 9 Felix Botner univentionstaff 2020-02-25 10:05:16 CET
I guess the test tests/10_ldap/110_univention_lastbind.py broke the jenkins tests.

e.g.

slave:

*** BEGIN *** [u'/usr/bin/py.test', '110_univention_lastbind.py'] ***
*** 10_ldap/110_univention_lastbind.py *** Test the management/univention-ldap/scripts/univention_lastbind.py script ***
*** START TIME: 2020-02-25 01:04:48 ***
============================= test session starts ==============================
platform linux2 -- Python 2.7.13, pytest-3.0.6, py-1.4.32, pluggy-0.4.0
rootdir: /usr/share/ucs-test/10_ldap, inifile:
collected 6 items

110_univention_lastbind.py ......

========================== 6 passed in 51.60 seconds ===========================
*** END TIME: 2020-02-25 01:05:41 ***
*** TEST DURATION (H:MM:SS.ms): 0:00:52.993215 ***
*** END *** 0 ***

after that ldap replication seems to be broken

25.02.20 01:05:54.441  LISTENER    ( WARN    ) : replication: Can't contact LDAP server: retrying
25.02.20 01:05:54.444  LISTENER    ( ERROR   ) : replication: Undefined attribute type; dn="cn=slave094,cn=dc,cn=computers,dc=autotest094,dc=local": Error
25.02.20 01:05:54.444  LISTENER    ( ERROR   ) :        additional info: entry update failed
25.02.20 01:05:54.445  LISTENER    ( PROCESS ) : Exporting /etc/krb5.keytab on domaincontroller_slave
25.02.20 01:05:54.528  LISTENER    ( ERROR   ) : 'failed.ldif' exists. Check for /var/lib/univention-directory-replication/failed.ldif

I will disable the test!
Comment 10 Felix Botner univentionstaff 2020-02-25 10:21:20 CET
(In reply to Felix Botner from comment #9) 
> I will disable the test!

done, restarted the test, lets see if this helps
Comment 11 Johannes Keiser univentionstaff 2020-02-26 14:46:24 CET
QA feedback

fbf0e1df5d Bug #49700: yaml
18ae51a511 Bug #49700: yaml
1daff19713 Bug #49700: debian changelog
6192d44d6c Bug #49700: install script only on master/backup in new binary package. gt/lt filtering for lastbind extended attribute

Successful build
Package: univention-ldap
Version: 15.0.0-36A~4.4.0.202002261432

Successful build
Package: univention-server
Version: 14.0.0-12A~4.4.0.202002261435
Comment 13 Johannes Keiser univentionstaff 2020-02-28 12:19:51 CET
7e586515c1 Bug #49700: yaml
4375d3b2c7 Bug #49700: Merge branch 'jkeiser/4.4-3/lastbind' into 4.4-3
a15502b2bd Bug #49700: debian changelog
dc7dff3762 Bug #49700: adjust 10_ldap/110_univention_lastbind.py
8a822c9e7a Bug #49700: adjust configuration of lastbind precision default
3cbf171905 Bug #49700: doc for ucs manual and sdb article

Successful build
Package: univention-ldap
Version: 15.0.0-37A~4.4.0.202002281212

Successful build
Package: ucs-test
Version: 9.0.3-161A~4.4.0.202002281215
Comment 14 Johannes Keiser univentionstaff 2020-03-03 17:25:24 CET
d1448fb1e0 Bug #49700: yaml
234fb751cd Bug #49700: Merge branch 'jkeiser/4.4-3/lastbind' into 4.4-3
12750924d7 Bug #49700: debian changelog
fa15028c31 Bug #49700: increase default for ldap/overlay/lastbind/precision
6f01e7551b Bug #49700: lastbind docs
Comment 15 Erik Damrose univentionstaff 2020-03-03 19:21:01 CET
OK: univention-ldap
-- UCR template for lastbind module, UCRVs ldap/overlay/lastbind ldap/overlay/lastbind/precision in package univention-ldap-server
OK: NEW package univention-ldap-config-master (gets installed on DC Master + Backup) with script /usr/share/univention-ldap/univention_lastbind.py
univention_lastbind.py:
- collect authTimestamp from all users with --allusers, or only one with --user
- Written to UDM Attribute lastbind, LDAPattr univentionAuthTimestamp
OK: ldapsearch for univentionAuthTimestamp with ">=" and "<=" comparison is possible

OK: docs, i added a small change in commit e585026d64
OK: SDB article https://help.univention.com/t/14404
OK: univention-ldap.yaml, univention-server.yaml

todo: tests
Comment 16 Felix Botner univentionstaff 2020-03-05 09:39:15 CET
10_ldap.110_univention_lastbind failed in AD Member Mode Setup's

see https://jenkins.knut.univention.de:8181/job/UCS-4.4/job/UCS-4.4-3/job/ADMemberMultiEnv/lastCompletedBuild/testReport/
Comment 18 Erik Damrose univentionstaff 2020-03-05 15:49:26 CET
I fixed the test in 
232ba22a Explicitly use portnumber with -h in ldapsearch
Comment 19 Johannes Keiser univentionstaff 2020-03-05 17:09:33 CET
f7ef0efd5f Bug #49700: debian changelog
cdce8fa501 Bug #49700: 110_univention_lastbind.py cleanup + test tracebacks

Successful build
Package: ucs-test
Version: 9.0.3-169A~4.4.0.202003051705

----

Added warning about deactivating the lastbind overlay module to sdb article
https://help.univention.com/t/activating-the-lastbind-overlay-module/14404
Comment 20 Erik Damrose univentionstaff 2020-03-06 14:34:26 CET
OK: Tests
OK: SDB article
OK: yaml

Verified
Comment 21 Felix Botner univentionstaff 2020-03-12 14:14:57 CET
Test still fails in ad-member mode.

Is guess the reason is the fixed binddn and bindpwd in the test.

Please use the value of 

 tests/domainadmin/account

as binddn and the filename in 

 tests/domainadmin/pwdfile

as bindpwdfile.
Comment 22 Erik Damrose univentionstaff 2020-03-12 17:53:23 CET
Lets see if my fix works:
ce788281  ucs-test 9.0.3-177A~4.4.0.202003121752
Comment 23 Erik Damrose univentionstaff 2020-03-13 11:03:04 CET
I found no scenario where the test failed in the last test run. Setting bug back to verified