Hi Maxwell,
Currently system_auth.roles table doesn’t have acquired superuser info
available in columns to filter on it. Below is the system_auth.roles table for
the example I have listed in the previous email. If you notice, though role1
and role11 acquired superuser status through grants, is_superuser column is
False for these roles and acquired superuser status is not apparent directly
from the columns of this table. member_of column shows immediate parent/grant
of a given role. But these grants can result in a huge tree of roles hierarchy
and there may be a role anywhere up in the hierarchy which is a superuser.
cassandra@cqlsh> select * from system_auth.roles;
role | can_login | is_superuser | member_of | salted_hash
-----------+-----------+--------------+------------+--------------------------------------------------------------
role2 | False | False | null |
null
role11 | False | False | {'role1'} |
null
super1 | False | True | null |
null
role1 | False | False | {'super1'} |
null
Thanks,
Shailaja
> On Feb 29, 2024, at 2:11 AM, guo Maxwell <cclive1...@gmail.com> wrote:
>
> 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 <mailto: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
>>
>>
>>
