Azure / Azure/data-api-builder
[Bug]: DEFAULT values not applied if database user is no db_owner
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
### 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
Contributor guide
Assessment
This issue has not been assessed yet.