Greenplum DB and create Rule on SELECT, INSERT, UPDATE, DELETE on table

Post date: Sep 20, 2012 7:11:56 PM

Q. Can the data that is returned somehow get TEMPORARILY (only available during the user session) stored in the database and be able to be used to further restrict the user record selections (maybe added to the WHERE clause, etc.) as they issue command such as select from a table, etc. ?

Ans: Yes. This can be possible by defining rules on the table itself. This is a great unique feature of Greenplum to restrict the access of a table data at the table level based on predefined rule. Please see below the implementation option..  

Rule and how to implement it for restriction database users to see a portion of data.

1. We can create a metadata table that stores mapping of user id , list of id's that should be restricted from the select statement. (My assumption is that we have limited users in this category who are directly connecting to the database). We can use this metadata table to rewrite the select statement in the rule at run time.

2. We can get the session user name by running current_user command in Greenplum database(user name of current execution context) and join this user name with metadata table above.

Run the create rule command for that table to create the rule.

CREATE [OR REPLACE] RULE name AS ON event TO table [WHERE condition] DO [ALSO | INSTEAD] { NOTHING | command | (command; command ...) }

About Greenplum Create Rule functionality

1. CREATE RULE defines a new rule applying to a specified table or view. CREATE OR REPLACE RULE will either create a new rule, or replace an existing rule of the same name for the same table.

2. The Greenplum Database rule system allows one to define an alternate action to be performed on insertions, updates, or deletions in database tables. A rule causes additional or alternate commands to be executed when a given command on a given table is executed. Rules can be used on views as well. It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the commands starts. It does not operate independently for each physical row as does a trigger.

3. ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT command. Thus, an ON SELECT rule effectively turns the table into a view, whose visible contents are the rows returned by the rule’s SELECT command rather than whatever had been stored in the table (if anything). It is considered better style to write a CREATE VIEW command than to create a real table and define an ON SELECT rule for it.