mibuso.com

I don't agree. Application Roles are a feature of SQL. They handle the complete security. The application simply refers to them. The roles and permissions in SQL are used to build and update the role. The security is being handle by SQL. Users don't have access directly to the database because none has been defined. That's what Application Roles are all about.

This makes sense, as when you are using Enhanced security model, you are actually getting SQL errors (that you do not have SELECT permissions/etc) instead of (before) getting Navision license permission errors.

Or maybe with Standard they took the time to trap he SQL error and return a more user-freindly (open to debate) message. With Enhanced is it feasible that they wanted to get the product out so skipped this step? It's not like they've ever rushed a product to market.

Yes if you are using enhanced security, NO if you are using standard security.

If you have a database with standard security, you will notice you only have ONE application role (public) and in that application role all tables of the db are defined with read,write,alter... access allowed!!! Every navision user then uses the same role, and to make the security compliant with the roles you defined in navision logins, it's the navision client that checks the security.....

My point is that because they didn't have a functional reason to introducing the 'enhanced' model, there is no functional reason to use it.

By the way, application roles is a SQL Server option, but individual permissions are not pushed down to SQL Server, that's why synchronizing is so fast on the standard model. All PERMISSIONS are managed within the app, not on SQL Server.

As I understand from this discussion - Enhanced security model might be a choice for DBAs who are security freaks and have no idea what navision is about. In this way, they basically can create roles/permission in SQL for each user manually.

Then other guy (let's call him NAV adminsitrator) could define navision permissions accordingly. And he would not be able to give more permissions than SQL DBA has already given in SQL.

I don't think this is workable, role names are not readable (its not userArole, but something with guid...so how to find out the role of a specific user.... but main reason is that if you do a syncronize in advanced security, role is first deleted and recreated from scratch...(and you need to syncronize a lot...)

When working in SQL - I actually means user created roles (basically, schemas with permissions to tables/etc) and not the roles from Navision.

Thats also what i mean...Navision roles are translated to application roles in sql. You can change the application role in sql to add or remove permissions, but when you do a syncronize from navision, these sql application roles will be deleted (and for this also youre modifications) and recreated based on the navision role...

$ndo$shadow, you mean? The public role has no rights. A regular user connecting from Access, Excel or whatever does not have even select permissions anywhere. When Navision assumes the $ndo$shadow persona, however, the user gets to do stuff.

My main issue with the $ar$.... as well as the $ndo$shadow roles is that if you're not DBO you cannot create a company. A couple of investment banks whose NAV:s we're managing see this as a humongous flaw as handing out CONTROL permissions on a SQL Server database is a NoNo when you're working in a datacenter environment, and they keep adding companies to NAV on a daily basis, so having the SQLDBA do it for them is unfeasable,

The only thing you'd ever do directly on SQL Server is create a user and assigning it SQL Server roles (public for regular users, db_owner for admins). Even with enhanced security you manage permissions, and assigning NAV roles to users from within the NAV client. Individual permissions are pushed down to SQL Server during synchronization.

This has nothing to do with whether the DBA is a "security freak". There's nothing wrong with making sure the app is secure, it is just unfortunate that it appears as though 'enhanced' security is more secure than 'standard', and in my opinion that is not the case.

I was just searching to see if there actually is anyone that uses AND is happy with Enhanced Security, and found this post.



I must say that this is probably the best explanation of Enhanced Security I have seen.

After studying the above entries and wondering why login works fine, if I add a windows user to a 5.01 standard cronus I compared the "all user" - role permission entries - and then I added the missing tabledata permissions in the 2000000xxx range like "windows login", "session" etc. and synchronized the users. With this, the enhanced security model works like a charm (I made a technical update from nav. 3.60 db to Nav. 5.0 SP1, SQL 2008).

Just read this interesting thread and desided to bump it up with a good reason to use Enhanced Security.

Ability to do post-mortem traces on who-did-what-and-when!

When using standard security, the Navision client assumes the identity of the application role $ndo$shadow when talking to SQL Server. When a transaction is made, the identity stored in the transaction log is $ndo$shadow, thus not possible to trace who did this or erased this list of customers before leaving the company...

On the other hand, using Enhanced Security will create one application role per user. Navision does not grant permissions to users but to application roles. So, whenever a user performs a transaction in Navision with Enhanced Security model active, this users application role will be saved in the transaction log. Using this id, you can trace back to the "who-did-what-and-when".

Then of course, there are issues with Enhanced Security as mentioned in this thread... but if the customer absolutely need the ability to do post-mortem analysis, the options are either Enhanced Security or C2-Audit.

Regards
Stefan

Doubles as MSDBA and Navision Developer (in that order)

Well, for this kind of logging, you can use the logging of NAV itself (very slow).
And also SQL server 2008 has good logging possibilities that are not so slow as the NAV solution.

If the customer really needs that, you can best invest a little instead of getting headaches caused by the Enhanced Security Model.