Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 326 Dynamics CRM
- 110 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 994 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
SQL Permission
sabzam
Member Posts: 1,149
Hi Everybody,
A user has got this error: Cannot find the object "Vendor Ledger Entry" because ti does not exist or you do not have permissions.
This is an SQL Error, which happens when posting from Navision 5.1. The issue here is that I have got exactly the same permissions and do not get any error. Anyone has got any idea what this error is referring to?
A user has got this error: Cannot find the object "Vendor Ledger Entry" because ti does not exist or you do not have permissions.
This is an SQL Error, which happens when posting from Navision 5.1. The issue here is that I have got exactly the same permissions and do not get any error. Anyone has got any idea what this error is referring to?
0
Comments
To avoid (a lot of) these errors set your Security Model to Standard instead of Enhanced. This takes away the option to synchronize single users, but you won't get these errors any more.
As a SQL DBA, that scares the bejeezus out of me.
Just out of curiosity - are you getting this on either a SQL 2005 or SQL 2008 server that was upgraded from SQL 2000?
I get this occasionally on my 2005 box. It was upgraded from 2000, and it did some really odd things with schemas and permissions when it brought over the tables during the upgrade process. My other box, which was installed as SQL 2005 from the beginning, doesn't have these messages.
Why :?:
This is how I work all the time. I regularly do work with databases that required me to be dbo. How do you work with databases otherwise?
My guess is you miss read something (or maybe I did).
This may be related to Navision's requirement that users creating/modifying tables need to be DB_Owner. In a more "normal" SQL development enviroment, all developers are not typically DB_Owner. DB_Owner has unrestricted access to the entire database. A non db_owner has access to objects they create plus objects to which they have been given rights. Of course, having objects owned by multiple users can complicate other things. Having everything owned by DB_Owner is a simpler approach and quite common in commercail products.
For security purposes. Security is required at every tier of the application architecture. At any given point, a user should only have the necessary permissions that enable them to do their job, and no more.
DB_Owner, by default, gives the user free range over that database. In a poorly-constructed security architecture, dbo is also linked to the server administrator account. You can do bad, BAD things not only to a SQL Server, but also to the host machine and even the domain if this is the case. Even if that's not the case, dbo gives the user the right to drop and add tables, sprocs and functions, indices, schemas, even databases.
It's simply not necessary to do the job, and opening more permissions than are necessary opens a potential vector. The only thing that you need is for the user to have read-write access to the schema-bound objects (SQL 2005-2008) that deal with Navision, or for them to have read-write access to the database.
I don't even handle this at the user level, honestly. I grant the permissions to AD Groups, and then let the inheritance of permissions from group to user do the heavy lifting for me.
As a DBA - this is one of the things I kill almost immediately when I see it. Only administrators/developers should have access to dbo, because those are the only people who should be adding/deleting/changing objects and schemas in the system. Everyone else gets a read-write or read-only permission set that grants them access to the data, but denies access to the SQL Server objects. The only time I would make an exception is if a company/organization didn't have administrators/developers, at which point I would grant appropriate access to one or more "power users".
I've been doing SQL devp and DBA work for a while now - a lot longer than I've been working with NAV, and I've seen some nasty things happen when user accounts are mapped to dbo. Business Analysts who have access to write SQL directly against the database, but don't really know anything about SQL, pick up a script off the net and drops the database. Programmers who used dbo in devp and deployed it only to have their system owned by a hacker who changed, dropped, and/or stole their information (I did this under contract for a bank once - they asked me to prove whether it could be done from their extranet site). One particularly nasty one where someone figured out that the dbo account was mapped to the domain admin account, and used that link to add their own account to a corporate domain that they weren't a part of.
NAV, specifically, may not have a problem with it, and may even insulate users from doing SOME of those things from within its boundaries, but from a larger organizational perspective, it's really a terrible idea, and most of the time, not necessary - just easier.
>> DBA hat off <<
"Having everything owned by DB_Owner is a simpler approach and quite common in commercail products."
Just FYI. SQL 2005 made it possible to separate the schema from the owner. This wasn't possible in previous versions, and is a huge improvement, specifically for this purpose.
It means that I can specify the owner of a schema without require that owner be the account that created the schema objects or dbo. It also means that I can change the owner of a schema, which wasn't possible under previous versions when most objects were created with a schema of dbo.
We're not talking about who owns the objects though - we're talking about who should have the rights to own the objects. Everyday users shouldn't - it's simply too dangerous and opens too many doors that have to be closed later.
OK, so I am not the greatest SQL Security expert, so for me dbo seems to make sense. But maybe I am wrong, so can you let me know what rights I really should be setting my self? Typical tasks I do would be:
Backups and Restore,
Create new (playground and development databases),
Import objects,
Table management (mdf, mdf and ldf) monitor and expand typically. Shrink log in non production databases.
Obviously normal users would just have public and nothing else, but I think we are discussing the system administrator or are we discussing different things.
By the way its pretty rare that my clients would give me dbo on their live database, it happens, but very rarely.
Given the functions that you're performing:
in SQL 2005/2008, the database role db_backupoperator should give you the ability to perform backups and restores, database role db_ddladmin should give you the rights to run create/alter/drop scripts, which I think would correspond to the importing of objects.
However, you're talking also about maintenance - growing/shrinking files, changing files, etc..., which is normally reserved for db_owner, and so, in that case, you may need that level of access. IF YOU created the database which you are trying to admin, then dbcreator would give you the ability to control those options without db_owner, but that role is at a Server level, and is honestly less secure because you have then been granted rights to the whole server, not just to one database.
To create databases, you would need the dbcreator SERVER role. If you needed to add users to that database, you could have the securityadmin server role (for entire server access), or be granted the db_securityadmin database role if you only need access to security admin within that database.
If it's SQL 2000... I'd probably grant you dbo access, because security isn't as granular. However, I wouldn't grant it to everyone else - only you.
I thought the op was talking about permissions for all users, not just developers/consultants/admins. If he wasn't, then my apologies for my diatribe
You can also have the diskadmin server role if you need to manage disk files, but this may be too broad a level of access, too. If you only need to manage a specific database or databases that you create, then the db_owner database role and the dbcreator server role may be sufficient.
Again, though, I don't recommend end users having high level privileges. If nothing else, a separate domain account or SQL Server login can be used to obtain higher permissions if they are needed for a specific operation (like changing/adding objects).
Here's the MSDN article on Server roles:
http://msdn.microsoft.com/en-us/library/ms175949(SQL.90).aspx
and here for database roles:
http://msdn.microsoft.com/en-us/library/ms189121(SQL.90).aspx
Just as an FYI.
I got this error once before for a particular user. He was getting it on Item Ledger Entry.
I synchronized, added his user account specifically to the database schema, gave him admin rights, synchronizing all the way - nothing worked.
I finally deleted his account from NAV, synchronized all, added him back, synchronized him, and then set his SQL permissions where I wanted them (read,write), and synchronized once more. It finally took.
I will tell you that I had recently upgraded my SQL Server from 2000 to 2005 (not a migration, an upgrade), and had several security issues after that due to the changes to the security structure in 2005.
OK, so could I ask you to put your security admin cap back on and answer a question.
Why are you using Enhanced security? The only advantage I have found so far is that some SOX auditors like the separation it offers, not that its a requirement, just that it makes it easier for them to understand. But its clear that you know about security, so what is the reason in your case?
Thanks in advance.
We were originally told that the Enhanced model gave us the ability to synchronize a single user, and that the Enhanced security model pushed all security permissions down to the SQL Server level, which I wanted because we had external, read-only applications that we wanted to integrate with AD security.
Since then, I've heard that both security models actually push the security down to SQL Server, with the difference being in the number of application roles that are created/maintained. I haven't tested this to see, in the Standard model, when User A is granted permission to 1 table in NAV, and User B is granted permission to 5 tables, if those users will only be able to access those respective tables at the SQL level without manual management of logins at the SQL Server level.
Basically - we started with it when we implemented NAV 4.0, and it hasn't been a high priority to revisit. The pointy-haired bosses have determined that there are other priorities.
I know one thing I wish MS would do... just as a pipedream of mine as a DBA... I wish they'd utilize schemas.
Right now, my company's name is Derby Industries, LLC. In my database, my item ledger entry table is listed as [Navision].[dbo].[Derby Industries LLC$Item Ledger Entry]
Schemas would simplify security settings for multiple companies at the SQL Server level, because you could instead have
[Navision].[Derby Industries LLC].[Item Ledger Entry]
Where [Derby Industries LLC] is the schema for a particular company. Since users can own/have permissions to entire schemas in SQL 2005+, then user-company segregation would become a snap...
Not that it's here nor there in terms of your question...
That helps actually, because it seems you actually had a reason for doing it. Most of the time I ask this question the answer is generally either a/ That was the default or b/ We thought it was more secure.
Now you have me thinking of a really frightening case. If Enahnaced passes secuity back to SQL, then does that mean say an AD user that has permission to create sales orders in NAV will have write permission on the Sales Line table? I.e. they could just log in with access connect to the table and change say "Qty. SHipped not invoiced" and destroy the database? Or am I missing something here.
PS Excuse my ignorance, as I have never seen a need for Enhanced, so I have never used it.
You'll have to excuse me on this one, because while I understand SQL security quite well, my knowledge of how NAV interacts with it is somewhat limited by what I've read (and what MS chooses to make available).
If I had to guess, though, I'd say yes. If an AD user/group has access to the Sales Line table in NAV, and that database is set up with Enhanced security, and that user had a mechanism to connect and introduce records or changes to records to the Sales Line table at the SQL Server level, then it should allow it, since all SQL knows is that the user has the permission to perform the operation.
As I understand it, there are three primary problems with introduction data changes at the database level in a SQL Server installation.
1. The SIFT "bucket" tables that will not be updated outside of the NAV environment. This problem is mitigated (I think) by v 5.1 where indexed views are introduced instead of multiple "buckets" that must be simultaneously updated.
2. The concurrency mechanism (timestamping) that NAV uses on SQL Server.
3. The inclusion of the Business tier in the internal objects of NAV, which are not accessible outside of the NAV environment except through NAS interop. I think this is mitigated in NAV 2009 through web services(?), though I have yet to see how those web services are exposed. I'm assuming that the NAS has been repurposed to do this, although with SQL 2005 providing web service interop at the SQL Server level, and with it's support of CLR assemblies (.Net), and with NAV 2009 objects being compiled as .Net assemblies, I would think that the web service functionality could potentially be pushed down to the SQL Server level without a NAS.
If those three problems were taken out of the equation, then I don't know if it would much matter if data were updated outside of the NAV environment...
On 3, it is all handled in the new middle tier, so the answer is NO you couldn't just consume web services directly from SQL and expect the business logic to trigger.
Sorry - I didn't make myself clear.
My understanding from Convergence 2008 was that you would be able to expose the business logic through web services in NAV 2009. They demoed adding customers, sales lines, etc...
Since objects in NAV 2009 are compiled as .Net assemblies, and since SQL Server 2005+ can work directly with .Net assemblies and it has the ability to directly expose web services, it should be theoretically possible to have NAV export the assemblies to SQL Server, and add the method calls to the SQL Server endpoint to expose as web services.
I doubt that it will happen that way, at least any time soon. It's a pretty typical enterprise process model, though. Front-end apps communicating with web services sitting on top of data access layers (or directly on top of data).
Do you know whether the NAS will be responsible for exposing the web services in NAV 2009? If so, will you be able to bind multiple methods to a single endpoint exposed by the NAS?
In response to your original question, though, I would think that a user with sufficient priveleges in NAV and an enhanced security setup could modify data directly at the SQL Server level. It's difficult for me to test this without setting up a new user because I'm a Domain Administrator, but it seems logical.
OK.
I need to look into it then. I thought that the web services would need a running instance of NAV to host the NAV objects and bind the endpoints, but haven't had any time to really investigate. Guess I need to do that.
Thanks.
RIS Plus, LLC
Is there a good document out there that explains it?
I've worked with web services in .Net and SQL for years, and I'm not getting this.
Web Services require something to host them. .Net web services are registered with the Windows operating system through Internet Information Services. This results in an endpoint that is registered with the http subsystem of Windows, and the method calls for that web service are bound to it. When a call is made, IIS kicks in, starts the assembly and the CLR, runs the method, and returns the result.
With SQL Server, it is capable of binding the endpoint and adding the method calls itself, and can bypass IIS.
If NAV object code can only be executed from within NAV (or the NAS/NODBC/Etc...), then what is hosting these web services that a) ties the method call back to the C/AL that will execute, and b) provides the runtime environment?
That's why I was wondering if the NAS would still be necessary. You're saying it's the "middle tier". Is that a standalone Windows/Web service that will now run and execute the C/AL?
The new help for NAV 2009 is also available online, and there are a lot of helpful tutorials in there: http://msdn.microsoft.com/en-us/library/cc160853.aspx. Look in the installation area to see if you can find the answers you are looking for. Also look around the NAV2009 forum, there's a lot of information there. One of the Microsoft people on this board, Freddy K, has a technical blog (http://blogs.msdn.com/freddyk/), and he is always contributing to this type of topic.
RIS Plus, LLC
Thanks, Den.