Hi ,
1. can this cql "SELECT role from system_auth.roles where is_superuser =
True ALLOW FILTERING ;" meet your needs if the user to execute the cql
have the right to do so ？
2. I think may be we can also add the ability to filter on list role/user
grammar, for example : list user where super = True;
Shailaja Koppu <s_ko...@apple.com> 于2024年2月28日周三 20:40写道：
> Hi Team,
>
> Currently LIST ROLES command doesn’t indicate if a role has superuser
> privilege, if acquired through a grant in roles hierarchy (LIST ROLES has
> super column true only if the role is created with SUPERUSER=true). For
> example, in the below example, super1 is a superuser, role1 acquired
> superuser status through grant of super1 and role11 acquired superuser
> status through grant of role1. LIST ROLES output has super column true only
> for super1.
>
>
> cassandra@cqlsh> create role super1 WITH SUPERUSER = true;
> cassandra@cqlsh> create role role1;
> cassandra@cqlsh> create role role11;
> cassandra@cqlsh> create role role2;
> cassandra@cqlsh> grant super1 to role1;
> cassandra@cqlsh> grant role1 to role11;
> cassandra@cqlsh> list roles;
>
> role | super | login | options | datacenters
> -----------+-------+-------+---------+-------------
> role1 | False | False | {} | ALL
> role11 | False | False | {} | ALL
> role2 | False | False | {} | ALL
> super1 | True | False | {} | ALL
>
>
> One way to check has a role acquired superuser status is by running LIST
> ROLES of <rolename> and looking for at least one row with super column
> true. This works fine to check superuser status of a given role.
>
> cassandra@cqlsh> list roles of role11;
>
> role | super | login | options | datacenters
> --------+-------+-------+---------+-------------
> role1 | False | False | {} | ALL
> role11 | False | False | {} | ALL
> super1 | True | False | {} | ALL
>
>
> But if we need to get list of all roles having superuser status (acquired
> through grant as well), there is no easy way to retrieve this from C*. This
> can be a requirement for an external service interacting with C* and
> performing their own checks (for example, Sidecar). So I am proposing a new
> CQL command LIST SUPERUSERS, which lists all roles having superuser status
> (acquired as well). We will ensure that the user running this command has
> DESCRIBE permission on root roles resource, i.e, to run this command user
> must be either a superuser or granted DESCRIBE permission on ALL ROLES.
> Here is the Jira <https://issues.apache.org/jira/browse/CASSANDRA-19417> and
> sample output for the above example.
>
> cassandra@cqlsh> list superusers;
>
> role
> -----------
> role1
> role11
> super1
>
> Alternatives thought of so far,
> - LIST ROLES SUPERUSERSONLY
> - LIST ROLES superuseronly=true
> - LIST USERS superuseronly=true command : I have a question here, is
> LIST USERS command deprecated? I see this link saying that
> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/cqlListUsers.html.
> if LIST USERS and LISR ROLES commands are same, why don’t we just pick one
> so we don’t have to maintain two different commands ?
> - LIST ROLES command default i.e, without NORECURSIVE clause : to print
> super column true for acquired superusers as well, but this may break
> existing tools/scripts of customers as we are changing the default
> behavior
>
>
> I prefer LIST SUPERUSERS command because - This command looks neat and
> simple and we don’t have to worry about handling/breaking other
> options/columns supported by these existing commands. For example we don’t
> have to worry about handling/breaking OF clause of LIST ROLES command. And
> any new options we add to these commands in the future, don’t have to worry
> about handling/breaking of SUPERUSERS option. Please let me know your
> thoughts on this.
>
>
> Thanks,
> Shailaja
>
>
>
>
