Options
Projections in different schema as table and user priv
Is there a way where in we have a schema where table is created(t_schema), multiple schemas(u1_schema, u2_schema) for different set of users which has specific projections on this table? Though the data used by users may be same/subsets of this table, this design is more so to isolate the changes for one user from others if the change in table isn't impacting others and more with team dynamics:(.
Also as query cannot be forced on to particular projection, can we have user be given grants to schema t_schema and u1_schema only, so none of the queries fired using this user uses projections in other schema?
Also as query cannot be forced on to particular projection, can we have user be given grants to schema t_schema and u1_schema only, so none of the queries fired using this user uses projections in other schema?
0
Comments
>>Is there a way where in we have a schema where table is created(t_schema), multiple schemas(u1_schema, u2_schema) for different set of users which has specific projections on this table?
Projections are table property and they are built along with each table. Your design can be something like this:
Schemas : u1_Schema , u2_Schema
Table T1 is created in u1_Schema and also in u2_Schema.
T1 will have projections with same structure in both schemas, as they have their corresponding tables in their own schemas.
So each user can work on their own set of data.
If you are thinking of a design like this.
Having a master schema and a master table in master schema.
Having schema u1_schema and u2_schema for users and creating projections in this schema for the master table from master schema, this can even work but with some limitations:
1. The master table will be affected by every DML operation on any of the projections in u1 and u2 schema.
2. Every time users will have to use fully qualified name of the projections in their SELECT and DML statements
>>Also as query cannot be forced on to particular projection, can we have user be given grants to schema t_schema and u1_schema only, so none of the queries fired using this user uses projections in other schema?
Even a query can be forced to use particular projections, but that is a not a solutions in this scenario.
A user can be given grants on particular schema and a search path can be set for that user while creating a user.
Once grants are given and search path is set the user queries will only search the schema dedicated to it and if no results found for a particular object it will return so.The user's queries will not look for objects from another schema as they will not be having any access to the other schema
For more info on this - grant access on schema
Hope this helps.
NC
From your solution above, setting the search path to master schema(which has the table and super proj) and u1_schema(user specific queries proj) will look for only projections in u1_schema and super projections in master schema
We load and query the tables and not projections directly. So my question is only below 2 points
1. The master table will be affected by every DML operation on any of the projections in u1 and u2 schema.
2. Every time users will have to use fully qualified name of the projections in their SELECT and DML statements
We load the table in master schema and all projections are automatically loaded (in u1_schema and u2_schema ). So what does point 1 mean?
With search path being set and queries or load still referring to master table only, will point 2 be required?
So Lets say user_1 is granted access to Master schema and indirectly to master table.
Now he has to create a projection / table for himself from master table.
Now according to design the user_1 will only be able to access this new projection (SELECT only).
Suppose, master user updates the master table, then master user needs to use a refresh function to update every projection linked with the master table, this way user_1 can see updated data as well from master table.
since user_1 is working with projections in u1_schema he can just do select and no DML (INSERT / UPDATE / DELETE) on projections. Projections don't allow DML operations on them.
Suppose he needs to update data himself or insert new data in his projection.
He has to update / INSERT in master table and again do a REFRESH operation to update his projection with the new data.
>>With search path being set and queries or load still referring to master table only, will point 2 be required?
The point is, where do you want your users to query data, whether its master table or whether is the new projections built specifically for every user.
If queries will be referencing the master table then there is no need of this design, you can just have master table and master schema and grant access to every user on master schema and its objects.
If queries need to be referenced to every users specific projections then the SELECT clause needs to use the fully qualified name of projection like Hope this helps.
NC
Hello
I just would like to confirm I really understood correctly the topics discussed above :
1-when a table is created in a logical schemas, all its projections will be created in the same schema (even if the deploy.sql does not mention it)
2- when a user is assigned a search_path, the queries he runs to tables (because users only know tables) will properly use the correct projections and he will not get privileges errors
Many thanks
Isa