Setting Database Access Permissions

Server security mainly depends on how correctly you can configure access permissions on objects. Providing a user with excessive permissions may cause many issues. No, a user will not use your errors. Instead, any hacker or I will do this. In this case, you can forget about your tables with data or the whole database.

For some reason, the security of the database is protection from the outside, such as a hacker. However, this happens very seldom. I am a programmer in a big company and an administrator does not even think about protecting the server ports, where everything is open. There is a bunch of databases, programs, and even an FTP server on a single server and it has never been hacked over the past 5 years. Fortunately, I persuaded the administrator to deploy the WEB server on a separate hardware. Otherwise, if someone knew the IP address of our main server, any slacker would be able to hack it. Neither the database nor Windows has been patched for several years.

However, internal issues arise every day due to an incorrect security policy. All users log in with administrator rights and can create anything they want. This is a real problem because the excessive permissions allow slackers to show their complete illiteracy. Therefore, we will consider security regardless of where the threat comes from – from a hacker or from a user.

In this article, we are going to consider all the necessary basics of security protection from both hackers and users. I chose MS SQL Server as an example because it contains everything available in other databases (Oracle, MySQL, etc.) and has additional security management capabilities. Someone might think that this makes MS steeper. However, sometimes, additional features can be excessive, causing problems.

Server roles

In Windows and other operating systems, there are groups and users to manage permissions. We can combine users into a group and grant rights to all of them at once, which is much easier than assigning rights to each user individually. For these purposes, in databases, there is the concept “role”.

Assume that 100 users are to have the permissions to read data from a specific table. To provide each user with this permission is a bother. It is much simpler to create a role that is permitted to read, and then add all the required users to it. The result is similar to grouping.

In SQL Server, there are two types of roles: server and database. Server roles are pre-defined and cannot be changed.

Open the Security/Server role branch in Enterprise Manager so that you can see a list of available roles in the right part of the window. The description defines what the user can do with the corresponding role.

Server roles in MS SQL Server and the role manager window

To add an existing user to a role, double-click the role row. In the window that appears, you can add users to the role or delete them. The Permission tab describes in details what each user can do.

Users

To manage users, open the Security/Logins branch in Enterprise Manager. In the right part, you will see a list of all server users. By default, the access is granted to domain administrators and built-in login accounts such as sa.

To add a new user, right-click anywhere in the empty right part of the window and select New login in the menu that appears. At the very top of the window, select a username. If you need to choose an existing domain or computer user, click the (…) button to the right of the input field and you will see the user search box in the domain.

Adding a new user

Below, you can select the type of authentication – Windows or SQL Server. If you select Windows, you do not need to specify a password because the server will fetch it from the system. However, you can toggle either Grant access (permit access) or Deny access (prohibit). In the latter case, the user will be registered in the database, but they will not be able to connect – it is prohibited.

If you select SQL Server authentication, you will need to set a password because in this case, it will be stored in the system tables of the database server. Please note that even if only the Windows authentication is specified in the server settings, you can create SQL server records, but you will not be able to log into the system with these records.

On the Server Roles tab, you can specify what server role will be granted to a user. Therefore,  even at the stage of creation, you can add users to the necessary roles.

User access to databases

On the Database Access tab, specify the databases the user can work with. Here, the window is divided into two parts: in the upper half, you can select the database to which the access is permitted, and in the bottom list, you can select the database role. This role in the database will define the user permissions. Several roles can be granted to one user.

Create an account qq, which will have access to the Northwind database. This is a standard test database created when deploying the server.

Save the changes.

Now, open the Databases/Northwind/Users branch and see the list of users who are permitted to access the selected database. Please note that there is the qq account here. There is no account in other databases because access to them for our new user is forbidden.

Database roles

Each database can have its own roles, which define the access permissions on objects. Many administrators do not like to bother with these permissions. Thus, they install the built-in default public, which permits almost everything. If there is a lack of permissions for the public role, simply add a user to the System Administrator server role. In this case, the database becomes vulnerable.

Each user should be provided with their own and necessary permissions. What is not permitted must be prohibited. Roles that already exist on the server must not be used because their permissions are open to everyone. It is recommended to even delete them all, especially the public one.

Creating a role

To create a new database role, right-click the Databases/Database Name/Roles branch. In the menu that appears, select New database role. The Database Role Properties – New Role window opens. In the upper part of the window, type the role name.

For example, we want to create a role for accountants. To do this, type Buh in the Name field.

Creating a database role

Below, select a role type, for example, a standard default one. In the center of the window, there is a list of users who will be added to the role. So far, the list is empty. However, if we click Add, we will add users, for example, to the qq account created earlier. There is nothing else to be done at the stage of creating a role. Save the changes by clicking OK.

Access permissions

Now, we are going to see how we can set permissions. Double-click the created Buh role to open the window for editing. Please note that the Permission button is available now. Only when the role is registered in the database, we can change its rights. Click this button to open the Database Role Properties window.

Setting access permissions for roles

At the top of the window, there is a list of database roles so that you can quickly switch between them. Now, the Buh role is selected. In the center of the window, there is a big grid with the following columns:

• Object – names of objects;
• Owner – an owner of an object;
• SELECT – permission to view data or execute the SELECT statement. It is available only for tables and views;
• INSERT – permission to add data or execute the INSERT statement. It is available only for tables and views;
• UPDATE – permission to modify data or execute the UPDATE statement. It is available only for tables and views;
• DELETE permission to delete data or execute the DELETE statement. It is available only for tables and views;
• EXEC – permission to execute stored procedures and functions. It is available only for stored procedures and functions;
• DRI (declarative referential integrity). It is available only for tables, views, and functions.

There are no permissions for the new role. To be able to view a table, for example, Categories, check the box at the intersection of the Categories row and the SELECT column. You will see a green tick in the box, which means a permission. The second click changes the tick to the red cross mark and means prohibition. This can be necessary if you grant a permission to the user and add them to another role, where access to the selected action is permitted. The third click removes any permissions on the action and leaves the box empty. This means that there is no access; however, it can be delegated if the user is added to another role with the permissions on the object or permissions are explicitly specified.

If you select a row with the object of the table or view, the Columns button becomes available at the bottom of the window. Assume that you selected the table and clicked this button. The window opens, in which you can set permissions for individual table columns.

Setting column permissions

This is indeed a great possibility because some columns responsible for the database integrity must not be changed by users or hackers. It is better to forbid the UPDATE or SELECT operations (if possible) for these columns.

Individualism

Roles are convenient to use when it is necessary to combine similar users. For example, numerous accountants need to get access to financial tables. To grant permissions to each accountant is time-consuming. It is much easier to create a role for the accountant, grant permissions to it and then add all the accounting accounts to this role.

However, there are cases when permissions should be unique for a user, or in addition to the permissions granted by the role, it is necessary to grant additional permissions. For example, one of the accountants needs to have access to tables of the human resources department. In this case, it is better to add permissions directly to the accountant rather than to create a new role.

We explored the roles first to get used to them. In most cases, there is a separate account for accounters, a separate account for economists, etc.  In this case, most people connect to the server using one account. Thus, to control who does what is impossible. It is better to use individual permissions where necessary, while each user must have their own account.

Permissions on tables

Let’s see how we can grant permissions on particular objects, for example, on tables.

Select the Databases/Northwind/Tables branch in the object tree. In the right part, a list of all tables opens. Right-click any table and select All tasks/Manage permissions. The Permission Properties window opens, which is similar to the Database Role Properties window with the list of users instead of the list of objects. The object is a table we clicked and its name will be listed in the drop-down menu of the window. Now, we need to set permissions on this object for different users.

Setting permissions on a table

The list of permissions is as follows: view, update, add, delete, execute, and manage. If you click the Columns button, the window opens for setting permissions on the object at the level of table fields for a particular user.

Views

We have two tables. One of them stores the list of employees, while another table contains information on the number of hours worked per month and the wages received (official and hidden wages).

Assume that a tax official comes to you and asks to show the wages of workers. In addition, they ask if you have paid all the taxes. What actions do you need to perform on your side?

The first proposal came from the third row – to create a new user who is permitted to read tables including a list of employees and salaries. In addition, you should not forget to close the column with the hidden wage. Actually, the solution is correct but absolutely ineffective.

The best option is to create a view, an SQL query that selects data. In the database, it looks like a table. You can select SQL data using queries and grant permissions. It turns out that the query will be executed against the query.

To create a view, run the following query:

CREATE VIEW salary AS
SELECT fields for a tax official
FROM Employees, Wages
WHERE joins

Now, there is a new Wage object in the Databases/Northwind/Views branch. If you right-click it and select All tasks/Manage permissions, the window for granting permissions will open. Set a permission for the tax official and save it. To review the content of the view, run the query:

SELECT *
FROM salary

As you can see, there is an access as to a simple table. The tax official will also think that they see actual data. However, in fact, this query will contain only the data we need.

In real life, the tax official cannot be deceived so easily because they are not fools. However, this example shows that the view can be a perfect security method. We can display the data users need and nothing else. At the same time, we have all the tools for managing permissions on the view without affecting the access permissions on the tables.

Thus, different views to the same tables may show different data. If you want to show an additional column, add the views to the query. In this case, there is no need for changing permissions.

System views

In each database, there may be system views created by the server automatically. I do not recommend granting a permission on them because they can show some additional information, which may help a hacker set permissions or simply destroy data. The system views start with the sys prefix and System is specified in the Type column of the list.

Procedures and functions

Modern database servers support stored procedures and functions. This is a PL/SQL or Transact-SQL code depending on the database that is executed on the database server. Using these procedures, we can perform any operations on the server or simply select data, as in the view. We can set permissions on each procedure.

When reviewing roles, we have already seen the procedures in the list of objects that you can set permissions on and only the EXEC column is available in these rows because the procedures can only be executed.

Stored procedures and functions are stored in a particular database. To see procedures of the Northwind database, select the Databases/Northwind/Stored Procedures branch. There are many system procedures, the names of which start with the dt_ prefix and  System is specified in the Type column. It is recommended not to grant access to these procedures, if possible. You can see functions in the Databases/Northwind/User defined function branch.

To change permissions on procedures and functions, right-click its name and select All tasks/Manage permissions in the menu. In the window that appears, you can change only the EXEC column for procedures and the EXEC and DRI columns for functions.

Permission policy

Some administrators set permissions based on the existing role, for example, public. This is not true because, in this role, there may be permissions users do not need. Thus, try to set a brand new permission.

As for me, I always create a new role and grant a minimum set of permissions. If users ask for more permissions and they are actually necessary, I add more permissions. If you permit everything by default, there is no guarantee that in future unnecessary and dangerous rights will be deleted.

Another issue to set fewer permissions is a habit. Users can get used to the fact that many permissions are granted to them and then the ban will cause a grave scandal. Nobody likes when their rights are infringed.

Tables/Databases

Databases store their settings and hidden properties in system tables and databases. They do not differ from other database objects and permissions can be set on them. In no case, do not permit users to access these tables without a special need.

In SQL Server, important system data is stored in the master and msdb databases. Thus, these databases are to be protected. In Oracle, each database exists as a separate object and system tables are stored together with users’ ones.

Almost all database servers offer to install test databases that can be used for learning or testing the system. If you have them, delete – because a public access is set to these databases. If a hacker knows names or properties of any existing object in the system, it will greatly simplify their task.

When connecting to a test database, you can execute some commands on the server and damage the OS or a working database. No additional stuff should be in the system. Moreover, such tables/databases have quite high permissions even for a guest.

Summary

Despite the fact that we used MS SQL Server as an example, the concepts of permissions, roles, and authentication exist in all databases.

Knowing all the rules we considered, the only thing you need is to explore the peculiarity of their use in your database.