The Essential Guide of SAP HANA SQL Synonyms – 6 Mins Read

After understanding SQL data types in SAP HANA, we will learn about the SQL synonyms and their significance in SAP HANA. To begin with, we’ll learn some basics and then move on to discuss the types of synonyms and methods to create SQL Synonyms in SAP HANA.

SQL Synonyms in SAP HANA

SQL synonyms are alternate names for a database object residing in a particular schema in SAP HANA. It means synonyms are an alias of database objects. The SQL synonyms are not database objects in themselves but are just used as pointers to the real database objects.

In SAP HANA, we can create synonyms for several database objects like tables, views, procedures, table functions, sequences, scalar functions, etc.

The syntax for creating a synonym is given below.

Create synonym <synonymname> for <sourceobjectname>;

To drop an existing synonym, use the syntax given below having the keyword DROP.

DROP SYNONYM <synonymname>;

In case of a public synonym, use:

DROP PUBLIC SYNONYM <synonymname>;

There are separate folders for Synonyms and Public Synonyms for each schema under the Catalog node in SAP HANA Studio.

The Concept behind SAP HANA SQL Synonyms

To understand the concept of SQL synonyms and their use in SAP HANA, let us imagine a scenario. Suppose there are two users in SAP HANA, USER1 and USER2. USER1 owns a data table named SALES_ASIA in SAP HANA database under a schema.

Now, USER2 wants to access and use the SALES_ASIA table to create views and do some analysis.

The platform of USER2 is the client application. There are two ways in which USER2 can access the table residing in USER1’s schema.

Either, USER2 can access the table directly (by getting access privilege from USER1) using the fully qualified object name i.e. SALES_ASIA or, USER2 can access the table (database object) via a synonym, for instance, SLS_ASIA.

You must be thinking, what benefit it will provide? Well, let’s suppose that there is any kind of change on the side of USER1. This means the change should also be made in the client’s application for the USER2 to continue accessing objects from USER1’s schema. Doesn’t sound efficient right?

Synonyms are created to prevent a user from being affected by any change occurring at the source. If the location of table SALES_ASIA changes from one server to the other, the new location will be linked to SALES_ASIA table which will be further linked to its synonym SLS_ASIA.

That is, USER2 can still access the table as he did before using the synonym SLS_ASIA, irrespective of the change in location of the original table.

Thus, synonyms keep the users away from the complexities of the backend and help them use data for analysis easily.

Using a Synonym in SAP HANA

In order to access data objects from SAP HANA database using synonyms, you must have access privileges to the base object. A base object is the one for which synonym is used to refer to.

There are no privileges that we can assign directly on synonyms, rather we need a GRANT OPTION statement to grant the privilege for using the source or base object.

Suppose, we use DF_IDR as the synonym for a schema DF_CITY (source object) containing EMPTAB (a data table). Now, using GRANT OPTION statement you can grant access privilege to the user trying to access DF_CITY with the synonym DF_IDR for the table EMPTAB.

However, giving access to the source object for accessing through a synonym does not grant access for the objects outside of that source object.

The SQL statement to do this:

grant select on EMPTAB to DF_IDR with grant option;

Types of SQL Synonyms in SAP HANA

There are three types of SQL synonyms in SAP HANA:

1. Public Synonyms

PUBLIC schema owns the public synonyms in an SAP HANA database. All the SAP HANA users can access objects by referring to public synonyms.

The owner creates the synonyms of the application for the database objects like tables, views, packages, etc. Those synonyms are available for use by other users to access the database objects.

We use the keyword, PUBLIC to create a public synonym in the SQL statement.

Whenever we update the data in the database object, all the users accessing that object using a synonym will see the updated data automatically.

Syntax:

CREATE PUBLIC SYNONYM <synonymname> for <sourceobjectname>;

2. Namespaces and Public Synonyms

Every database object has a special name and that name reserves some namespace in the HANA system. When you create a synonym for a HANA object such as tables, views, procedures, functions, etc., that synonym will also share the namespace with other objects.

This leads us to reveal a rule in SAP HANA, that no synonym name can be identical to an existing object in the SAP HANA system.

However, there is a catch! As there are two types of synonyms i.e. public and private synonym and if the name of a public and a private synonym is identical then the system will give precedence to the private synonym.

Public and private objects may have identical synonyms because they have different name spaces. And thus, a private synonym or object will always have precedence over a public one.

3. Private Synonyms

We use private synonyms only within a schema. Thus, only the owner of the database schema can use private synonyms for referencing and hiding the original object names.

Syntax:

CREATE SYNONYM <synonymname> for <sourceobjectname>;

Switching the Base Object via Synonym

It is also possible to change or switch a base object via a synonym which different users are accessing. So, one way to change the base object is to change the synonym and create a new one. But one other way is to simply switch the object without disturbing the synonym and its user base.

A sample script to make this switch happen:

-- user DATAFLAIR1: select via synonym from table
select * from SYNDF1;
--> row TABLE1

-- user DATAFLAIR2: switch object from Table to user defined function
rename table TABLE1 to TABLE1_BACKUP;
create view TABLE1 as select field1 from UDF1();
grant select on TABLE1 to DATAFLAIR1;

-- user DATAFLAIR1: select from same synonym again, now pointing to a view/table function:
select * from SYNDF1;
--> row UDF1

Summary

This comes to the end of the SAP HANA SQL Synonyms tutorial. We saw how important SQL synonyms are for using the database objects and maintaining good coordination between the consumer and the provider of those objects.

Clear with the concept of SAP HANA SQL Synonyms? Any doubts, feel free to enter in the comments section. You will receive a quick solution.