Обсуждение: security barrier INSERT
Hi guys, I’m seeing some non-intuitive behavior with the new updateable security barrier views in 9.4. Below is the behaviorof 9.4b3:<div class=""><br class="" /></div><div class=""><div class="" style="margin: 0px; font-size: 11px; font-family:Menlo;"></div><blockquote class="" type="cite"><div class="" style="margin: 0px; font-size: 11px; font-family:Menlo;">=# create table widget ( id integer);</div><div class="" style="margin: 0px; font-size: 11px; font-family:Menlo;">CREATE TABLE</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;">=# create viewwidget_sb WITH (security_barrier=true) AS SELECT * from widget where id = 22;</div><div class="" style="margin: 0px;font-size: 11px; font-family: Menlo;">CREATE VIEW</div><div class="" style="margin: 0px; font-size: 11px; font-family:Menlo;">=# insert into widget_sb (id) values (23);</div><div class="" style="margin: 0px; font-size: 11px; font-family:Menlo;">INSERT 0 1</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;">=# select * fromwidget;</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;"> id </div><div class="" style="margin:0px; font-size: 11px; font-family: Menlo;">----</div><div class="" style="margin: 0px; font-size: 11px; font-family:Menlo;"> 23</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;">(1 row)</div></blockquote></div><divclass=""><br class="" /></div><div class="">I think the insert should fail, since the viewcan only contain widgets with id = 22, and the widget being inserted has id 23. In reality, the insert to the behindtable succeeds as shown above, although it still remains absent from the view:</div><div class=""><br class="" /></div><divclass=""><blockquote class="" type="cite">=# select * from widget_sb;<br class="" /> id <br class="" />----<brclass="" />(0 rows)</blockquote></div><div class=""><br class="" /></div><div class="">IMHO this is nonintuitive,the intuitive behavior of a security_barrier view should be to forbid inserting rows that can’t appear in theview.</div><div class=""><br class="" /></div><div class="">Have I missed something fundamental here?</div><div class=""><brclass="" /></div><div class="">Drew</div>
Drew,
IMHO this is nonintuitive, the intuitive behavior of a security_barrier view should be to forbid inserting rows that can’t appear in the view.
Isn't that what WITH CHECK OPTION is meant to accomplish?
-Adam
Adam Brightwell - adam.brightwell@crunchydatasolutions.com
Database Engineer - www.crunchydatasolutions.com