I’m new to Navision but not to SQL so I might be missing something here. We have Navision 3.X running on SQL 2000 using Active Directory authentication. Here’s the issue in a nutshell. We use Active Directory to authenticate our users. This same permissions granted to the users via Navision Roles and Users is going to minimally be as generous with table level permissions as are required to allow a user to do their job from within Navision. So it only goes to assume that any user who can modify data in the application could use a tool such as MSAccess to modify (accidentally of course) the same data without many of the constraints of the Navision application protecting the Referential Integrety of the data. In other apps I would try to see the sp_setapprole stored procedure used to handle this but I don’t see that as an option with Navision. Any idea how I can protect my SQL Database from direct access via other tools? What if I want to give some users special Read Permissions to tables that in Navision they can modify? Doesn’t that leave me vulnerable? I don’t really even see much in the way of Triggers protecting the tables on that level?
For Navision users who are not db_owner roles in the SQL databases, they have no permission to any table in the database via their regular SQL login. Navision is indeed using sp_setapprole for those users, to provide a change in effective permissions at run time. If the user is a db_owner role there is no need to issue this call. Try this out by creating a new SQL user and giving him no permissions (easier with a SQL login because you don’t need a windows account, just use sp_addlogin or do it in Ent. Mgr.). Then add him as a user in Navision (as a Database Login or Windows Login as appropriate) and give whatever Navision roles you like. Then log into SQL directly with that user and see if he has any additional permissions - he won’t have.
Thanks, My Reseller is giving me bad information then. We are using Active Directory Authentication. Same holds true for this method?
Yes it also applies to windows authentication (unified login, windows logins, AD authentication - whatever you like to call it). The issue is simply weather the user is a db_owner or not in that database. I used SQL logins as an example just because its easier to test with. Windows authentication is the preferred method and most secure as an authentication method.
This form of user authentication and security is employed by a number of large US Corporations and conforms to the Sarbanes Oxley requirements.