Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Fri Feb 02, 2018 7:13 am Post subject: Security concept with ROLE
Dear all,
the DB2 Syntax diagram for DB2 zOS shows something like this:
GRANT SELECT ON anySchema.AnyView TO ROLE <anyRole>
I also found this:
CREATE ROLE <anyRole>.
Question: How can I add Users to this role <anyRole>?
Is this way "state of the art"?
Or is something like this better:
Setup in RACF <anyRACFGroup>
Connect User to <anyRACFGroup>
in DB2: GRANT SELECT ON anySchema.AnyView TO <anyRACFGroup>
This works for accessing the view by SPUFI.
But it doesn't work submitting any JCL, calling EXEC PGM=IKJEFT01 with BIND PACKAGE. This results to -551 and the remark, that UserXY doesn't have the privilege for anySchema.AnyView . (all other necessary privileges like BINDADD are available).
Background of my question: Our installation is very old. We have now to create a lot of new tables, views and we are thinking about to use the ROLE concept - but nobody really knows, how this works.
Which concept is state of the art?
And please: Don't advise me to ask any administrator. This doesn't help - our administrators are not really sure about the ROLE concept and asked me for help.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Fri Feb 02, 2018 11:50 am Post subject: Re: Security concept with ROLE
bauer wrote:
Dear all,
the DB2 Syntax diagram for DB2 zOS shows something like this:
GRANT SELECT ON anySchema.AnyView TO ROLE <anyRole>
I also found this:
CREATE ROLE <anyRole>.
Question: How can I add Users to this role <anyRole>?
Bauer,
Unless I am missing something isn't it as simple as granting?
Code:
grant role <role_name> to user <username>
and for a table
Code:
grant select on table <table_name> to role <role_name>
bauer wrote:
Connect User to <anyRACFGroup>
in DB2: GRANT SELECT ON anySchema.AnyView TO <anyRACFGroup>
This works for accessing the view by SPUFI.
But it doesn't work submitting any JCL, calling EXEC PGM=IKJEFT01 with BIND PACKAGE. This results to -551 and the remark, that UserXY doesn't have the privilege for anySchema.AnyView . (all other necessary privileges like BINDADD are available).
If you are using IKJEFT01, then you need to grant access to the plans DSNTIAUL, DSNTEP02 to your users and they can run the queries in batch as long as they are already granted the table view.
bauer wrote:
Background of my question: Our installation is very old. We have now to create a lot of new tables, views and we are thinking about to use the ROLE concept - but nobody really knows, how this works.
bauer
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Fri Feb 02, 2018 12:27 pm Post subject:
kolusu,
thank you for your reply and time.
This
Code:
grant role <role_name> to user <username>
works only for DB2 LUW, not DB2 zOS.
I did some addtional reading in the manuals and asked some other experts.
To connect the user to the role in DB2 zOS something like
Code:
CREATE TRUSTED CONTEXT
is necessary. Oversized for me.
The solution using the RACF groups is, to add the OWNER clause to the BIND PACKAGE Statement.
And the owner clause should be filled with the RACF group.
This was just tested by myself and this works.
About your comment regarding DSNTIAUL, DSNTEP02 and the GRANTs, not sure about the current settings.
Regarding your question about the DB2 Version: Probably my poor english and misunderstanding. "very old Installation" means the basic settings, procedure / process for binding packages / granting views was established 25 years ago, the db2 version is a 11 version.
So upgrades of course were done in the past for DB2 but no upgrades / reviews for the process how to bind / grant views and packages.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
