Recommended security setup for SQL and ODBC?

pdj

What is the recommended setup for a NAV SQL implementation where users should be able to read from the SQL db using ODBC?

I would like to be able to setup the entire security in NAV when we get new users (besides the AD creation). I have changed to enhanced security, since this was the only way to get the permissions created on SQL for each user. However, I still can’t get my users to update the pivot tables I make in Excel. I’m a db_owner but my users are only db_public. How come they get permission error on a simple select on a table they can access from NAV? (And yes, I have done a full sync. before trying)

Then I though this wasn’t the right approach after all. Most of my users have access to modify and delete records (surprise) but I won’t like them to do that from ODBC accidentally. Is there any way to prevent this?

Is it a better to go back to standard security and create a generic SQL user with full read access, and then tell the id and password to the users that needs it? This could actually be considered a better security than enhanced security...

I saw elsewhere in the forum that someone created SQL Views, and only assigned Read permissions to these View/Tables. I don’t see how that changes anything. If using enhanced security the user might still have full access to the “real” table. And I can’t see how Standard security can benefit from this method either.

I think all the solutions above sounds quite bad, so please give me any input to improve it.

pdj

More than 50 views but no answers?

ara3n

ok I'll try and answer.

Whether you have enhanced or standard security model doesn't make a difference on a user connecting directly to SQL. The difference between between the two models is the way the application role is created. Navision uses the application role once the connected is established to sql server.

When a user connects to sql directly, they are using their db role, which by default is the public role. You can give them db_reader role and they'll be able to do sql select statements. I don't know if you can create custom roles on sql and assign them to users, I'll leave it up to you to research.

What I do with clients is that I create a separate db login that has db_reader role and only give the info to people who need to know.
Most people who who need data, you write reports for them. Otherwise they have no business to look at the data directly.

pdj

Thanks a lot for the info. I think I'll settle with the db-user for odbc access.

ara3n wrote:

Most people who who need data, you write reports for them. Otherwise they have no business to look at the data directly.

Well, as long as they only get read access I don't see any problems in making pivot-tables in Excel. They are extremely flexible and very fast. (And would take ages to make in matrix forms in NAV.)

dmccrae

Rashed has a good approach.

If you want more fine-grained permissions you can assign them to any database user explicitly for a table.

E.g. if you have a login named 'MYDOMAIN\Alice', you will also have a database user with that name in your database. You can write, for example:

GRANT SELECT ON [CRONUS International Ltd_$Customer] TO [MYDOMAIN\Alice]

to give select permission on this table. Or you can also create a SQL group if you need more users, and make them members of that group.

pdj

dmccrae wrote:

E.g. if you have a login named 'MYDOMAIN\Alice', you will also have a database user with that name in your database. You can write, for example:

GRANT SELECT ON [CRONUS International Ltd_$Customer] TO [MYDOMAIN\Alice]

Really? I thought all permissions granted directly on SQL were deleted when syncronizing logins from NAV.
So I can create a sql-group with Select permission to selected tables, and then assign the group to i.e. "MYDOMAIN\Domain Users" or another AD group.

dmccrae

The synchronization process does not interfere with SQL permissions that you have granted on the actual user accounts in the server - unless you actually remove the user altogether from NAV.

With the group idea, I was thinking more of granting your AD group that you are interested in, directly as a login in SQL, e.g:

sp_grantlogin 'MYDOMAIN\Department ABC'

You then create a database user (user mapping) in your database for this login, so that you can use the database. And then you can grant your SQL permissions on tables in the database to this group:

GRANT SELECT ON [CRONUS International Ltd_$Customer] to [MYDOMAIN\Department ABC]

Any windows user that is a member of this group will be able to login to the server, and use the database and tables based on your permissions (but nothing more).

There is no need to create a SQL group.