Re: How to prevent developers from creating views over production files

All a view is, is an encapsulated SQL statement...

Unlike a logical file, which usually contains an access path.

What problem are you trying to prevent?

Charles

On Tue, Feb 11, 2020 at 3:00 PM Dean Eshleman <Dean.Eshleman@xxxxxxxxxxxx>
wrote:

Hi,

We have just started to use SQL views and we discovered that developers
can create views in their libraries over production files. Currently,
developers have read rights to production files. They don't have object
management rights which prevents them from creating logical files over the
production files. Here is are a few excerpts from the SQL reference manual
for the CREATE VIEW statement. After each one I explain how they meet the
criteria.


1. The privileges held by the authorization ID of the statement must
include at least
one of the following:

* The privilege to create in the schema. For more information, see
Privileges necessary to create in a schema.
* Database administrator authority

They meet this criteria because they have authority to create stuff in
their own library


1. The privileges held by the authorization ID of the statement must
include at least
one of the following:

* The following system authorities:
- *USE to the Create Logical File (CRTLF) CL command
- *CHANGE to the data dictionary if the library into which the view is
created is an SQL schema with a data dictionary

* Database administrator authority
They meet this criteria because they can use the CRTLF command to create
stuff in test libraries.


1. The privileges held by the authorization ID of the statement must
also include at
least one of the following:

* For each table and view referenced directly through the fullselect,
or indirectly through views referenced in the fullselect:
- The SELECT privilege on the table or view, and
- The system authority *EXECUTE on the library containing the table or
view

* Database administrator authority

They meet this authority because they can read the production files.

So, I'm at a loss for how to prevent developers from creating views over
production files. It seems like it should follow the same authority
requirements as creating a logical file, but it doesn't. Anyone have any
ideas?

Dean Eshleman
Software Development Architect
Everence

1110 North Main Street
PO Box 483
Goshen, IN 46527
(800) 348-7468 ext. 3528
(574) 533-9515 ext. 3528
[Join our Talent Network]<http://www.everence.com/careers>

Confidentiality Notice: This information is intended only for the
individual or entity named. If you are not the intended recipient, do not
use or disclose this information. If you received this e-mail in error,
please delete or otherwise destroy it and contact us at (800) 348-7468 so
we can take steps to avoid such transmission errors in the future. Thank
you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com