CODEWITHSUNDEEP
viewpermissionsibm-midrangedb2-400
Totty.js
Totty.js

Reputation: 15831

How to create view db2/iseries with not default permissions?

Everytime I create a new view I have to change the permissions to all, and it's quite painfully to do a lot of times the same thing.

I also ask if is possible to change some default options in the database I create the view in order the permissions to be set to public.

Thanks

Upvotes: 0

Views: 1391

Answers (2)

WarrenT
WarrenT

Reputation: 4532

It sounds like you are using SQL naming. Use System naming instead, this will grant public authority according to the QCRTAUT system value. See Birgitta Hauser's article.

System naming has the added advantage of using the job's library list to resolve unqualified object references. By not hard-coding schema names, but allowing the system to find them according to the library list, enabling your code to work in different environments (ex. development, testing, training, production) without modifying the code, simply by running with a different library list. Therefore you can install code into production exactly the same code that was tested.

Upvotes: 2

Benny Hill
Benny Hill

Reputation: 6240

The following is from the V6R1 documentation:

Authorization

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.

Administrative authority

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 Administrative authority

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 Administrative authority

View ownership: If SQL names were specified:

If a user profile with the same name as the schema into which the view is created exists, the owner of the view is that user profile.
Otherwise, the owner of the view is the user profile or group user profile of the job executing the statement.

If system names were specified, the owner of the view is the user profile or group user profile of the job executing the statement.

View authority:

If SQL names are used, views are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, views are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the view is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the view.

The owner always acquires the SELECT privilege WITH GRANT OPTION on the view and the authorization to drop the view.

The owner can also acquire the INSERT, UPDATE, and DELETE privileges on the view. If the view is not read-only, then the same privileges will be acquired on the new view as the owner has on the table or view identified in the first FROM clause of the fullselect. These privileges can be granted only if the privileges from which they are derived can also be granted.

Upvotes: 1

Related Questions