Security : SQL and ODBC

HalMdy

Usually, permissions to the Navision DB on the SQL side is simply an "all access"-like permission (DBOwner, ...) and it works perfectly, but ... with that kind of permission, an OBDC expert could make a lot of "bad" thinks in the datas. How to prevent that ? In other words, how to restrict the ODBC access ?

kine

Users using Nav have no permissions to any table in the database. If you will login with some user account to MS SQL server, you will have no permissions to read/modify data in any table. It is because NAV is using Application role to access the data and it is why user accounts are not permitted to do something directly. Of course, if you manually add users into DB_OWNER... :-)

HalMdy

So, if I understand correctly, users must be defined in SQL without any permission ? I've read the installation guide and it's not so clear ... What about access to master DB ?

:oops: Confused to ask this kind of "basic" question, but it's clearly "not clear" and I'm afraid we have made some silly security setup from years ...

kine

1) I will begin with minimal requirements for MS SQL and Users:
If you are using DB users, the User need just Public permission for the DB. All needed access is done through application role, the select, modify etc. permissions are assigned to this application role by Navision. It means, no additional permissions are granted for the user. He just can login, but nothing more. Same is for the Windows users, but they do not need to have explicit Public permission for the DB, Navision will set it automatically.

2) In some cases, you need to grand additional permissions for the users. For example, when the user is designer and need to change table definitions etc., he must be in DB_Owner role to be able to alter the tables. If user needs to run permission synchronization process, he need to have additional permissions on the SQL server. The permissions can differ by SQL version (2000 and 2005), because MS SQL 2005 is more strict in this.

HalMdy

Thx Kine !

We will try ...

kine

I want to be sure that you understand: if you want to use MS SQL ODBC to access the data, you need to assign appropriate permissions to the user who will connect, because the MS SQL ODBC is not using any Navision security module. If you will use C/FRONT to access the data, Navision security module will be used and user do not need any additional permissions on MS SQL.

HalMdy

Hey again ...

We try to create users (DB or Windows logins) with only public permissions to the DB and, for testing "SUPER" in Navision itself.

When connecting, we have error msg "You don't have access to table 'Objects' ...
](*,)

Thx for help ...

bbrown

The PUBLIC role does not grant permissions to user objects.

kine

Yes, PUBLIC is just that you can connect, nothing more. It is not about permissions to read data etc.

JBrown

So I'm using Windows Auth w/AD security groups to define my users within Navision. I've read there is a bug in Navision that requires you to add a users domain account as well as their AD SG within Navision. You are to assign the Navision permissions to the SG and leave the user account with no roles. Doing this then sync's the user and group to the SQL server, however now that the user account is in SQL server when a user wishes to connect to the DB with ODBC the driver wants to use their user login as auth and not the security group they belong to.

Is this a confirmed bug/workaround and how can I sync a groups permission from Navision to the users in this group for ODBC access?

bbrown

Navision uses Application Roles to manage user access in SQL. The access granted by these Application Roles extends only to users running Navision. Your ODBC security is independent of these Application Roles. You need to setup a Database Role (SQL) that grant required permissions and assign it to your users

kriki

[Topic moved from Navision forum to SQL General forum]