Hello to all. I have a couple of questions about user security in Navision. Can anyone explain me the mechanism that is used for user authentication?
- For example if I enter a new username in Navision in Security->Windows Logins menu but do not assign this user to any groups and after it I choose Synchronize - SQL Server will add this username to its database as a user with public rights, e.g. this user will have access to the Navision database without any rights to write or read data, am I right?
- If I’ve added a user in Navision to some groups - will SQL Server change permissions for this user for database objects, e.g. will SQL Server add this user to another security groups or change security assignment on objects?
- What about application role in Navision database? What for was it created?
- If any user has some access level to Navision database through Navision progam - can he connect to this database with a third-party program (such as Microsoft Query) and get more rights on the database objects?
Thank you [^]
Hi I think You’ll find all Your answers in the manual for “Installation and System Management for Microsoft SQL Server Option”. It’s on the product CD (w1w1isql.pdf). If You don’t have the CD I can email You the pdf if You wan’t. Regards //Lars
Thank you, Lars. I will try to find answers in this manual.
I can help you out a little, and for the benefit of others. You are correct that a new login created in SQL Server by adding a user to Navision, be it a Database Login or Windows Login (the latter being a Unified login in Windows XYZ), is created along with a user in the database that is a member of the ‘public’ group, and has no SQL Server permissions. If a user logs in with Query Analyzer or another tool, he has no permission to do anything. Any such permissions need to be added manually, outside of Navision. The permissions you assign in the Navision security system are totally independent of SQL Server, and SQL Server knows nothing about them. The Synchronize facility only sycnhronizes users/logins, not permissions. Nothing you do with the Navision permissions will change the permissions of the user in SQL Server. The application role in the database is the role that has all permissions to all tables you have created from within Navision. This role is activated after you log into Navision (if you’re not a db_owner member) and therefore gives the current logged in user all rights to all tables in SQL Server, and is a totally internal mechanism. The Navision security system is working above this to apply the necessary permissions you have set up in Navision, thus preventing unauthorised access to tables in the normal way. The application role cannot be used by users in external tools such as Query Analyzer because it requires a password that they don’t have. The system allows the Navision permission system to work as expected, whilst disallowing any permissions outside of Navision (unless set up manually by an administrator). A couple of notes: 1. Database Logins (in SQL Server these are called SQL Logins) cannot be created from Navision - in the User table - they must first be created externally. Only Windows logins can be created from within Navision. 2. In SQL Server there are logins and users. Logins are server-wide and user are per-database. A login is mapped to a single user, but in possibly many databases. Adding a login in Navision will handle both SQL Server login and the SQL Server user in the current database. 3. From version 3.01 onwards it is not necessary to perform a ‘Synchronize’ after changing logins through the UI. The only time Synchronize is nececcary, is after a restore where the security tables have been restored and there are logins in Navision that do not match logins in SQL Server. In this case you will get an informational messages (i.e. the restore will be successful) telling you this and requiring a Synchronize to be done. Hope this helps.
ok, thank you for your answer. So I still have a question about security permission for users. Which security permission on the database objects should I give to user in the SQL Server? Public role is unacceptable because it doesn’t have any default rights for read/write. I mean any user has public role membership as default, but Navision doesn’t create any specific roles for users. In wich group should I include new users? Thank you
What is it that you want to achieve? Do you want to manually give users permissions to do something outside of Navision? If so, you’re right that Navision does not create any SQL Server roles equivelant to those in the Navision security system. You’ll have to create the roles and assign user memberships yourself. However, the indirect permissions in Navision cannot be created in SQL Server. A table either does or does not have a particular permission in SQL Server, unless you get into using stored procedures.
ok, let me explain my question. First of all, for example, I would like to add new user to Navision. I am using “Windows only” authentication method on SQL Server. I should open Navision, select Tools->Security->Windows logins and add new user from a list, ok? Next step. I should choose Synchronize option. Aftewr this SQL Server will create login for this username from domain and will map this login for the Navision database, ok? This login will be added to Public role. Next step. On a client PC I would like to run Navision under user’s account, I am trying to open database, on the appropriate screen I choose SQL server name, database name and “Windows Authentication” option, BUT… when I press “OK” I receive message with something like this: “User doesn’t have sufficient permission on object Session, Database NavisionDB…” My question is - what should I do to eliminate this problem? Now I can solve this problem by adding this login to the db_datawriter role (or to any more powerful roles). Is it possible to solve this problem with another way? p.s. Sorry for my english, I know it not very well…[:)]
I see. Well, this should not happen. All I can suggest is that you use Enterprise Manager and check that the Session view is included in the list of objects that have permissions in the [$ndo$shadow] application role (look under Roles in your database. Choose properties of $ndo$shadow and click Permissions). The Session view should have all permissions (as should all other objects in the database).
You was absolutely right. This application role didn’t have enough rights on the Session view. Now it works perfect from this point of view. Thank you.[;)]