CMDBuild Forum

Row Level Priviledges

Old Versions
#1

Hi - I have a large number of organisations that have a parent\child relationship.

 
What would the best approach to be to implement row level security based on a Parent organisation and associated child relationships?
 
For example my Computer Class has an Organisation attribute.
Organisation A has 20 sub-organisations 1-20.
 
I want to create a User & Group that allows a view of all computers which have an organisation set as either A or 1-20.
 
Many Thanks
Nick
#2

As in your case you can not specify explicitly the list of the names of the organizations on wich every user can work, the best solution to work in a parametric way is to use a SQL functions that return the correct policies.
You can see an example of setting permissions on rows through SQL function on the Administration Manual, page 69, screenshot at the bottom.
CMDBuid Team

#3

Thank you for your response, much appreciated I thought this might be the way but wanted to check I wasn’t missing anything.

 
Many Thanks
Nick

#4
We are struggling to create a function to use for this purpose, do you have any example code that we could base our function on at all please?
 
Thanks
Nick
 
Previously Tecnoteca wrote:
As in your case you can not specify explicitly the list of the names of the organizations on wich every user can work, the best solution to work in a parametric way is to use a SQL functions that return the correct policies.
You can see an example of setting permissions on rows through SQL function on the Administration Manual, page 69, screenshot at the bottom.
CMDBuid Team

 

#5
Dear Nick,
 
functions used to filter rows should return a set of integers with all the Id of the cards you want the group to be able to see.
An example could be:
 
CREATE OR REPLACE FUNCTION filter_computer_rows(IN "UserId" integer,IN "GroupId" integer, "ClassName" character varying)
  RETURNS SETOF integer AS
$BODY$
SELECT "Id" FROM "Computer" WHERE "Company" in (SELECT "Id" FROM "Company" WHERE "Code"='A' OR "Parent" in (SELECT "Id" FROM "Company" WHERE "Code"='A'));
$BODY$
  LANGUAGE sql VOLATILE;
COMMENT ON FUNCTION filter_computer_rows(integer, integer, character varying) IS 'TYPE: function';
 
Please note that the parameters UserId, GroupId, ClassName are compulsory even if you don't use them.
Regards,
 
CMDBuild Team
 
Previously Nick Fudger wrote:
We are struggling to create a function to use for this purpose, do you have any example code that we could base our function on at all please?
 
Thanks
Nick
 
Previously Tecnoteca wrote:
As in your case you can not specify explicitly the list of the names of the organizations on wich every user can work, the best solution to work in a parametric way is to use a SQL functions that return the correct policies.
You can see an example of setting permissions on rows through SQL function on the Administration Manual, page 69, screenshot at the bottom.
CMDBuid Team