Database Related

What Is A Role In A Database?

A database role can be defined as the collection of permissions that can be assigned to a user.  In a majority of today’s Relational Database Management Systems (RDBMS), there are predefined roles that may be assigned to any user of the database. However, a database user can also create their own role if they have the privilege of using the function CREATE ROLE.

When talking about SQL Server, there are two primary types of database roles. The first are fixed database roles that are predefined when the database is created. The second are flexible database role that can be created. Members of the db_owner database are those who can manage the fixed database role membership. They have the authorization to carry out all configurations and maintenances on the database. The flexible database roles are managed and by any user within the database that has been set the authority to do so.

Every database also has the public role that is predefined. It is a role that cannot dropped by a user nor can anyone add or remove users to this role. The permissions granted to this role are automatically inherited by all users within the database. Therefore it is necessary to take caution with the public role. Only assign to it the permissions that you want all users of the database to have.

Within a newly initialized database system, there is only one predefined role. This role is that of a super-user. By default, the name of this user is the same as the operating system that initializes the database. The role is named postgres customarily. For creating more roles, you need to connect as this initial role to have the authority to create more roles. Every connection to the database server that is made then on is in the name of some particular role. This role is what defines the initial access privileges for the client that has accessed the database server.

Most RDBMS have the CREATE ROLE syntax for defining a role. Once a role has been created, the GRANT syntax is then used further for granting permissions and authorization to that particular database role. However, this is just the general syntax that is used, in reality, the syntax varies in detail from RDBMS to another.

Leave a Reply