我的方案遵循
- Azure函数（有多个功能应用程序），并具有可用的托管身份。
- 每个功能应用程序都可以访问Azure SQL中的特定模式。这是具有多个架构方案的单个数据库。
根据Microsoft文档，我必须执行以下操作以允许函数应用程序访问DB。
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO
这很好，但是问题是它可以访问整个数据库。我想允许每个功能应用程序在数据库中仅访问单个架构。特别是在执行EF核心迁移时会产生问题。 How can we give specific function app to completely manage specific schema of database.
My scenario is following
- Azure Functions ( There are multiple function apps) with Managed Identity available.
- Each Function App has access to specific schema in Azure SQL. This is Single Database with Multiple schema scenario.
As per microsoft document I have to do following to allow function app access to DB.
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO
This works fine but issue is that it can access entire database. I want to allow each function app to access only single schema within a database. Specially it creates issue when EF core migration is being executed. How can we give specific function app to completely manage specific schema of database.
发布评论
评论（1）
您可以使用
db_datawriter等，而不是db_datareader。请参阅以下示例：
grant On on schema :: humanResources to cole_humanresourcesdept;其中
humanResources是一个架构，prole_humanResources -deptept是用户。请参阅
Instead of
db_datareader,db_datawriter, etc. role assignment, you can useGRANTpermission statement.Refer the below example:
GRANT SELECT ON SCHEMA::HumanResources TO role_HumanResourcesDept;where
HumanResourcesis a schema androle_HumanResourcesDeptis the user.Refer https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15