SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-39
Creating a Protection View
°
The purge authority for the protection view must include all users who have
purge authority for the underlying table. Normally, the purge authority for a
protection view is the same as the purge authority for the underlying table.
°
The security string at creation time must meet security dependency
requirements. If the creator’s default security string violates the rules, the
creation attempt fails. The creator can then include the SECURE clause in the
CREATE VIEW statement and reissue the statement.
°
If an alteration of the security string for the underlying table violates the
security criteria, SQL issues a warning and automatically changes the security
strings for the protection view.
Examples
This example creates a protection view of the EMPLOYEE table. The table columns
are renamed in the view definition.
>> CREATE VIEW $VOL1.PERSNL.EMPLIST
+> (EMPLOYEE_NUMBER, LAST_NAME, FIRST_NAME, DEPARTMENT)
+> AS SELECT EMPNUM, LAST_NAME, FIRST_NAME, DEPTNUM
+> FROM EMPLOYEE
+> FOR PROTECTION
+> WITH HEADINGS
+> CATALOG $VOL1.PERSNL;
This example shows a view definition including the WITH CHECK option and the effect
on an attempted insertion of a row that violates the WITH CHECK condition. Only rows
with EMPNUM values greater than 1 and less than 1000 can be inserted.
>>CREATE VIEW $VOL1.PERSNL.EMPVIEW
+> AS SELECT EMPNUM, FIRST_NAME, LAST_NAME,
+> DEPTNUM, JOBCODE, SALARY
+> FROM $VOL1.PERSNL.EMPLOYEE
+> WHERE EMPNUM > 1 AND
+> EMPNUM < 1000
+> FOR PROTECTION
+> CATALOG $VOL1.PERSNL
+> SECURE "NCNC"
+> WITH CHECK OPTION;
--- SQL operation complete.
-- Because the WITH CHECK OPTION is defined, the following
-- INSERT statement is rejected:
>>INSERT INTO $VOL1.PERSNL.EMPVIEW (*)
+> VALUES (1200, "WAYLAN","JAMES", 4000, 250, 55000.00 );
^
*** ERROR from SQL [-8300]: File system error occurred on
\SYS1.$VOL1.PERSNL.EMPVIEW.
*** ERROR from File System [1026]: The selection expression
on an SQL view has been violated.