Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Confusion on SET CURRENT SQLID.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Thu Oct 16, 2008 8:59 am    Post subject: Confusion on SET CURRENT SQLID.
Reply with quote

Hi all,I am confused about Authorization ID and owner,let me saying:
assuming I have SYSADM authorization.
I execute:
Code:
SET CURRENT SQLID='XXXXXXXX';
CREATE DATABASE .....


and the sql abend saying id 'XXXXXXXX' has no authorization to create Database,

then I execute:
Code:
GRANT CREATEDBC to 'XXXXXXXX';

and execute the creating sql again, it works,so my questions are:

1. SET CURRENT SQLID.
what is your purpose when use this ?As to me it's just a implicit qualifier for unqualified-objects,please correct me.Or by using this the processing ID is able to hold the privileges of the SQLID to process SQL right after 'SET CURRENT SQLID=....'?such as processing ID A(Primary ID) can not create database,but ID B(SQLID ) has the authorization, so ID A can process successfully : SET CURRENT SQLID=B CREATE DATABASE .....?

2. GRANT SYSADM TO XXXXXXXX.
as to me ,the 'TO' value must be a existing ID(either Primary or Secondary)?
But I can execute successfully :
GRANT .. TO &VALUE, here &VALUE can be any string?So can the TO value be any string other than the existing IDs or there is something else magic?

hopeful I am clear, thanks you all in advance.
Back to top
View user's profile Send private message

vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Oct 16, 2008 1:03 pm    Post subject:
Reply with quote

1.

In this case it looks like the secondary id didnt have create databse access before. DB2 uses the CURRENT SQLID to check for the privileges. You should also have access to use some TSO user id as your secondary id. By default I cant set current sqlid to my DBA's user id.

As far as I know, String has to be a Valid TSO USER ID. It cannot be any random string.

Please correct me if am wrong....
Back to top
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Thu Oct 16, 2008 1:18 pm    Post subject:
Reply with quote

thanks for your response vini,

one thing I am eager to know what is the purpose of 'SET CURRENT SQLID..' in your shop?
is it for the privilege purpose due to the processing ID's privilege is not high enough so the privilege of 'SQLID' is needed.

or is it for the owner(qualifier) purpose , assume we first execute the 'SET CURRENT SQLID='ABC'...' as following we execute the 'CREATE table table_name columns......' without specifying the owner(qualifier) such as A.table_name,then the table will be ABC.table_name?

thanks.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Oct 16, 2008 2:19 pm    Post subject:
Reply with quote

Hi,
Quote:
is it for the privilege purpose due to the processing ID's privilege is not high enough so the privilege of 'SQLID' is needed.
Sorry, I didn't get this, What does this mean..?

Quote:
In DB2 the value of special register CURRENT SQLID determines what you are allowed to do. You can issue a SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1 to determine the value of your CURRENT SQLID. In a program using static SQL you can use an EXEC SQL SET :hostvar = CURRENT SQLID to get the same value.

When you have found the value of your CURRENT SQLID then you can make queries against the DB2 catalogoue tables with the suffix AUTH in order to determine what you are allowed to do. Your WHERE clause must include the predicate GRANTEE = the current sqlid.

The highest normal level of authorization in DB2 is called SYSADM. If your CURRENT SQLID is granted this privilege you can change the CURRENT SQLID to any other SQLID by issuing a SET CURRENT SQLID = something. It is possible to allow other SQLID's to change their SQLID's, but only if the system programmers at your installation has manipulated the two security exits in DB2. One of the exits is called the signon exit (DSN3@SGN) and the other is called the connection exit (DSN3@ATH). IBM has made a sample of each of these two exits. Some installations uses these two samples as is. They allow most users to change their CURRENT SQLID but only to a set of well defined values.

When you can change CURRENT SQLID and you are not SYSADM then there is no way in DB2 to see what values you are allowed to change to. Only the security exits knows this. This is because the security exits gains control before there is established any connection to DB2. In order to carry out a SQL statement you have to have a CURRENT SQLID and therefore you cannot query DB2 about your possible SQLID values, otherwise you would get access to DB2 without any identification. Therefore the two security exits must rely on other sources in order to validate a change in SQLID. The samples uses RACF, but it is possible to use other sources except DB2 itself.

If you want to know what SQLID's you are allowed to change to and you are not SYSADM, you have to know how the security exits works at your installation. This sounds pretty simple, but usually it isn't. There is definately room for improvement in this part of DB2. Well, you probably know what SQLID's you can change to or you can ask someone. The DB2 databaseadministrators are the first to be asked, but they typically don't know anything about the security exits, because they are all SYSADM's and changes the SQLID as they wish. Next one in line is the DB2 systems progammer who made the exit work many years ago. He has left the company or is working with something completely different. Then you can read more about the security exits in the DB2 administration guide.
Back to top
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Thu Oct 16, 2008 5:40 pm    Post subject:
Reply with quote

Hi Anuj, I really appreciate your useful reply, and sorry for the unclear depiction.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Oct 16, 2008 5:48 pm    Post subject:
Reply with quote

http://www.mainframesupport.dk/tips/tip0436.html

;)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
No new posts Confusion while passing data to a cal... amitc23 COBOL Programming 5 Mon Mar 07, 2016 12:36 pm
No new posts DB2 - CURRENT DATE + 2 YRS logic balaji81_k DB2 3 Thu Nov 26, 2015 3:07 am
No new posts Appending previous record SEQNUM valu... selvamsrinivasan85 DFSORT/ICETOOL 6 Fri Oct 16, 2015 2:09 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us