github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/delegate/show_roles.go (about)

     1  // Copyright 2019 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package delegate
    12  
    13  import (
    14  	"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
    15  	"github.com/cockroachdb/cockroach/pkg/sql/sqltelemetry"
    16  )
    17  
    18  // delegateShowRoles implements SHOW ROLES which returns all the roles.
    19  // Privileges: SELECT on system.users.
    20  func (d *delegator) delegateShowRoles() (tree.Statement, error) {
    21  	sqltelemetry.IncrementShowCounter(sqltelemetry.Roles)
    22  	return parse(`
    23  SELECT
    24  	u.username,
    25  	IFNULL(string_agg(o.option || COALESCE('=' || o.value, ''), ', ' ORDER BY o.option), '') AS options,
    26  	ARRAY (SELECT role FROM system.role_members AS rm WHERE rm.member = u.username ORDER BY 1) AS member_of
    27  FROM
    28  	system.users AS u LEFT JOIN system.role_options AS o ON u.username = o.username
    29  GROUP BY
    30  	u.username
    31  ORDER BY 1;
    32  `)
    33  }