Why don't we just update the is_superuser column of a role when it
effectively achieves a superuser status when it is granted some superuser
role? Similarly, we would remove its superuser status when there are no
superuser roles granted to it anymore.
I think that at least for the second case (when a superuser role is revoked
and there is none anymore), there would need to be some transaction because
as it checks if there are any superuser roles or not to set it to false,
somebody else might grant that superuser role to it again so we might end
up with having is_superuser set to false while it might still have a
superuser role granted.
I am not sure if this is achievable and I am sorry if this was already
answered / rejected elsewhere.
On Thu, Feb 29, 2024 at 11:33 AM <shailajako...@icloud.com> wrote:
> 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> 于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
>>
>>
>>
>>
>
