Using Synonyms in SAP HANA

In this blog post I try to explain what synonyms are and how they work in HANA SQL, but most of it will apply to other databases as well.

Use Case, Users and Schemas

Lets consider the following use case:

• One user (SYN_PROV) creates/owns DB objects like tables, and provides access to its objects via synonyms. That could be e.g. a replicated ERP Schema.
• A second user (SYN_CONS) consumes the objects of the first User, directly or via synonym.This could be a schema we develop in, create DB views etc.
• A third user (SYN_CONS2) shall only consume objects of the second user/schema, but never directly the objects of the first user. That could be an end user, that is not allowed to access the replicated ERP Schema directly

For simplicity reasons we will create three users and work directly with those users and their default schema, which has the same name as the user. When you read something like “–user SYN_PROV:” in the examples, this means, that the coding has to be executed by this user and the current schema is the default schema of the user. The easiest way to do this is to open three SQL consoles, one for each user.

-- with some Admin-User: create user that PROVides the base object, and two CONSumer users

create user SYN_PROV PASSWORD "Init1234" NO FORCE_FIRST_PASSWORD_CHANGE;

create user SYN_CONS PASSWORD "Init1234" NO FORCE_FIRST_PASSWORD_CHANGE;

create user SYN_CONS2 PASSWORD "Init1234" NO FORCE_FIRST_PASSWORD_CHANGE;

What is a Synonym?

Synonyms are aliases for DB objects, but are no real DB objects by themselves (even though you can find them in view SYS.OBJECTS) . Whenever you use a synonym, try to mentally replace it by its base object, that’s pretty much the same what the DB is doing. A synonym belongs to its own schema, which is independent of the schema the DB object belongs to (of course, a synonym and its base object can have the same schema). Currently, synonyms can be created for tables (this includes virtual tables), views, procedures, table functions, scalar functions and sequences. You can use synonyms to hide the concrete object names from consumers, allowing you to exchange the concrete objects later, without the need of changing their consumers. Also, you can use a synonym to give an object a more convenient name, possibly in your own schema. There is no measurable performance penalty for using synonyms instead of their base objects.

Synonyms cannot be used as base objects for other synonyms. If you want/need to point synonyms to other synonyms, you have to put a real DB object in between (i.e. a view for tables, views and table functions, a procedure for procedures, a scalar function for scalar functions, not possible for sequences).

First, I will create some base objects for synonyms in schema SYN_PROV.

-- user SYN_PROV: create some base object: a table and an UDF

create column table TAB1 (field1 nvarchar(20));

insert into TAB1 (field1) values ('row TAB1');

select * from TAB1;

--> row TAB1

create function UDF1 

  returns table (field1 nvarchar(20))

  language SQLSCRIPT

  as

  begin

    return select 'row UDF1' field1 from SYS.DUMMY;

  end;

select * from UDF1();

--> row UDF1

Creating a Synonym

For creating a synonym a user needs the “SELECT” privilege for tables, views and sequences as base object, and the “EXECUTE” privilege for procedures and functions as base object. After creation, the synonym exists independently of the base object. This means a synonym remains existing even if its creator has no privilege to access the base object anymore or even after the base object was deleted. Access to the synonyms would result in an error in both cases.

Lets create two synonyms in schema SYN_CONS.

-- user SYN_PROV: grant access for creating synonym

grant select on TAB1 to SYN_CONS;

grant execute on UDF1 to SYN_CONS;

-- user SYN_CONS: create some synonyms 

create synonym SYN1 for SYN_PROV.TAB1;

select * from SYN1;

--> row TAB1

create synonym SYN2 for SYN_PROV.UDF1;

select * from SYN2();

--> row UDF1

Using a Synonym and Granting Access

For using a synonym a user needs the corresponding privilege for accessing the base object. There is no such thing as a privilege to access the synonym itself. Even a schema privilege for the synonym’s schema is not sufficient to use a synonym. When you grant a synonym privilege to a user, behind the scenes access to the base object is granted. When a user created a view based on a synonym and wants to grant access to this view to a second user or role, the first user needs the GRANT OPTION on the base object. This is exactly the same behavior, as if the view would be created directly on the base object.

Lets create a view on a synonym and consume the view, both in schema SYN_CONS.

-- user SYN_CONS: use synonym in view

create view CONS_VIEW as select field1 from SYN1;

select * from CONS_VIEW;

--> row TAB1

So far, so good. Now lets grant another user access to the synonym and view.

-- user SYN_CONS: provide access to other user/schema

grant select on schema SYN_CONS to SYN_CONS2; -- good idea, but…

-- user SYN_CONS2: try to consume synonym/view from SYN_CONS

select * from SYN_CONS.SYN1; 

--> Error: not authorized, why not?

select * from SYN_CONS.CONS_VIEW; 

--> Error: not authorized, why not?

The statements failed, because SYN_CONS has SELECT privilege for TAB1 of SYN_PROV, but — without having the GRANT OPTION cannot grant privileges to access objects from schema SYN_PROV. In HANA (and other DBs) a user that grants access to its view to another user needs the GRANT OPTION on the underlying objects. Let’s give the GRANT OPTION TO user SYN_PROV and try again.

-- user SYN_PROV: provide grant option to SYN_CONS

grant select on TAB1 to SYN_CONS with grant option; 

-- user SYN_CONS2: try again after getting grant option

select * from SYN_CONS.SYN1;

--> Error: not authorized, again?

select * from SYN_CONS.CONS_VIEW; 

--> row TAB1

What might look like a bug at first glance becomes clear when you mentally replace the synonym by its base object. The base object TAB1 of the synonym belongs to schema SYN_PROV, not to schema SYN_CONS to which access was granted earlier. Whereas in a lot of cases a synonym on a table behaves like a projection view containing all the fields, here the synonym behaves differently. Let’s explicitly give SYN_CONS2 access to the synonym and try again.

-- user SYN_CONS: provide access to synonym

grant select on SYN_CONS.SYN1 to SYN_CONS2;

-- user SYN_CONS2: try again after getting grant option

select * from SYN_CONS.SYN1;

--> row TAB1

select * from SYN_CONS.CONS_VIEW;

--> row TAB1

That means, if a synonym “points” to an object outside of the synonym’s schema, just granting access to the synonym’s schema does not grant access to the base object in another schema. Not even if the owner has grant rights on the underlying objects. So this is important to note: providing access to an entire schema is usually equivalent in result as providing access to each individual components. However, for granting access to synonym source objects, another step has to take place: granting access to the source object. And that step is automatically performed when granting access to a specific synonym.

When we look at the object privileges of user SYN_CONS2, we see, that not the object privilege to SYN_CONS1.SYN1 was granted, but the object privilege to access the base object of the synonym.

That leads to another idea: let’s revoke the privilege to access schema SYN_CONS from SYN_CONS2 and see what happens.

-- user SYN_CONS: revoke schema access from SYN_CONS2

revoke select on schema SYN_CONS from SYN_CONS2;

-- user SYN_CONS2: try again after revoking schema access

select * from SYN_CONS.SYN1; -- ok, but why?

--> row TAB1

select * from SYN_CONS.CONS_VIEW; 

--> Error: not authorized as expected

We know by now what happened here. We only revoked the SELECT privilege to schema SYS_CONS, but not to SYS_PROV.TAB1, which is needed when selecting from the synonym and which the user still has.

Namespaces and Public Synonyms

Synonyms share the same namespace with tables,views, functions and procedures. If there is already one of those objects with a certain name existing in a schema, you cannot create a synonym with an identical name.

Synonyms can be private – belonging to a specific schema, or public – residing in the special schema PUBLIC. Public synonyms are visible by every user without explicitly specifying the schema name. The privilege for accessing the base object of a public synonym is still needed. Public and private synonyms have their own namespace, i.e. there can be public and private objects or synonyms with identical names. In this case, the private synonym or object has precedence over the public synonym.

The following three statements are equivalent, assuming there was no private synonym/object with the name DUMMY defined:

-- execute with any user:

select count(*) from DUMMY;        -- public synonym

select count(*) from PUBLIC.DUMMY; -- public synonym with explicit schema name

select count(*) from SYS.DUMMY;    -- base table

--> 1  (for any of the above statements)

The following statement shows, that a private synonym or object has precedence over a public synonym. Keep that in mind, when you try to redefine public synonyms (think twice before doing it!):

-- execute with any user:

create view DUMMY as select top 2 table_name from SYS.TABLES;

select count(*) from DUMMY; -- private object, priority over public synonym

--> 2

Switching the Base Object

To a certain degree it is possible to change the base object of a synonym, without changing the consumers of the synonyms. Of course, the syntax of the synonym consumer has to remain intact when changing the base object.

In the last example the base object of a synonym will be switched from a table to a table function. The consumers of the synonym would still work, as long as they only select from the synonym (you cannot update, delete etc. from a table function).

-- user SYN_CONS: select via synonym from table

select * from SYN1;

--> row TAB1

-- user SYN_PROV: switch object from Table to UDF

rename table TAB1 to TAB1_BACKUP;

create view TAB1 as select field1 from UDF1();

grant select on TAB1 to SYN_CONS;

-- user SYN_CONS: select from same synonym again, now pointing to a view/table function:

select * from SYN1;

--> row UDF1

I have not checked, whether complex objects like calculation views support this feature.