Options for Granting Privileges in SQL Server, Oracle and PostgreSQL

Problem

Granting a privilege on a database object is one of the most common activities DBAs must perform while paying attention to giving enough permissions to do the job, but not too much access. Remember the principle of least privileges! This is tricky sometimes, but as we have seen in many other examples, there are some differences in achieving this in the three RDBMS, plus there are some new roles introduced in SQL Server 2022. In this article, we will look at how to grant permissions in SQL Server, Oracle, and PostgreSQL.

Solution

This SQL tutorial will review the different ways of granting privileges in SQL Server, Oracle, and PostgreSQL, the differences in the concepts of roles, schemas, and owners that are also involved, and the way to grant permissions on a whole schema. While writing this tip, I realized that it was getting bigger and bigger, so it is now a two-part tip as the topics involved are really too many! In this first part, we will focus on the concept of Roles.

For the examples in this article, we will use the free sample database, Chinook, which can be downloaded here.

Granting Privileges in SQL Server

In SQL Server, there are different ways to grant permissions using Management Studio or T-SQL scripts. First, we need to describe the entities (principals) on which we can give permissions and what are the possible permissions. In particular, we need to discuss Principals and Roles.

• Principals are the SQL Logins or Active Directory Logins that are used to access SQL Server, as well as the corresponding database users and roles:
  • Server Level principals
  • Database Level principals
• Roles are principals, but they can also be assigned to principals, thus helping us to assign specific permissions. We then have a subdivision of roles depending on the level at which they act:
  • Server roles
  • Database roles
  • Application roles

The best practice is to grant specific privileges to roles and then assign the roles to the login/user to separate and not grant directly the same privileges to several users. For example, let’s assume we need to assign read permissions to the tables Invoice and InvoiceLine to finance users.

Let’s create a database role:

CREATE ROLE [Finance]

Grant the SELECT privilege on the two tables above (Invoice and InvoiceLine) to this role:

GRANT SELECT ON [dbo].[Invoice] TO [Finance]
GRANT SELECT ON [dbo].[InvoiceLine] TO [Finance]

Now, to grant a finance user the privilege to read from the tables, we need to assign the Finance role to the user. Let’s create a login and user with the role assigned:

USE [master]
GO
CREATE LOGIN [l.callahan] WITH PASSWORD=N'GoldenHorde', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Chinook]
GO
CREATE USER [l.callahan] FOR LOGIN [l.callahan]
GO
ALTER ROLE [Finance] ADD MEMBER [l.callahan]
GO

Now let’s try to connect using this SQL Login and check what this user can do:

The query below selects the top 30 rows from the Invoice table:

select top 30 * from Invoice;

But what if we try another table, like the Artist table:

select top 30 * from artist;

Since the user has no privileges assigned to this table, only the Invoice and InvoiceLine tables, the user is denied access. Remember the principle of least privilege!

We have just created an example of a Database Role informally. In the same way, there are a few fixed Database Roles that come with SQL Server and can help manage some permissions. For example, every database user has the public database role assigned. There is a list of all these roles in the official documentation with a description of the permissions, and the permissions assigned to the fixed database roles cannot be changed: Database-level roles.

One of the fixed database roles that I use a lot is db_datareader. As the name states, any principal with this role can select from any table in the database. For example, we need to allow users that compile reports to read from any table. So, we create a new login and database user named Report and assign it the db_datareader role:

USE [master]
GO
CREATE LOGIN [Report] WITH PASSWORD=N'pippo120', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Chinook]
GO
CREATE USER [Report] FOR LOGIN [Report]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Report]
GO

 Now we can access the database Chinook using the Report user:

Select from a couple of different tables:

select top 30 * from Artist;

select * from Genre;

As we can see, this user can now read from any table in the database. However, if we try to write to it:

update Genre
set name='Punk'
where GenreId = 4

We get the permission was denied message.

Server Roles SQL Server 2022

So far, we’ve talked about database roles. But, as mentioned before, there are also server-level roles. They can be created ad hoc as user-defined server roles (starting with SQL Server 2012), and there are some fixed roles as well.

There were some novelties introduced in SQL Server 2022, including 10 additional fixed server roles that are distinguished by the prefix ##MS_ and the suffix ##. (Official documentation)

Let’s discuss a few examples. The most powerful of the fixed server roles is sysadmin. With sysadmin assigned, we have permission to do everything in the SQL Server instance, almost as SA.

We can add a management login that should be able to perform any activity:

Note: Creating a new login in SSMS, the new fixed roles appear when we select the Server Roles page.

As before, we can use T-SQL:

USE [master]
GO
CREATE LOGIN [Management] WITH PASSWORD=N'Riesenhotter44', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Management]
GO

Now, we can test the new login:

Let’s try to take a database offline on the server:

USE [master]
GO
ALTER DATABASE [WideWorldImporters] SET OFFLINE
GO

To check that this is not possible with another user, we can try to bring it back online with the Report login:

USE [master]
GO
ALTER DATABASE [WideWorldImporters] SET ONLINE
GO

Now let’s bring it back online using the Management user:

USE [master]
GO
ALTER DATABASE [WideWorldImporters] SET  ONLINE
GO

Again, the new fixed server roles are identified by the prefix ##MS_ and the suffix ##. The complete list can be found in the official documentation.

Let’s try another example using ##MS_DatabaseConnector##. In practice, members of this fixed server role can connect to any database on the SQL Server instance without requiring a user account in the database.

Let’s assume that we have a supervisor that needs to connect to all databases in our instance without having a mapped user or other powerful privileges/roles:

USE [master]
GO
CREATE LOGIN [Supervisor] WITH PASSWORD=N'PinoLavatrice', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [Supervisor]
GO

As you can see, we have not mapped any database for this login. Let’s try to connect. Usually, we would not be able to expand any of the databases from Management Studio:

We can connect and expand all the databases.

Granting Privileges in Oracle

In Oracle, we have to deal with slightly different principals and roles. Even if the basic GRANT statement is the same in all three SQL dialects (with a couple of caveats we will cover in the second part), we will see that the principals differ in each of the three RDBMS.

As pointed out in a previous tip, the different terminologies and main differences in the three RDBMS: Relational Database Comparison between SQL Server, Oracle and PostgreSQL.

In Oracle, we have a Schemas/Users concept that differs from SQL Server, as explained in the tip mentioned above. But we also have Roles. Roles can be created ad hoc, just like in SQL Server, but there are also some fixed ones. Apart from these, we have system privileges that can be granted directly to the user or the ad hoc role. These roles can be assigned to users/schemas like in SQL Server for user logins. Let’s try the same example of creating a Finance role and assigning it to a specific user:

create role finance;

We have created the role just like in SQL Server. Now assign the privilege of select on the invoice tables to this role:

grant select on chinook.invoice to finance;
grant select on chinook.invoiceline to finance;

Create a specific user and grant the role finance to it:

CREATE USER "l.callahan" IDENTIFIED BY "GoldenHorde"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "l.callahan" ;
GRANT "FINANCE" TO "l.callahan" ;
ALTER USER "l.callahan" DEFAULT ROLE "CONNECT","FINANCE";

Here we immediately notice some differences in user creation. First, we need to assign a default and a temporary tablespace to the user. The default tablespace will be used to create new objects, while the temp is for temporary objects like CTEs. Tablespace is Oracle's basic storage structure, like SQL Server's database files.

Next, we grant the role to the user with a simple GRANT statement. This differs from SQL Server in that we ALTER the role to add a user.

Finally, it is not enough to grant the role; we must also set it as default, or it will not be used. The other remark is that we need to give a user the CONNECT role because it will not be able to log in (and obviously, this role needs to be default, too). This concept can be useful to give a user the ability to perform certain tasks that require high privileges, but not always, so we just assign the role with higher permissions to the user without putting it as DEFAULT, then when needed, the user himself can perform on a session the SET ROLE statement and assign the role to it, when the session is closed it loses the role or alternatively the user can disable it with SET ROLE NONE;

Now let’s try to connect with this new user and test it. Using SQL Developer, we create a new connection it:

Pay attention to the username here: since it has a “.” in it, we must use double quotes like before in the user creation; otherwise, we will get an invalid username/password error!

Once connected, we can try the same queries used in SQL Server:

select *
from chinook.Invoice
fetch FIRST 30 rows only;

Apart from the different dialect of Oracle PL/SQL that does not include a TOP clause but a FETCH FIRST n ROWS ONLY, we have the same result. Now, let’s try a table where the user is not authorized:

select *
from chinook.artist
fetch FIRST 30 rows only;

Please also notice the different types of errors raised by Oracle. It gives no information about the table artist. It says it does not exist!

Unfortunately, in Oracle, we do not have a role such as the db_datareader of SQL Server, but there is a system privilege for that. Let’s make the same example, and create a user Report that needs to read from every Schema:

CREATE USER "report" IDENTIFIED BY "pippo120"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
 
GRANT "CONNECT" TO "report" ;
ALTER USER "report" DEFAULT ROLE "CONNECT";
 
-- SYSTEM PRIVILEGES
GRANT SELECT ANY TABLE TO "report" ;

We have granted the user Report the system privilege to select any database table. Let’s now try it:

select *
from chinook.artist
fetch first 30 rows only;

And from another random table like before:

select *
from chinook.genre;

Now let’s try to write to the same table:

update chinook.Genre
set name='Punk' 
where GenreId=4;

As expected, we get an insufficient privileges error.

Speaking of system privileges in Oracle, we also have a similar Role like sysadmin in SQL Server, the DBA role. Let’s take a look at this and other roles using the create user GUI of SQL Developer, and create the same Management user as before:

Switch now to the Granted Roles Tab to view a list of all available roles:

We just use the CONNECT and DBA roles, we will return to the Roles lists and system views in the second part of the tip.

Now go to the SQL tab to obtain the SQL script:

CREATE USER "Management" IDENTIFIED BY "Riesenhotter44"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
 
-- QUOTAS
 
-- ROLES
GRANT "DBA" TO "Management" ;
GRANT "CONNECT" TO "Management" ;
ALTER USER "Management" DEFAULT ROLE "DBA","CONNECT";

And after creating the user, let’s test it:

Let’s try to lock user l.callahan. Remember that in Oracle, there is only one database per instance unless in a CDB/PDB environment.

ALTER USER "l.callahan" ACCOUNT LOCK ;

No problem. But if we try now to unlock the user with the Report user:

We cannot, as we do not have enough privileges.

Now we can unlock it using the Management user:

Granting Privileges in PostgreSQL

In PostgreSQL, we have a similar concept of user/schemas in Oracle, but they are referred to as Roles. Roles are principals in PostgreSQL, this is from the documentation: “The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.”

A Role in PostgreSQL can have the login privilege and act as a user, otherwise, they are like Roles we’ve seen so far. So it is both a principal and can be assigned to principals, just like in SQL Server.

Let’s create the same example as earlier. We can create a role Finance with select permissions on tables Invoice and InvoiceLine and assign it to a user:

CREATE ROLE "Finance" WITH
   NOLOGIN
   NOSUPERUSER
   NOCREATEDB
   NOCREATEROLE
   INHERIT
   NOREPLICATION
   CONNECTION LIMIT -1;
grant select on "Invoice" to "Finance";
grant select on "InvoiceLine" to "Finance";
CREATE ROLE "l.callahan" WITH
   LOGIN
   NOSUPERUSER
   NOCREATEDB
   NOCREATEROLE
   INHERIT
   NOREPLICATION
   CONNECTION LIMIT -1
   PASSWORD 'Goldenhorde';
 
GRANT "Finance" TO "l.callahan";

You see that the command is always CREATE ROLE, but in the case of the login/user, we have specified the LOGIN option and a password. While in the role Finance, we used the option NOLOGIN, with no password.

Now we’ll connect to the database with this new user and perform the usual queries:

select *
from "Invoice"
fetch first 30 rows only;

Please note that PostgreSQL uses standard SQL for returning n rows of FETCH FIRST N ROWS ONLY and a proprietary notation of LIMIT N. Now we can try against another table:

select *
from "Artist"
fetch first 30 rows only;

Also, in PostgreSQL, we have Predefined Roles as in SQL Server and Oracle. Here’s the complete list in the documentation entitled Predefined Roles. In fact, in PostgreSQL, we have the pg_read_all_data role that is almost equivalent to the db_datareader role of SQL Server.

We can now try to give this role to the Report user as we’ve done in SQL Server:

CREATE ROLE "Report" WITH
   LOGIN
   NOSUPERUSER
   NOCREATEDB
   NOCREATEROLE
   INHERIT
   NOREPLICATION
   CONNECTION LIMIT -1
   PASSWORD 'pippo120';
 
GRANT pg_read_all_data TO "Report";

And we can now try it:

select *
from "Artist"
fetch first 30 rows only;

Let’s try another table:

select *
from "Genre"
fetch first 30 rows only;

But, if we try to write:

update "Genre"
set "Name"='Punk' 
where "GenreId"=4;

As expected, we do not have the privilege to write to any database table.

As stated before, the pg_read_all_data role is almost identical to the db_datareader database role of SQL Server. This is because the PostgreSQL roles are more on a server (cluster) level, similar to server roles, not specific to databases.

We also have a way to define a power user, like with the sysadmin role in SQL Server or the DBA role in Oracle, but it is not done with a role in this case. During the create roles scripts, we used NOSUPERUSER. But if we change that parameter to SUPERUSER, we have the equivalent of a sysadmin user in SQL Server. Let’s use the same example as with the two other RDBMS. First, we create a Management superuser:

CREATE ROLE "Management" WITH
   LOGIN
   SUPERUSER
   CREATEDB
   CREATEROLE
   INHERIT
   NOREPLICATION
   CONNECTION LIMIT -1
   PASSWORD 'Riesenhotter44';

Now we can connect and test this new user creating a new database:

CREATE DATABASE "Apache"
    WITH
    OWNER = "Management"
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

Note two things in the CREATE DATABASE script:

1. OWNER – We always need to define ownership on the database assigned to a role (with or without login).
2. IS_TEMPLATE – In PostgreSQL, we can define a database as a template to be used to create other databases. In this case, this is not a template database.

If we try that with a non superuser and without the create database privilege such as the Report user:

We receive permission denied.

Conclusion

In this tip, we reviewed some of the roles and ways to assign them to users/logins in the SQL Server, Oracle, and PostgreSQL. We also looked at the differences in roles and some of the new predefined server roles of SQL Server 2022. In the next segment of this topic, we will dig in deep into GRANT and the ways to assign grants on schemas, not only tables, and inheritance, as well as all the various system views that can help manage all the privileges. So…stay tuned!

Next Steps

As always, here are some links to the official documentation:

• SQL Server:
  • Get started with Database Engine permissions
  • Database-level roles
  • Server-level roles
• Oracle:
  • Managing User Privileges and Roles
  • Predefined Roles in an Oracle Database Installation
• PostgreSQL:
  • Database Roles
  • Predefined Roles

And some references to other tips on roles:

• SQL Server 2022 Server-Level Role Updates
• Tighten SQL Server security with custom server and database roles
• SQL Server Database Users to Roles Mapping Report