In this blog, I will show some examples of granting privileges on views to others and explain in what situation we need "WITH GRANT OPTION".
Motivation
The motivation of writing this blog comes from this question The scenario in that thread is kind of complex. I will not explain that scenario in details here. If you are interested, you can take a look there.
Problem
Here is a simpler scenario/problem with the following steps.
1. There are three user A, B, C and each user has his/her own schema.
2. User A creates "table A" in schema A and grants the select privilege on "table A" to user B.
3. User B creates "view B" in schema B and "view B" is based on "table A".
4. Now here comes the question. Can user B grant the select privilege on "view B" to user C? Can user C select data from "view B"?
To answer the questions, let's first do some tests in SAP HANA. I am using SAP HANA SPS 08 Rev. 80.
Example 1
Step 1: SYSTEM creates three users, USER_A, USER_B and USER_C.
CREATE USER USER_A PASSWORD Initial1;
CREATE USER USER_B PASSWORD Initial1;
CREATE USER USER_C PASSWORD Initial1;
Step 2: USER_A creates TABLE_A under schema USER_A and grants the select privilege on that table to USER_B.
CREATE COLUMN TABLE USER_A.TABLE_A (ID INTEGER);
GRANT SELECT ON USER_A.TABLE_A TO USER_B;
Step 3: USER_B creates VIEW_B under schema USER_B and VIEW_B is based on TABLE_A.
CREATE VIEW USER_B.VIEW_B AS SELECT * FROM USER_A.TABLE_A;
Step 4: USER_B tries to grant the select privilege on VIEW_B to USER_C but fails.
GRANT SELECT ON USER_B.VIEW_B TO USER_C;
So why can USER_B not grant the select privilege on VIEW_B (which is created by himself/herself) to USER_C???
The reason is very obvious. Although VIEW_B is created by USER_B, VIEW_B is based on TABLE_A which USER_C has no privilege to select. Imagine if USER_B managed to execute the Grant SQL, privileges would be nothing. Users (e.g. USER_C) could use this "workaround" to get everything (e.g. TABLE_A) through others (e.g. USER_B).
The solution is also very simple. We just need to let USER_A "say something" to USER_B, something like:
"Hey buddy, you can play my basketball (TABLE_A) yourself and if you have a game (VIEW_B) with others (USER_C) you can also use my basketball (which means you can let others (USER_C) to touch my basketball (TABLE_A) in your game (VIEW_B))".
Hope you can understand this sentence well. It took me some time to create it. :razz: Now "WITH GRANT OPTION" can play a role here which can let grantee to grant the privilege to others further or something like "cascade connection" in this view scenario. So, let's try it.
Step 5: USER_A grants the select privilege on TABLE_A to USER_B WITH GRANT OPTION.
GRANT SELECT ON USER_A.TABLE_A TO USER_B WITH GRANT OPTION;
Step 6: USER_C can select VIEW_B successfully.
SELECT * FROM USER_B.VIEW_B;
Example 2
Now let's try another example which is similar with the scenario in In this example, we will let USER_A grant select privilege on TABLE_A to USER_C first.
Step 1: SYSTEM creates three users, USER_A, USER_B and USER_C.
CREATE USER USER_A PASSWORD Initial1;
CREATE USER USER_B PASSWORD Initial1;
CREATE USER USER_C PASSWORD Initial1;
Step 2: USER_A creates TABLE_A under schema USER_A and grants the select privilege on that table to USER_B and USER_C. Notice: There is no WITH GRANT OPTION in this step.
CREATE COLUMN TABLE USER_A.TABLE_A (ID INTEGER);
GRANT SELECT ON USER_A.TABLE_A TO USER_B;
GRANT SELECT ON USER_A.TABLE_A TO USER_C;
Step 3: USER_B creates VIEW_B under schema USER_B based on TABLE_A and grants the select privilege on the whole schema USER_B to USER_C.
CREATE VIEW USER_B.VIEW_B AS SELECT * FROM USER_A.TABLE_A;
GRANT SELECT ON SCHEMA USER_B TO USER_C;
Step 4: USER_C tries to select VIEW_B but fails.
SELECT * FROM USER_B.VIEW_B;
Again why??? Maybe you are confused now as follows.
1. Since USER_A grants select privilege on TABLE_A to USER_C, USER_C can select TABLE_A. It's true. USER_C can run the following SQL successfully.
SELECT * FROM USER_A.TABLE_A;
2. Since USER_B grants select privilege on the whole schema USER_B to USER_C, USER_C should be enabled to select everything under schema USER_B. But is it true? From the error message, point 2 is not true. But why???
We can still use the basketball example. Imagine the following.
1. USER_A says to USER_B "Hey USER_B, you can play my basketball yourself."
2. USER_A says to USER_C "Hey USER_C, you can play my basketball yourself."
3. USER_B says to USER_C "Hey USER_C, you can always play basketball with me."
There is no problem if USER_C joins USER_B's games in which USER_B uses his own basketball. But if USER_B uses USER_A's basketball in a game, can USER_C join this game? Nope, since USER_A does not say to USER_B "If you have a game (VIEW_B) with others (USER_C) you can also use my basketball (which means you can let others (USER_C) to touch my basketball (TABLE_A) in your game (VIEW_B))". That's the reason. Hope you can also understand it well.
If you do not understand the reason. Here is another reason for you. Imagine the following if you still think there should be no error in step 4.
1. If there were no error in step 4, USER_B would know USER_C could select TABLE_A.
2. If there were error in step 4, USER_B would know USER_C could not selct TABLE_A.
Users (e.g. USER_B) could use this "method/workaround" to know/infer some privileges of others (e.g. USER_C).
But why can USER_B know/infer this??? Does USER_A tell him? Nope. Does USER_C tell him? Nope. The privileges of USER_C should be a secret to USER_B!!! That's why USER_C cannot select VIEW_B so far. So, we still need "WITH GRANT OPTION" to solve the problem.
Step 5: USER_A grants the select privilege on TABLE_A to USER_B WITH GRANT OPTION.
GRANT SELECT ON USER_A.TABLE_A TO USER_B WITH GRANT OPTION;
Step 6: USER_C can select VIEW_B successfully now.
SELECT * FROM USER_B.VIEW_B;
Example 3
If we say there is USER_D now. USER_C wants to create VIEW_C based on VIEW_B under schema USER_C and let USER_D select VIEW_C. What will happen and how does the SQL look like? I will not explain more about this example. You can take this as an exercise. :razz:
I just pasted my code here.
--SYSTEM
CREATE USER USER_A PASSWORD Initial1;
CREATE USER USER_B PASSWORD Initial1;
CREATE USER USER_C PASSWORD Initial1;
CREATE USER USER_D PASSWORD Initial1;
--USER_A
CREATE COLUMN TABLE USER_A.TABLE_A (ID INTEGER);
GRANT SELECT ON USER_A.TABLE_A TO USER_B WITH GRANT OPTION;
--USER_B
CREATE VIEW USER_B.VIEW_B AS SELECT * FROM USER_A.TABLE_A;
GRANT SELECT ON USER_B.VIEW_B TO USER_C WITH GRANT OPTION;
--USER_C
CREATE VIEW USER_C.VIEW_C AS SELECT * FROM USER_B.VIEW_B;
GRANT SELECT ON USER_C.VIEW_C TO USER_D;
--USER_D
SELECT * FROM USER_C.VIEW_C;
Conclusion
Based on the above examples, we can answer the question at the beginning.
1. If your view is based on other objects which is not created by you and you want to let others read your view, you need "WITH GRANT OPTION" from the owner of your dependent objects.
2. In addition, you have the select privilege on the whole schema does not mean you can select everything under the schema.
You can also find it from SAP HANA Developer Guide Object Privileges - SAP HANA Developer Guide - SAP Library
"Some database objects depend on other objects. Views, for example, are defined as queries on other tables and views. The authorization for an operation on the dependent object (the queried tables and views) requires privileges for the dependent object and the underlying object. In case of views, the SAP HANA database implements the standard SQL behavior. A user has the authorization for an operation on a view if the following is true:
- The privilege for operations on the view has been granted to the user or a role assigned to the user.
- The owner of the view has the corresponding privileges on the underlying objects with the option to grant them to others."
NOTICE: This mechanism/principle should be applied not only in SAP HANA but in other databases as well, e.g. Oracle.
Hope you enjoyed reading my blog and doing the exercise. :smile:
- SAP Managed Tags:
7 Comments
