I don't have one set that way, I was able to create one with those but I have not tested its set up. It was merly an idea to try. You could wrap the SQL proc in a UDTF to give the access to the DB in typical selects.
-Matt
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Friday, September 24, 2021 2:16 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Restricting access to data only from certain views
Can you do SET OPTION DYNUSRPRF=*OWNER, USRPRF=*OWNER in a UDTF? Rob Berendt -- IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104 Ft. Wayne,
External (rob@xxxxxxxxx<mailto:rob@xxxxxxxxx>)
Report This Email<
https://shared.outlook.inky.com/report?id=d2luY29mb29kcy9tYXR0LnR5bGVyQHdpbmNvZm9vZHMuY29tLzI4NmZlNzFmMDMwNGFjNzBkYmVhZjA4OGIzZjNlNDE4LzE2MzI1MTQ1NTkuMTU=#key=9dc99ed5e01ea129c9b180c1f595f05c>
Can you do SET OPTION DYNUSRPRF=*OWNER, USRPRF=*OWNER in a UDTF?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
https://urldefense.com/v3/__http://www.dekko.com__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr20_xKrT$<
https://urldefense.com/v3/__http:/www.dekko.com__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr20_xKrT$>
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Tyler, Matt
Sent: Friday, September 24, 2021 3:25 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: RE: Restricting access to data only from certain views
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Also, consider an inline table function.
-Matt
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Rob Berendt
Sent: Friday, September 24, 2021 1:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: RE: Restricting access to data only from certain views
I think I'm going to have to use a stored procedure which adopts the appropriate authority. Rob Berendt -- IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation
External (rob@xxxxxxxxx<mailto:rob@xxxxxxxxx<mailto:rob@xxxxxxxxx%3cmailto:rob@xxxxxxxxx>>)
Report This Email<
https://urldefense.com/v3/__https://shared.outlook.inky.com/report?id=d2luY29mb29kcy9tYXR0LnR5bGVyQHdpbmNvZm9vZHMuY29tLzY5YzAxOWU4ZmYyMGQyNTNiMmE5OTQwMTJjZjAzZmMzLzE2MzI1MTEwMjUuMjg=*key=071fd3974ddf8b310506634e9283a09e__;Iw!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr1WcOVzX$<
https://urldefense.com/v3/__https:/shared.outlook.inky.com/report?id=d2luY29mb29kcy9tYXR0LnR5bGVyQHdpbmNvZm9vZHMuY29tLzY5YzAxOWU4ZmYyMGQyNTNiMmE5OTQwMTJjZjAzZmMzLzE2MzI1MTEwMjUuMjg=*key=071fd3974ddf8b310506634e9283a09e__;Iw!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr1WcOVzX$> >
I think I'm going to have to use a stored procedure which adopts the appropriate authority.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
https://urldefense.com/v3/__http://www.dekko.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWuq7BzNR$<
https://urldefense.com/v3/__http:/www.dekko.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWuq7BzNR$><
https://urldefense.com/v3/__http:/www.dekko.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWuq7BzNR$>
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx%3cmailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>>> On Behalf Of Rob Berendt
Sent: Friday, September 24, 2021 2:22 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx%3cmailto:midrange-l@xxxxxxxxxxxxxxxxxx>>>
Subject: Restricting access to data only from certain views
Let's say you have a table called TABLEX in schema PF and a view called VIEWX in schema LF.
Can you set it up so that the person can only see the data in TABLEX by using the view VIEWX?
I do not want to use any exit points if I can avoid it. I prefer to secure the data and not play whack-a-mole trying to figure out ways to get to the data.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
https://urldefense.com/v3/__http://www.dekko.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWuq7BzNR$<
https://urldefense.com/v3/__http:/www.dekko.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWuq7BzNR$><
https://urldefense.com/v3/__http:/www.dekko.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWuq7BzNR$>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWnnll_N1$<
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWnnll_N1$><
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWnnll_N1$>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWvzzqdvp$<
https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWvzzqdvp$><
https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWvzzqdvp$> .
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto:support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWsWSWFAe$<
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWsWSWFAe$><
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWsWSWFAe$>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWnnll_N1$<
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWnnll_N1$><
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWnnll_N1$>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWvzzqdvp$<
https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWvzzqdvp$><
https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWvzzqdvp$> .
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto:support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWsWSWFAe$<
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWsWSWFAe$><
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!qe6ANo-vHMdDv7-P1bXleDDpwWxPnuvtdyPfcDaPZcgugixBgayAYzJWZbYfWsWSWFAe$>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr6L2j4ar$<
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr6L2j4ar$>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2BzqK5x$<
https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2BzqK5x$> .
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2MqFTmb$<
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2MqFTmb$>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr6L2j4ar$<
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr6L2j4ar$>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2BzqK5x$<
https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2BzqK5x$> .
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2MqFTmb$<
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!ppGW9BDPzRjbg78mBApvAAlnDqRDpWo3F-3fsqQqmvru_h4b4Nyf0GEP6Hckr2MqFTmb$>
midrange.com
