Azure数据库权限问题 [英] Azure DB permission issue
问题描述
我对来自本地的Azure Sql数据库相对较新。从服务器级别移至数据库防火墙规则，因此只有"dba"和可以使用提升的权限连接到所有数据库。开发人员AD组是AD管理员，并被
新DBA AD组取代。我在此开发人员组中，但在添加到DBA AD组后被删除。服务器管理员不是开发人员组。为数据库防火墙规则添加了相关的IP，并从服务器防火墙规则中删除了除我们的公共IP以外在db级别使用Select，Execute和View Definition在每个数据库中添加
开发人员组作为包含用户。一切似乎都很好，但目的是限制开发人员组在此服务器上创建对象。但是，我可以使用developers@mycompany.com
连接并创建dbo对象。开发人员组不在任何数据库角色中，并且不是主服务器中的用户，并且不拥有相关数据库。 我作为db用户删除了开发人员组，但仍然可以连接，虽然我没有等待很久才将
添加回来。我错过了什么？提前致谢！
¥
$
I'm relatively new to Azure Sql Database coming from on-prem. Moved to database firewall rules from server level so only "dba's" can connect to all DBs with elevated permissions. A developers AD group was the AD admin and was replaced with a
new DBA AD group. I was in this developers group but was removed after being added to the DBA AD group. The server admin is not the developers group. Added relevant IPs to db firewall rules and removed all but our public IP from server firewall rules. Added
the developers group as a contained user in each db with Select, Execute and View Definition at the db level. All seemed fine but the intention is to restrict developers group from creating objects on this server. However, I can connect using developers@mycompany.com
and can create dbo objects. The developers group is not in any db roles and is not a user in master and does not own the db in question. I dropped the developers group as a db user but was still able to connect although I didn't wait long before adding
it back. What am I missing? Thanks in advance!
推荐答案
您好，
在您的特定情况下，您可能还有一些剩余的角色分配，即授予您对developers@mycompany.com的访问权限，例如在Active中目录角色/组分配？！没有环顾四周，很难进行逆向工程。我在下面详述的是
设置此过程的过程。
In your specific case, you may have some remaining role assignment that is granting access to developers@mycompany.com such as in the Active Directory role/group assignment?! Hard to reverse engineer without looking around. What I have detailed below is the process for setting this up.
这是
文档。它涵盖了所有PaaS部署选项（SQL单实例，弹性池，托管实例，数据仓库），并涵盖了所有身份验证方法。您需要
才能知道有"受管实例"的明确说明。和"SQL数据库服务器"。因此，假设您有Azure SQL数据库（单实例或弹性池），您将按如下步骤浏览文档：
This is the document when it comes to configuring Azure Active Directory authentication with Azure SQL. It covers all PaaS deployment options (SQL Single Instance, Elastic Pools, Managed Instance, Data Warehouse) and covers all authentication methods. You do need to be aware that there are explicit instructions for "Managed Instance" and "SQL Database Server". So, assuming you have an Azure SQL Database (Single Instance or Elastic Pools), you would step through the document as follows:
接下来的两节：
将Azure订阅关联或添加到Azure Active Directory
为Azure SQL服务器创建Azure AD管理员
此时您应该有一个SQL管理员（在设置实例时创建）在当前用于访问数据库的那个）并且您现在在目录中配置了Active Directory SQL Admin。下一步是将
AAD SQL Admin配置到Azure SQL实例。
At this point you should have an SQL Administrator (created when you set-up the instance at that which is currently used to access the database) and you now have an Active Directory SQL Admin configured in the directory. The next step is to provision that AAD SQL Admin to the Azure SQL instance.
只是关于使用个人帐户与分发帐户（电子邮件别名）的说明。基于使用组与个人帐户（AAD密码，MFA，AD集成），您可以使用AAD身份验证方法。如果这将是一个组
，那么这应该只是db_owner权限。此处只有一个级别的权限，与SQL Admin相同。
Just a note about using individual accounts versus distribution account (email alias). You are limited in the AAD authentication method available based upon using groups versus individual account (AAD Password, MFA, AD Integrated). If this is going to be a group, this should be db_owner privileges only. There is only one level of permissions here and that is the same as SQL Admin.
配置过程对于托管实例和Azure SQL数据库服务器是唯一的：
The provisioning process is unique to Managed Instance and Azure SQL Database Server:
为Azure SQL数据库服务器提供Azure Active Directory管理员
- 和 -
托管实例配置进程：
为您的托管实例为您的托管实例提供Azure Active Directory管理员
这是您添加开发人员和其他不需要AAD身份验证的SQL管理员权限的用户的地方。这还包括应用程序访问。
This is where you add your developers and other users you do not SQL Admin privileges who require AAD authentication. This also includes application access.
"一旦配置了基于Azure AD的包含数据库用户，就可以授予用户额外的权限，就像你给
一样授予任何其他类型的权限用户。通常为数据库角色授予权限，并将用户添加到角色。"
我希望此信息有所帮助。如果您有其他问题，请告诉我们。
I hope this information help. Please let us know if you have additional questions.
~Mike
这篇关于Azure数据库权限问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
