oracle/controllers/user_repository.go (386 lines of code) (raw):
// Copyright 2021 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
package controllers
import (
"context"
"errors"
"fmt"
"strings"
"bitbucket.org/creachadair/stringset"
"k8s.io/klog/v2"
"github.com/GoogleCloudPlatform/elcarro-oracle-operator/oracle/pkg/agents/common/sql"
dbdpb "github.com/GoogleCloudPlatform/elcarro-oracle-operator/oracle/pkg/agents/oracle"
)
// users describe the managed Oracle PDB users.
type users struct {
databaseName string
databaseRoles map[string]bool
nameToUser map[string]*user
// envUserNames keeps track of the managed users.
// The value will be initialized/refreshed with method users.readEnv
envUserNames []string
}
// diff returns users, which should be created/updated/deleted by comparing k8s spec with real environment.
func (us *users) diff(ctx context.Context, client dbdpb.DatabaseDaemonClient) (toCreateUsers, toUpdateUsers, toDeleteUsers, toUpdatePwdUsers []*user, err error) {
if err := us.readEnv(ctx, client); err != nil {
return nil, nil, nil, nil, fmt.Errorf("failed to read the env users: %v", err)
}
var specUserNames []string
for k := range us.nameToUser {
specUserNames = append(specUserNames, k)
}
toCreate, toCheck, toDelete := compare(specUserNames, us.envUserNames)
toCreateUsers, err = us.getUsers(toCreate)
if err != nil {
return nil, nil, nil, nil, err
}
toCheckUsers, err := us.getUsers(toCheck)
if err != nil {
return nil, nil, nil, nil, err
}
for _, d := range toDelete {
du := newNoSpecUser(us.databaseName, d)
if err := du.readEnv(ctx, client); err != nil {
return nil, nil, nil, nil, fmt.Errorf("failed to read the env user %v: %v", du, err)
}
toDeleteUsers = append(toDeleteUsers, du)
}
for _, u := range toCheckUsers {
toGrant, toRevoke, toUpdatePwd, err := u.diff(ctx, client)
if err != nil {
return nil, nil, nil, nil, fmt.Errorf("failed to read the env user %v: %v", u, err)
}
if len(toGrant) != 0 || len(toRevoke) != 0 {
toUpdateUsers = append(toUpdateUsers, u)
}
if toUpdatePwd {
toUpdatePwdUsers = append(toUpdatePwdUsers, u)
}
}
return toCreateUsers, toUpdateUsers, toDeleteUsers, toUpdatePwdUsers, nil
}
func (us *users) readEnv(ctx context.Context, client dbdpb.DatabaseDaemonClient) error {
envUserNames, err := queryDB(
ctx,
client,
us.databaseName,
"select username from dba_users where ORACLE_MAINTAINED='N' and INHERITED='NO'",
"USERNAME",
func(userName string) bool {
return userName != pdbAdmin
},
)
if err != nil {
return fmt.Errorf("failed to load users from DB %v", err)
}
us.envUserNames = envUserNames
roles, err := queryDB(
ctx,
client,
us.databaseName,
"select role from dba_roles",
"ROLE",
func(roleName string) bool {
return true
},
)
if err != nil {
return fmt.Errorf("failed to load roles from DB %v", err)
}
us.databaseRoles = make(map[string]bool)
for _, role := range roles {
us.databaseRoles[role] = true
}
return nil
}
func (us *users) getUsers(names []string) ([]*user, error) {
var res []*user
for _, name := range names {
u, ok := us.nameToUser[name]
if !ok {
return nil, fmt.Errorf("failed to find %s in %v", name, us.nameToUser)
}
res = append(res, u)
}
return res, nil
}
func newUsers(databaseName string, userSpecs []*User) *users {
nameToUser := make(map[string]*user)
for _, us := range userSpecs {
nameToUser[strings.ToUpper(us.Name)] = newUser(databaseName, us)
}
return &users{
databaseName: strings.ToUpper(databaseName),
nameToUser: nameToUser,
}
}
// user describe a managed Oracle PDB user.
type user struct {
databaseName string
userName string
specPrivs []string
// envDbaSysPrivs keeps track of the privileges granted to the user (dba_sys_privs table).
// The value will be initialized/refreshed with method user.readEnv
envDbaSysPrivs []string
// envDbaRolePrivs keeps track of the roles granted to the user (dba_role_privs table).
// The value will be initialized/refreshed with method user.readEnv
envDbaRolePrivs []string
// gsmSecNewVer is new GSM secret version from the spec.
gsmSecNewVer string
// gsmSecCurVer is the current GSM secret version.
gsmSecCurVer string
// newPassword is used by both gsm and plaintext;
// can be overwritten later if GSM is enabled.
newPassword string
// curPassword is only used for plaintext status diff.
curPassword string
}
func (u *user) readEnv(ctx context.Context, client dbdpb.DatabaseDaemonClient) error {
sysPrivs, err := queryDB(
ctx,
client,
u.databaseName,
fmt.Sprintf("select privilege from dba_sys_privs where grantee='%s'", sql.StringParam(u.userName)),
"PRIVILEGE",
func(string) bool {
return true
},
)
if err != nil {
return fmt.Errorf("failed to query dba sys privileges: %v", err)
}
u.envDbaSysPrivs = sysPrivs
rolePrivs, err := queryDB(
ctx,
client,
u.databaseName,
fmt.Sprintf("select granted_role from dba_role_privs where grantee='%s'", sql.StringParam(u.userName)),
"GRANTED_ROLE",
func(string) bool {
return true
},
)
if err != nil {
return fmt.Errorf("failed to query dba role privileges: %v", err)
}
u.envDbaRolePrivs = rolePrivs
return nil
}
// diff returns privileges, which should be granted/revoked by comparing k8s spec with real environment.
func (u *user) diff(ctx context.Context, client dbdpb.DatabaseDaemonClient) (toGrant, toRevoke []string, toUpdatePwd bool, err error) {
if err := u.readEnv(ctx, client); err != nil {
return nil, nil, false, fmt.Errorf("failed to read the env user: %v", err)
}
var envPrivs []string
envPrivs = append(envPrivs, u.envDbaSysPrivs...)
envPrivs = append(envPrivs, u.envDbaRolePrivs...)
toGrant, _, toRevoke = compare(u.specPrivs, envPrivs)
// Always update password if the request version is not equal to the current version
// or the request version is latest (if the latest password equals to the current one,
// the SQL underlying won't report error as expected).
toUpdateGsmPwd := (u.gsmSecNewVer != "" && !strings.EqualFold(u.gsmSecNewVer, u.gsmSecCurVer)) || strings.HasSuffix(u.gsmSecNewVer, "latest")
if toUpdateGsmPwd {
var gsmPwd string
gsmPwd, err = AccessSecretVersionFunc(ctx, u.gsmSecNewVer)
if err != nil {
return nil, nil, false, fmt.Errorf("failed to read GSM secret: %v", err)
}
u.newPassword = gsmPwd
}
toUpdatePlaintextPwd := u.curPassword != "" && u.curPassword != u.newPassword
return toGrant, toRevoke, toUpdateGsmPwd || toUpdatePlaintextPwd, nil
}
func (u *user) create(ctx context.Context, client dbdpb.DatabaseDaemonClient) error {
var grantCmds []string
for _, p := range u.specPrivs {
grantCmds = append(grantCmds, sql.QueryGrantPrivileges(p, u.userName))
}
sqls := append(
[]string{
sql.QuerySetSessionContainer(u.databaseName),
sql.QueryCreateUser(u.userName, u.newPassword),
},
grantCmds...,
)
if _, err := client.RunSQLPlus(ctx, &dbdpb.RunSQLPlusCMDRequest{
Commands: sqls,
}); err != nil {
return fmt.Errorf("failed to create user %v: %v", u, err)
}
return nil
}
func (u *user) update(ctx context.Context, client dbdpb.DatabaseDaemonClient, roles map[string]bool) error {
if err := u.updateRolePrivs(ctx, client, roles); err != nil {
return err
}
if err := u.updateSysPrivs(ctx, client, roles); err != nil {
return err
}
return nil
}
func (u *user) updateUserPassword(ctx context.Context, client dbdpb.DatabaseDaemonClient) error {
_, _, toUpdate, err := u.diff(ctx, client)
if err != nil {
return fmt.Errorf("failed to get diff to update user %v: %v", u, err)
}
if !toUpdate {
return nil
}
if err := u.updatePassword(ctx, client); err != nil {
return fmt.Errorf("failed to alter user %s: %v", u.userName, err)
}
return nil
}
func (u *user) updateRolePrivs(ctx context.Context, client dbdpb.DatabaseDaemonClient, roles map[string]bool) error {
toGrant, toRevoke, _, err := u.diff(ctx, client)
if err != nil {
return fmt.Errorf("failed to get diff to update user %v: %v", u, err)
}
var toGrantRoles, toRevokeRoles []string
for _, g := range toGrant {
if roles[g] {
toGrantRoles = append(toGrantRoles, g)
}
}
for _, r := range toRevoke {
if roles[r] {
toRevokeRoles = append(toRevokeRoles, r)
}
}
if err := u.grant(ctx, client, toGrantRoles); err != nil {
return fmt.Errorf("failed to grant roles %v to user %s: %v", toGrantRoles, u.userName, err)
}
if err := u.revoke(ctx, client, toRevokeRoles); err != nil {
return fmt.Errorf("failed to revoke roles %v from user %s: %v", toRevokeRoles, u.userName, err)
}
return nil
}
func (u *user) updateSysPrivs(ctx context.Context, client dbdpb.DatabaseDaemonClient, roles map[string]bool) error {
toGrant, toRevoke, _, err := u.diff(ctx, client)
if err != nil {
return fmt.Errorf("failed to get diff to update user %v: %v", u, err)
}
var toGrantPrivs, toRevokePrivs []string
for _, g := range toGrant {
if !roles[g] {
toGrantPrivs = append(toGrantPrivs, g)
}
}
for _, r := range toRevoke {
if !roles[r] {
toRevokePrivs = append(toRevokePrivs, r)
}
}
if err := u.grant(ctx, client, toGrantPrivs); err != nil {
return fmt.Errorf("failed to grant privs %v to user %s: %v", toGrantPrivs, u.userName, err)
}
if err := u.revoke(ctx, client, toRevokePrivs); err != nil {
return fmt.Errorf("failed to revoke privs %v from user %s: %v", toRevokePrivs, u.userName, err)
}
return nil
}
func (u *user) updatePassword(ctx context.Context, client dbdpb.DatabaseDaemonClient) error {
alterUserCmds := []string{sql.QueryAlterUser(u.userName, u.newPassword)}
sqls := append([]string{sql.QuerySetSessionContainer(u.databaseName)}, alterUserCmds...)
if _, err := client.RunSQLPlus(ctx, &dbdpb.RunSQLPlusCMDRequest{
Commands: sqls,
Suppress: true,
}); err != nil {
return fmt.Errorf("failed to alter user %s: %v", u.userName, err)
}
return nil
}
func (u *user) grant(ctx context.Context, client dbdpb.DatabaseDaemonClient, toGrant []string) error {
if len(toGrant) == 0 {
return nil
}
var grantCmds []string
for _, p := range toGrant {
grantCmds = append(grantCmds, sql.QueryGrantPrivileges(p, u.userName))
}
sqls := append([]string{sql.QuerySetSessionContainer(u.databaseName)}, grantCmds...)
if _, err := client.RunSQLPlus(ctx, &dbdpb.RunSQLPlusCMDRequest{
Commands: sqls,
}); err != nil {
return fmt.Errorf("failed to grant %v to user %s: %v", toGrant, u.userName, err)
}
return nil
}
func (u *user) revoke(ctx context.Context, client dbdpb.DatabaseDaemonClient, toRevoke []string) error {
if len(toRevoke) == 0 {
return nil
}
var revokeCmds []string
for _, p := range toRevoke {
revokeCmds = append(revokeCmds, sql.QueryRevokePrivileges(p, u.userName))
}
sqls := append([]string{sql.QuerySetSessionContainer(u.databaseName)}, revokeCmds...)
if _, err := client.RunSQLPlus(ctx, &dbdpb.RunSQLPlusCMDRequest{
Commands: sqls,
}); err != nil {
return fmt.Errorf("failed to revoke %v from user %s: %v", toRevoke, u.userName, err)
}
return nil
}
func (u *user) delete() (suppressedSQLs string) {
return sql.QuerySetSessionContainer(u.databaseName) + fmt.Sprintf("; DROP USER %s CASCADE;", sql.MustBeObjectName(u.userName))
}
func (u *user) String() string {
return fmt.Sprintf("{database: %q, name: %q, specPrivs: %v, envSysPrivs: %v, envRolePrivs %v}", u.databaseName, u.userName, u.specPrivs, u.envDbaSysPrivs, u.envDbaRolePrivs)
}
func (u *user) GetUserName() string {
return u.userName
}
func (u *user) GetUserEnvPrivs() []string {
var privs []string
privs = append(privs, u.envDbaRolePrivs...)
privs = append(privs, u.envDbaSysPrivs...)
return privs
}
func newUser(databaseName string, specUser *User) *user {
var privs []string
for _, p := range specUser.Privileges {
upperP := strings.ToUpper(p)
// example: GRANT SELECT ON TABLE t TO SCOTT
if strings.Contains(upperP, " ON ") {
klog.ErrorS(errors.New("object privileges not supported, will be omitted by operator"), "not supported privileges", "priv", p)
} else {
privs = append(privs, upperP)
}
}
user := &user{
databaseName: strings.ToUpper(databaseName),
userName: strings.ToUpper(specUser.Name),
// Used by both gsm and plaintext
// can be overwritten later if GSM is enabled.
newPassword: specUser.Password,
// Only used for plaintext status diff.
curPassword: specUser.LastPassword,
specPrivs: privs,
}
if specUser.PasswordGsmSecretRef != nil {
user.gsmSecCurVer = specUser.PasswordGsmSecretRef.LastVersion
user.gsmSecNewVer = fmt.Sprintf(gsmSecretStr, specUser.PasswordGsmSecretRef.ProjectId, specUser.PasswordGsmSecretRef.SecretId, specUser.PasswordGsmSecretRef.Version)
}
return user
}
func newNoSpecUser(databaseName, userName string) *user {
return &user{
databaseName: strings.ToUpper(databaseName),
userName: strings.ToUpper(userName),
}
}
func queryDB(ctx context.Context, client dbdpb.DatabaseDaemonClient, databaseName, sqlQuery, key string, filter func(val string) bool) ([]string, error) {
resp, err := client.RunSQLPlusFormatted(ctx, &dbdpb.RunSQLPlusCMDRequest{
Commands: []string{
sql.QuerySetSessionContainer(databaseName),
sqlQuery,
},
})
if err != nil {
return nil, fmt.Errorf("queryDB failed to query data: %v", err)
}
rows, err := parseSQLResponse(resp)
if err != nil {
return nil, fmt.Errorf("queryDB failed to parse data from %v: %v", resp, err)
}
userNames, err := queryRowsByKey(rows, key, filter)
if err != nil {
return nil, fmt.Errorf("failed to retrieve %v from %v", key, rows)
}
return userNames, nil
}
func queryRowsByKey(rows []map[string]string, rowKey string, filter func(val string) bool) ([]string, error) {
var res []string
for _, row := range rows {
v, ok := row[rowKey]
if !ok {
return nil, fmt.Errorf("failed to retrieve %v from %v", rowKey, row)
}
if filter(v) {
res = append(res, v)
}
}
return res, nil
}
// compare returns set difference left\right intersection right\left
func compare(left, right []string) (leftMinusRight, intersection, rightMinusLeft []string) {
leftSet := stringset.New(left...)
rightSet := stringset.New(right...)
return leftSet.Diff(rightSet).Elements(), leftSet.Intersect(rightSet).Elements(), rightSet.Diff(leftSet).Elements()
}