Repository: AthenZ/athenz

Issue:
no limit on role/group audit log entries in the db
currently we have no limits on the number of audit log entries we create for role/group membership changes. This means a role or a group with large number of changes could effectively fill up the database with audit log entries. For example, for our health check operations, we have a role where we add a member, get an access token, then delete and make sure after the changes are propagated, the server 403 for the access token call. the audit log for this role was over 200K entries. So if someone tries to read the role and requests all the audit log entries to be returned, we have to return 200K entries.

The server should automatically impose a limit on the number of entries in the audit log tables and automatically clean up the records when necessary. We'll introduce the following new system properties:

athenz.zms.jdbc.audit_log_role_max_limit=0
athenz.zms.jdbc.audit_log_role_keep_count=0

The athenz.zms.jdbc.audit_log_role_max_limit property specifies the maximum number of entries to be maintained in the role audit log table which keeps track of membership changes in the role. Once this limit hits, the server will automatically only keep the most recent changes based on the value configured for the athenz.zms.jdbc.audit_log_role_keep_count property. If either property is 0 or a negative number, the server will not remove any audit log entries.

athenz.zms.jdbc.audit_log_group_max_limit=0
athenz.zms.jdbc.audit_log_group_keep_count=0

The athenz.zms.jdbc.audit_log_group_max_limit property specifies the maximum number of entries to be maintained in the group audit log table which keeps track of membership changes in the group. Once this limit hits, the server will automatically only keep the most recent changes based on the value configured for the athenz.zms.jdbc.audit_log_group_keep_count property. If either property is 0 or a negative number, the server will not remove any audit log entries.

Since Athenz itself maintains a server level audit log which could be used for history, the data being expunged from the tables is ok since the audit log maintains the full history for governance requirements.

To maintain existing behavior the default values for the all settings is 0 thus no entries will be expunged.

Hints from issue comments:

Solution patch:
diff --git a/libs/java/server_common/conf/server_common.properties b/libs/java/server_common/conf/server_common.properties
index d0ca3b0c8..c0c3f755a 100644
--- a/libs/java/server_common/conf/server_common.properties
+++ b/libs/java/server_common/conf/server_common.properties
@@ -50,3 +50,23 @@ athenz.otel_separate_domain_counter_metrics=false
 # with the 3 attributes, httpMethod, apiName and httpStatusCode in the counter.
 # The default value is false.
 athenz.otel_skip_domain_counter_metrics=false
+
+# The athenz.zms.jdbc.audit_log_role_max_limit property specifies the
+# maximum number of entries to be maintained in the role audit log table
+# which keeps track of membership changes in the role. Once this limit hits,
+# the server will automatically only keep the most recent changes based
+# on the value configured for the athenz.zms.jdbc.audit_log_role_keep_count
+# property. If either property is 0 or a negative number, or the keep count
+# is larger than the max limit, the server will not remove any audit log entries.
+#athenz.zms.jdbc.audit_log_role_max_limit=0
+#athenz.zms.jdbc.audit_log_role_keep_count=0
+
+# The athenz.zms.jdbc.audit_log_group_max_limit property specifies the
+# maximum number of entries to be maintained in the group audit log table
+# which keeps track of membership changes in the group. Once this limit hits,
+# the server will automatically only keep the most recent changes based
+# on the value configured for the athenz.zms.jdbc.audit_log_group_keep_count
+# property. If either property is 0 or a negative number, or the keep count
+# is larger than the max limit, the server will not remove any audit log entries.
+#athenz.zms.jdbc.audit_log_group_max_limit=0
+#athenz.zms.jdbc.audit_log_group_keep_count=0
diff --git a/libs/java/server_common/pom.xml b/libs/java/server_common/pom.xml
index c3bb42f2a..8dbc6e6c1 100644
--- a/libs/java/server_common/pom.xml
+++ b/libs/java/server_common/pom.xml
@@ -27,7 +27,7 @@
   <packaging>jar</packaging>
 
   <properties>
-    <code.coverage.min>0.9425</code.coverage.min>
+    <code.coverage.min>0.9437</code.coverage.min>
   </properties>
 
   <dependencyManagement>
diff --git a/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConnection.java b/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConnection.java
index cc2487f1c..388a5cb0c 100644
--- a/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConnection.java
+++ b/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConnection.java
@@ -733,6 +733,14 @@ public class JDBCConnection implements ObjectStoreConnection {
             + " FROM service JOIN domain ON service.domain_id=domain.domain_id WHERE service.name";
     private static final String SQL_SEARCH_COUNT_SERVICE_IDENTITY_PREFIX = "SELECT COUNT(*)"
             + " FROM service JOIN domain ON service.domain_id=domain.domain_id WHERE service.name";
+    private static final String SQL_COUNT_ROLE_AUDIT_LOG = "SELECT COUNT(*) FROM role_audit_log WHERE role_id=?";
+    private static final String SQL_COUNT_GROUP_AUDIT_LOG = "SELECT COUNT(*) FROM principal_group_audit_log WHERE group_id=?";
+    private static final String SQL_CLEANUP_ROLE_AUDIT_LOG = "DELETE t1 FROM role_audit_log AS t1"
+            + " LEFT JOIN (SELECT audit_log_id FROM role_audit_log WHERE role_id=? ORDER BY created DESC LIMIT ?) AS t2"
+            + " ON t1.audit_log_id = t2.audit_log_id WHERE t1.role_id=? AND t2.audit_log_id IS NULL";
+    private static final String SQL_CLEANUP_GROUP_AUDIT_LOG = "DELETE t1 FROM principal_group_audit_log AS t1"
+            + " LEFT JOIN (SELECT audit_log_id FROM principal_group_audit_log WHERE group_id=? ORDER BY created DESC LIMIT ?) AS t2"
+            + " ON t1.audit_log_id = t2.audit_log_id WHERE t1.group_id=? AND t2.audit_log_id IS NULL";
 
     private static final String CACHE_DOMAIN    = "d:";
     private static final String CACHE_ROLE      = "r:";
@@ -760,6 +768,11 @@ public class JDBCConnection implements ObjectStoreConnection {
     Map<String, Integer> objectMap;
     boolean transactionCompleted;
     DomainOptions domainOptions;
+    private int auditLogRoleMaxLimit;
+    private int auditLogRoleKeepCount;
+    private int auditLogGroupMaxLimit;
+    private int auditLogGroupKeepCount;
+
     private Object synchronizer = new Object();
     private volatile static Map<String, List<String>> SERVER_TRUST_ROLES_MAP;
     private volatile static long SERVER_TRUST_ROLES_TIMESTAMP;
@@ -804,6 +817,14 @@ public class JDBCConnection implements ObjectStoreConnection {
         this.serviceTagsLimit = serviceTagsLimit;
     }
 
+    public void setAuditLogLimits(int auditLogRoleMaxLimit, int auditLogRoleKeepCount, int auditLogGroupMaxLimit,
+            int auditLogGroupKeepCount) {
+        this.auditLogRoleMaxLimit = auditLogRoleMaxLimit;
+        this.auditLogRoleKeepCount = auditLogRoleKeepCount;
+        this.auditLogGroupMaxLimit = auditLogGroupMaxLimit;
+        this.auditLogGroupKeepCount = auditLogGroupKeepCount;
+    }
+
     @Override
     public void close() {
 
@@ -2997,6 +3018,12 @@ public class JDBCConnection implements ObjectStoreConnection {
         int affectedRows;
         final String caller = "insertRoleAuditEntry";
 
+        // before inserting a new entry we need to see what is
+        // the current count of audit log entries and, if necessary,
+        // do the proper clean-up of old entries based on our config
+
+        enforceRoleAuditLogLimit(roleId, caller);
+
         try (PreparedStatement ps = con.prepareStatement(SQL_INSERT_ROLE_AUDIT_LOG)) {
             ps.setInt(1, roleId);
             ps.setString(2, processInsertValue(admin));
@@ -6715,6 +6742,12 @@ public class JDBCConnection implements ObjectStoreConnection {
         int affectedRows;
         final String caller = "insertGroupAuditEntry";
 
+        // before inserting a new entry we need to see what is
+        // the current count of audit log entries and, if necessary,
+        // do the proper clean-up of old entries based on our config
+
+        enforceGroupAuditLogLimit(groupId, caller);
+
         try (PreparedStatement ps = con.prepareStatement(SQL_INSERT_GROUP_AUDIT_LOG)) {
             ps.setInt(1, groupId);
             ps.setString(2, processInsertValue(admin));
@@ -8468,4 +8501,68 @@ public class JDBCConnection implements ObjectStoreConnection {
         }
         return new ServiceIdentities().setList(serviceIdentities).setServiceMatchCount(serviceMatchCount);
     }
+
+    int getAuditLogEntryCount(final String sqlCommand, final int objectId, final String caller) {
+
+        // we're going to fetch the current number of entries for the
+        // given object id. if we get any type of exception, we're just
+        // going to log and ignore.
+
+        int count = 0;
+        try (PreparedStatement ps = con.prepareStatement(sqlCommand)) {
+            ps.setInt(1, objectId);
+            try (ResultSet rs = executeQuery(ps, caller)) {
+                if (rs.next()) {
+                    count = rs.getInt(1);
+                }
+            }
+        } catch (SQLException ex) {
+            LOG.error("{}: unable to determine total count of audit log entries for id {}: {}",
+                    caller, objectId, ex.getMessage());
+        }
+        return count;
+    }
+
+    void cleanUpAuditLog(final String sqlCommand, int objectId, int keepCount, final String caller) {
+        try (PreparedStatement ps = con.prepareStatement(sqlCommand)) {
+            ps.setInt(1, objectId);
+            ps.setInt(2, keepCount);
+            ps.setInt(3, objectId);
+            executeUpdate(ps, caller);
+        } catch (SQLException ex) {
+            LOG.error("{}: unable to clean up audit log entries for id {}: {}",
+                    caller, objectId, ex.getMessage());
+        }
+    }
+
+    void enforceRoleAuditLogLimit(int roleId, final String caller) {
+        enforceAuditLogLimit(roleId, caller, auditLogRoleMaxLimit, auditLogRoleKeepCount,
+                SQL_COUNT_ROLE_AUDIT_LOG, SQL_CLEANUP_ROLE_AUDIT_LOG);
+    }
+
+    void enforceGroupAuditLogLimit(int groupId, final String caller) {
+        enforceAuditLogLimit(groupId, caller, auditLogGroupMaxLimit, auditLogGroupKeepCount,
+                SQL_COUNT_GROUP_AUDIT_LOG, SQL_CLEANUP_GROUP_AUDIT_LOG);
+    }
+
+    private void enforceAuditLogLimit(int objectId, final String caller, int maxLimit, int keepCount,
+            final String countSql, final String cleanupSql) {
+
+        // first verify that the limits are configured and valid
+
+        if (keepCount <= 0 || keepCount >= maxLimit) {
+            return;
+        }
+
+        // first get the number of current entries for the given object id
+
+        int count = getAuditLogEntryCount(countSql, objectId, caller);
+
+        // if the current count is bigger than our limit
+        // then we'll need to delete some entries as configured
+
+        if (count >= maxLimit) {
+            cleanUpAuditLog(cleanupSql, objectId, keepCount, caller);
+        }
+    }
 }
diff --git a/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConsts.java b/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConsts.java
index 883383b8c..db0127f09 100644
--- a/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConsts.java
+++ b/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCConsts.java
@@ -161,6 +161,10 @@ public final class JDBCConsts {
 
     public static final String ZMS_PROP_JDBC_NOTIFY_DETAILS_SELF_SERVE_ROLE  = "athenz.zms.jdbc.notify_details_self_serve_role";
     public static final String ZMS_PROP_JDBC_NOTIFY_DETAILS_SELF_SERVE_GROUP = "athenz.zms.jdbc.notify_details_self_serve_group";
+    public static final String ZMS_PROP_MYSQL_AUDIT_LOG_ROLE_MAX_LIMIT       = "athenz.zms.jdbc.audit_log_role_max_limit";
+    public static final String ZMS_PROP_MYSQL_AUDIT_LOG_ROLE_KEEP_COUNT      = "athenz.zms.jdbc.audit_log_role_keep_count";
+    public static final String ZMS_PROP_MYSQL_AUDIT_LOG_GROUP_MAX_LIMIT      = "athenz.zms.jdbc.audit_log_group_max_limit";
+    public static final String ZMS_PROP_MYSQL_AUDIT_LOG_GROUP_KEEP_COUNT     = "athenz.zms.jdbc.audit_log_group_keep_count";
 
     public static final String NOTIFY_DETAILS_SELF_SERVE_ROLE = "self-serve role";
     public static final String NOTIFY_DETAILS_SELF_SERVE_GROUP = "self-serve group";
diff --git a/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStore.java b/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStore.java
index c972e843a..4a08d3875 100644
--- a/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStore.java
+++ b/libs/java/server_common/src/main/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStore.java
@@ -32,6 +32,10 @@ public class JDBCObjectStore implements ObjectStore {
     private int groupTagsLimit;
     private int serviceTagsLimit;
     private int policyTagsLimit;
+    private int auditLogRoleMaxLimit;
+    private int auditLogRoleKeepCount;
+    private int auditLogGroupMaxLimit;
+    private int auditLogGroupKeepCount;
     private DomainOptions domainOptions;
     private final Object synchronizer = new Object();
 
@@ -45,6 +49,13 @@ public class JDBCObjectStore implements ObjectStore {
         if (this.roSrc == null) {
             this.roSrc = this.rwSrc;
         }
+
+        // extract the audit log limits from system properties
+
+        auditLogRoleMaxLimit = Integer.parseInt(System.getProperty(JDBCConsts.ZMS_PROP_MYSQL_AUDIT_LOG_ROLE_MAX_LIMIT, "0"));
+        auditLogRoleKeepCount = Integer.parseInt(System.getProperty(JDBCConsts.ZMS_PROP_MYSQL_AUDIT_LOG_ROLE_KEEP_COUNT, "0"));
+        auditLogGroupMaxLimit = Integer.parseInt(System.getProperty(JDBCConsts.ZMS_PROP_MYSQL_AUDIT_LOG_GROUP_MAX_LIMIT, "0"));
+        auditLogGroupKeepCount = Integer.parseInt(System.getProperty(JDBCConsts.ZMS_PROP_MYSQL_AUDIT_LOG_GROUP_KEEP_COUNT, "0"));
     }
     
     @Override
@@ -56,6 +67,8 @@ public class JDBCObjectStore implements ObjectStore {
             jdbcConn.setObjectSynchronizer(synchronizer);
             jdbcConn.setOperationTimeout(opTimeout);
             jdbcConn.setTagLimit(domainTagsLimit, roleTagsLimit, groupTagsLimit, policyTagsLimit, serviceTagsLimit);
+            jdbcConn.setAuditLogLimits(auditLogRoleMaxLimit, auditLogRoleKeepCount, auditLogGroupMaxLimit,
+                    auditLogGroupKeepCount);
             jdbcConn.setDomainOptions(domainOptions);
             return jdbcConn;
         } catch (Exception ex) {



Test patch:
diff --git a/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCConnectionTest.java b/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCConnectionTest.java
index 038eafddf..b3175a9fd 100644
--- a/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCConnectionTest.java
+++ b/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCConnectionTest.java
@@ -17665,4 +17665,307 @@ public class JDBCConnectionTest {
         }
         jdbcConn.close();
     }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitMaxLimitZero() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(0, 10, 100, 10);
+
+        // When max limit is 0, should return early without executing any queries
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitMaxLimitNegative() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(-1, 10, 100, 10);
+
+        // When max limit is negative, should return early without executing any queries
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitKeepCountZero() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 0, 100, 10);
+
+        // When keep count is 0, should return early without executing any queries
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitKeepCountNegative() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, -1, 100, 10);
+
+        // When keep count is negative, should return early without executing any queries
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitCountBelowMax() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock the count query to return 50 (below max of 100)
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(50);
+
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify count query was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // Verify cleanup was not executed (only 1 call for count, not 2 for count+cleanup)
+        Mockito.verify(mockPrepStmt, times(0)).executeUpdate();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitCountAtMax() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock the count query to return exactly 100 (at max limit)
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(100);
+        Mockito.doReturn(5).when(mockPrepStmt).executeUpdate();
+
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify count query was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // Verify cleanup was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeUpdate();
+        // Verify cleanup parameters (objectId at index 1 set twice: once in count, once in cleanup)
+        Mockito.verify(mockPrepStmt, times(2)).setInt(1, 5);  // objectId at index 1
+        Mockito.verify(mockPrepStmt, times(1)).setInt(2, 10); // keepCount at index 2
+        Mockito.verify(mockPrepStmt, times(1)).setInt(3, 5);  // objectId at index 3
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitCountAboveMax() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock the count query to return 150 (above max of 100)
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(150);
+        Mockito.doReturn(50).when(mockPrepStmt).executeUpdate();
+
+        jdbcConn.enforceRoleAuditLogLimit(7, "testCaller");
+
+        // Verify count query was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // Verify cleanup was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeUpdate();
+        // Verify cleanup parameters (objectId at index 1 set twice: once in count, once in cleanup)
+        Mockito.verify(mockPrepStmt, times(2)).setInt(1, 7);  // objectId at index 1
+        Mockito.verify(mockPrepStmt, times(1)).setInt(2, 10); // keepCount at index 2
+        Mockito.verify(mockPrepStmt, times(1)).setInt(3, 7);  // objectId at index 3
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceRoleAuditLogLimitSQLException() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock SQLException in getAuditLogEntryCount
+        Mockito.when(mockPrepStmt.executeQuery()).thenThrow(new SQLException("Database error"));
+
+        // Should not throw exception, but log error and return (count will be 0)
+        jdbcConn.enforceRoleAuditLogLimit(5, "testCaller");
+
+        // Verify count query was attempted
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // No cleanup should happen since count returns 0 on error
+        Mockito.verify(mockPrepStmt, times(0)).executeUpdate();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitMaxLimitZero() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 0, 10);
+
+        // When max limit is 0, should return early without executing any queries
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitKeepBiggerThanLimit() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 10, 11);
+
+        // group keep value is bigger than the limit so
+        // should return early without executing any queries
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitMaxLimitNegative() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, -1, 10);
+
+        // When max limit is negative, should return early without executing any queries
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitKeepCountZero() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 0);
+
+        // When keep count is 0, should return early without executing any queries
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitKeepCountNegative() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, -1);
+
+        // When keep count is negative, should return early without executing any queries
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify no queries were executed
+        Mockito.verify(mockPrepStmt, times(0)).executeQuery();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitCountBelowMax() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock the count query to return 50 (below max of 100)
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(50);
+
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify count query was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // Verify cleanup was not executed (only 1 call for count, not 2 for count+cleanup)
+        Mockito.verify(mockPrepStmt, times(0)).executeUpdate();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitCountAtMax() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock the count query to return exactly 100 (at max limit)
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(100);
+        Mockito.doReturn(5).when(mockPrepStmt).executeUpdate();
+
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify count query was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // Verify cleanup was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeUpdate();
+        // Verify cleanup parameters (objectId at index 1 set twice: once in count, once in cleanup)
+        Mockito.verify(mockPrepStmt, times(2)).setInt(1, 5);  // objectId at index 1
+        Mockito.verify(mockPrepStmt, times(1)).setInt(2, 10); // keepCount at index 2
+        Mockito.verify(mockPrepStmt, times(1)).setInt(3, 5);  // objectId at index 3
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitCountAboveMax() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock the count query to return 150 (above max of 100)
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(150);
+        Mockito.doReturn(50).when(mockPrepStmt).executeUpdate();
+
+        jdbcConn.enforceGroupAuditLogLimit(7, "testCaller");
+
+        // Verify count query was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // Verify cleanup was executed
+        Mockito.verify(mockPrepStmt, times(1)).executeUpdate();
+        // Verify cleanup parameters (objectId at index 1 set twice: once in count, once in cleanup)
+        Mockito.verify(mockPrepStmt, times(2)).setInt(1, 7);  // objectId at index 1
+        Mockito.verify(mockPrepStmt, times(1)).setInt(2, 10); // keepCount at index 2
+        Mockito.verify(mockPrepStmt, times(1)).setInt(3, 7);  // objectId at index 3
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitSQLException() throws Exception {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        // Mock SQLException in getAuditLogEntryCount
+        Mockito.when(mockPrepStmt.executeQuery()).thenThrow(new SQLException("Database error"));
+
+        // Should not throw exception, but log error and return (count will be 0)
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify count query was attempted
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // No cleanup should happen since count returns 0 on error
+        Mockito.verify(mockPrepStmt, times(0)).executeUpdate();
+        jdbcConn.close();
+    }
+
+    @Test
+    public void testEnforceGroupAuditLogLimitCleanUpException() throws SQLException {
+        JDBCConnection jdbcConn = new JDBCConnection(mockConn, true);
+        jdbcConn.setAuditLogLimits(100, 10, 100, 10);
+
+        Mockito.when(mockResultSet.next()).thenReturn(true).thenReturn(false);
+        Mockito.when(mockResultSet.getInt(1)).thenReturn(150);
+
+        // the cleanup will throw an exception
+        Mockito.when(mockPrepStmt.executeUpdate()).thenThrow(new SQLException("Database error"));
+
+        // Should not throw exception, but log error and return (count will be 0)
+        jdbcConn.enforceGroupAuditLogLimit(5, "testCaller");
+
+        // Verify count query was attempted
+        Mockito.verify(mockPrepStmt, times(1)).executeQuery();
+        // No cleanup should happen since count returns 0 on error
+        Mockito.verify(mockPrepStmt, times(1)).executeUpdate();
+        jdbcConn.close();
+    }
 }
diff --git a/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStoreTest.java b/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStoreTest.java
index c09baa342..09b34a4bd 100644
--- a/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStoreTest.java
+++ b/libs/java/server_common/src/test/java/com/yahoo/athenz/common/server/store/impl/JDBCObjectStoreTest.java
@@ -19,6 +19,7 @@ import java.sql.Connection;
 import java.sql.SQLException;
 
 import com.yahoo.athenz.common.server.ServerResourceException;
+import com.yahoo.athenz.zms.DomainOptions;
 import org.mockito.Mockito;
 import org.testng.annotations.Test;
 
@@ -40,6 +41,8 @@ public class JDBCObjectStoreTest {
         Mockito.doReturn(roMockConn).when(mockDataRoSrc).getConnection();
 
         JDBCObjectStore store = new JDBCObjectStore(mockDataRwSrc, mockDataRoSrc);
+        store.setDomainOptions(new DomainOptions());
+        store.setTagLimit(100, 25, 25, 25, 25);
         
         JDBCConnection jdbcConn = (JDBCConnection) store.getConnection(true, true);
         assertEquals(jdbcConn.con, rwMockConn);
diff --git a/servers/zms/src/test/java/com/yahoo/athenz/zms/ZMSObjectAuditLogTest.java b/servers/zms/src/test/java/com/yahoo/athenz/zms/ZMSObjectAuditLogTest.java
new file mode 100644
index 000000000..56693d9cc
--- /dev/null
+++ b/servers/zms/src/test/java/com/yahoo/athenz/zms/ZMSObjectAuditLogTest.java
@@ -0,0 +1,193 @@
+/*
+ * Copyright The Athenz Authors
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.yahoo.athenz.zms;
+
+import com.yahoo.athenz.common.server.util.config.dynamic.DynamicConfigBoolean;
+import org.mockito.Mockito;
+import org.testng.annotations.*;
+
+import java.util.List;
+
+import static org.mockito.Mockito.when;
+import static org.testng.Assert.assertEquals;
+import static org.testng.Assert.assertTrue;
+
+public class ZMSObjectAuditLogTest {
+
+    private final ZMSTestInitializer zmsTestInitializer = new ZMSTestInitializer();
+
+    @BeforeClass
+    public void startMemoryMySQL() {
+        zmsTestInitializer.startMemoryMySQL();
+    }
+
+    @AfterClass
+    public void stopMemoryMySQL() {
+        zmsTestInitializer.stopMemoryMySQL();
+    }
+
+    @BeforeMethod
+    public void setUp() throws Exception {
+        zmsTestInitializer.setUp();
+
+        DynamicConfigBoolean dynamicConfigBoolean = Mockito.mock(DynamicConfigBoolean.class);
+        when(dynamicConfigBoolean.get()).thenReturn(true);
+        zmsTestInitializer.getZms().validateUserRoleMembers = dynamicConfigBoolean;
+    }
+
+    @AfterMethod
+    public void clearConnections() {
+        zmsTestInitializer.clearConnections();
+    }
+
+    @Test
+    public void testZMSAuditLogNoLimit() {
+
+        // by default, we have no limit
+
+        ZMSImpl zmsImpl = zmsTestInitializer.zmsInit();
+        RsrcCtxWrapper ctx = zmsTestInitializer.getMockDomRsrcCtx();
+        final String auditRef = zmsTestInitializer.getAuditRef();
+
+        final String domainName = "audit-log-no-limit";
+        final String roleName = "role1";
+        final String groupName = "group1";
+
+        TopLevelDomain dom1 = zmsTestInitializer.createTopLevelDomainObject(domainName, "Test Domain1",
+                "testOrg", "user.user1");
+        zmsImpl.postTopLevelDomain(ctx, auditRef, null, dom1);
+
+        Role role = zmsTestInitializer.createRoleObject(domainName, roleName, null,
+                "user.john", "user.jane");
+        zmsImpl.putRole(ctx, domainName, roleName, auditRef, false, null, role);
+
+        Group group = zmsTestInitializer.createGroupObject(domainName, groupName,
+                "user.john", "user.jane");
+        zmsImpl.putGroup(ctx, domainName, groupName, auditRef, false, null, group);
+
+        for (int i = 0; i < 25; i++) {
+            final String memberName = "user.member" + i;
+            Membership membership = new Membership().setMemberName(memberName);
+            zmsImpl.putMembership(ctx, domainName, roleName, memberName, auditRef, false, null, membership);
+            GroupMembership groupMembership = new GroupMembership().setMemberName(memberName);
+            zmsImpl.putGroupMembership(ctx, domainName, groupName, memberName, auditRef, false, null, groupMembership);
+        }
+
+        Role resRole = zmsImpl.getRole(ctx, domainName, roleName, true, false, true);
+        assertEquals(resRole.getRoleMembers().size(), 27);
+        assertEquals(resRole.getAuditLog().size(), 27);
+
+        Group resGroup = zmsImpl.getGroup(ctx, domainName, groupName, true, false);
+        assertEquals(resGroup.getGroupMembers().size(), 27);
+        assertEquals(resGroup.getAuditLog().size(), 27);
+
+        zmsImpl.deleteTopLevelDomain(ctx, domainName, auditRef, null);
+    }
+
+    @Test
+    public void testZMSRoleAuditLogLimit() throws InterruptedException {
+
+        // set the role limit with max of 11 and then keep of 2
+        // set the role limit with max of 10 and then keep of 3
+
+        System.setProperty("athenz.zms.jdbc.audit_log_role_max_limit", "11");
+        System.setProperty("athenz.zms.jdbc.audit_log_role_keep_count", "2");
+        System.setProperty("athenz.zms.jdbc.audit_log_group_max_limit", "10");
+        System.setProperty("athenz.zms.jdbc.audit_log_group_keep_count", "3");
+
+        ZMSImpl zmsImpl = zmsTestInitializer.zmsInit();
+        RsrcCtxWrapper ctx = zmsTestInitializer.getMockDomRsrcCtx();
+        final String auditRef = zmsTestInitializer.getAuditRef();
+
+        final String domainName = "audit-log-with-limit";
+        final String roleName = "role1";
+        final String groupName = "group1";
+
+        TopLevelDomain dom1 = zmsTestInitializer.createTopLevelDomainObject(domainName, "Test Domain1",
+                "testOrg", "user.user1");
+        zmsImpl.postTopLevelDomain(ctx, auditRef, null, dom1);
+
+        Role role = zmsTestInitializer.createRoleObject(domainName, roleName, null,
+                "user.john", "user.jane");
+        zmsImpl.putRole(ctx, domainName, roleName, auditRef, false, null, role);
+
+        Group group = zmsTestInitializer.createGroupObject(domainName, groupName,
+                "user.john", "user.jane");
+        zmsImpl.putGroup(ctx, domainName, groupName, auditRef, false, null, group);
+
+        // role-setup: when we get to i 9, then we'll hit our limit of 11
+        // thus we're going to clean up and only keep last 2,
+        // and then we'll be adding another one so we should
+        // end up with 3 entries only
+        // group-setup: when get to i 8, then 'll hit our limit of 10
+        // thus we're going to clean up and only keep the last 3,
+        // and then we'll be adding two more so we should end
+        // up with 5 entries
+
+        for (int i = 0; i < 10; i++) {
+            final String memberName = "user.member" + i;
+            Thread.sleep(1000);
+            Membership membership = new Membership().setMemberName(memberName);
+            zmsImpl.putMembership(ctx, domainName, roleName, memberName, auditRef, false, null, membership);
+            GroupMembership groupMembership = new GroupMembership().setMemberName(memberName);
+            zmsImpl.putGroupMembership(ctx, domainName, groupName, memberName, auditRef, false, null, groupMembership);
+        }
+
+        Role resRole = zmsImpl.getRole(ctx, domainName, roleName, true, false, true);
+        assertEquals(resRole.getRoleMembers().size(), 12);
+
+        assertEquals(resRole.getAuditLog().size(), 3);
+        assertTrue(roleAuditLogEntryExists(resRole.getAuditLog(), "user.member9"));
+        assertTrue(roleAuditLogEntryExists(resRole.getAuditLog(), "user.member8"));
+        assertTrue(roleAuditLogEntryExists(resRole.getAuditLog(), "user.member7"));
+
+        Group resGroup = zmsImpl.getGroup(ctx, domainName, groupName, true, false);
+        assertEquals(resGroup.getGroupMembers().size(), 12);
+
+        assertEquals(resGroup.getAuditLog().size(), 5);
+        assertTrue(groupAuditLogEntryExists(resGroup.getAuditLog(), "user.member9"));
+        assertTrue(groupAuditLogEntryExists(resGroup.getAuditLog(), "user.member8"));
+        assertTrue(groupAuditLogEntryExists(resGroup.getAuditLog(), "user.member7"));
+        assertTrue(groupAuditLogEntryExists(resGroup.getAuditLog(), "user.member6"));
+        assertTrue(groupAuditLogEntryExists(resGroup.getAuditLog(), "user.member5"));
+
+        zmsImpl.deleteTopLevelDomain(ctx, domainName, auditRef, null);
+
+        System.clearProperty("athenz.zms.jdbc.audit_log_role_max_limit");
+        System.clearProperty("athenz.zms.jdbc.audit_log_role_keep_count");
+        System.clearProperty("athenz.zms.jdbc.audit_log_group_max_limit");
+        System.clearProperty("athenz.zms.jdbc.audit_log_group_keep_count");
+    }
+
+    boolean roleAuditLogEntryExists(List<RoleAuditLog> auditLogs, final String member) {
+        for (RoleAuditLog auditLog : auditLogs) {
+            if (auditLog.getMember().equals(member)) {
+                return true;
+            }
+        }
+        return false;
+    }
+
+    boolean groupAuditLogEntryExists(List<GroupAuditLog> auditLogs, final String member) {
+        for (GroupAuditLog auditLog : auditLogs) {
+            if (auditLog.getMember().equals(member)) {
+                return true;
+            }
+        }
+        return false;
+    }
+}