@Rocky how can I get login user count in 3.5.16 and in 4.0? I need count SHOW USERS; gives the list

To get the count of users in StarRocks (versions 3.5.16 and 4.0), you cannot directly count the output of the

SHOW USERS

command using SQL. Instead, you should query the system metadata views introduced in version 3.x. Depending on whether you want the total number of defined user accounts or the number of currently logged-in users, use the following queries: 1. Count of Total Defined Users (Accounts) The system view

sys.grants_to_users

contains all user identities that have privileges (in StarRocks, every user identity is tracked here). This is the recommended way to query the "full user list" programmatically.

sql
SELECT count(DISTINCT GRANTEE) AS user_count 
FROM sys.grants_to_users;

_Note: You must have the

user_admin

role to query this view._ 2. Count of Currently Logged-in (Connected) Users If you need the count of users who are actively connected to the cluster right now: * Total active connections:

sql
    SELECT count(*) FROM information_schema.processlist;

* Unique users currently connected:

sql
    SELECT count(DISTINCT USER) FROM information_schema.processlist;

Summary for 3.5.16 and 4.0 * StarRocks 3.5.16: Supports both

sys.grants_to_users

and

information_schema.processlist

. * StarRocks 4.0: Continues to support these views. There is no significant change in the SQL syntax for retrieving these counts between these versions. Why not

SHOW USERS

? While

SHOW USERS

provides a human-readable list, StarRocks (like MySQL) does not support wrapping

SHOW

commands in a subquery for counting. Using the

sys

or

information_schema

databases is the standard way to perform aggregations on metadata. References • sql-reference/sys/sys.md • administration/user_privs/authorization/privilege_faq.md • administration/user_privs/authentication/native_authentication.md • sql-reference/information_schema/information_schema.md • sql-reference/sql-statements/account-management/SHOW_USERS.md • sql-reference/information_schema/tables.md • administration/user_privs/authorization/User_privilege.md • sql-reference/information_schema/user_privileges.md

@Rocky

sys.grants_to_users

this is empty and I dont need currently connetected users I need total

To get the total count of user identities in StarRocks 3.5.x and 4.0 using SQL (since you cannot perform a

COUNT

directly on the output of

SHOW USERS

), you should query the system metadata views in the

sys

database. Because StarRocks 3.x and 4.x primarily use Role-Based Access Control (RBAC), users are typically assigned roles rather than having privileges granted to them directly. This is why your

sys.grants_to_users

view is empty—it only records direct, object-level privilege assignments to specific users. 1. Count Users via Role Assignments (Recommended for RBAC) In an RBAC system, every active user is assigned at least one role (including the built-in

root

or

admin

roles). You can count all unique users who have been assigned any role by querying

sys.role_edges

.

sql
SELECT count(DISTINCT GRANTEE) AS total_user_count
FROM sys.role_edges
WHERE GRANTEE_TYPE = 'USER';

* Requirement: You must be logged in as a user with the

user_admin

or

root

role to see all records in this view. * Coverage: This will include

root

,

admin

, and any other user identities that have been granted a role. 2. Count Users with Direct Privileges If you occasionally grant privileges directly to users (without roles), those users appear in

sys.grants_to_users

. If you want to be absolutely sure you aren't missing anyone, you can

UNION

the two:

sql
SELECT count(DISTINCT user_identity) AS total_user_count
FROM (
    SELECT GRANTEE AS user_identity FROM sys.role_edges WHERE GRANTEE_TYPE = 'USER'
    UNION
    SELECT GRANTEE AS user_identity FROM sys.grants_to_users
) t;

3. Why

sys.grants_to_users

was empty for you * Pure RBAC: If you follow best practices and only assign roles (e.g.,

GRANT 'db_admin' TO 'my_user'@'%'

), the user relationship is stored in

role_edges

, not

grants_to_users

. * Permissions: If you are not querying as

root

or a user with

user_admin

privileges, the system views will return an empty result or only show your own identity for security reasons. Summary of Differences (3.5.16 vs 4.0) The metadata structure for users and roles remains consistent between 3.5.x and 4.0. Both versions rely on the

sys

and

information_schema

databases for programmatic access to metadata. Pro Tip: If you just need a quick look and don't mind the manual count, you can also use

SHOW ALL AUTHENTICATION;

, which lists every user identity and their authentication method. However, like

SHOW USERS

, this cannot be used in a

SELECT COUNT(...)

subquery. References • sql-reference/sys/sys.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/sql-statements/account-management/SHOW_USERS.md • sql-reference/sql-statements/account-management/SHOW_AUTHENTICATION.md • sql-reference/sql-statements/account-management/SHOW_ROLES.md References • administration/user_privs/authentication/native_authentication.md • sql-reference/sql-statements/account-management/SHOW_USERS.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/information_schema/user_privileges.md