Azure / Azure/data-api-builder

[Bug]: DEFAULT values not applied if database user is no db_owner

Abierto
#1,360 2 comentarios 0 reacciones 1 asignado Ver en GitHub

@Aniruddh25 ya está trabajando en esto.

Desde el 10/4/2023.

bug mssql triage
Lenguaje dominante
C#
Estrellas
1.5k
Forks
372
Merge medio
3 d 22 h
PR fusionados (30 d)
9

Descripción

### What happened?

If the user used to connect to the database is not in the db_owner role, columns default values are not read. Here's the repo (on SQL Server).

Create user:

```sql
create login dab_read_only with password = 'XXXXX';
go
create user dab_read_only from login dab_read_only;
go
alter role db_datareader add member dab_read_only
alter role db_datawriter add member dab_read_only
go
```

Create table:

```sql
CREATE TABLE [dbo].[TestDefault]
(
[Id] [uniqueidentifier] NOT NULL,
[NoDefault] [nvarchar](1000) NOT NULL,
[Default1] [bit] NOT NULL,
[Default2] [varchar](128) NOT NULL,
[Default3] [int] NOT NULL,
[Default4] [datetime2](0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
[Default5] [int] NOT NULL DEFAULT(200),
)
GO
ALTER TABLE [dbo].[TestDefault] ADD PRIMARY KEY NONCLUSTERED
(
[id] ASC
)
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ((0)) FOR [Default1]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ('public') FOR [Default2]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT (100) FOR [Default3]
GO
```

Create the configuration file with the following commands:

```
dab init --database-type mssql --connection-string 'Server=tcp:localhost,1433;Initial Catalog=DAB_TestDefault;Persist Security Info=False;User ID=dab_read_only;Password=XXXXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;'
dab add TestDefault --source "dbo.TestDefault" --permissions "anonymous:*"
```

then start DAB and try to issue a POST request, for example:

```http
POST https://localhost:5001/api/TestDefault
content-type: application/json

{
"NoDefault": "This has no default"
}
```

will return the following error

```json
{
"error": {
"code": "BadRequest",
"message": "Invalid request body. Missing field in body: Id.",
"status": 400
}
}
```

instead of correctly inserting the data. By adding the user to the `db_owner` role, the problem get fixed:

```sql
alter role db_owner add member dab_read_only
```

Which is weird since the data in the system view `` is available also to non db_owner.

```
select * from sys.default_constraints
```

### Version

0.5.34

### What database are you using?

Azure SQL

### What hosting model are you using?

Local (including CLI)

### Which API approach are you accessing DAB through?

REST, GraphQL

### Relevant log output

```Text
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
Route matched with {action = "Insert", controller = "Rest"}. Executing controller action with signature System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.IActionResult] Insert(System.String) on controller Azure.DataApiBuilder.Service.Controllers.RestController (Azure.DataApiBuilder.Service).
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
5ed080e1-aa25-4130-9d74-37c40677f814: Invalid request body. Missing field in body: Id.
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
5ed080e1-aa25-4130-9d74-37c40677f814: at Azure.DataApiBuilder.Service.Services.RequestValidator.ValidateColumn(ColumnDefinition column, String exposedName, IEnumerable`1 fieldsInRequestBody, Boolean isReplacementUpdate)
at Azure.DataApiBuilder.Service.Services.RequestValidator.ValidateInsertRequestContext(InsertRequestContext insertRequestCtx, ISqlMetadataProvider sqlMetadataProvider)
at Azure.DataApiBuilder.Service.Services.RestService.ExecuteAsync(String entityName, Operation operationType, String primaryKeyRoute)
at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(String route, Operation operationType)
```

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

Guía de contribución

Abrir la guía de contribución

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.