ODBC Server Installation and Management Manual

Managing the NonStop ODBC Server
HP NonStop ODBC Server Installation and Management Manual—429395-002
4-19
Database Access Controls
This method uses more storage than simply creating a shadow catalog, but supports
metadata on the tables, which some applications require.
•
Specifying Published Columns in a Protection View
You can further restrict access to specific columns by specifying particular columns to
be published in the protection view, as follows.
1. Create a shadow catalog in a separate subvolume by using the following
statement:
NOSCOM> USERCAT INSTALL $data1.privcat
2. Add a protection view to the shadow catalog with a column list
SQLCI> CREATE VIEW $data1.privcat.y01q01 AS SELECT c1, c2 ...
FROM $data2.payroll.y01q01 FOR PROTECTION
The created view can be updated if all the columns in the base table, not included in
the column list, are defined in SQL/MP with a default value. NonStop ODBC Server will
list only those columns named in the CREATE VIEW statement.
Restricting Access to Read-Only
To restrict a client to read-only sessions, the recommended method is to secure the
relevant SQL/MP objects accordingly. Setting the ACCESS_MODE option in the user’s
profile (in ZNSPROF) to read-only is a less secure choice because the client can reset
the ACCESS_MODE option by using the pass-through SET ACCESS_MODE
statement to change the session behavior.
Restricting the Creation of Objects in a Database
The simplest way to restrict the creation of SQL objects in a database is to secure the
NonStop ODBC Server catalog tables for the appropriate write access. For, by
securing the NonStop ODBC Server catalog tables as “COCO,” only the owner can
write to them, which means that only the owner can create objects in the database. In
addition, the SQL/MP catalog tables associated with that database also must be
secured to provide read/write access to the same group of users.
Security for Stored Procedures
Security for a stored procedure is controlled by the security of the NonStop ODBC
Server catalog tables in which the stored procedure is registered, the same as any
other SQL object registered in the NonStop ODBC Server catalog tables. In addition,
there is the security of the Pathway server where the stored procedure has been set
up. It is the system administrator’s responsibility to set up the security of the Pathway
server to provide the correct level of user access.