Roles, Schemas, Grants and Owners in SQL Server, Oracle and PostgreSQL

Problem

The second part of the series will review additional concepts on roles, schemas, grants, privileges, and owners that were not addressed in the first part. In particular, we will explore the concept of owner, the various system views used to manage and list all roles and granted privileges, and the various ways to grant privileges on a whole schema and make new objects inherit those privileges.

Solution

Once again, we will use the free Chinook database. You can download a copy to work through the examples in this tip.

SQL Server

Let’s begin by granting a privilege to a whole schema instead of a single object, such as a table. We will also explore the concept of owner and the various system views.

In SQL Server, this is quite easy: use GRANT on SCHEMA. That’s all!

Let’s see an example. Create the schema Finance with ownership to role Finance. With this simple statement, we created the schema Finance and assigned ownership to the role Finance. In this way, the role has all the privileges on this schema.

CREATE SCHEMA [Finance] AUTHORIZATION Finance

Now, assign some objects to this schema:

alter schema Finance transfer Invoice;
alter schema Finance transfer InvoiceLine;

Using the ALTER SCHEMA… TRANSFER…, we can transfer tables or other objects to this schema.

We can now create the role Supervisor and assign it read privileges on the schema Finance:

CREATE ROLE [Supervisor];
GRANT SELECT ON schema :: Finance to Supervisor;

Very easy! Notice the different syntax vs. a normal GRANT. In this case, we must specify ON SCHEMA :: instead of simply putting in the name of the object.

Let’s expand on the concept of schema owner and object ownership in general. As seen in this example, we have explicitly assigned ownership of the schema Finance that we have created to the database role Finance using the AUTHORIZATION option. With that, this role now has all privileges on this schema, which cannot be revoked, unless we change the ownership of the schema. Ownership can be given both to a database role or directly to a user. Obviously, a role would be a better choice. Lastly, there are CREATE commands for which the AUTHORIZATION option is unavailable, such as the CREATE TABLE. In this case, the ownership is inherited directly from the schema; in fact, we can use the code below to check the two tables under schema Finance:

SELECT type_desc, name, USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
FROM sys.objects 
WHERE SCHEMA_NAME(schema_id) = 'Finance' and type='U'

As you can see, the owner of the two tables, even if we’ve not specified it, has been assigned to the database role Finance that owns the schema Finance. However, if we check the other tables, we’ll see that those are in the default dbo schema owned by dbo:

SELECT type_desc, name,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name, SCHEMA_NAME(schema_id) as name_schema
FROM sys.objects 
WHERE SCHEMA_NAME(schema_id) <> 'Finance' and type='U'

All new objects that will be created under schema Finance will automatically get the ownership set to the owner of the schema. Since we granted the SELECT privilege to the role Supervisor on the schema, this privilege will also be inherited on new objects created or transferred to the schema. Let’s see an example by moving the table Customer to Finance:

alter schema Finance transfer customer;

Suppose we check the privileges explicitly given to the three tables under the schema Finance. We will not find any, as the privilege has been given implicitly, granting it to the whole schema:

select object_name(perm.major_id) as ObjectName, princ.name, perm.permission_name, perm.state_desc, perm.class_desc
from sys.database_permissions perm
inner join sys.database_principals princ
on perm.grantee_principal_id = princ.principal_id
where OBJECT_NAME(perm.major_id) in ('Customer','Invoice','InvoiceLine')

Check privileges given on three tables under schema Finance

Note: Since we gave the ownership of the schema Finance to the database role Finance, we now do not see any more of the SELECT privilege on the Invoice and InvoiceLine tables that we granted to role Finance in the first tip of this series.

We need to use a different strategy to check if the role Supervisor has indirect privileges on these tables.

First, create a new login Super, and assign it to the database role Supervisor:

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

Now we can impersonate the user Super and check all the permissions on table Invoice with the function fn_my_permissions:

EXECUTE AS USER = 'Super'
select *
from fn_my_permissions('Finance.Invoice','OBJECT')

As expected, we see that the user Super has SELECT privileges on the table Invoice and all its columns.

Oracle

In Oracle, we have a somewhat different approach to assigning privileges to the entire schema. In two words: you can’t! A little workaround is needed, like explicitly assigning it to all the objects in the schema with some dynamic SQL script.

So, let’s try the same example seen in the SQL Server section. First, create a new schema:

CREATE USER "QFINANCE" IDENTIFIED BY "FINANCE"  
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

You can see immediately that we need to give a different name to the user/schema since we already have a role with the name Finance. Otherwise, in Oracle, we’ll get the error ORA-01920: user name '***' conflicts with another user or role name. Keep in mind that the concept of user and schema are the same in Oracle. We will return on that speaking of the owner of this schema.

Let’s move the two tables, Invoice and InvoiceLine, to schema QFINANCE. Again in Oracle, this cannot be done directly. Some workarounds could be 1) copying the table, 2) using impdp/expdp, or 3) if you have enterprise edition, using partitioning! For this case, let’s use copy:

create table qfinance.invoice as
select *
from chinook.invoice;
 
create table qfinance.invoiceline as
select *
from chinook.invoiceline;

Create the constraints, indexes, and triggers present on the tables and then delete the old tables:

alter table QFINANCE.INVOICE add CONSTRAINT "PK_INVOICE" PRIMARY KEY ("INVOICEID");
GRANT REFERENCES ON CHINOOK.CUSTOMER to qfinance; 
alter TABLE QFINANCE.INVOICE ADD CONSTRAINT "FK_INVOICECUSTOMERID" FOREIGN KEY ("CUSTOMERID")
REFERENCES "CHINOOK"."CUSTOMER" ("CUSTOMERID") ENABLE;
      
ALTER TABLE QFINANCE.INVOICELINE ADD CONSTRAINT "PK_INVOICELINE" PRIMARY KEY ("INVOICELINEID");
ALTER TABLE QFINANCE.INVOICELINE ADD CONSTRAINT "FK_INVOICELINEINVOICEID" FOREIGN KEY ("INVOICEID")
REFERENCES "QFINANCE"."INVOICE" ("INVOICEID") ENABLE; 
GRANT REFERENCES ON CHINOOK.track to qfinance; 
ALTER TABLE QFINANCE.INVOICELINE ADD CONSTRAINT "FK_INVOICELINETRACKID" FOREIGN KEY ("TRACKID")
REFERENCES "CHINOOK"."TRACK" ("TRACKID") ENABLE;
 
drop table chinook.invoiceline;
drop table chinook.invoice;

Create constraints, indexes and triggers presents on the tables and then delete the old tables

Note: Since some of the Foreign Keys point to tables not in the schema now, we must grant the REFERENCES privilege to schema QFINANCE to create these foreign key constraints.

If done in a production environment, this process requires some planned downtime.

As you may already have guessed, the schema and user QFINANCE is now the owner of the two tables under it, Invoice and InvoiceLine. Thus, it already has all the privileges on those, and we did not have to grant any to create the Foreign Key on InvoiceLine referencing to table Invoice. There is no way to grant ownership of the schema to role Finance as we did in SQL Server—here, the schema is also a user!

Now create the role Supervisor, and with an easy dynamic SQL script, assign the SELECT privilege on the two tables in schema QFINANCE:

create role supervisor;
SELECT 'GRANT SELECT ON '||OWNER||'.'||table_name||' TO supervisor;'
 FROM dba_all_tables
 where owner='QFINANCE';

Create Supervisor role and assign the SELECT privilege on the two tables in schema QFINANCE

We have queried the system view dba_all_tables to get all the tables owned by schema QFINANCE and created two scripts for granting the SELECT privilege. By running the two scripts, we assigned the privilege.

Similar to SQL Server, all objects created under schema QFINANCE are owned by the owner of the schema, in this case, the same user QFINANCE. But, since it is not possible in Oracle to assign a privilege to the whole schema, we will need to grant the SELECT privilege on any new object if needed by the role supervisor!

So if we also move the table Customer under QFINANCE schema, we will need to grant SELECT to Supervisor to that table. Let’s try it:

create table QFINANCE.CUSTOMER AS
SELECT *
FROM CHINOOK.customer;
 
ALTER TABLE QFINANCE.CUSTOMER ADD CONSTRAINT "PK_CUSTOMER" PRIMARY KEY ("CUSTOMERID");
GRANT REFERENCES ON CHINOOK.EMPLOYEE TO QFINANCE;
ALTER TABLE QFINANCE.CUSTOMER ADD CONSTRAINT "FK_CUSTOMERSUPPORTREPID" FOREIGN KEY ("SUPPORTREPID")
     REFERENCES "CHINOOK"."EMPLOYEE" ("EMPLOYEEID") ENABLE;

If you remember, we also have an FK pointing to this table. To get all FK references to a specific table, here is a practical script to use:

with foreign_key as 
(SELECT c.constraint_name, c.r_constraint_name, c.table_name, C.OWNER
FROM dba_constraints c 
WHERE constraint_type='R') 
      
SELECT FOREIGN_KEY.OWNER,FOREIGN_KEY.table_name,foreign_key.constraint_name as "Constraint Name",
D.TABLE_NAME AS referenced_table_name,d.constraint_name as "Referenced PK"
FROM dba_constraints d inner join 
foreign_key
on d.constraint_name=foreign_key.r_constraint_name 
WHERE D.table_name='CUSTOMER' AND D.OWNER='CHINOOK';

We also need to alter this foreign key constraint to have it all properly set up:

ALTER TABLE QFINANCE.INVOICE DROP CONSTRAINT "FK_INVOICECUSTOMERID";
alter table qfinance.invoice add CONSTRAINT "FK_INVOICECUSTOMERID" FOREIGN KEY ("CUSTOMERID")
     REFERENCES "QFINANCE"."CUSTOMER" ("CUSTOMERID") ENABLE;

Now we can finally drop the table in schema CHINOOK:

DROP TABLE CHINOOK.CUSTOMER;

To check the permissions assigned to the role Supervisor, we can use the system view ROLE_TAB_PRIVS:

SELECT *
FROM ROLE_TAB_PRIVS
where role='SUPERVISOR';

Check permissions assigned to Supervisor

As expected, the role Supervisor has the SELECT privilege only on the Invoice and InvoiceLine tables. We need to explicitly grant the privilege also to the new table added to schema QFINANCE:

GRANT SELECT ON QFINANCE.CUSTOMER TO supervisor;

Rechecking the privileges on the role:

We can also check the privileges assigned to the tables using the system view dba_tab_privs:

SELECT *
FROM dba_tab_privs
WHERE owner = 'QFINANCE' 
ORDER BY OWNER, TABLE_NAME;

PostgreSQL

Next, we will try to accomplish the same tasks in PostgreSQL. Here we have a similar approach as SQL Server, but with a caveat. First, create schema Finance owned by role Finance:

CREATE SCHEMA "Finance"
    AUTHORIZATION "Finance";

Notice the exact same syntax as in SQL Server. Now let’s move tables to it:

ALTER TABLE "Invoice" SET SCHEMA "Finance";
ALTER TABLE "InvoiceLine" SET SCHEMA "Finance";

Very easy. One important thing to note is that in PostgreSQL, there is a default schema Public, and if not specified, all objects are created under this schema (similar in SQL Server with the dbo schema).

To check the tables under a specific schema, we query the system information table pg_tables:

select schemaname, tablename, tableowner 
from pg_tables 
where schemaname='Finance';

Check the tables under a specific schema Finance

And checking in schema public:

Check the tables under a specific schema public

We can see that we have successfully moved the two tables from schema public to Finance. Note: The owner of the tables is still user postgres since these tables were created by this superuser, even if they are now under schema Finance which is owned by role Finance. This is quite different with respect to SQL Server.

Now we can create the role Supervisor and grant SELECT to all schema Finance to it:

create role "Supervisor" WITH
   NOLOGIN
   NOSUPERUSER
   NOCREATEDB
   NOCREATEROLE
   INHERIT
   NOREPLICATION
   CONNECTION LIMIT -1;

There are two ways to assign a privilege on a whole schema to a role/user. The first way is to use GRANT (like in SQL Server):

grant usage on schema "Finance" to "Supervisor";
grant select on all tables in schema "Finance" to "Supervisor";

Assign a privilege on a whole schema with GRANT

The first line grants the connection on schema Finance to the role Supervisor, and the second grants SELECT on all tables in that schema.

Now let’s check permissions on the tables using information_schema view table_privileges:

select grantee, table_schema, table_name, privilege_type
from information_schema.table_privileges
where table_schema='Finance' and grantee='Supervisor';

We can see that role Supervisor has SELECT privilege on both tables under schema Finance. All is OK. We were able to grant a privilege on all tables under a schema.

But there is a caveat: Any new table created under that schema will not inherit the privileges we have granted. If we need that behavior, we must use the second way:

alter default privileges in schema "Finance" grant select on tables to "Supervisor";

alter default privileges to grant privileges to any new table created

Let’s move the table Customer to schema Finance and recheck the privileges:

ALTER TABLE "Customer" SET SCHEMA "Finance";
select grantee, table_schema, table_name, privilege_type
from information_schema.table_privileges
where table_schema='Finance' and grantee='Supervisor';

Wait, it’s exactly the same as before moving the Customer table. We do not have permissions on it as Supervisor. Let’s check from a role point of view by querying information_schema view role_table_grants:

select grantee, table_schema, table_name, privilege_typefrom information_schema.role_table_grants
where table_schema='Finance' and grantee='Supervisor';

Hmmm…we get the same result: role Supervisor has no privileges on table Customer. This is because we have moved a table and not created it, as noted in the official documentation:

“You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. The privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas.”

So, if we only move a table, it does not inherit the privilege. However, if we create a new table and recheck it:

create table "Finance".test_customer (like "Finance"."Customer");
select grantee, table_schema, table_name, privilege_type
from information_schema.table_privileges
where table_schema='Finance' and grantee='Supervisor';

Create a new table and check privileges again

The new table has inherited the SELECT privilege for the role Supervisor. Note: We used the option LIKE to create a new table with the same structure as the Customer one.

One more thought about the owner of schemas and tables: In this particular case, I created everything with superuser postgres; the owner of all tables created is postgres. You may have noticed it querying the system table pg_tables at the beginning. The owner has all permissions on those tables. In PostgreSQL, we can change the owner of the tables. For example, we change the owner of all the tables under schema Finance to role Finance, using some dynamic SQL:

select 'alter table '||'"'||schemaname||'"'||'.'||'"'||tablename||'"'||' owner to "Finance";' 
from pg_tables 
where schemaname='Finance';

Change the owner of all the tables under schema Finance to role Finance

Running the alter queries, we can change the owner:

Now checking in pg_tables, we see the new owner:

Conclusion

In this tip, we reviewed the different ways to assign grants on schemas, the various system views that help manage all the privileges, and the concept of owner for SQL Server, Oracle and PostgreSQL.

Next Steps

As always, here are links to the official documentation (schema and owner):
Check out these interesting tips on schemas, owner, and privileges:

Resources

Community

Subscribe