Table of Contents

Picking the right business intelligence (BI) tool is essential to helping you beat your competitors, better serve your customers, and make smarter data-driven decisions. However, there's no one-size-fits-all tool for every enterprise. Not all BI users are created equal, and not all users should have the same level of access to sensitive and confidential data.

User-level security is a way to restrict your enterprise data on a "need to know" basis, making it much less likely that you'll suffer a devastating data breach. But what is user-level security exactly, and why is it so important for your BI systems?

What is User-Level Security?

User-level security is an IT data security practice. When it's in place, the security settings vary among individual users. User-level security might apply to a file, database, or other resources. For example, you might prohibit members of your HR team from looking at accounting data, while also blocking your accountants from looking at HR complaints and disciplinary actions (unless there's a good reason to make an exception).

It's important to distinguish between user-level security and share-level security. These are two different security settings, as we explain:

  • In user-level security, specific users may (or may not) access a certain resource. Organizations achieve this by creating new user accounts for separate individuals. Each has a unique login username and password that the user must enter correctly to access the resource. Implementing user-level security gives you fine-grained control over user access. This makes it easier to analyze usage patterns and guard against insider threats.
  • In share-level security, access control relies on having the correct password, without assigning individual usernames. The password is universal and shared among all people who should have access to the resource. While share-level security can be more convenient, it presents obvious security issues. For example, it's possible for the password to be distributed outside the intended user group.

User-Level Security for Microsoft Access

In terms of business intelligence and analytics, "user-level security" most often refers to the Microsoft Access database management system. This is part of the Microsoft Windows BI ecosystem (along with other software such as SharePoint, Power BI, and SQL Server). By default, Access defines two default groups—the Admins group for administrators and the Users group for ordinary users—although you can add more if you like.

User-level security is more difficult to achieve in Access databases newer than Access 2007, as the code has changed. Access still supports user-level security for databases that have not been converted from the .mdb format. You can tell whether an Access database supports user-level security by seeing whether it has the older .mdb file format, rather than the newer .accdb, .accde, .accdc, and .accdr formats.

The User-Level Security Wizard in Access walks you through setting up the user-level security functionality in older Access databases. Below is a quick tutorial of the process:

  1. In the Access user interface, go to Tools > Security > User-Level Security Wizard.
  2. Create a new workgroup information file, which contains user and group names for the people who will use the application. You can also modify the current file if one exists.
  3. On the next screen, you'll see the proposed file name and a randomly generated workgroup ID (WID). You can also optionally enter your name and company. At the bottom of the screen, select whether you want this file to serve as the default permissions for all Access databases.
  4. Define the database objects to which these new security settings should apply by selecting or unchecking the appropriate boxes.
  5. Select the groups that should be added with special permissions. For example, you can define group memberships for users who should only have read-only access, or who can only add new data fields.
  6. Define the default Users group permissions, which will apply to all users on the given machine.
  7. If necessary, add users by clicking on the "Add New User" button. When you create user accounts, you'll also need to create a unique password for each user. Next, assign the new users to the appropriate group memberships.
  8. Finally, you can optionally create a backup (unsecured/unencrypted) database. Click on the "Finish" button to complete the wizard.

Alternatives to User-Level Security for BI Systems

If user-level security has largely been eliminated from modern Microsoft Access databases, what can you do if you want to implement different levels of BI security for different users?

One option is "row-level security." This has largely replaced user-level security in Microsoft applications such as SQL Server and Power BI, as well as other BI and analytics applications such as Tableau. Row-level security is even more fine-grained than user-level security: you can control the visibility of individual rows within a database for different users and groups of users.

Row-level security and user-level security each come with their own pros and cons. For example, a database may contain certain records, fields, or rows that you want certain types of users to access—as well as other records, fields, and rows that need to be absolutely off-limits. Row-level security allows users to view and query precisely the data they need, without having to split the database up and create a more complicated access policy. However, row-level security is more intricate and fine-grained than user-level security, and it may be easier for administrators to slip up and accidentally expose information to the wrong users.

How Integrate.io Can Help with BI Security

Whether you've implemented user-level security, row-level security, or even share-level security, we can all agree that keeping your BI data secure should be of primary importance. But what does BI security look like in practice?

By using ETL tools like Integrate.io, it's much easier to keep your sensitive and confidential data securely under lock and key. Integrate.io is a powerful, feature-rich platform for ETL and data integration, with security our top priority. We use SSL/TLS encryption for all of our websites and microservices, and also follow best practices for physical security, network security, and system security.

Get in touch with our team of data experts today for a chat about your business needs and objectives, or to start your 7-day pilot of the Integrate.io platform.