NAV 5 - SQL2005 (Windows Logins) Issue

Tomas

Working on SQL2005/NAV5 [Security Model: Enhanced]

I have noticed strange issue.
If we create a Windows Login in Navision, and synchronize all security, username in sql will be created (with its own schema attached to it).

Let's say we do not give super role to the new user, just some roles to read customer table, etc. After synchronization, when new users logs into navision he is getting error message:

---------------------------
Microsoft Dynamics NAV
---------------------------
The following SQL Server error(s) occurred while accessing the License Agreement table:

229,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'CRONUS UK Ltd_$License Agreement', database 'Test', schema 'dbo'.

---------------------------
OK   
---------------------------

And let's say we try to open Budgets with same user (for which we do not have permissions). We will get error (instead of - you do not have to read... ):

---------------------------
Microsoft Dynamics NAV
---------------------------
The following SQL Server error(s) occurred while accessing the G/L Budget Name table:

229,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'CRONUS UK Ltd_$G_L Budget Name', database 'Test', schema 'dbo'.

---------------------------
OK   
---------------------------

Furthermore, if I would go to SQL and there would add db_owner role, I would get error message (which is actually what i expect to get):

---------------------------
Microsoft Dynamics NAV
---------------------------
You do not have permission to read the G/L Budget Name table.

---------------------------
OK   
---------------------------

Had anyone else noticed similar issues? Any ideas and discussions would be helpful.

ara3n

Every user needs the ALL role in order to login. Have you given that to the user?

Tomas

ara3n wrote:

Every user needs the ALL role in order to login. Have you given that to the user?

Yes, I did.

What I am trying to say is, that instead of normal Navision error that you do not have permissions, I am actually getting SQL error (which is strange).

........

Let's say, if you create a login in SQL first, it will have default schema defined as DBO by default.

If user is created at the moment you synchronize all security, the user will have a default schema named by the user itself.

ara3n

in 2005, the security is stricter, in that if you don't have the select permission, you won't be able to actually see the table.


It is wierd that you are seeing SQL Errors instead of Navision. My guess synchronization didn't finish or is screwed up.
Personally I've given up on enhanced security model.

Tomas

ara3n wrote:

in 2005, the security is stricter, in that if you don't have the select permission, you won't be able to actually see the table.


It is wierd that you are seeing SQL Errors instead of Navision. My guess synchronization didn't finish or is screwed up.
Personally I've given up on enhanced security model.

Security was synchronized with user 'sa'. I did not get any error messages while synchronizing. And keeping in mind, that new user was actually created when synchronizing, I am assuming that synchronization was successful.

In SQL2005 probably public role is not good enough with NAV Enhanced security model.

As for synchronization being screwed up - problem is that I was able to replicate same issue on two different machines. ](*,)

kriki

[Topic moved from Navision forum to SQL General forum]

Why not use Standard Security model? It makes life a lot easier.

Tomas

kriki wrote:

[Topic moved from Navision forum to SQL General forum]

Why not use Standard Security model? It makes life a lot easier.

It wouldn't be so interesting then... :?

ara3n

Tomas wrote:

kriki wrote:

[Topic moved from Navision forum to SQL General forum]

Why not use Standard Security model? It makes life a lot easier.

It wouldn't be so interesting then... :?

To make it even more interesting, you should open a help ticket with MS.

DenSter

Seriously, why aren't you using standard security? If you need any external apps accessing the NAV database, create users for them manually directly in SQL Server.

Tomas

Possible (and no so possible) solutions to this problem:
1. Add db_owner role to the user (yeah, right - not possible)
2. In the database, find user. Check it's schema. Go find that schema. Change default owner of the schema to dbo (works like a charm, just if there are a lot of users, then there are a lot of clicking in order to solve this issue). And in the future, you will firstly need to create a user in SQL before adding that user into Navision (even if you are creating Windows logins).
3. Change Security model to Standard (which usually everyone goes for). And by everyone I mean myself included.

Thanks for everyone's input, anyways.

BGI

ara3n wrote:

It is wierd that you are seeing SQL Errors instead of Navision....

No it's not weird....this is the difference between enhanced and standard security...
Enhanced: SQL wil be in charge of permission to a table. For each user a role will be created and in that role all tables will be present with the correct permissions. Nav clients sends selects etc.. directly to sql who will allow or dissallow according to the role settings.

Standard: One role exists in sql, and that role is used by every users and in that role all permissions on all tables are allowed...

The nav client will check prior to giving the select statement (or alter or delete or whatever) to sql if the user has rights into this table , and if not navision client will block and give the error message....

Stay with the suggestion of everyone here, use always standard security....never enhanced (not seen one example of a client who really needs enhanced security, but seen a lot of problems with it....)

bbrown

Sometime ago I asked MS Support to give me an example of a customer where Enhanced Security would be the better choice. They were unable to give me an example. That being said - Use Standard.

DenSter

bbrown wrote:

Sometime ago I asked MS Support to give me an example of a customer where Enhanced Security would be the better choice. They were unable to give me an example. That being said - Use Standard.

From what I understand itis that when Navision was purchased by Microsoft, some MS team did an evaluation of the Navision security system, and found that because the security was not enforced directly on SQL Server, that the system was not secure (or some other words that meant that it did not comply with Microsoft standards), and they forced the NAV team to change the security system to push permissions down into the user level on SQL Server.

I don't remember exactly which version it was, but that is when the 'enhanced' security option was the only option for SQL Server. When that caused problems in just about every single NAV implementation they decided to give back the old security system, and give NAV users the option to select the basic system, and thereby giving the users the responsibility of selecting a "less secure" security system.

bbrown

My question to them was not "what it was". I was quite clear on the what. My question to them was "why would one use it". NAV security has always been enforced directly on SQL. It uses SQL Application roles which are a SQL feature and not a NAV feature.

BGI

bbrown wrote:

... NAV security has always been enforced directly on SQL. It uses SQL Application roles which are a SQL feature and not a NAV feature...

The use of roles on the standard system, is only there to be able to access the database via a navision client and not directly via a sql login...

So once you have access the security per table is taken over by navision...

I think it's only how you put it, but imho when using standard, it's the navision client that enforces the real security....

bbrown

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.

Tomas

bbrown wrote:

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.

bbrown

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.

BGI

bbrown wrote:

.. The roles and permissions in SQL are used to build and update the role...

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.....

DenSter

bbrown wrote:

My question to them was not "what it was". I was quite clear on the what. My question to them was "why would one use it". NAV security has always been enforced directly on SQL. It uses SQL Application roles which are a SQL feature and not a NAV feature.

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.

Tomas

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.

BGI

Tomas wrote:

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...)

Tomas

BGI wrote:

Tomas wrote:

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.

BGI

Tomas wrote:

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...

hrillo666

BGI wrote:

If you have a database with standard security, you will notice you only have ONE application role (public)

$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,

DenSter

Tomas wrote:

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.

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.

David_Singleton

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

Tomas wrote:

Enhanced security model might be a choice for DBAs who are security freaks and have no idea what navision is about.

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

petsch

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).

geschwint

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)

kriki

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.