MVSFORUMS.com

Bharath.AK

Hello,

Please help me out with this scenario:

I have 2 tables - One is GROUP and another is LIBUSER.
GROUP contains AdGroup and Library variables;
LIBUSER contains Library and AdUser variables.

Sample data for GROUP is:

kolusu · Posted: Fri May 20, 2011 11:19 am Post subject:

Bharath.AK,

What is the Final output you want from the select statement?

Kolusu

Bharath.AK · Posted: Fri May 20, 2011 11:42 am Post subject:

Hello Kolusu,

The thing is we are working on restricting our SAS users to have access to only few libraries instead of all available libraries.

When the value of AdUser in LIBUSER table is 'ALL', then all users have access to that library. If the value is other than 'ALL', that means only specific users hould have access to that library.

create table temp as
select distinct library from Group a, LIBUSER d
where a.library=d.library

Here we have all the Users and libraries in TEMP table.

Now we are trying to restrict users and have to put a condition to restrict on a library level

if AdUser = ALL, accesses by every user. etc.

Did I answer your question? Please let me know.
_________________
Regards,
BK

***** " If You Have To Do It, You Might As Well Do It Right.....If It Is Worth Doing At All, It's Worth Doing It Right !!! " *****

Bharath.AK · Posted: Fri May 20, 2011 11:53 am Post subject:

Kolusu, if this helps,

1. If the LIBUSER.AdUser NOT = 'ALL', then I should create the TEMP table as
create table temp as
select distinct a.adgroup, d.aduser
from group a, libuser d
where a.library=d.library

2. If the LIBUSER.AdUser = 'ALL', then I should create the TEMP table as
create table temp as
select distinct a.adgroup, d.aduser
from group a, libuser d
where a.library=d.library and userlogin = d.libuser

Here we get userlogin value from some other source (can be manageable) And these should be accomplished in a single select statement.
_________________
Regards,
BK

***** " If You Have To Do It, You Might As Well Do It Right.....If It Is Worth Doing At All, It's Worth Doing It Right !!! " *****

kolusu · Posted: Fri May 20, 2011 1:13 pm Post subject:

Bharath.AK,

Here is an untested sql which would dynamically build your DDL statements. I assumed that you have a column named LOGON_USER where you pass the login name to select the ADUSER column

Bharath.AK · Posted: Fri May 20, 2011 1:19 pm Post subject:

Kolusu, thatk you for your time to help me out in this regard. Actually I am not coding this PROC SQL stmt on mainframe, but in a SAS application. SYSIBM.SYSDUMMY1 is a utility used in JCL, correct?

I'm sorry I should have told this earlier itself. I will try to amend the code accordingly, but when you get time, can you also pls provide the SAS editor application of this code?

Thanks a lot again.
_________________
Regards,
BK

***** " If You Have To Do It, You Might As Well Do It Right.....If It Is Worth Doing At All, It's Worth Doing It Right !!! " *****

kolusu · Posted: Fri May 20, 2011 1:26 pm Post subject:

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

If you want to restrict who can use which tables, suggest you get rid of the "custom" "who can use what" that has been implemented.

Use or restriction of "things" is better accomplished by having proper permissions or security settings.

Possibly i misunderstand. . .

Bharath.AK · Posted: Wed May 25, 2011 9:43 am Post subject:

Thank you kolusu & papadi...

I'm not at all into sql or mainframe or db2 programming, but had to work on this. Yes, I have to 'learn' db2 (but not 'brush up') Smile

And yes, I definitely will avoid following those misleading sources of information henceforth.

I've got the solution for my issue with no much complexity anyway.

Thanks for all your time to help me in this regard.

Cheers Very Happy

kolusu · Posted: Wed May 25, 2011 1:21 pm Post subject:

GuyC · Supermod Joined: 27 May 2011 Posts: 35 Topics: 0

Does this solve the requirement?