SOX Audits and Dynamics NAV, Part 1: “The Auditors are Here and They Have a Few Questions”
If you work at the intersection of systems and accounting, as I do, the title of this post may put you on edge—because you know how the story goes. Your system is working fine. You may even have time to get to some of the “nice to haves” as the “must haves” are going pretty well. But then the auditors show up. And you end up spending a LOT of time with them—without much to show for it.
In this series of posts, I’m going to help you prepare for that audit. The series is based on a presentation I gave at the last NAVUG Focus conference. My hope is that in the course of preparing for that audit, you’ll learn more about your system. And this knowledge will pay dividends, both in terms of saving time and giving you the warm feeling that folks won’t do something stupid (or malicious) to your system.
It’s almost impossible to guess exactly what your auditors will ask for. For some clients, I’ve answered questions for six months, and they STILL had more auditor questions to respond to days before the final audit was due. Oh, joy.
While you can’t anticipate auditor questions, what you can do is implement proper controls in your system. I’ve done this for multiple systems, but in this series of posts I’m going to apply it to Dynamics NAV specifically. I’m going to focus on:
- Month end closing in Dynamics NAV
- NAV security from an audit perspective
- Change log (i.e. tracking changes to data)
- IT things that you need to know—even if you’re not responsible for administration.
I already covered the first topic—month end closing in Dynamics NAV—in a post I wrote last year.
As I go through the remaining topics, you’ll see I rely mostly on my reporting and database skills. I know most auditors don’t test systems by looking at data. Instead, they work with end users to see who can and who can’t perform a certain task. Or, if the system assigns names to transactions, they’ll ask for records of who touched which transactions.
However, as a data guy, I know that if I have control over key data in the system, I don’t have to worry about which processes the auditors will use to test it. I’m already covered.
Additionally, as a business intelligence guy, I find it’s much faster to look at data to see what people can do rather than talk to people and then ask for records to back up what they say. I trust what the system tells me.
So, let’s get started with the first topic in this series: NAV security from an audit perspective.
NAV Security From an Audit Perspective
NAV security is a complex beast. Let me make it simpler by stating that the ability to READ data in a table generally isn’t an audit problem. Being able to CHANGE data is. *(See additional commentary on this point at the end of this blog post.)
So, the first thing I do in security is grant read access to all tables for all users. This way I can focus on who can update data.
Let’s start with a quick tour of NAV security.
Permission and Permission Sets
NAV security is based on users getting permissions to a table. Having “permission to a table” means that a user can:
- Read: Look at the data without changing it
- Insert: Ad new records to a table
- Delete: Remove records from a table
- Modify: Change records directly or indirectly to a table. (Indirect change is what happens when a process updates multiple tables when posting.)
Permission is granted as part of a “permission set.” Permissions sets are just that—sets of permissions, each one granting some rights to a group of tables that a user needs to perform a certain task.
To find permissions, we navigate to Permission Sets:
Here, I’ve filtered for the permissions I want.
Let’s look at two permission sets: P&P-JOURNAL and P&P-JOURNAL, POST.
P&P-JOURNAL allows users to process checks, among other things.
Now, we choose Permissions from the ribbon:
We can see that in order to make a Purchase and Payable Journal (i.e. P&P-JOURNAL), the user needs Read rights to the Vendor Table and Read, Insert, Modify and Delete rights to the General Journal Table.
Now, let’s look at the second permission set: P&P-VENDOR, EDIT:
Looking at the Vendor line, we see that the user has all permissions to the Vendor Table—Read, Insert, Modify and Delete.
Assigning Permission Sets to a User
We can assign permissions sets to users in two ways—directly through the Users page or through a User Group.
Assigning Permission Sets Directly to a User
If we navigate to the Users Page, we can simply add a permission set to the user on the User Permission Sets fast tab, as below:
Assigning Permissions Sets Through a User Group
In later editions of NAV, we can create permission sets through User Groups, which we can then assign to individual users.
Let’s say, for example, that we have five people who process purchase invoices and update vendor information. (Granted, this isn’t great for separation of duties, but it’s useful as an example.) So, we create a user group call AP and assign two permission sets. We do this through the User Groups page:
Then choose User Group Permission Sets from the navigation:
Once there, we choose the appropriate permission sets:
We can then assign users to this user group from the User Group Members:
Or we can go back to the Users page and use the User Groups fast tab:
By now, you get the point that it’s really easy to have multiple permission sets pointing to the same tables. As a result, it can be challenging to figure out definitively who has access to which tables.
Before finishing with the topic of assigning permissions, I have two more points to make:
1. Highest Permission Wins
When a user has multiple permissions for a given table, the permission with the greatest level of access wins. So, in the example above, the user can update the Vendor Table.
2. Permissions and the General Journal Table
The General Journal Table is the Swiss army knife of NAV. If you have access to the General Journal Table, you can do LOTS of things. For example, the same table supports both cash receipts and cash payments, which is NOT a good thing in any kind of secured environment.
So with these permissions, you have to go one level deeper to make sure no one has more access than they should.
For example, let’s go back to our P&P-Journal permission set. Find the General Journal line, move to the right and look at the column for Security Filter.
If we click it, we see that we have a filter which limits this permission to the PAYMENTS journal type.
With this filter, we ensure that even though the user can access the journal lines, they can only make payments journals.
Preparing NAV Security for an Audit
This whirlwind tour should make clear that tables are the basis for NAV security. But there are hundreds and hundreds of tables in NAV! How are you supposed to go through them all?
In short, you don’t have to. Because the first step in making sure that only the right folks have the right access is to narrow your focus.
Step #1: Narrow your focus
While NAV has hundreds of tables, from a separation of duties/control perspective, only a few really matter. Because even the most difficult diligent auditor has only a limited amount of time.
Therefore, we start our analysis by looking at all the NAV tables and labeling them as follows:
- Setup – Accessed only occasionally, such as payment terms. Very few folks should have access to these tables
- Master Data – Customer, Vendor, Chart of Accounts and similar tables. Auditors are interested in these tables.
- Transactions – Where actual dollars are entered in the system. This is another area that auditors focus on. Multiple users will have access to these tables.
- User Admin – These are security tables. Only administrators need access.
- NA – Tables that no one cares about, such as comment lines.
And here’s a sample of my labeling:
I built this list in Excel and the uploaded it to a custom SQL Server Table in my database.
Step #2: Create a report to see who has access to key tables
Once we identify the key tables, we create a report that checks which users have permissions for those tables.
Thankfully (with the exception of the security filters mentioned above), NAV stores all security related data in regular SQL Server columns. It’s not easy to see who has access to what from the screen. But it’s relatively ease to see from the database.
So, we create a query and then run a simple SSRS report to filter by table type and find out which users have access:
Through this method, we can quickly answer questions, such as “Who has access to Vendors?” or “Who can touch the chart of accounts?”
If you’d like either the SQL code or the SSRS report, please let me know.
My Next Post: Change Audit Log
I hope this overview of Dynamics NAV security and your audit was helpful. In my next post, I’ll review the change audit log.
* More on my statement above that the ability to READ data in a table generally isn’t an audit problem, but being able to CHANGE data is. There are two obvious exceptions to this.
- Payroll Data: In all my NAV experience, I haven’t dealt with payroll. If you do, then my advice for granting read access to everyone on every table will need to be adjusted.
- Tax ID# data for 1099 Vendors: I can almost guarantee that no one in your company has thought about their financial system being a source of PII (personally identifying information). Thankfully, this doesn’t come up much in financial audits. If it did, fixing the problem wouldn’t be obvious with standard NAV (and it wouldn’t be obvious with most other systems either).