Database Roles and Privileges
A database role is a set of privileges and permissions that can be given to database system users. After you create a user with a password, the next thing you will likely need to do is assign roles.
In a database system like SQL roles can also be described as security groups or security principles. All of these terms describe a mechanism for controlling the abilities and capabilities of database users, including the information that a user can view, select, drop, flush and edit. Most modern database systems, like Oracle, MongoDB, MySQL and PostgreSQL come with predefined or built-in roles or groups, that can be granted to database users out of the box.
A database administrator or a DBA in your DBMS (database management system) will likely be responsible for assigning roles to database users. In fact, in addition to the create user process in a database, the next most important user related activity a DBA will perform is likely the granting of roles and security groups.
Granting Database Roles to Users
Assigning users to roles is often referred to as a grant role. Roles can be granted when a database user is newly created or they can be assigned to existing database users. And you can also revoke roles at any time. Once a role is granted a user will have permission to perform an operation or command in a database. As a DBA, you can typically list users in a group, to see who has access to what, using a show grant or show user privileges type command, letting you quickly view server state. You may grant your database users roles manually through a GUI (like SQL Management Studio), or you may use a command line interface like PowerShell or you could use automated scripts. And in some cases, you can even inherit roles from other sources like your computers operating system, a content management system (CMS) or a customer management system like Sitecore.
A user can be granted multiple roles and put into multiple security groups giving you maximum flexibility when you are configuring role authorization and privileges. In fact, in a large database, a user will more than likely be granted numerous roles. This can sometimes cause issues if a user is granted roles that conflict, and role consolidation is not logically possible (however some databases are intelligent enough to detect conflicts, and may give you warning when a conflict is detected between resources). Other databases will probably default to using a role with the most restrictions, as this approach is the most secure (however be aware that this means that a user may get a permission denied message when they don’t expect it). Also, roles may be different on production and test versions of a database. You may want to give developers full permission on a test database, but only partial permissions on a production database. User permissions can vary by context, even for the same types of users.
Finally, in multi-tenant databases (where your database system is shared by different applications), you may grant roles by application or container. This means that even if your database server is hosting multiple applications, you can assign roles by application instead of the entire database at once.
Database Built-In Default Roles
Most databases have a predefined set of roles that describe different privileges and permissions. These roles often use obscure and technical names. For example, in SQL server a dd_ddladmin role allows users to modify, create or drop any objects in database. Other default roles in SQL include db_datareader and db_datawriter. These roles are little more self explanatory then dd_ddladmin – db_datareader is for giving read access to a table to users. Db_datawriter is for giving rite access to a table to users.
And there are some roles that are common to most databases like a “db_owner” role that you need to be especially careful about as a user with this role will have full permission to alter your database, making them a super user. Obviously you want to be very careful about anything that will grant all privileges in one swoop.
MongoDB has even more obscure names for its default roles including changeStream, collStats, killCursors, readwrite etc. These terms simply refer to different commands a MongoDB database user has access to.
In any case, even if your database uses unusual terminology to describe a role, the underlying meaning is usually quite easy – a role will almost always revolve around a database users’ ability to create, delete, flush, select, view or modify data. A role may also refer to a specific database command that performs a batch action to alter data in a certain way, a command that allows a user to view summary statistics or a command that allows a user to create a new index or view using existing data.
And it’s important to realize that these default roles may not have a direct correlation to the front-end layer of your software application. For example, if you have a form on your front end that lets you enter user name, phone number and address, in your database you may need to individually grant users privileges on the name, phone number and address table rows. For these types of cases, you will probably want to create your own custom roles that have a more direct relationship with the structure of your software application.
Creating New Roles
Default database roles may be too cumbersome, since they really don’t consider the design or schema of your application. In fact, usage of these low-level roles is likely to be inefficient when you are trying to grant permissions to your database users.
In this case you can create custom roles (databases like Oracle and MariaDB even have create role type commands to create new roles). Custom roles are often created and assigned according to the position somebody holds in your organization (for example management, customer service, sales, end user etc.). Managing user roles in this manner is likely the most logical, as people with the same position likely need access to the same data.
You may also consider creating roles for different components of your system (for example your web API; your administrative control panel; your companion IOS or Android mobile app; your Windows or Mac desktop software application; your ecommerce website; or automated batch jobs). These types of components often need to interact with your database, but they already may have their own security. In this case you can assume that anybody who has access to the component, is authorized to view or edit the database in your database. But instead of using a role assigned to a user, you can use a role assigned to the component. This option is also useful for batch jobs that may perform functions like flushing or archiving data on a regular basis.
You can also often create a role hierarchy. This means that you can create a role that inherits from another role, which you can further customize. This type of object-oriented type inheritance can significantly speed up role creation and management. And after you create user with a password, if have a role hierarchy properly setup, giving the newly created user the right permissions will be easy and quick.
Tracker Ten Desktop Application
Our own Microsoft windows Tracker Ten application does not need to support complex roles, as it is intended as a single user database application. In our case, you can simply setup encrypted read and write passwords to control access to your database file.
