Security: Users and Permissions


There are many different facets to "security".

For an organization that maintains an on-premise data center , for example, they must be concerned about physical security, including locked doors, cameras, gates, barricades, guards, etc.

Most organizations must also concern themselves with security for hardware devices, including networks, routers, switches, access points, servers, desktop and mobile devices, and even things such as printers, fax machines and phones.

Just as important, there are many different types of information security, such as firewalls, anti-virus and anti-malware software, anti-ransomware measures, and user management.

User Access and Control

There are two main concepts central to any system that manages user access: authentication and authorization.

Authentication is the process of verifying that someone is who they say they are. Software applications do this by using passwords, face-recognition, key cards, tokens, etc. The idea is that the user claims to be a certain person, and their password is the "proof". This same thing happens when a person uses a badge to gain entry to a physical facility; they are presenting their credentials and asking to be authenticated.

It is also common to provide credentials to machines or devices, so they can be authenticated. Web servers often communicate with each other to exchange data, and they use tokens to "prove" their identity so they can gain access. The same thing happens when an automobile calls OnStar - the remote system will verify the car's identity using some form of authentication.

Once a person (or machine) is authenticated, there is an ongoing process of authorization that takes places to allow or deny access to various resources or systems. A Comic-Con conference attendee, for example, may have access to certain areas and be denied access to other areas, such as the VIP suite. In this type of case, the authorizations are often pre-printed on the credentials badge.

For software systems, authorization sometimes occurs on every request. For example, once a user logs in, a token, cookie or other file may be used to track that they have been authenticated. On every request to a new web page, for example, the token is presented to the server and the application uses the token to determine whether the user is authorized to the requested content or action.

SQL Server utilizes both authentication and authorization, at multiple levels. For example, users may be authenticated at the server level and/or at each database level. A user may be authorized (GRANTED) permissions to use various databases, tables, views, stored procedures, or specific data. They also may be DENIED access to any of those same types of objects.

Managing Users

Software authorization is carried out using permissions. Permissions include such things as viewing data, inserting data, updating and deleting records, and activities such as creating tables, running stored procedures, and backing up or restoring a database.

There are several different common methods to manage users for a software system. These are common for most types of systems and applications.

Where possible, it is preferred to manage user roles, rather than individual users. For example, it is easier to set permissions for a role named "Managers", than to set permissions for every individual person who is a manager. Remember, too, that the term "user" may refer to another machine, not just a person. Users are then assigned to one or many roles, as needed. Think of a role as a "group". Users belong to one or more groups.

When permissions are set using roles, an individual user receives all the permissions from all the roles to which they belong. Consider a user named Frank, who might be assigned to the following roles:

  • Accountant
  • Manager
  • Safety Team Lead
  • Financial Reports Viewer
  • Database Backup Specialist

Each role has a set of permissions, such as the ability to view certain tables or use specific web pages. As Frank uses the software system, he will receive the cumulative set of permissions for all of these roles. As an Accountant, perhaps he can perform certain data entry functions in the application. But Accountants are normally not allowed to see financial reports. Because Frank is also assigned to the Financial Reports Viewer role, he also has permissions to see financial reports.

When users receive the cumulative permissions for all the roles to which they are assigned, they are given the highest (or most capable) permission from all of those roles. In Frank's case, the Financial Reports Viewer role has higher permissions than the Accountant role, and authorizes him to do more than if he only had the permissions granted to an Accountant.

The DENY permission has a special function in authorization. If a user is assigned a DENY permission, it overrides all other permissions of the same type. Consider Frank and his permissions. Perhaps Frank has announced his retirement and will be leaving at the end of the month. As such, the IT team may assign him to the Retiring Users roles. Perhaps that role has a DENY permission for financial reports. And DENY permmission override all other permissions for the same objects. So, even though Frank is normally permitted to see financial reports (as a member of the Financial Reports Viewer role), this new role has a DENY permission that overrides all other permissions for financial reports, and Frank can no longer view them.

Managing Unwanted Users

We all know that many security efforts revolve arouund unwanted users, such as hackers. It is beyond the scope of this article to discuss the various types of intrusions and mitigations that are related to hackers. However, it might be helpful to point out a few things about how this affects database management.

First, it is best practice in SQL Server, to use schemas to help hide or obfuscate the database design. The term obfuscate is commonly used in software design and development, involving methods to make it harder for hackers and others to see, discover and reverse engineer software and data.

Most other databases do not use schemas. Oracle and mySQL, for example, call the database itself a "schema". But you cannot organize or sub-divide the database into areas in the way that SQL Server does with schemas. There are three common reasons to use schemas:

  • Organization: schemas provide a way to group sets of objects to better see and use them
  • Obfuscation: if hackers do not know what schema is being used, they are hindered in trying to query tables and views - because they do not know in what schema those objects exist
  • Security: schemas provide a simple way to GRANT or DENY access to an entire group of objects within a schema


Consider the company that employes Frank in the example above. Their software application has several modules related to Accounting, as well as modules on Scheduling, Safety, Human Resources, etc. At the database level, it might be possible to place all tables and procedures related to Accounting under an "acx" schema. That would make it easier to GRANT the Accountant and Financial Reports Viewer access to the acx schema, instead of each individual table in the group. It would also be possible to easily DENY access to the acx schema to other users - so even if they stumbled on a web page or report in the accounting module - they would be prevented from seeing any data in those tables.

Lastly, even if hackers somehow obtained access to the database, they would have a hard time finding the accounting information, because they would have to guess where the tables are located. For example, they might be interested in querying tables for accounts, customers, vendors, etc. But if a hacker could not see the database structure, they would have to know to query acx.accounts, not just accounts or dbo.accounts. (Notice the suggested schema is acx, not acct or accounting - obfuscation...)