Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package v1
import (
"context"
"database/sql"
"fmt"
"io"
"math"
"regexp"
"slices"
"strings"
"time"
"log/slog"
"connectrpc.com/connect"
"github.com/pkg/errors"
"google.golang.org/protobuf/types/known/durationpb"
"github.com/bytebase/bytebase/backend/common"
"github.com/bytebase/bytebase/backend/common/log"
"github.com/bytebase/bytebase/backend/common/permission"
"github.com/bytebase/bytebase/backend/component/dbfactory"
"github.com/bytebase/bytebase/backend/component/export"
"github.com/bytebase/bytebase/backend/component/iam"
"github.com/bytebase/bytebase/backend/component/parsercontext"
"github.com/bytebase/bytebase/backend/enterprise"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
"github.com/bytebase/bytebase/backend/generated-go/v1/v1connect"
"github.com/bytebase/bytebase/backend/plugin/advisor/code"
"github.com/bytebase/bytebase/backend/plugin/db"
parserbase "github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/plugin/sharepoint"
"github.com/bytebase/bytebase/backend/runner/schemasync"
"github.com/bytebase/bytebase/backend/store"
"github.com/bytebase/bytebase/backend/store/model"
"github.com/bytebase/bytebase/backend/utils"
)
// SQLService is the service for SQL.
type SQLService struct {
v1connect.UnimplementedSQLServiceHandler
store *store.Store
schemaSyncer *schemasync.Syncer
dbFactory *dbfactory.DBFactory
licenseService *enterprise.LicenseService
iamManager *iam.Manager
// queryHistoryService backs the deprecated query history aliases until
// they are removed.
queryHistoryService *QueryHistoryService
// newSharePointClient defaults to sharepoint.NewClient when nil.
newSharePointClient func(sharepoint.Config) (sharepoint.Client, error)
}
// NewSQLService creates a SQLService.
func NewSQLService(
store *store.Store,
schemaSyncer *schemasync.Syncer,
dbFactory *dbfactory.DBFactory,
licenseService *enterprise.LicenseService,
iamManager *iam.Manager,
queryHistoryService *QueryHistoryService,
) *SQLService {
return &SQLService{
store: store,
schemaSyncer: schemaSyncer,
dbFactory: dbFactory,
licenseService: licenseService,
iamManager: iamManager,
queryHistoryService: queryHistoryService,
}
}
// AdminExecute executes the SQL statement.
func (s *SQLService) AdminExecute(ctx context.Context, stream *connect.BidiStream[v1pb.AdminExecuteRequest, v1pb.AdminExecuteResponse]) error {
if err := s.licenseService.IsFeatureEnabled(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_SQL_EDITOR_ADMIN_MODE); err != nil {
return connect.NewError(connect.CodePermissionDenied, err)
}
var driver db.Driver
var conn *sql.Conn
var connectionName string
clean := func() {
if conn != nil {
if err := conn.Close(); err != nil {
slog.Warn("failed to close connection", log.BBError(err))
}
}
if driver != nil {
driver.Close(ctx)
}
}
defer clean()
for {
request, err := stream.Receive()
if err != nil {
if err == io.EOF {
return nil
}
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to receive request: %v", err))
}
user, instance, database, err := s.prepareRelatedMessage(ctx, request.Name)
if err != nil {
return err
}
// We only need to get the driver and connection once.
if driver == nil || connectionName != request.Name {
clean()
connectionName = request.Name
driver, err = s.dbFactory.GetAdminDatabaseDriver(ctx, instance, database, db.ConnectionContext{})
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to get database driver: %v", err))
}
sqlDB := driver.GetDB()
if sqlDB != nil {
conn, err = sqlDB.Conn(ctx)
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to get database connection: %v", err))
}
}
}
queryRestriction := getEffectiveQueryDataPolicy(
ctx,
s.store,
s.licenseService,
request.Limit,
database.ProjectID,
)
queryContext := db.QueryContext{
OperatorEmail: user.Email,
Container: request.GetContainer(),
MaximumSQLResultSize: queryRestriction.MaximumResultSize,
Limit: int(queryRestriction.MaximumResultRows),
}
if request.Schema != nil {
queryContext.Schema = *request.Schema
}
if queryRestriction.MaxQueryTimeoutInSeconds > 0 {
queryContext.Timeout = &durationpb.Duration{Seconds: queryRestriction.MaxQueryTimeoutInSeconds}
}
result, duration, queryErr := executeWithTimeout(
ctx,
driver,
conn,
request.Statement,
queryContext,
)
s.createQueryHistory(instance, database, store.QueryHistoryTypeQuery, request.Statement, user.Email, duration, queryErr)
response := &v1pb.AdminExecuteResponse{}
if queryErr != nil {
response.Results = []*v1pb.QueryResult{
{
Error: queryErr.Error(),
Statement: request.Statement,
},
}
} else {
response.Results = result
}
if err := stream.Send(response); err != nil {
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to send response: %v", err))
}
}
}
// buildExportQueryContext assembles the db.QueryContext for an export request.
// queryRetry reads SkipMasking off the QueryContext to decide whether to mask
// the results and check sensitive predicate columns. No driver reads the field.
func buildExportQueryContext(restriction *store.EffectiveQueryDataPolicy, userEmail string, schema *string, container string, skipMasking bool) db.QueryContext {
qc := db.QueryContext{
Limit: int(restriction.MaximumResultRows),
OperatorEmail: userEmail,
MaximumSQLResultSize: restriction.MaximumResultSize,
Container: container,
SkipMasking: skipMasking,
}
if restriction.MaxQueryTimeoutInSeconds > 0 {
qc.Timeout = &durationpb.Duration{Seconds: restriction.MaxQueryTimeoutInSeconds}
}
if schema != nil {
qc.Schema = *schema
}
return qc
}
// preCheckAccess returns the user's most capable active access grant matching
// the given query, or nil if none. When `requireExport` is true the SQL
// filter is narrowed to grants with `export == true`, so an unmask-only
// grant on the same statement can't shadow a separately-active export
// grant via slice-order ties (see PR #20491 review).
//
// The returned grant has a non-nil Payload — callers may deref
// `Payload.Unmask` / `Payload.Export` without an additional check.
func (s *SQLService) preCheckAccess(ctx context.Context, statement string, instance *store.InstanceMessage, database *store.DatabaseMessage, schema *string, container string, requireExport bool) *store.AccessGrantMessage {
project, err := s.store.GetProject(ctx, &store.FindProjectMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
ResourceID: &database.ProjectID,
})
if err != nil {
slog.Warn("failed to find project", slog.String("project_id", database.ProjectID), log.BBError(err))
return nil
}
if project == nil {
slog.Warn("project not found", slog.String("project_id", database.ProjectID))
return nil
}
if !project.Setting.AllowJustInTimeAccess {
slog.Debug("JIT is not enabled in the project", slog.String("project_id", database.ProjectID))
return nil
}
user, ok := GetUserFromContext(ctx)
if !ok || user == nil {
return nil
}
requestSchema := ""
if schema != nil {
requestSchema = *schema
}
grants, err := s.store.ListActiveAccessGrants(ctx, &store.FindActiveAccessGrantMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
ProjectID: database.ProjectID,
Creator: user.Email,
Target: formatDatabaseResourceName(instance, database),
Statement: statement,
Schema: requestSchema,
Container: container,
RequireExport: requireExport,
ExpireTime: time.Now().UTC(),
})
if err != nil {
slog.Warn("failed to find active access grant", log.BBError(err))
return nil
}
grant := selectBestAccessGrant(grants)
if grant == nil {
return nil
}
readOnly, err := isReadOnlyStatementForAccessGrant(ctx, instance.Metadata.GetEngine(), statement)
if err != nil {
slog.Warn("failed to validate access grant query", log.BBError(err))
return nil
}
if !readOnly {
slog.Warn("skip access grant for non-read-only query", slog.String("instance", instance.ResourceID), slog.String("database", database.DatabaseName))
return nil
}
return grant
}
// selectBestAccessGrant picks the highest-ranked grant by capability count
// (Unmask + Export). A grant with both wins outright; otherwise the first
// single-capability grant in slice order wins (ties resolve to the input
// ordering). Nil-payload grants are skipped so callers can deref
// `Payload.Unmask` / `Payload.Export` without an additional check.
//
// This auto-find ranking is inherently ambiguous when two grants tie on
// score — for deterministic selection callers should pass an explicit
// `access_grant` in the request (see `resolveExplicitAccessGrant`).
func selectBestAccessGrant(grants []*store.AccessGrantMessage) *store.AccessGrantMessage {
var best *store.AccessGrantMessage
bestScore := -1
for _, grant := range grants {
if grant.Payload == nil {
continue
}
// Rank by Unmask only — Export plays no role in selection:
//
// - Export callers pass `requireExport=true`, which adds
// `export == true` to the SQL filter, so every grant in
// this slice already has `Export=true` and a per-grant Export
// bump would just be a uniform constant.
// - Query callers don't read `Payload.Export` from the returned
// grant — they only consume `Payload.Unmask` for
// `SkipMasking`.
//
// So preferring Unmask=true is the only ranking signal that
// affects observable behavior, and matches PR #20491 bot review
// (#3349086819) requesting Unmask wins ties for Query.
score := 0
if grant.Payload.Unmask {
score++
}
if score > bestScore {
best = grant
bestScore = score
}
}
return best
}
func (s *SQLService) Query(ctx context.Context, req *connect.Request[v1pb.QueryRequest]) (*connect.Response[v1pb.QueryResponse], error) {
request := req.Msg
// Prepare related message.
user, instance, database, err := s.prepareRelatedMessage(ctx, request.Name)
if err != nil {
return nil, err
}
accessGrant := s.preCheckAccess(ctx, request.Statement, instance, database, request.Schema, request.GetContainer(), false /* requireExport */)
statement := request.Statement
// In Redshift datashare, Rewrite query used for parser.
if database.Metadata.GetDatashare() {
statement = strings.ReplaceAll(statement, fmt.Sprintf("%s.", database.DatabaseName), "")
}
// Validate the request.
// Engines without the per-statement ACL rely on this read-only gate to refuse
// writes (they do not classify DML/DDL in the access check). New-ACL engines
// classify per statement instead.
if !request.Explain && !common.EngineSupportQueryNewACL(instance.Metadata.GetEngine()) {
if err := validateQueryRequest(instance, statement); err != nil {
return nil, err
}
}
// EXPLAIN ANALYZE of a write executes it, so refuse an explain whose wrapped
// form is not read-only. See validateExplainStatements.
if request.Explain {
if err := validateExplainStatements(instance, statement, request.GetQueryOption().GetExplainFormat()); err != nil {
return nil, err
}
}
if request.Explain {
if err := validateExplainFormat(instance.Metadata.GetEngine(), request.GetQueryOption().GetExplainFormat()); err != nil {
return nil, err
}
}
// The MCP read-only clamp. Unconditional on Explain, unlike the gate
// above: an explain request carries the bare statement and the driver
// prepends EXPLAIN, so skipping it would hand a read-only session an
// unclassified write to send.
clamped, err := mcpReadOnlyClampApplies(ctx)
if err != nil {
return nil, err
}
if clamped {
if err := refuseNonReadOnlyStatement(instance.Metadata.GetEngine(), statement); err != nil {
// The same kind of refusal the ceiling gate marks, taken at the
// one point that can see the request's argument. Query is audited
// and this runs in its handler, so the row is stored either way;
// the mark stamps it WARNING.
setPermissionDenied(ctx)
return nil, err
}
}
mcpWrite := !clamped && mcpSession(ctx) && !mcpStatementIsRead(ctx, instance.Metadata.GetEngine(), statement)
// query_database takes no explain, so an explain of a possible write cannot
// be confirmed.
if mcpWrite && request.Explain {
return nil, permissionDeniedError(ctx, errors.Errorf("an MCP session explains only statements Bytebase can show are reads. %s", mcpConsoleWayOut))
}
queryRestriction := getEffectiveQueryDataPolicy(
ctx,
s.store,
s.licenseService,
request.Limit,
database.ProjectID,
)
var resolvedDataSourceID string
if mcpWrite {
resolvedDataSourceID, err = mcpWriteDataSourceID(instance, request.DataSourceId, queryRestriction.AllowAdminDataSource)
} else {
resolvedDataSourceID, err = resolveDataSourceID(ctx, instance, request.DataSourceId, statement, queryRestriction.AllowAdminDataSource)
}
if err != nil {
return nil, err
}
dataSource, err := checkAndGetDataSourceQueriable(ctx, s.store, s.licenseService, database, resolvedDataSourceID)
if err != nil {
return nil, err
}
// After the data source checks, so a request they refuse is refused before
// the user is asked.
if mcpWrite && !mcpWriteConfirmed(ctx, request) {
return nil, mcpConfirmationRequired(database, request.GetSchema())
}
driver, err := s.dbFactory.GetDataSourceDriver(ctx, instance, dataSource, db.ConnectionContext{
DatabaseName: database.DatabaseName,
DataShare: database.Metadata.GetDatashare(),
// A clamped request asks for the read-only session whatever data
// source it landed on, so the depth does not depend on the customer
// having configured a read-only data source. The driver is opened and
// closed inside this handler, so the session cannot outlive the
// request or be reused by another one.
ReadOnly: clamped || dataSource.GetType() == storepb.DataSourceType_READ_ONLY,
})
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to get database driver: %v", err))
}
defer driver.Close(ctx)
sqlDB := driver.GetDB()
var conn *sql.Conn
if sqlDB != nil {
conn, err = sqlDB.Conn(ctx)
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to get database connection: %v", err))
}
defer conn.Close()
}
startTime := time.Now()
queryContext := db.QueryContext{
Explain: request.Explain,
DataSourceType: dataSource.GetType(),
Limit: int(queryRestriction.MaximumResultRows),
OperatorEmail: user.Email,
Option: request.QueryOption,
Container: request.GetContainer(),
MaximumSQLResultSize: queryRestriction.MaximumResultSize,
SkipMasking: accessGrant != nil && accessGrant.Payload.Unmask,
}
if request.Schema != nil {
queryContext.Schema = *request.Schema
}
if queryRestriction.MaxQueryTimeoutInSeconds > 0 {
queryContext.Timeout = &durationpb.Duration{Seconds: queryRestriction.MaxQueryTimeoutInSeconds}
}
// Span-level ACL still runs even when a JIT grant matched. The grant
// authorizes only its target databases — any cross-database reference
// (e.g. `SELECT * FROM db_b.secret` from a grant on `db_a`) still has
// to clear the regular IAM check on the foreign database. See PR
// #20487 review for the cross-DB exfiltration scenario this guards.
optionalAccessCheck := s.accessCheckWithGrant(accessGrant, false /* export */)
results, _, duration, queryErr := queryRetryStopOnError(
ctx,
s.store,
user,
instance,
database,
driver,
conn,
statement,
queryContext,
s.licenseService,
optionalAccessCheck,
s.schemaSyncer,
)
slog.Debug("query finished",
log.BBError(queryErr),
slog.Duration("duration", time.Since(startTime)),
slog.String("instance", instance.ResourceID),
slog.String("database", database.DatabaseName),
)
// Update activity.
s.createQueryHistory(instance, database, store.QueryHistoryTypeQuery, statement, user.Email, duration, queryErr)
if queryErr != nil {
if len(results) == 0 {
if _, ok := queryErr.(*connect.Error); ok {
return nil, queryErr
}
return nil, connect.NewError(connect.CodeInternal, errors.New(queryErr.Error()))
}
// populate the detailed_error field of the last query result
var qe *queryError
var pe *parserbase.SyntaxError
if errors.As(queryErr, &qe) {
if len(qe.resources) > 0 {
results[len(results)-1].DetailedError = &v1pb.QueryResult_PermissionDenied{
PermissionDenied: &v1pb.PermissionDeniedDetail{
Resources: qe.resources,
RequiredPermissions: []string{
qe.permission,
},
},
}
} else if qe.commandType != v1pb.QueryResult_CommandError_TYPE_UNSPECIFIED {
results[len(results)-1].DetailedError = &v1pb.QueryResult_CommandError_{
CommandError: &v1pb.QueryResult_CommandError{
CommandType: qe.commandType,
},
}
}
} else if errors.As(queryErr, &pe) {
results[len(results)-1].DetailedError = &v1pb.QueryResult_SyntaxError_{
SyntaxError: &v1pb.QueryResult_SyntaxError{
StartPosition: convertToPosition(pe.Position),
},
}
}
}
slog.Debug("request finished",
slog.Duration("duration", time.Since(startTime)),
slog.String("instance", instance.ResourceID),
slog.String("database", database.DatabaseName),
)
response := &v1pb.QueryResponse{
Results: results,
}
if accessGrant != nil {
response.AppliedAccessGrant = common.FormatAccessGrant(accessGrant.ProjectID, accessGrant.ID)
}
return connect.NewResponse(response), nil
}
func getEffectiveQueryDataPolicy(
ctx context.Context,
stores *store.Store,
licenseService *enterprise.LicenseService,
limit int32,
projectID string,
) *store.EffectiveQueryDataPolicy {
value := &store.EffectiveQueryDataPolicy{
MaximumResultSize: common.DefaultMaximumSQLResultSize,
MaximumResultRows: math.MaxInt32,
}
if err := licenseService.IsFeatureEnabled(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_QUERY_POLICY); err == nil {
policy, err := stores.GetEffectiveQueryDataPolicy(ctx, common.GetWorkspaceIDFromContext(ctx), common.FormatProject(projectID))
if err != nil {
slog.Error("failed to get the query data policy", log.BBError(err))
return value
}
value = policy
}
if limit > 0 {
value.MaximumResultRows = min(limit, value.MaximumResultRows)
}
if value.MaximumResultRows == math.MaxInt32 {
value.MaximumResultRows = 0
}
return value
}
type accessCheckFunc func(context.Context, *store.InstanceMessage, *store.DatabaseMessage, *store.UserMessage, []*parserbase.QuerySpan, bool /* isExplain */, []parserbase.Statement, string /* requestSchema */, bool /* multiStatement */) error
// hasMultipleStatements reports whether a batch has more than one non-empty statement.
// Multi-statement batches can change the session's default schema mid-batch (e.g. Postgres
// `SET search_path`), so DML/DDL in them falls back to the database-level check. SUP-222.
func hasMultipleStatements(statements []parserbase.Statement) bool {
n := 0
for _, st := range statements {
if st.Empty {
continue
}
n++
if n > 1 {
return true
}
}
return false
}
func extractSourceTable(comment string) (string, string, string, error) {
pattern := `\((\w+),\s*(\w+)(?:,\s*(\w+))?\)`
re := regexp.MustCompile(pattern)
matches := re.FindStringSubmatch(comment)
if len(matches) == 3 || (len(matches) == 4 && matches[3] == "") {
databaseName := matches[1]
tableName := matches[2]
return databaseName, "", tableName, nil
} else if len(matches) == 4 {
databaseName := matches[1]
schemaName := matches[2]
tableName := matches[3]
return databaseName, schemaName, tableName, nil
}
return "", "", "", errors.Errorf("failed to extract source table from comment: %s", comment)
}
func getSchemaMetadata(engine storepb.Engine, dbMetadata *model.DatabaseMetadata) *model.SchemaMetadata {
switch engine {
case storepb.Engine_POSTGRES:
return dbMetadata.GetSchemaMetadata(common.BackupDatabaseNameOfEngine(storepb.Engine_POSTGRES))
case storepb.Engine_MSSQL:
return dbMetadata.GetSchemaMetadata("dbo")
default:
return dbMetadata.GetSchemaMetadata("")
}
}
func replaceBackupTableWithSource(ctx context.Context, stores *store.Store, instance *store.InstanceMessage, database *store.DatabaseMessage, spans []*parserbase.QuerySpan) error {
switch instance.Metadata.GetEngine() {
case storepb.Engine_POSTGRES:
// Don't need to check the database name for postgres here.
// We backup the table to the same database with bbdataarchive schema for Postgres.
case storepb.Engine_ORACLE:
if database.DatabaseName != common.BackupDatabaseNameOfEngine(storepb.Engine_ORACLE) {
return nil
}
default:
if database.DatabaseName != common.BackupDatabaseNameOfEngine(instance.Metadata.GetEngine()) {
return nil
}
}
dbMetadata, err := stores.GetDBSchema(ctx, &store.FindDBSchemaMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
InstanceID: database.InstanceID,
DatabaseName: database.DatabaseName,
})
if err != nil {
return err
}
schema := getSchemaMetadata(instance.Metadata.GetEngine(), dbMetadata)
if schema == nil {
return nil
}
for _, span := range spans {
remapBackupTableColumns(instance.Metadata.GetEngine(), span, schema)
}
return nil
}
// remapBackupTableColumns points the columns span reads from backup tables at
// the source columns, whose masking policies apply to the backups too.
func remapBackupTableColumns(engine storepb.Engine, span *parserbase.QuerySpan, schema *model.SchemaMetadata) {
span.SourceColumns = generateNewSourceColumnSet(engine, span.SourceColumns, schema)
span.PredicateColumns = generateNewSourceColumnSet(engine, span.PredicateColumns, schema)
for i := range span.Results {
span.Results[i].SourceColumns = generateNewSourceColumnSet(engine, span.Results[i].SourceColumns, schema)
}
}
func generateNewSourceColumnSet(engine storepb.Engine, origin parserbase.SourceColumnSet, schema *model.SchemaMetadata) parserbase.SourceColumnSet {
result := make(parserbase.SourceColumnSet)
for column := range origin {
if isBackupTable(engine, column) {
tableSchema := schema.GetTable(column.Table)
if tableSchema == nil {
result[column] = true
continue
}
sourceDatabase, sourceSchema, sourceTable, err := extractSourceTable(tableSchema.GetTableComment())
if err != nil {
slog.Debug("failed to extract source table", log.BBError(err))
result[column] = true
continue
}
newColumn := generateNewColumn(engine, column, sourceDatabase, sourceSchema, sourceTable)
result[newColumn] = true
} else {
result[column] = true
}
}
return result
}
func generateNewColumn(engine storepb.Engine, column parserbase.ColumnResource, database, schema, table string) parserbase.ColumnResource {
switch engine {
case storepb.Engine_POSTGRES:
return parserbase.ColumnResource{
Instance: column.Instance,
Server: column.Server,
Database: column.Database,
Schema: database,
Table: table,
Column: column.Column,
}
default:
return parserbase.ColumnResource{
Instance: column.Instance,
Server: column.Server,
Database: database,
Schema: schema,
Table: table,
Column: column.Column,
}
}
}
// privateDatabaseLinkOwner is implemented by a driver whose session account can own
// private database links (Oracle).
type privateDatabaseLinkOwner interface {
OwnsPrivateDatabaseLink(ctx context.Context, conn *sql.Conn) (bool, error)
}
// refuseLinkedReadsShadowedByPrivateLinks refuses a linked read when the executing account
// owns any private database link. Oracle resolves @name to the executing account's private
// link before a public one, and the sync recorded the admin account's links only, so a
// private link on another account can shadow the synced definition. No link name is
// compared: Oracle expands a bare name with the database domain and accepts connection
// qualifiers, and the synced list cannot settle either. The admin data source is the synced
// account and is exempt. Runs on the executing connection right before execution, so a link
// created by an earlier statement of the same request is seen. A failed check refuses.
// BYT-10239 replaces this with resolution from the executing session's own links.
func refuseLinkedReadsShadowedByPrivateLinks(ctx context.Context, driver db.Driver, conn *sql.Conn, instance *store.InstanceMessage, queryContext db.QueryContext, spans []*parserbase.QuerySpan) error {
if instance.Metadata.GetEngine() != storepb.Engine_ORACLE || queryContext.DataSourceType == storepb.DataSourceType_ADMIN {
return nil
}
linked := linkedColumns(spans)
if len(linked) == 0 {
return nil
}
owner, ok := driver.(privateDatabaseLinkOwner)
if !ok || conn == nil {
return privateLinkRefusal(ctx, linked, false, errors.New("the driver exposes no session connection"))
}
owns, err := owner.OwnsPrivateDatabaseLink(ctx, conn)
return privateLinkRefusal(ctx, linked, owns, err)
}
// linkedColumns lists the columns reached through a resolved database link, in a stable order.
func linkedColumns(spans []*parserbase.QuerySpan) []parserbase.ColumnResource {
var linked []parserbase.ColumnResource
for _, span := range spans {
if span == nil {
continue
}
for column := range span.SourceColumns {
if column.Instance != "" {
linked = append(linked, column)
}
}
}
slices.SortFunc(linked, func(a, b parserbase.ColumnResource) int { return strings.Compare(a.String(), b.String()) })
return linked
}
// privateLinkRefusal is the verdict of refuseLinkedReadsShadowedByPrivateLinks: nil only when
// the check ran and found no private link.
func privateLinkRefusal(ctx context.Context, linked []parserbase.ColumnResource, owns bool, err error) error {
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Wrapf(err, "failed to check the executing account's private database links; %s", linkedTableRefusal(linked[0], "is not executed")))
}
if owns {
return permissionDeniedError(ctx, errors.New(linkedTableRefusal(linked[0], "cannot be authorized: the executing account owns private database links, which Oracle resolves before the public links Bytebase synced. Run the query under the admin data source, or remove the private links from the account behind the read-only data source (BYT-10239)")))
}
return nil
}
// refuseLinkedTargetsOutsideProject mirrors authorizeWriteTargets: the access check
// evaluates the session project's IAM policy only, so a target in another project is
// refused. Runs after remoteColumnRefusal, so every linked column is on the connected
// instance. SUP-222 / BYT-9698, BYT-10226.
func (s *SQLService) refuseLinkedTargetsOutsideProject(ctx context.Context, columns parserbase.SourceColumnSet, instance *store.InstanceMessage, database *store.DatabaseMessage, perm permission.Permission) (*queryError, error) {
targets := map[string]*store.DatabaseMessage{}
for _, column := range linkedColumns([]*parserbase.QuerySpan{{SourceColumns: columns}}) {
target, seen := targets[column.Database]
if !seen {
databaseName := column.Database
var err error
target, err = s.store.GetDatabase(ctx, &store.FindDatabaseMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
InstanceID: &instance.ResourceID,
DatabaseName: &databaseName,
})
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to resolve linked database %q: %v", column.Database, err))
}
targets[column.Database] = target
}
if qe := linkedTargetProjectRefusal(ctx, column, target, database.ProjectID, perm); qe != nil {
return qe, nil
}
}
return nil, nil
}
// linkedTargetProjectRefusal decides refuseLinkedTargetsOutsideProject for one column, given
// the database the link resolved to (nil when Bytebase does not track it).
func linkedTargetProjectRefusal(ctx context.Context, column parserbase.ColumnResource, target *store.DatabaseMessage, requestProjectID string, perm permission.Permission) *queryError {
var message string
switch {
case target == nil:
message = linkedTableRefusal(column, "is in a database Bytebase does not track on this instance")
case target.ProjectID != requestProjectID:
message = linkedTableRefusal(column, fmt.Sprintf("is in project %q: reading another project's database through a link is not supported in SQL Editor", target.ProjectID))
default:
return nil
}
return &queryError{
err: permissionDeniedError(ctx, errors.New(message)),
permission: perm,
}
}
// linkedTableRefusal names the linked table as written (an unqualified unresolved reference
// has no database) followed by the reason.
func linkedTableRefusal(column parserbase.ColumnResource, reason string) string {
table := column.Table
if column.Database != "" {
table = column.Database + "." + table
}
return fmt.Sprintf("table %s reached through database link %q %s", table, column.Server, reason)
}
// remoteColumnRefusal refuses an Oracle database link Bytebase could not resolve or that
// resolves to another instance. The error carries no resource: a placeholder would satisfy
// a negated condition (table_name != "x") and request-access would offer a grant that
// cannot help. BYT-10226.
func remoteColumnRefusal(ctx context.Context, columns parserbase.SourceColumnSet, connectedInstanceID string, perm permission.Permission) *queryError {
var remote []parserbase.ColumnResource
for column := range columns {
if column.Server != "" {
remote = append(remote, column)
}
}
slices.SortFunc(remote, func(a, b parserbase.ColumnResource) int { return strings.Compare(a.String(), b.String()) })
for _, column := range remote {
var message string
switch {
case column.Instance == "":
message = linkedTableRefusal(column, "cannot be authorized: Bytebase could not establish which database it reaches. A link is authorized when the connect string Bytebase synced for it names the host, port and service of a data source of exactly one instance")
case column.Instance != connectedInstanceID:
// Links to another instance are refused until the masker resolves a linked column's
// policy on its own instance; it lists databases by name on the connected instance
// (BYT-10237).
message = linkedTableRefusal(column, fmt.Sprintf("is on instance %q: querying another instance through a database link is not supported in SQL Editor", column.Instance))
default:
continue
}
return &queryError{
err: permissionDeniedError(ctx, errors.New(message)),
permission: perm,
}
}
return nil
}
func isBackupTable(engine storepb.Engine, column parserbase.ColumnResource) bool {
switch engine {
case storepb.Engine_POSTGRES:
return column.Schema == common.BackupDatabaseNameOfEngine(storepb.Engine_POSTGRES)
case storepb.Engine_ORACLE:
return column.Database == common.BackupDatabaseNameOfEngine(storepb.Engine_ORACLE)
default:
return column.Database == common.BackupDatabaseNameOfEngine(engine)
}
}
func queryRetry(
ctx context.Context,
stores *store.Store,
user *store.UserMessage,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
driver db.Driver,
conn *sql.Conn,
statements []parserbase.Statement,
originalStatement string,
queryContext db.QueryContext,
licenseService *enterprise.LicenseService,
optionalAccessCheck accessCheckFunc,
schemaSyncer *schemasync.Syncer,
multiStatement bool,
) ([]*v1pb.QueryResult, []*parserbase.QuerySpan, time.Duration, error) {
// An explain request carries the bare statements the driver will wrap, so
// their spans name the databases the plan describes.
spans, err := buildQuerySpans(ctx, stores, instance, database, statements, queryContext.Schema)
if err != nil {
return nil, nil, time.Duration(0), err
}
if optionalAccessCheck != nil {
// A smuggled write (EXPLAIN ANALYZE of a write) is refused earlier, in
// the Query handler, before it reaches execution.
if err := optionalAccessCheck(ctx, instance, database, user, spans, queryContext.Explain, statements, queryContext.Schema, multiStatement); err != nil {
return nil, nil, time.Duration(0), err
}
slog.Debug("optional access check", slog.String("instance", instance.ResourceID), slog.String("database", database.DatabaseName))
}
maskingEnabled := !queryContext.Explain && !queryContext.SkipMasking &&
licenseService.IsFeatureEnabledForInstance(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_DATA_MASKING, instance) == nil
queryContext.MaskingEnabled = maskingEnabled
if maskingEnabled {
// Columns a stale snapshot lacks can hide a condition from the predicate
// check, so sync before execution. MaskResults refuses the result if the
// columns stay unresolved. Permanently empty tables re-sync on every query
// until BYT-10074 adds throttling.
if unresolved := unresolvedColumnDatabases(spans, instance); len(unresolved) > 0 {
if err := syncDatabaseMetadata(ctx, stores, schemaSyncer, instance, unresolved); err != nil {
return nil, nil, time.Duration(0), err
}
if spans, err = buildQuerySpans(ctx, stores, instance, database, statements, queryContext.Schema); err != nil {
return nil, nil, time.Duration(0), err
}
}
refusal, err := sensitivePredicateRefusal(ctx, stores, spans, instance, user)
if err != nil {
return nil, nil, time.Duration(0), err
}
if refusal != "" {
return refusedQueryResults(refusal, originalStatement), nil, time.Duration(0), nil
}
if err := preExecuteMaskingCheck(ctx, stores, instance.Metadata.GetEngine(), database, spans); err != nil {
return nil, nil, time.Duration(0), err
}
}
if err := refuseLinkedReadsShadowedByPrivateLinks(ctx, driver, conn, instance, queryContext, spans); err != nil {
return nil, nil, time.Duration(0), err
}
slog.Debug("start execute with timeout", slog.String("instance", instance.ResourceID), slog.String("database", database.DatabaseName), slog.String("statement", originalStatement))
results, duration, queryErr := executeWithTimeout(
ctx,
driver,
conn,
originalStatement,
queryContext,
)
if queryErr != nil {
return nil, nil, duration, queryErr
}
slog.Debug("execute success", slog.String("instance", instance.ResourceID), slog.String("statement", originalStatement), slog.Duration("duration", duration))
if queryContext.Explain {
if format, ok := db.ExplainResultFormat(instance.Metadata.GetEngine(), queryContext.Option.GetExplainFormat()); ok {
for _, result := range results {
// A driver that stated the plan itself keeps it, with whether it
// ran and anything it carried: Elasticsearch answers an explain
// with the profile of a search it executed.
if result.Error == "" && result.QueryPlan == nil {
result.QueryPlan = &v1pb.QueryResult_QueryPlan{Format: format}
}
}
}
return results, nil, duration, nil
}
syncDatabaseMap := make(map[string]bool)
for i, r := range results {
if i >= len(spans) || r.Error != "" {
continue
}
if spans[i].NotFoundError != nil {
for k := range spans[i].SourceColumns {
slog.Debug("database metadata need to sync", slog.String("instance", instance.ResourceID), slog.String("database", k.Database), slog.String("schema", k.Schema), slog.String("table", k.Table), slog.String("column", k.Column))
syncDatabaseMap[k.Database] = true
}
}
}
if err := syncDatabaseMetadata(ctx, stores, schemaSyncer, instance, syncDatabaseMap); err != nil {
return nil, nil, duration, err
}
// Retry getting query span.
if len(syncDatabaseMap) > 0 {
slog.Debug("retry query after sync metadata", slog.String("instance", instance.ResourceID), slog.String("database", database.DatabaseName))
spans, err = buildQuerySpans(ctx, stores, instance, database, statements, queryContext.Schema)
if err != nil {
return nil, nil, time.Duration(0), err
}
// A table the first span did not find may surface a condition the
// pre-execution check could not see.
if maskingEnabled {
refusal, err := sensitivePredicateRefusal(ctx, stores, spans, instance, user)
if err != nil {
return nil, nil, duration, err
}
if refusal != "" {
return refusedQueryResults(refusal, originalStatement), nil, duration, nil
}
}
}
// The second query span should not tolerate any error, but we should retail the original error from database if possible.
for i, result := range results {
if i < len(spans) && result.Error == "" {
if spans[i].FunctionNotSupportedError != nil {
return nil, nil, duration, connect.NewError(connect.CodeInternal, errors.Errorf("failed to mask data: %v", spans[i].FunctionNotSupportedError))
}
if spans[i].NotFoundError != nil {
return nil, nil, duration, connect.NewError(connect.CodeInternal, errors.Errorf("failed to mask data: %v", spans[i].NotFoundError))
}
}
}
if maskingEnabled {
slog.Debug("mask query results", slog.String("instance", instance.ResourceID), slog.String("database", database.DatabaseName))
if dm := getDocumentMasker(instance.Metadata.GetEngine()); dm != nil {
semanticTypeToMaskerMap, err := buildSemanticTypeToMaskerMap(ctx, stores)
if err != nil {
return nil, nil, duration, connect.NewError(connect.CodeInternal, errors.New(err.Error()))
}
if err := dm.maskResults(ctx, stores, database, spans, results, semanticTypeToMaskerMap, queryContext); err != nil {
return nil, nil, duration, err
}
} else {
masker := NewQueryResultMasker(stores)
if err := masker.MaskResults(ctx, spans, results, instance, user); err != nil {
return nil, nil, duration, connect.NewError(connect.CodeInternal, errors.New(err.Error()))
}
}
}
return results, spans, duration, nil
}
// refusedQueryResults reports a refusal as the statement's error rather than
// the request's, so every engine shows the message without an RPC code prefix.
func refusedQueryResults(refusal, statement string) []*v1pb.QueryResult {
return []*v1pb.QueryResult{{Error: refusal, Statement: statement}}
}
// buildQuerySpans analyzes statements for access checks and masking.
func buildQuerySpans(
ctx context.Context,
stores *store.Store,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
statements []parserbase.Statement,
schema string,
) ([]*parserbase.QuerySpan, error) {
spans, err := parserbase.GetQuerySpan(
ctx,
parserbase.GetQuerySpanContext{
InstanceID: instance.ResourceID,
GetDatabaseMetadataFunc: parsercontext.BuildGetDatabaseMetadataFunc(stores),
ListDatabaseNamesFunc: parsercontext.BuildListDatabaseNamesFunc(stores),
GetLinkedDatabaseMetadataFunc: parsercontext.BuildGetLinkedDatabaseMetadataFunc(stores, instance.Metadata.GetEngine()),
},
instance.Metadata.GetEngine(),
statements,
database.DatabaseName,
schema,
!store.IsObjectCaseSensitive(instance),
)
if err != nil {
return nil, err
}
// After replacing backup table with source, we can apply the original access check and mask sensitive data for backup table.
// If err != nil, this function will return the original spans.
if err := replaceBackupTableWithSource(ctx, stores, instance, database, spans); err != nil {
slog.Debug("failed to replace backup table with source", log.BBError(err))
}
return spans, nil
}
// unresolvedColumnDatabases returns the databases whose stale metadata keeps
// masking from resolving a span's columns.
func unresolvedColumnDatabases(spans []*parserbase.QuerySpan, instance *store.InstanceMessage) map[string]bool {
databases := make(map[string]bool)
for _, span := range spans {
if !maskingBlockedByUnresolvedColumns(span, instance) {
continue
}
for _, name := range span.UnresolvedColumnsError.Databases() {
slog.Debug("database metadata need to sync: unresolved columns",
slog.String("instance", instance.ResourceID),
slog.String("database", name),
slog.String("detail", span.UnresolvedColumnsError.Error()))
databases[name] = true
}
}
return databases
}
func syncDatabaseMetadata(ctx context.Context, stores *store.Store, schemaSyncer *schemasync.Syncer, instance *store.InstanceMessage, databaseNames map[string]bool) error {
for name := range databaseNames {
slog.Debug("sync database metadata", slog.String("instance", instance.ResourceID), slog.String("database", name))
d, err := stores.GetDatabase(ctx, &store.FindDatabaseMessage{Workspace: common.GetWorkspaceIDFromContext(ctx), InstanceID: &instance.ResourceID, DatabaseName: &name})
if err != nil {
return err
}
if d == nil {
// Leave the span error in place so masking can reject an untracked database.
slog.Debug("skip metadata sync: database not tracked",
slog.String("instance", instance.ResourceID),
slog.String("database", name))
continue
}
if err := schemaSyncer.SyncDatabaseSchema(ctx, d); err != nil {
return errors.Wrapf(err, "failed to sync database schema for database %q", name)
}
}
return nil
}
// queryRetryStopOnError runs the query and stops on encountering errors.
// The error is both present in the returned QueryResult and error, the caller decides what to do.
func queryRetryStopOnError(
ctx context.Context,
stores *store.Store,
user *store.UserMessage,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
driver db.Driver,
conn *sql.Conn,
statement string,
queryContext db.QueryContext,
licenseService *enterprise.LicenseService,
optionalAccessCheck accessCheckFunc,
schemaSyncer *schemasync.Syncer,
) ([]*v1pb.QueryResult, []*parserbase.QuerySpan, time.Duration, error) {
// MSSQL requires statements to be executed as a batch to preserve variable scope.
// For example, "DECLARE @meta int = 1; SELECT @meta" must be sent as one batch,
// otherwise the second statement will fail with "variable not defined" error.
// Split for span analysis, but pass original statement for batch execution.
if instance.Metadata.GetEngine() == storepb.Engine_MSSQL {
statements, err := parserbase.SplitMultiSQL(instance.Metadata.GetEngine(), statement)
if err != nil {
return nil, nil, 0, err
}
results, spans, duration, err := queryRetry(ctx, stores, user, instance, database, driver, conn, statements, statement, queryContext, licenseService, optionalAccessCheck, schemaSyncer, hasMultipleStatements(statements))
if len(results) == 0 {
results = resultForTimeout(statement, err)
}
return results, spans, duration, err
}
// Split the statement into individual SQLs
statements, err := parserbase.SplitMultiSQL(instance.Metadata.GetEngine(), statement)
if err != nil {
// Engines without splitter support (e.g. Redis) and statements that fail to
// split (e.g. MongoDB parse errors) fall back to treating the entire
// statement as a single unit. Where GetQuerySpan is supported (e.g. MongoDB),
// it re-parses the raw text and surfaces the parse error; otherwise
// queryRetry returns nil spans (old behavior).
results, spans, duration, err := queryRetry(ctx, stores, user, instance, database, driver, conn, []parserbase.Statement{{Text: statement}}, statement, queryContext, licenseService, optionalAccessCheck, schemaSyncer, false)
if len(results) == 0 {
results = resultForTimeout(statement, err)
}
return results, spans, duration, err
}
// A multi-statement batch can change the session schema mid-batch (e.g. SET search_path),
// which the per-statement access check below cannot track; flag it so DML/DDL falls back
// to the database-level check. SUP-222.
multiStatement := hasMultipleStatements(statements)
var allResults []*v1pb.QueryResult
var allSpans []*parserbase.QuerySpan
var totalDuration time.Duration
for _, stmt := range statements {
// Skip empty statements
if stmt.Empty {
continue
}
results, spans, duration, err := queryRetry(ctx, stores, user, instance, database, driver, conn, []parserbase.Statement{stmt}, stmt.Text, queryContext, licenseService, optionalAccessCheck, schemaSyncer, multiStatement)
totalDuration += duration
if err != nil {
allResults = append(allResults, &v1pb.QueryResult{
Error: err.Error(),
Statement: stmt.Text,
})
allSpans = append(allSpans, nil)
return allResults, allSpans, totalDuration, err
}
allResults = append(allResults, results...)
allSpans = append(allSpans, spans...)
// results may have swollen error.
for _, result := range results {
if result.Error != "" {
return allResults, allSpans, totalDuration, nil
}
}
}
return allResults, allSpans, totalDuration, nil
}
// queryTimeoutError marks a run the server cut off at its deadline, apart from
// an error the engine reported, so a caller can answer it with the statement
// that was running.
type queryTimeoutError struct {
timeout time.Duration
}
func (e *queryTimeoutError) Error() string {
return fmt.Sprintf("timeout reached: %v", e.timeout)
}
// resultForTimeout names the statement a timed-out run was cut off in. No
// driver returns a result for a statement it never finished, so without this
// the editor has an error and nothing to point at.
func resultForTimeout(statement string, err error) []*v1pb.QueryResult {
var timedOut *queryTimeoutError
if !errors.As(err, &timedOut) {
return nil
}
return []*v1pb.QueryResult{{Error: err.Error(), Statement: statement}}
}
func executeWithTimeout(
ctx context.Context,
driver db.Driver,
conn *sql.Conn,
statement string,
queryContext db.QueryContext,
) ([]*v1pb.QueryResult, time.Duration, error) {
queryCtx := ctx
var timeout time.Duration
// For access control feature, we will use the timeout from request and query data policy.
// Otherwise, no timeout will be applied.
if queryContext.Timeout != nil {
timeout = queryContext.Timeout.AsDuration()
slog.Debug("create query context with timeout", slog.Duration("timeout", timeout))
newCtx, cancelCtx := context.WithTimeout(ctx, timeout)
defer cancelCtx()
queryCtx = newCtx
}
start := time.Now()
result, err := driver.QueryConn(queryCtx, conn, statement, queryContext)
select {
case <-queryCtx.Done():
// canceled or timed out
return nil, time.Since(start), &queryTimeoutError{timeout: timeout}
default:
// So the select will not block
}
sanitizeResults(result)
return result, time.Since(start), err
}
// Export exports the SQL query result.
func (s *SQLService) Export(ctx context.Context, req *connect.Request[v1pb.ExportRequest]) (*connect.Response[v1pb.ExportResponse], error) {
request := req.Msg
exportTime := time.Now()
// Prepare related message.
user, instance, database, err := s.prepareRelatedMessage(ctx, request.Name)
if err != nil {
return nil, err
}
statement := request.Statement
// In Redshift datashare, Rewrite query used for parser.
if database.Metadata.GetDatashare() {
statement = strings.ReplaceAll(statement, fmt.Sprintf("%s.", database.DatabaseName), "")
}
// requireExport=true narrows the CEL filter to grants with export=true,
// so a tied unmask-only grant can't shadow a separately-active export
// grant via slice order (PR #20491 bot review).
accessGrant := s.preCheckAccess(ctx, request.Statement, instance, database, request.Schema, request.GetContainer(), true /* requireExport */)
// The validation below admits a write on MySQL and on an engine with no
// validator, and the driver runs it.
if mcpSession(ctx) && !mcpStatementIsRead(ctx, instance.Metadata.GetEngine(), statement) {
return nil, permissionDeniedError(ctx, errors.Errorf("an MCP session exports only statements Bytebase can show are reads. %s", mcpConsoleWayOut))
}
// Validate the request.
// New query ACL experience.
if instance.Metadata.GetEngine() != storepb.Engine_MYSQL {
if err := validateQueryRequest(instance, statement); err != nil {
return nil, exportConnectError(err)
}
}
resolvedDataSourceID, err := resolveDataSourceID(ctx, instance, request.DataSourceId, statement, false)
if err != nil {
return nil, err
}
dataSource, err := checkAndGetDataSourceQueriable(ctx, s.store, s.licenseService, database, resolvedDataSourceID)
if err != nil {
return nil, err
}
sharePointTarget, err := s.prepareExportDestination(ctx, request, database)
if err != nil {
return nil, err
}
// The JIT grant satisfies bb.sql.export only on its target databases, so
// span-level ACL still runs for every other database.
optionalAccessCheck := s.accessCheckWithGrant(accessGrant, true /* export */)
skipMasking := false
if accessGrant != nil {
skipMasking = accessGrant.Payload.Unmask
}
files, duration, exportErr := doExport(ctx, s.store, s.dbFactory, s.licenseService, request, user, instance, database, optionalAccessCheck, s.schemaSyncer, dataSource, skipMasking)
var exportResponse *v1pb.ExportResponse
if exportErr == nil {
exportResponse, exportErr = sendExport(ctx, sharePointTarget, user, database, files, request, exportTime)
}
s.createQueryHistory(instance, database, store.QueryHistoryTypeExport, statement, user.Email, duration, exportErr)
if exportErr != nil {
return nil, exportConnectError(exportErr)
}
if accessGrant != nil {
exportResponse.AppliedAccessGrant = common.FormatAccessGrant(accessGrant.ProjectID, accessGrant.ID)
}
return connect.NewResponse(exportResponse), nil
}
// exportConnectError returns the connect error Export answers with. A refusal
// from the read-only or access check arrives as a queryError, which errors.As
// cannot see through, so its own connect error (such as PERMISSION_DENIED) is
// returned.
func exportConnectError(err error) *connect.Error {
var connectErr *connect.Error
var qe *queryError
if errors.As(err, &qe) && errors.As(qe.err, &connectErr) {
return connectErr
}
if errors.As(err, &connectErr) {
return connectErr
}
return connect.NewError(connect.CodeInternal, err)
}
// doExport runs an SQL Editor export with masking applied and returns its
// statement and result files.
func doExport(
ctx context.Context,
stores *store.Store,
dbFactory *dbfactory.DBFactory,
licenseService *enterprise.LicenseService,
request *v1pb.ExportRequest,
user *store.UserMessage,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
optionalAccessCheck accessCheckFunc,
schemaSyncer *schemasync.Syncer,
dataSource *storepb.DataSource,
skipMasking bool,
) ([]sharepoint.File, time.Duration, error) {
if dataSource == nil {
return nil, 0, connect.NewError(connect.CodeNotFound, errors.Errorf("cannot found valid data source"))
}
driver, err := dbFactory.GetDataSourceDriver(ctx, instance, dataSource, db.ConnectionContext{
DatabaseName: database.DatabaseName,
DataShare: database.Metadata.GetDatashare(),
ReadOnly: true,
})
if err != nil {
return nil, 0, connect.NewError(connect.CodeInternal, errors.Errorf("failed to get database driver: %v", err))
}
defer driver.Close(ctx)
sqlDB := driver.GetDB()
var conn *sql.Conn
if sqlDB != nil {
conn, err = sqlDB.Conn(ctx)
if err != nil {
return nil, 0, err
}
defer conn.Close()
}
queryRestriction := getEffectiveQueryDataPolicy(
ctx,
stores,
licenseService,
request.Limit,
database.ProjectID,
)
queryContext := buildExportQueryContext(queryRestriction, user.Email, request.Schema, request.GetContainer(), skipMasking)
queryContext.DataSourceType = dataSource.GetType()
// Split the statement for span analysis
statements, err := parserbase.SplitMultiSQL(instance.Metadata.GetEngine(), request.Statement)
if err != nil {
// Fall back to single statement for engines without splitter support
statements = []parserbase.Statement{{Text: request.Statement}}
}
// queryRetry masks the results.
results, _, duration, queryErr := queryRetry(
ctx,
stores,
user,
instance,
database,
driver,
conn,
statements,
request.Statement,
queryContext,
licenseService,
optionalAccessCheck,
schemaSyncer,
hasMultipleStatements(statements),
)
if queryErr != nil {
return nil, duration, queryErr
}
files, err := buildExportFiles(ctx, stores, instance, database, results, request)
if err != nil {
return nil, duration, err
}
return files, duration, nil
}
// formatExportResult writes a query result in the requested format.
func formatExportResult(
ctx context.Context,
writer io.Writer,
stores *store.Store,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
result *v1pb.QueryResult,
request *v1pb.ExportRequest,
) error {
switch request.Format {
case v1pb.ExportFormat_CSV:
return export.CSVToWriter(writer, result)
case v1pb.ExportFormat_JSON:
return export.JSONToWriter(writer, result)
case v1pb.ExportFormat_SQL:
return exportSQLWithContext(ctx, writer, stores, instance, database, result, request)
case v1pb.ExportFormat_XLSX:
return export.XLSXToWriter(writer, result)
default:
return errors.Errorf("unsupported export format: %s", request.Format.String())
}
}
// exportSQLWithContext exports SQL INSERT statements with proper context.
func exportSQLWithContext(
ctx context.Context,
w io.Writer,
stores *store.Store,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
result *v1pb.QueryResult,
request *v1pb.ExportRequest,
) error {
resourceList, err := export.GetResources(
ctx,
stores,
instance.Metadata.GetEngine(),
database.DatabaseName,
request.Statement,
instance,
parsercontext.BuildGetDatabaseMetadataFunc(stores),
parsercontext.BuildListDatabaseNamesFunc(stores),
parsercontext.BuildGetLinkedDatabaseMetadataFunc(stores, instance.Metadata.GetEngine()),
)
if err != nil {
return errors.Wrapf(err, "failed to extract resource list")
}
statementPrefix, err := export.SQLStatementPrefix(instance.Metadata.GetEngine(), resourceList, result.ColumnNames)
if err != nil {
return err
}
return export.SQLToWriter(w, instance.Metadata.GetEngine(), statementPrefix, result)
}
func (s *SQLService) createQueryHistory(instance *store.InstanceMessage, database *store.DatabaseMessage, queryType store.QueryHistoryType, statement string, userEmail string, duration time.Duration, queryErr error) {
qh := &store.QueryHistoryMessage{
Creator: userEmail,
Project: database.ProjectID,
Database: formatDatabaseResourceName(instance, database),
Statement: statement,
Type: queryType,
Payload: &storepb.QueryHistoryPayload{
Error: nil,
Duration: durationpb.New(duration),
},
}
if queryErr != nil {
qh.Payload.Error = new(queryErr.Error())
}
// Use a fresh context with timeout for creating query history
// to avoid being affected by request cancellation
historyCtx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()
if _, err := s.store.CreateQueryHistory(historyCtx, qh); err != nil {
queryErr := ""
if v := qh.Payload.Error; v != nil {
queryErr = *v
}
slog.Error(
"failed to create query history",
log.BBError(err),
slog.String("instance", database.InstanceID),
slog.String("database", database.DatabaseName),
slog.String("project", database.ProjectID),
slog.String("query_error", queryErr),
)
}
}
// SearchQueryHistories is a deprecated delegating alias for
// QueryHistoryService.SearchQueryHistories, kept so gRPC/Connect callers of
// the old method name keep working during upgrade. It will be removed in a
// future release.
func (s *SQLService) SearchQueryHistories(ctx context.Context, req *connect.Request[v1pb.SearchQueryHistoriesRequest]) (*connect.Response[v1pb.SearchQueryHistoriesResponse], error) {
return s.queryHistoryService.SearchQueryHistories(ctx, req)
}
// ListQueryHistories is a deprecated delegating alias for
// QueryHistoryService.ListQueryHistories, kept so gRPC/Connect callers of
// the old method name keep working during upgrade. It will be removed in a
// future release.
func (s *SQLService) ListQueryHistories(ctx context.Context, req *connect.Request[v1pb.ListQueryHistoriesRequest]) (*connect.Response[v1pb.ListQueryHistoriesResponse], error) {
return s.queryHistoryService.ListQueryHistories(ctx, req)
}
// GetQueryHistory is a deprecated delegating alias for
// QueryHistoryService.GetQueryHistory, kept so gRPC/Connect callers of the
// old method name keep working during upgrade. It will be removed in a
// future release.
func (s *SQLService) GetQueryHistory(ctx context.Context, req *connect.Request[v1pb.GetQueryHistoryRequest]) (*connect.Response[v1pb.QueryHistory], error) {
return s.queryHistoryService.GetQueryHistory(ctx, req)
}
// The Build*Func parser-context helpers moved to
// `backend/component/parsercontext` so the runner layer can use them
// without an import cycle. See parsercontext.BuildGetDatabaseMetadataFunc,
// BuildListDatabaseNamesFunc, BuildGetLinkedDatabaseMetadataFunc.
// accessCheckWithGrant returns the per-statement access check for Query, or for
// Export when export is true. accessGrant, which may be nil, applies only to its
// target databases; every other source database keeps its span-level IAM check,
// which blocks cross-database exfiltration like `SELECT * FROM db_b.t` through
// a grant on `db_a`. Cross-project resources are not supported.
func (s *SQLService) accessCheckWithGrant(accessGrant *store.AccessGrantMessage, export bool) accessCheckFunc {
var grantedTargets map[string]struct{}
if accessGrant != nil && accessGrant.Payload != nil {
grantedTargets = make(map[string]struct{}, len(accessGrant.Payload.Targets))
for _, t := range accessGrant.Payload.Targets {
grantedTargets[t] = struct{}{}
}
}
return func(ctx context.Context, instance *store.InstanceMessage, database *store.DatabaseMessage, user *store.UserMessage, spans []*parserbase.QuerySpan, isExplain bool, statements []parserbase.Statement, requestSchema string, multiStatement bool) error {
return s.accessCheckWithGrantedTargets(ctx, instance, database, user, spans, isExplain, statements, grantedTargets, export, requestSchema, multiStatement)
}
}
// accessCheckWithGrantedTargets authorizes each statement span. For Export,
// reading data, including info-schema output, requires bb.sql.export in place
// of bb.sql.select or bb.sql.info; explain, DML and DDL keep their permissions.
func (s *SQLService) accessCheckWithGrantedTargets(
ctx context.Context,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
user *store.UserMessage,
spans []*parserbase.QuerySpan,
isExplain bool,
statements []parserbase.Statement,
grantedTargets map[string]struct{},
export bool,
requestSchema string,
multiStatement bool,
) error {
project, err := s.store.GetProject(ctx, &store.FindProjectMessage{Workspace: common.GetWorkspaceIDFromContext(ctx), ResourceID: &database.ProjectID})
if err != nil {
return err
}
if project == nil {
return connect.NewError(connect.CodeInvalidArgument, errors.Errorf("project %q not found", database.ProjectID))
}
workspacePolicy, err := s.store.GetWorkspaceIamPolicy(ctx, common.GetWorkspaceIDFromContext(ctx))
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Wrapf(err, "failed to get workspace iam policy"))
}
projectPolicy, err := s.store.GetProjectIamPolicy(ctx, common.GetWorkspaceIDFromContext(ctx), project.ResourceID)
if err != nil {
return connect.NewError(connect.CodeInternal, errors.New(err.Error()))
}
readPermission := func(perm permission.Permission) permission.Permission {
if export && (perm == permission.SQLSelect || perm == permission.SQLInfo) {
return permission.SQLExport
}
return perm
}
// grantSatisfies reports whether the access grant satisfies perm on target.
// A grant approves one read-only statement on its target databases: for
// Export it satisfies bb.sql.export, and for Query the statement's own
// permission. Reading the statement's plan is a separate capability, so
// bb.sql.explain always comes from IAM.
grantSatisfies := func(target string, perm permission.Permission) bool {
if _, granted := grantedTargets[target]; !granted {
return false
}
if export {
return perm == permission.SQLExport
}
return perm != permission.SQLExplain
}
checkDatabaseAccess := func(perm permission.Permission) error {
databaseFullName := formatDatabaseResourceName(instance, database)
if grantSatisfies(databaseFullName, perm) {
return nil
}
attributes := map[string]any{
common.CELAttributeRequestTime: time.Now(),
common.CELAttributeResourceDatabase: databaseFullName,
}
env := ""
if database.EffectiveEnvironmentID != nil {
env = *database.EffectiveEnvironmentID
}
attributes[common.CELAttributeResourceEnvironmentID] = env
ok, err := s.hasDatabaseAccessRights(
ctx,
user,
perm,
attributes,
workspacePolicy.Policy,
projectPolicy.Policy,
)
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to check access control for database: %q, error %v", databaseFullName, err))
}
if !ok {
// queryError does not implement Unwrap, and Query reports a refusal
// inside its response rather than as an RPC error, so connect.CodeOf
// cannot see this verdict. The mark is the only way it reaches the
// audit interceptor.
setPermissionDenied(ctx)
return &queryError{
err: connect.NewError(
connect.CodePermissionDenied,
errors.Errorf("permission denied to access resources: %v", databaseFullName),
),
resources: []string{databaseFullName},
permission: perm,
}
}
return nil
}
// No spans: an engine without a span extractor. Check at the database level,
// with the explain permission for an explain request.
if len(spans) == 0 {
perm := readPermission(permission.SQLSelect)
if isExplain {
perm = permission.SQLExplain
}
return checkDatabaseAccess(perm)
}
// Spans align with non-empty statements (GetQuerySpan skips Empty statements).
nonEmptyStatements := make([]parserbase.Statement, 0, len(statements))
for _, st := range statements {
if !st.Empty {
nonEmptyStatements = append(nonEmptyStatements, st)
}
}
for i, span := range spans {
var perm permission.Permission
// New query ACL experience.
if common.EngineSupportQueryNewACL(instance.Metadata.GetEngine()) {
switch span.Type {
case parserbase.QueryTypeUnknown:
return connect.NewError(connect.CodePermissionDenied, errors.New("disallowed query type"))
case parserbase.DDL:
perm = permission.SQLDdl
case parserbase.DML:
perm = permission.SQLDml
case parserbase.Explain:
perm = permission.SQLExplain
case parserbase.SelectInfoSchema:
perm = permission.SQLInfo
case parserbase.Select:
perm = permission.SQLSelect
default:
}
} else if span.Type == parserbase.Select {
perm = permission.SQLSelect
}
if perm == "" {
// always fallback to bb.sql.select
perm = permission.SQLSelect
}
perm = readPermission(perm)
// A plan names the tables it reads, so reading one needs bb.sql.explain on
// every database the statement references, not only the connected one.
// A plain EXPLAIN, typed or requested, executes nothing and is done here;
// an explain that executes is checked for what it executes, below.
if isExplain || perm == permission.SQLExplain || (span.ExplainAnalyze && common.EngineSupportQueryNewACL(instance.Metadata.GetEngine())) {
if err := s.checkExplainAccess(ctx, user, instance, database, span, workspacePolicy.Policy, projectPolicy.Policy); err != nil {
return err
}
// Elasticsearch states a plan only for a search it ran: an explain
// request there profiles the search, so it reads what the search
// reads and needs that permission too, as EXPLAIN ANALYZE does.
explainRuns := span.ExplainAnalyze ||
(isExplain && instance.Metadata.GetEngine() == storepb.Engine_ELASTICSEARCH)
if !explainRuns {
continue
}
}
// DML/DDL: authorize per write-target so table/schema-scoped grants work. Resolve the
// write targets of the statement aligned with this span (spans skip Empty statements, so
// nonEmptyStatements is indexed in lockstep).
//
// A single statement is authorized at full per-(schema,table) precision. A multi-statement
// batch can rebind the session's default schema (SET search_path) or database (USE) mid-
// batch, so an unqualified write's schema/table identity is unreliable; such a batch is
// authorized at the DATABASE level only. Crucially that database-level check still runs
// against each TARGET database, not the request database: a qualified cross-database write
// (e.g. MySQL `INSERT INTO other_db.t`) is not rebindable, so a grant on the request
// database must not authorize it. SUP-222 / BYT-9698.
if perm == permission.SQLDml || perm == permission.SQLDdl {
stmtText := ""
if i < len(nonEmptyStatements) {
stmtText = nonEmptyStatements[i].Text
}
targets, err := s.resolveWriteTargets(ctx, instance, database, stmtText, requestSchema)
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to resolve write targets: %v", err))
}
if len(targets) == 0 {
// No resolvable table target (non-table DDL, unsupported engine, parse failure) →
// database-level check on the request database.
if err := checkDatabaseAccess(perm); err != nil {
return err
}
continue
}
databaseLevelOnly := multiStatement || len(nonEmptyStatements) != 1
writeGrantedTargets := grantedTargets
if export {
// An export grant satisfies only bb.sql.export; see grantSatisfies.
writeGrantedTargets = nil
}
deniedResources, err := s.authorizeWriteTargets(ctx, user, instance, database, perm, targets, writeGrantedTargets, databaseLevelOnly, workspacePolicy.Policy, projectPolicy.Policy)
if err != nil {
return err
}
if len(deniedResources) > 0 {
setPermissionDenied(ctx)
return &queryError{
err: connect.NewError(
connect.CodePermissionDenied,
errors.Errorf("permission denied to access resources: %v", deniedResources),
),
resources: deniedResources,
permission: perm,
}
}
continue
}
// For non-SELECT queries or SELECT queries with no source columns (e.g., SELECT 1),
// check at database level and skip column-level checks
if span.Type != parserbase.Select || len(span.SourceColumns) == 0 {
if err := checkDatabaseAccess(perm); err != nil {
return err
}
continue
}
// Oracle database links. Only a Select span carries linked columns, and a link never
// makes it SelectInfoSchema (plsql getOmniQuerySpan). A DML, DDL or EXPLAIN PLAN span has
// no source columns, so a link it reads through is not checked here (BOT-133).
// Before the JIT-grant skip below: an unresolved link can carry a local
// database name (ALLOWED_S.T@REMOTE2), which a grant on that database would otherwise
// authorize. A SQL Server linked-server reference keeps its pre-existing path (BYT-10235).
if instance.Metadata.GetEngine() == storepb.Engine_ORACLE {
if qe := remoteColumnRefusal(ctx, span.SourceColumns, instance.ResourceID, perm); qe != nil {
return qe
}
if qe, err := s.refuseLinkedTargetsOutsideProject(ctx, span.SourceColumns, instance, database, perm); err != nil {
return err
} else if qe != nil {
return qe
}
}
var deniedResources []string
for column := range span.SourceColumns {
columnDatabaseFullName := formatDatabaseResourceName(instance, &store.DatabaseMessage{
InstanceID: instance.ResourceID,
DatabaseName: column.Database,
})
grantTargetName := formatDatabaseResourceName(instance, &store.DatabaseMessage{
ProjectID: database.ProjectID,
InstanceID: instance.ResourceID,
DatabaseName: column.Database,
})
if grantSatisfies(grantTargetName, perm) {
continue
}
attributes := map[string]any{
common.CELAttributeRequestTime: time.Now(),
common.CELAttributeResourceDatabase: columnDatabaseFullName,
common.CELAttributeResourceSchemaName: column.Schema,
common.CELAttributeResourceTableName: column.Table,
}
ok, err := s.hasDatabaseAccessRights(
ctx,
user,
perm,
attributes,
workspacePolicy.Policy,
projectPolicy.Policy,
)
if err != nil {
return connect.NewError(connect.CodeInternal, errors.Errorf("failed to check access control for database: %q, error %v", column.Database, err))
}
if !ok {
resource, ok := attributes[common.CELAttributeResourceDatabase].(string)
if !ok {
resource = ""
}
if schema, ok := attributes[common.CELAttributeResourceSchemaName]; ok && schema != "" {
resource = fmt.Sprintf("%s/schemas/%s", resource, schema)
}
if table, ok := attributes[common.CELAttributeResourceTableName]; ok && table != "" {
resource = fmt.Sprintf("%s/tables/%s", resource, table)
}
deniedResources = append(deniedResources, resource)
}
}
if len(deniedResources) > 0 {
setPermissionDenied(ctx)
return &queryError{
err: connect.NewError(
connect.CodePermissionDenied,
errors.Errorf("permission denied to access resources: %v", deniedResources),
),
resources: deniedResources,
permission: perm,
}
}
}
return nil
}
// checkExplainAccess requires bb.sql.explain on every database a plan of the
// span's statement would describe: the connected database and each tracked
// database the statement's tables live in, authorized like a write's targets.
// Access grants do not apply; they convey data access, not plan access. What
// the span cannot place in a tracked database — a procedure call that names no
// table, a system schema such as Oracle's SYS — is left to the connected
// database's check.
func (s *SQLService) checkExplainAccess(
ctx context.Context,
user *store.UserMessage,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
span *parserbase.QuerySpan,
workspacePolicy *storepb.IamPolicy,
projectPolicy *storepb.IamPolicy,
) error {
targets := []writeTargetResource{{database: database.DatabaseName}}
var referenced []string
for column := range span.SourceColumns {
if column.Database != "" && column.Database != database.DatabaseName {
referenced = append(referenced, column.Database)
}
}
slices.Sort(referenced)
for _, name := range slices.Compact(referenced) {
tracked, err := s.findDatabaseAsTyped(ctx, instance, name)
if err != nil {
return err
}
if tracked != nil {
targets = append(targets, writeTargetResource{database: tracked.DatabaseName})
}
}
deniedResources, err := s.authorizeWriteTargets(ctx, user, instance, database, permission.SQLExplain, targets, nil, true, workspacePolicy, projectPolicy)
if err != nil {
return err
}
if len(deniedResources) > 0 {
setPermissionDenied(ctx)
return &queryError{
err: connect.NewError(
connect.CodePermissionDenied,
errors.Errorf("permission denied to access resources: %v", deniedResources),
),
resources: deniedResources,
permission: permission.SQLExplain,
}
}
return nil
}
// findDatabaseAsTyped resolves a database name as a statement spells it. An
// engine that folds case accepts DB_B for db_b, so there the name is matched
// case-insensitively against the instance's databases. nil means Bytebase does
// not track the name.
func (s *SQLService) findDatabaseAsTyped(ctx context.Context, instance *store.InstanceMessage, name string) (*store.DatabaseMessage, error) {
workspace := common.GetWorkspaceIDFromContext(ctx)
database, err := s.store.GetDatabase(ctx, &store.FindDatabaseMessage{Workspace: workspace, InstanceID: &instance.ResourceID, DatabaseName: &name})
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to resolve database %q: %v", name, err))
}
if database != nil || store.IsObjectCaseSensitive(instance) {
return database, nil
}
databases, err := s.store.ListDatabases(ctx, &store.FindDatabaseMessage{Workspace: workspace, InstanceID: &instance.ResourceID})
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to list databases of %q: %v", instance.ResourceID, err))
}
for _, candidate := range databases {
if strings.EqualFold(candidate.DatabaseName, name) {
return candidate, nil
}
}
return nil, nil
}
// authorizeWriteTargets evaluates perm for each resolved DML/DDL write target and returns the
// resources that were denied (empty = all authorized). A target whose database is already
// JIT-granted is skipped. checkExplainAccess reuses it, at the database level, for the
// databases a plan describes.
//
// databaseLevelOnly selects the precision of the check. For a single statement it is false and
// each target is authorized at full per-(schema,table) precision. For a multi-statement batch it
// is true: an earlier statement can rebind the session's default schema (SET search_path) or
// database (USE), so per-(schema,table) identity is unreliable and only the TARGET database is
// authorized (each distinct database once) — schema/table attributes are omitted so a
// schema/table-scoped grant fails closed, and a denial names the bare database.
//
// resource.schema_name (full-precision mode only) is asserted ONLY when the target schema is
// known: an empty schema (the sentinel mapped to "") omits the attribute, so a schema-scoped
// grant fails closed (CEL errors on the missing attribute → the binding is skipped) while a
// table-only grant still matches. A present-but-empty value would instead let a negated condition
// (schema_name != "x") wrongly match.
//
// resource.environment_id is always set, and is resolved PER TARGET database: a cross-database
// write (e.g. MySQL/MSSQL `INSERT INTO other_db.t`) targets a database that may live in a
// different environment than the one the editor opened, and resource.database already names that
// target — copying the request database's environment (or authorizing against the request
// database at all) would let a grant on the request database authorize a write into another
// database/environment. The request database needs no lookup (the common same-database case);
// other targets are fetched once and cached, and an unknown target fails closed. The attribute is
// always present because hasDatabaseAccessRights keeps the environment clause for SQLDml/SQLDdl,
// so omitting it would reintroduce a "no such attribute" error. SUP-222 / BYT-9698.
func (s *SQLService) authorizeWriteTargets(
ctx context.Context,
user *store.UserMessage,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
perm permission.Permission,
targets []writeTargetResource,
grantedTargets map[string]struct{},
databaseLevelOnly bool,
iamPolicies ...*storepb.IamPolicy,
) ([]string, error) {
// One timestamp per request so every target evaluates request.time consistently.
requestTime := time.Now()
// environment_id of the request database, reused without a lookup for same-database targets.
envByDatabase := map[string]string{database.DatabaseName: ""}
if database.EffectiveEnvironmentID != nil {
envByDatabase[database.DatabaseName] = *database.EffectiveEnvironmentID
}
checkedDatabase := map[string]bool{} // databaseLevelOnly: authorize each target database once
var deniedResources []string
for _, t := range targets {
databaseFullName := formatDatabaseResourceName(instance, &store.DatabaseMessage{
InstanceID: instance.ResourceID,
DatabaseName: t.database,
})
grantTargetName := formatDatabaseResourceName(instance, &store.DatabaseMessage{
ProjectID: database.ProjectID,
InstanceID: instance.ResourceID,
DatabaseName: t.database,
})
if _, granted := grantedTargets[grantTargetName]; granted {
continue
}
// A target with no table name is a database-only target: DDL on a non-table object
// (view/procedure/function/trigger/…) whose only auth-relevant identity is its database.
// Authorize it at the database level — like a multi-statement batch — so a qualified
// cross-database object DDL is gated by its own database, not the request database, with
// no spurious table-name match. SUP-222 / BYT-9698.
useDatabaseLevel := databaseLevelOnly || t.table == ""
if useDatabaseLevel {
if checkedDatabase[t.database] {
continue
}
checkedDatabase[t.database] = true
}
// A denial names the bare database for a database-level check, else the schema/table target.
deniedResource := databaseFullName
if !useDatabaseLevel {
deniedResource = formatWriteTargetResource(databaseFullName, t.schema, t.table)
}
env, known := envByDatabase[t.database]
if !known {
targetName := t.database
targetDB, err := s.store.GetDatabase(ctx, &store.FindDatabaseMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
InstanceID: &instance.ResourceID,
DatabaseName: &targetName,
})
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to resolve target database %q: %v", t.database, err))
}
if targetDB == nil {
// Unknown target database → its environment can't be established → fail closed.
deniedResources = append(deniedResources, deniedResource)
continue
}
if targetDB.ProjectID != database.ProjectID {
// A write target in a DIFFERENT project than the SQL-Editor session must be gated
// by that target project's IAM policy, which this check (evaluating the request
// project's policy) does not consult. Fail closed to preserve the project
// boundary — a cross-project write is denied. SUP-222 / BYT-9698.
deniedResources = append(deniedResources, deniedResource)
continue
}
if targetDB.EffectiveEnvironmentID != nil {
env = *targetDB.EffectiveEnvironmentID
}
envByDatabase[t.database] = env
}
attributes := writeTargetAttributes(requestTime, databaseFullName, env, t, useDatabaseLevel)
allowed, err := s.hasDatabaseAccessRights(ctx, user, perm, attributes, iamPolicies...)
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to check access control for target %q: %v", deniedResource, err))
}
if !allowed {
deniedResources = append(deniedResources, deniedResource)
}
}
return deniedResources, nil
}
// writeTargetAttributes returns the attributes a grant condition is evaluated against
// for write target t; a database-level check carries no table or schema.
func writeTargetAttributes(requestTime time.Time, databaseFullName, env string, t writeTargetResource, databaseLevel bool) map[string]any {
attributes := map[string]any{
common.CELAttributeRequestTime: requestTime,
common.CELAttributeResourceDatabase: databaseFullName,
common.CELAttributeResourceEnvironmentID: env,
}
if !databaseLevel {
attributes[common.CELAttributeResourceTableName] = t.table
if t.schema != "" {
attributes[common.CELAttributeResourceSchemaName] = t.schema
}
}
return attributes
}
// formatWriteTargetResource renders a denied write target as
// instances/i/databases/d[/schemas/s]/tables/t; the schema segment is omitted when unknown.
func formatWriteTargetResource(databaseFullName, schema, table string) string {
resource := databaseFullName
if schema != "" {
resource = fmt.Sprintf("%s/schemas/%s", resource, schema)
}
return fmt.Sprintf("%s/tables/%s", resource, table)
}
// writeTargetResource is a (database, schema, table) write target of a DML/DDL statement.
type writeTargetResource struct {
database string
schema string
table string
}
// resolveWriteTargets returns the tables a DML/DDL statement writes/changes, using
// the same extractor plan-check trusts. Returns (nil, nil) when the engine has no
// extractor, a statement can't be parsed or read, or no targets are found — callers must
// then fall back to the database-level check (never fail open). See SUP-222.
//
// Some engines' extractors report auxiliary tables referenced by the change (e.g.
// the joined/using tables of an UPDATE … JOIN … / DELETE … USING …) alongside the
// mutated table, others only the primary target; either way this can only ever
// over-restrict (fail-closed), never under-restrict. The exception is a SQL Server target
// left as written (BOT-158, BOT-160): it is checked as a table of that name in the request
// database.
//
// DDL on a non-table object (view/procedure/function/trigger/…) has no table to scope. When
// such DDL names an EXPLICIT target database (e.g. CREATE VIEW other_db.v), the extractor
// records a database-only target so the write is gated by that database; an UNqualified one
// records nothing and falls back to the request-database check. Object DDL the extractor does
// not model still resolves to zero targets → request-database fallback.
func (s *SQLService) resolveWriteTargets(
ctx context.Context,
instance *store.InstanceMessage,
database *store.DatabaseMessage,
statement string,
requestSchema string,
) ([]writeTargetResource, error) {
engine := instance.Metadata.GetEngine()
stmts, err := parserbase.ParseStatements(engine, statement)
if err != nil {
//nolint:nilerr // unparseable here → fall back to database-level check (already parsed once for the span)
return nil, nil
}
asts := parserbase.ExtractASTs(stmts)
if len(asts) == 0 {
return nil, nil
}
// Cache-first: GetQuerySpan already fetched and cached this database's metadata earlier in
// the same request, so reuse it instead of issuing a second store query on the write path.
dbMeta, err := s.store.GetDBSchemaSnapshot(ctx, common.GetWorkspaceIDFromContext(ctx), database.InstanceID, database.DatabaseName)
if err != nil {
return nil, err
}
if dbMeta == nil {
return nil, nil
}
targetSchema := schemaForWriteTargetResolution(engine, database.DatabaseName, requestSchema)
if engine == storepb.Engine_POSTGRES {
targetSchema = postgresWriteTargetSchemaForRequest(requestSchema, dbMeta)
}
changeSummary, err := parserbase.ExtractChangedResources(
engine, database.DatabaseName, targetSchema, dbMeta, asts, statement,
)
if err != nil {
//nolint:nilerr // unsupported engine → fall back to database-level check
return nil, nil
}
return writeTargetsFromChangeSummary(changeSummary), nil
}
// writeTargetsFromChangeSummary returns the write targets changeSummary records. It returns
// nil when there are none, or when the extractor skipped a statement, which may write any table.
func writeTargetsFromChangeSummary(changeSummary *parserbase.ChangeSummary) []writeTargetResource {
if changeSummary == nil || changeSummary.ChangedResources == nil || changeSummary.SkippedStatements > 0 {
return nil
}
var targets []writeTargetResource
databasesWithTableTarget := map[string]bool{}
for _, db := range changeSummary.ChangedResources.Build().GetDatabases() {
for _, sc := range db.GetSchemas() {
schema := sc.GetName()
// An unqualified target whose executed schema can't be determined here resolves
// to the sentinel; treat it as unknown ("") so the schema_name attribute is
// omitted (fail-closed for schema-scoped grants, still matches table-only ones).
if schema == unresolvedSchemaSentinel {
schema = ""
}
for _, tbl := range sc.GetTables() {
databasesWithTableTarget[db.GetName()] = true
targets = append(targets, writeTargetResource{
database: db.GetName(),
schema: schema,
table: tbl.GetName(),
})
}
}
}
// Database-only targets (Tier 2): a qualified non-table object DDL (e.g. CREATE VIEW
// other_db.v) records only its target database, with no table. It is authorized at the
// database level against that database (a table-less target → useDatabaseLevel in
// authorizeWriteTargets), so a qualified cross-database object DDL is gated by its own
// database, not the request database. Skip any database already covered by a (more precise)
// table target. See SUP-222 / BYT-9698.
for _, db := range changeSummary.ChangedResources.GetDatabaseOnlyTargets() {
if databasesWithTableTarget[db] {
continue
}
targets = append(targets, writeTargetResource{database: db})
}
return targets
}
// unresolvedSchemaSentinel is an impossible schema name (it contains NUL, which no real
// PostgreSQL/MSSQL identifier can) used as the default schema for UNqualified write targets
// when the schema the engine will actually resolve cannot be known ahead of execution
// (Postgres's connection-user `$user` search_path, MSSQL's login default_schema). It
// propagates only to unqualified targets — qualified ones keep their explicit schema — and
// is mapped to "" so the resource.schema_name attribute is omitted before the grant check.
const unresolvedSchemaSentinel = "\x00bb_unresolved_schema\x00"
// schemaForWriteTargetResolution returns the default schema to resolve UNqualified write
// targets against, per engine — the schema the engine will actually write to, or the
// sentinel when that can't be determined here (so the schema_name attribute is omitted and
// schema-scoped grants fail closed). Qualified targets ignore this and keep their qualifier.
func schemaForWriteTargetResolution(engine storepb.Engine, databaseName, requestSchema string) string {
switch engine {
case storepb.Engine_POSTGRES:
// Execution uses QueryRequest.schema first when set; otherwise the connection
// user's default ($user, public) is not knowable here.
if requestSchema != "" {
return requestSchema
}
return unresolvedSchemaSentinel
case storepb.Engine_MSSQL:
// No per-request schema pin; execution uses the login's default_schema, which is not
// in synced metadata. Never trust a request schema for MSSQL.
return unresolvedSchemaSentinel
case storepb.Engine_ORACLE:
// Execution pins CURRENT_SCHEMA to the connection database (= schema) once at pool Open
// (oracle.go), and the SQL Editor opens a fresh pool per request, so an unqualified write
// resolves to databaseName — knowable and execution-accurate. (A mid-batch ALTER SESSION
// is covered by the multi-statement fallback.) This pin is the reason the schema is
// auth-grade here; if the driver ever stops pinning per-connection, switch to the sentinel.
return databaseName
case storepb.Engine_MYSQL, storepb.Engine_TIDB:
// No schema layer; the extractor emits an empty schema and resource.schema_name is omitted.
return ""
default:
// Fail closed for any other engine. An engine that reaches here without an explicit,
// execution-verified schema mapping must NOT have its unqualified writes resolved against
// a guessed default — e.g. a pg-family extractor (COCKROACHDB, REDSHIFT) would fill in the
// metadata search_path, a REAL schema name, which we'd then assert as resource.schema_name
// and over-allow a schema-scoped grant. The sentinel maps to an omitted attribute, so
// schema-scoped grants fail closed while table-only grants still match. SUP-222 / BYT-9698.
return unresolvedSchemaSentinel
}
}
func postgresWriteTargetSchemaForRequest(requestSchema string, dbMeta *model.DatabaseMetadata) string {
if requestSchema == "" {
return unresolvedSchemaSentinel
}
if dbMeta.GetSchemaMetadata(requestSchema) != nil {
return requestSchema
}
return unresolvedSchemaSentinel
}
// sanitizeResults sanitizes the strings in the results by replacing all the invalid UTF-8 characters with its hexadecimal representation.
func sanitizeResults(results []*v1pb.QueryResult) {
for _, result := range results {
for _, row := range result.GetRows() {
for _, value := range row.GetValues() {
if value != nil {
if value, ok := value.Kind.(*v1pb.RowValue_StringValue); ok {
value.StringValue = common.SanitizeUTF8String(value.StringValue)
}
}
}
}
}
}
func (s *SQLService) prepareRelatedMessage(ctx context.Context, requestName string) (*store.UserMessage, *store.InstanceMessage, *store.DatabaseMessage, error) {
user, err := s.getUser(ctx)
if err != nil {
return nil, nil, nil, connect.NewError(connect.CodeInternal, errors.New(err.Error()))
}
targetProjectID, instanceID, databaseName, err := common.GetDatabaseResourceName(requestName)
if err != nil {
return nil, nil, nil, connect.NewError(connect.CodeInvalidArgument, errors.Wrapf(err, "failed to parse %q", requestName))
}
database, err := s.store.GetDatabase(ctx, &store.FindDatabaseMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
InstanceID: &instanceID,
DatabaseName: &databaseName,
})
if err != nil {
return nil, nil, nil, connect.NewError(connect.CodeInternal, errors.Wrapf(err, "failed to get database"))
}
if database == nil {
return nil, nil, nil, connect.NewError(connect.CodeNotFound, errors.Errorf("database %q not found", requestName))
}
if targetProjectID != nil && database.ProjectID != *targetProjectID {
return nil, nil, nil, connect.NewError(connect.CodeNotFound, errors.Errorf("database %q not found in project %q", requestName, *targetProjectID))
}
project, err := s.store.GetProject(ctx, &store.FindProjectMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
ResourceID: &database.ProjectID,
})
if err != nil {
return nil, nil, nil, connect.NewError(connect.CodeInternal, errors.Wrapf(err, "failed to get project %q", database.ProjectID))
}
if project == nil || project.Deleted {
return nil, nil, nil, connect.NewError(connect.CodeNotFound, errors.Errorf("project %q not found", database.ProjectID))
}
instance, err := s.store.GetInstance(ctx, &store.FindInstanceMessage{
Workspace: common.GetWorkspaceIDFromContext(ctx),
ResourceID: &database.InstanceID,
})
if err != nil {
return nil, nil, nil, connect.NewError(connect.CodeInternal, errors.New(err.Error()))
}
if instance == nil {
return nil, nil, nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q not found", database.InstanceID))
}
if instance.Deleted {
return nil, nil, nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q not found", database.InstanceID))
}
if (targetProjectID == nil) != (instance.ProjectID == nil) ||
targetProjectID != nil && *targetProjectID != *instance.ProjectID {
return nil, nil, nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("database name %q is not canonical for its instance", requestName))
}
return user, instance, database, nil
}
// validateExplainFormat refuses a format the driver cannot produce. Drivers
// map whatever reaches them onto their own syntax, so a request that slipped
// through would silently come back in a format the caller cannot parse.
func validateExplainFormat(engine storepb.Engine, format v1pb.QueryOption_ExplainFormat) error {
supported := db.SupportedExplainFormats(engine)
// An engine with no explain at all is refused whatever the caller asked for,
// including nothing. Its driver would otherwise run the statement as an
// ordinary query — and an explain request skips the read-only validation
// above, on the understanding that the driver turns the statement into a
// plan.
if len(supported) == 0 {
return connect.NewError(connect.CodeInvalidArgument, errors.Errorf("%s does not support EXPLAIN", engine))
}
if format == v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED {
return nil
}
if slices.Contains(supported, format) {
return nil
}
names := make([]string, 0, len(supported))
for _, f := range supported {
names = append(names, f.String())
}
return connect.NewError(connect.CodeInvalidArgument, errors.Errorf("%s does not support explain format %s, supported formats: %s", engine, format, strings.Join(names, ", ")))
}
// validateExplainStatements refuses an explain request whose planned form would
// execute a write. The driver splits a multi-statement request and builds the
// EXPLAIN of each statement (see pg.go and its siblings), so this validates the
// same per-statement form built the same way: a smuggled "ANALYZE DELETE FROM t"
// is not a statement the parser can plan, and an EXPLAIN ANALYZE DELETE the caller
// wrote itself is rebuilt as a plain EXPLAIN or refused before it runs.
//
// An engine whose EXPLAIN is not a statement prefix (Oracle EXPLAIN PLAN, SQL
// Server SHOWPLAN, Spanner/BigQuery plan APIs) runs its own plan API, which does
// not execute the statement, and so has nothing registered to validate here.
func validateExplainStatements(instance *store.InstanceMessage, statement string, format v1pb.QueryOption_ExplainFormat) error {
engine := instance.Metadata.GetEngine()
if !parserbase.HasExplainStatement(engine) {
return nil
}
statements, err := parserbase.SplitMultiSQL(engine, statement)
if err != nil {
// No splitter for this engine: validate the whole statement planned once.
// Execution goes through the same splitter, so a request that fails to split
// here fails there too rather than executing.
statements = []parserbase.Statement{{Text: statement}}
}
for _, stmt := range statements {
if stmt.Empty {
continue
}
planned, err := parserbase.ExplainStatement(engine, stmt.Text, db.ExplainFormat(format))
if err != nil {
return connect.NewError(connect.CodeInvalidArgument, err)
}
if err := validateQueryRequest(instance, planned); err != nil {
return err
}
}
return nil
}
func validateQueryRequest(instance *store.InstanceMessage, statement string) error {
ok, _, err := parserbase.ValidateSQLForEditor(instance.Metadata.GetEngine(), statement)
if err != nil {
if syntaxErr, ok := err.(*parserbase.SyntaxError); ok {
err := connect.NewError(connect.CodeInvalidArgument, syntaxErr)
if detail, detailErr := connect.NewErrorDetail(&v1pb.PlanCheckRun_Result{
Code: int32(code.StatementSyntaxError),
Content: syntaxErr.Message,
Title: "Syntax error",
Status: v1pb.Advice_ERROR,
Report: &v1pb.PlanCheckRun_Result_SqlReviewReport_{
SqlReviewReport: &v1pb.PlanCheckRun_Result_SqlReviewReport{
StartPosition: convertToPosition(syntaxErr.Position),
},
},
}); detailErr == nil {
err.AddDetail(detail)
}
return err
}
return err
}
if !ok {
return &queryError{
err: connect.NewError(connect.CodeInvalidArgument, errors.New("Support read-only command statements only")),
commandType: v1pb.QueryResult_CommandError_NON_READ_ONLY,
}
}
return nil
}
func (s *SQLService) hasDatabaseAccessRights(
ctx context.Context,
user *store.UserMessage,
perm permission.Permission,
attributes map[string]any,
iamPolicies ...*storepb.IamPolicy,
) (bool, error) {
bindings := utils.GetUserIAMPolicyBindings(ctx, s.store, common.GetWorkspaceIDFromContext(ctx), user, iamPolicies...)
for _, binding := range bindings {
permissions, err := s.iamManager.GetPermissions(ctx, common.GetWorkspaceIDFromContext(ctx), binding.Role)
if err != nil {
return false, errors.Wrapf(err, "failed to get permissions")
}
if !permissions[perm] {
continue
}
expression := binding.Condition.GetExpression()
// resource.environment_id only applies to DDL/DML permissions.
// For other permissions (e.g., SELECT), strip the environment condition
// to prevent it from incorrectly denying access.
if perm != permission.SQLDdl && perm != permission.SQLDml {
expression = stripEnvironmentCondition(expression)
}
ok, err := evaluateQueryExportPolicyCondition(expression, attributes)
if err != nil {
slog.Error("failed to evaluate condition", log.BBError(err), slog.String("condition", expression))
continue
}
if ok {
return true, nil
}
}
return false, nil
}
var envConditionRe = regexp.MustCompile(`resource\.environment_id\s+in\s+\[[^\]]*\]`)
// stripEnvironmentCondition removes the "resource.environment_id in [...]" clause
// from a CEL expression string.
func stripEnvironmentCondition(expression string) string {
result := envConditionRe.ReplaceAllString(expression, "")
result = strings.TrimSpace(result)
result = strings.TrimPrefix(result, "&&")
result = strings.TrimSuffix(result, "&&")
return strings.TrimSpace(result)
}
func (*SQLService) getUser(ctx context.Context) (*store.UserMessage, error) {
user, ok := GetUserFromContext(ctx)
if !ok {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("user not found"))
}
if user.MemberDeleted {
return nil, connect.NewError(connect.CodePermissionDenied, errors.Errorf("the user has been deactivated"))
}
return user, nil
}
func resolveDataSourceID(ctx context.Context, instance *store.InstanceMessage, dataSourceID string, statement string, allowAdminDataSource bool) (string, error) {
if dataSourceID != "" {
return dataSourceID, nil
}
return routeDataSource(instance, allowAdminDataSource, func() bool {
return requiresAdminDataSource(ctx, instance.Metadata.GetEngine(), statement)
})
}
var errMultipleReadOnlyDataSources = errors.New("instance has multiple read-only data sources, please specify data_source_id explicitly")
// routeDataSource picks the data source for a request that names none.
func routeDataSource(instance *store.InstanceMessage, allowAdminDataSource bool, needsAdmin func() bool) (string, error) {
var adminDataSourceID string
var readOnlyDataSourceID string
readOnlyCount := 0
for _, dataSource := range instance.Metadata.GetDataSources() {
switch dataSource.GetType() {
case storepb.DataSourceType_ADMIN:
adminDataSourceID = dataSource.GetId()
case storepb.DataSourceType_READ_ONLY:
readOnlyCount++
readOnlyDataSourceID = dataSource.GetId()
default:
}
}
if allowAdminDataSource && adminDataSourceID != "" && needsAdmin() {
return adminDataSourceID, nil
}
switch {
case readOnlyCount == 1:
return readOnlyDataSourceID, nil
case readOnlyCount > 1:
return "", connect.NewError(connect.CodeFailedPrecondition, errMultipleReadOnlyDataSources)
case adminDataSourceID != "":
return adminDataSourceID, nil
default:
return "", connect.NewError(connect.CodeFailedPrecondition, errors.New("instance has no admin data source"))
}
}
func requiresAdminDataSource(ctx context.Context, engine storepb.Engine, statement string) bool {
readOnly, _, err := parserbase.ValidateSQLForEditor(engine, statement)
if err != nil {
return false
}
if !readOnly {
return true
}
if !shouldClassifyStatementByQuerySpan(engine) {
return false
}
spans, err := getQuerySpansForStatement(ctx, engine, statement)
if err != nil {
return false
}
for _, span := range spans {
if span == nil {
continue
}
if span.Type == parserbase.DDL || span.Type == parserbase.DML {
return true
}
}
return false
}
func checkAndGetDataSourceQueriable(
ctx context.Context,
storeInstance *store.Store,
licenseService *enterprise.LicenseService,
database *store.DatabaseMessage,
dataSourceID string,
) (*storepb.DataSource, error) {
if dataSourceID == "" {
return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("data source id is required"))
}
instance, err := storeInstance.GetInstance(ctx, &store.FindInstanceMessage{Workspace: common.GetWorkspaceIDFromContext(ctx), ResourceID: &database.InstanceID})
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to get instance %v with error: %v", database.InstanceID, err.Error()))
}
if instance == nil {
return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q not found", database.InstanceID))
}
dataSource := func() *storepb.DataSource {
for _, ds := range instance.Metadata.GetDataSources() {
if ds.GetId() == dataSourceID {
return ds
}
}
return nil
}()
if dataSource == nil {
return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("data source %q not found", dataSourceID))
}
// Always allow non-admin data source.
if dataSource.GetType() != storepb.DataSourceType_ADMIN {
if err := licenseService.IsFeatureEnabledForInstance(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_INSTANCE_READ_ONLY_CONNECTION, instance); err != nil {
return nil, connect.NewError(connect.CodePermissionDenied, errors.New(err.Error()))
}
return dataSource, nil
}
//nolint:nilerr
if err := licenseService.IsFeatureEnabled(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_QUERY_POLICY); err != nil {
return dataSource, nil
}
queryDataPolicy, err := storeInstance.GetEffectiveQueryDataPolicy(ctx, common.GetWorkspaceIDFromContext(ctx), common.FormatProject(database.ProjectID))
if err != nil {
return nil, connect.NewError(connect.CodeInternal, errors.Errorf("failed to get query data policy with error: %v", err.Error()))
}
if queryDataPolicy.AllowAdminDataSource {
return dataSource, nil
}
ds := utils.DataSourceFromInstanceWithType(instance, storepb.DataSourceType_READ_ONLY)
if ds != nil {
return nil, connect.NewError(connect.CodePermissionDenied, errors.Errorf("data source %q is not queryable", dataSourceID))
}
return dataSource, nil
}