Background
I am in the process of creating an application (referred to as MyApp) which must read data out from a SQL Server database handled by Navision. Navision users should to be able to use my application, without modifying permissions in the database.
我正在创建一个应用程序(称为MyApp),它必须从Navision处理的SQL Server数据库中读取数据。 Navision用户应该能够使用我的应用程序,而无需修改数据库中的权限。
Navision's handling of permissions seems to be on the application layer. It performs the checking of permissions without storing them in the database.
Navision对权限的处理似乎在应用程序层。它执行权限检查而不将它们存储在数据库中。
Problem
Navision overwrites users, permissions, and other related objects in the database when synchronizing with the database, so the normal approach of creating a DB user and just using that won't work.
在与数据库同步时,Navision会覆盖数据库中的用户,权限和其他相关对象,因此创建数据库用户并使用它的常规方法将无法正常工作。
Possible Solution
What I think would be the most appropriate solution is to create a MyApp role in Active-Directory, which grants the necessary permissions on the DB, and add this role to all users.
我认为最合适的解决方案是在Active-Directory中创建MyApp角色,该角色授予数据库必要的权限,并将此角色添加到所有用户。
I do not know how to do this, or even if it's possible. Other solutions, or proposals, are welcome, but please only suggest solutions with can be managed from within ActiveDirectory or Navision.
我不知道该怎么做,或者即使它有可能。欢迎提供其他解决方案或建议,但请仅建议可以在ActiveDirectory或Navision中管理解决方案。
The server is an SQL Server 2008 server running Navison 5, and the client is Navision 6. I'm using Active Directory for Windows Server 2K8.
服务器是运行Navison 5的SQL Server 2008服务器,客户端是Navision 6.我正在使用Windows Server 2K8的Active Directory。
EDIT:
My app is a crate creating and designing application. It needs to read out the customers' names and IDs, and a few items in the items table, and that is why I need this functionality
我的应用程序是一个创建和设计应用程序的箱子。它需要读出客户的名称和ID,以及items表中的一些项目,这就是我需要此功能的原因
2 个解决方案
#1
1
In general, skipping the NAV layer and reading/writing directly to the DB is not recommended at all as you're bypassing all of NAVs business logic which is stored in the table and report objects in NAV.
通常,不建议跳过NAV层并直接读取/写入DB,因为您绕过存储在表中的所有NAV业务逻辑并报告NAV中的对象。
What does your app intend to do (broad strokes if you can't get specific) and would using a NAV add-in or dataport be feasible?
您的应用程序打算做什么(如果您无法获得具体的广泛笔画)并且使用NAV加载项或数据端口是否可行?
#2
2
If you use the enhanced security model in NAV, user permissions are synchronized to SQL Server. However, these SQL permissions are mapped to an app role in SQL Server, rather than the user's login. If you use the standard security model, all users map to a single SQL app role which is a super user (less secure).
如果在NAV中使用增强的安全模型,则会将用户权限同步到SQL Server。但是,这些SQL权限映射到SQL Server中的应用程序角色,而不是用户的登录名。如果使用标准安全模型,则所有用户都映射到单个SQL应用程序角色,该角色是超级用户(安全性较低)。
If you want to access the data in SQL Server using the NAV security model (i.e. through the SQL App roles NAV creates), you should use the CFront API (installed via the SDK option). If you are using NAV 2009 web services are also an option.
如果要使用NAV安全模型(即通过NAV创建的SQL App角色)访问SQL Server中的数据,则应使用CFront API(通过SDK选项安装)。如果您使用的是NAV 2009 Web服务也是一种选择。
If you want to access SQL Server directly, then you will have to manage the permissions yourself using SQL Server. If you create a SQL script to grant permission it is easy to restore anything which NAV might delete during Synchronize Logins.
如果要直接访问SQL Server,则必须使用SQL Server自行管理权限。如果您创建一个SQL脚本来授予权限,则可以轻松恢复NAV在同步登录期间可能删除的任何内容。
You can't grant SQL permission from Active Directory exactly as you described. Instead you must map Active Directory groups to either SQL Server logins or NAV Windows Logins (depending on whether you decide to access SQL directly or go through a supported NAV API). Note: the permissions associated with the role are managed in SQL or NAV respectively; not in AD.
您无法完全按照您的描述从Active Directory授予SQL权限。相反,您必须将Active Directory组映射到SQL Server登录或NAV Windows登录(取决于您是决定直接访问SQL还是通过支持的NAV API)。注意:与角色关联的权限分别在SQL或NAV中管理;不是在AD。
From an administration perspective, you can simply add and remove users from this Active Directory group. If you use the NAV enhanced security model each user in the AD group must also have an entry in Windows Logins, and whenever you make changes you must Synchronize Logins. This slight inconvenience is a hangover from the native database.
从管理角度来看,您只需在此Active Directory组中添加和删除用户即可。如果使用NAV增强安全模型,则AD组中的每个用户还必须在Windows登录中具有条目,并且无论何时进行更改,都必须同步登录。这种轻微的不便是来自本机数据库的宿醉。
#1
1
In general, skipping the NAV layer and reading/writing directly to the DB is not recommended at all as you're bypassing all of NAVs business logic which is stored in the table and report objects in NAV.
通常,不建议跳过NAV层并直接读取/写入DB,因为您绕过存储在表中的所有NAV业务逻辑并报告NAV中的对象。
What does your app intend to do (broad strokes if you can't get specific) and would using a NAV add-in or dataport be feasible?
您的应用程序打算做什么(如果您无法获得具体的广泛笔画)并且使用NAV加载项或数据端口是否可行?
#2
2
If you use the enhanced security model in NAV, user permissions are synchronized to SQL Server. However, these SQL permissions are mapped to an app role in SQL Server, rather than the user's login. If you use the standard security model, all users map to a single SQL app role which is a super user (less secure).
如果在NAV中使用增强的安全模型,则会将用户权限同步到SQL Server。但是,这些SQL权限映射到SQL Server中的应用程序角色,而不是用户的登录名。如果使用标准安全模型,则所有用户都映射到单个SQL应用程序角色,该角色是超级用户(安全性较低)。
If you want to access the data in SQL Server using the NAV security model (i.e. through the SQL App roles NAV creates), you should use the CFront API (installed via the SDK option). If you are using NAV 2009 web services are also an option.
如果要使用NAV安全模型(即通过NAV创建的SQL App角色)访问SQL Server中的数据,则应使用CFront API(通过SDK选项安装)。如果您使用的是NAV 2009 Web服务也是一种选择。
If you want to access SQL Server directly, then you will have to manage the permissions yourself using SQL Server. If you create a SQL script to grant permission it is easy to restore anything which NAV might delete during Synchronize Logins.
如果要直接访问SQL Server,则必须使用SQL Server自行管理权限。如果您创建一个SQL脚本来授予权限,则可以轻松恢复NAV在同步登录期间可能删除的任何内容。
You can't grant SQL permission from Active Directory exactly as you described. Instead you must map Active Directory groups to either SQL Server logins or NAV Windows Logins (depending on whether you decide to access SQL directly or go through a supported NAV API). Note: the permissions associated with the role are managed in SQL or NAV respectively; not in AD.
您无法完全按照您的描述从Active Directory授予SQL权限。相反,您必须将Active Directory组映射到SQL Server登录或NAV Windows登录(取决于您是决定直接访问SQL还是通过支持的NAV API)。注意:与角色关联的权限分别在SQL或NAV中管理;不是在AD。
From an administration perspective, you can simply add and remove users from this Active Directory group. If you use the NAV enhanced security model each user in the AD group must also have an entry in Windows Logins, and whenever you make changes you must Synchronize Logins. This slight inconvenience is a hangover from the native database.
从管理角度来看,您只需在此Active Directory组中添加和删除用户即可。如果使用NAV增强安全模型,则AD组中的每个用户还必须在Windows登录中具有条目,并且无论何时进行更改,都必须同步登录。这种轻微的不便是来自本机数据库的宿醉。