Skip to main content
Question

Access Policy on table with LAP projections

  • October 15, 2019
  • 1 reply
  • 10 views
MaciejPaliwoda
Forum|alt.badge.img+2

Hello Team,
My customer has a table, containing some attributes like:

installation_ek int, 
csUserId_ek int,
csUserStation_ek int,
completeness int NOT NULL,
endTime timestamp NOT NULL,
endLocalTime timestamp NULL, 
receiptSerial int,
startTime timestamp NOT NULL,
value INT NOT NULL,
serialNumber varchar(55) NOT NULL,
receivedTime timestamp,
countryCode VARCHAR(10)

Table contains couple of billion of records, and he need "visibility limitation " for both raw and agregated by some of the attirbutes: cs_dw.installation_ek, cs_dw.countryCode, cs_dw.completeness

For the aggregated queries he created LAP projections ( min, sum on the value field), but he cannot apply security policy then.

I recommended him just to create view which is an equivalent of LAP, and it seems to work properly.
Is there any other solution - he is affraid about the poor performance in the production (AWS, whith small machines)... especially if there will be some additinal joins, and agregations.

Thanks in advance

    1 reply

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • November 25, 2019

    Hi,

    If the result set produced by the LAP is not needed real time, maybe you can create a new table that stores the data of the LAP? Then you can add a security policy to the new table. Of course, you'd have to periodically refresh the data in the new table...

    Example:

    dbadmin=> SELECT export_objects('', 'simple_test');
                                                                                                                                                                                                                                                                     export_objects                                                                                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE TABLE public.simple_test
(
    c1 int,
    c2 int
);


CREATE PROJECTION public.simple_test /*+createtype(L)*/
(
 c1,
 c2
)
AS
 SELECT simple_test.c1,
        simple_test.c2
 FROM public.simple_test
 ORDER BY simple_test.c1,
          simple_test.c2
SEGMENTED BY hash(simple_test.c1, simple_test.c2) ALL NODES KSAFE 1;

CREATE PROJECTION public.simple_test_lap
(
 c1,
 c2
)
AS
 SELECT simple_test.c1,
        sum(simple_test.c2) AS c2
 FROM public.simple_test
 GROUP BY simple_test.c1
KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

(1 row)

dbadmin=> SELECT * FROM simple_test ORDER BY 1, 2;
 c1 | c2
----+----
  1 |  1
  1 |  2
  1 |  3
  2 |  1
  2 |  2
(5 rows)

dbadmin=> SELECT * FROM simple_test_lap ORDER BY 1, 2;
 c1 | c2
----+----
  1 |  6
  2 |  3
(2 rows)

dbadmin=> CREATE ACCESS POLICY ON simple_test FOR ROWS WHERE
dbadmin->   NOT ENABLED_ROLE('dbadmin')
dbadmin-> ENABLE;
ROLLBACK 6501:  Access policy cannot be created on table "simple_test" since it has an aggregate projection

dbadmin=> CREATE TABLE simple_test_lap_tbl AS SELECT * FROM simple_test_lap;
CREATE TABLE

dbadmin=> CREATE ACCESS POLICY ON simple_test_lap_tbl FOR ROWS WHERE
dbadmin->   NOT ENABLED_ROLE('dbadmin')
dbadmin-> ENABLE;
CREATE ACCESS POLICY

dbadmin=> SHOW enabled_roles;
     name      |               setting
---------------+--------------------------------------
 enabled roles | dbduser*, dbadmin*, pseudosuperuser*
(1 row)

dbadmin=> SELECT * FROM simple_test_lap_tbl;
 c1 | c2
----+----
(0 rows)

    Just a thought!

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement