Hi,
I want to verify like If in a Job if we have Database Stage (Oracle Enterprise,DB2 apI,ODBC --) and the password is parameterised and I want to check whether that is encrypted or not.Can you please suggest if there is any universe qurery to check if there is any database stage in a job and there are any parameters with encrypted type.
Thanks
dstest
Regarding Encrypted Parameters
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The answer is yes, but it's messy.
Jobs With Encrypted Parameters
You would need to unpack the CParameters collection in the ROOT record for the job in DS_JOBOBJECTS, and then decode the enumerated parameter type.
Parameter types are in the multi-valued field #19 in this record. From JOBCONTROL.H you can learn that the value 1 is associated with Encrypted as a parameter type, and that there are fewer than 10 types so a search for "1" alone should suffice. Therefore we have a shortcut available. Something like the following should do it.
As noted, this is a shortcut, though it does answer the question you asked about jobs that contain one or more encrypted parameters.
Jobs With Database Stages
This one is slightly easier, as the inner query can be driven directly off OLETYPE. You want ODBC, UV and Custom stage types (in server jobs database stages are all in the CCustomStage class).
If you want both in one report, simply form a UNION in INTERSECTION on the inner query.
Jobs With Encrypted Parameters
You would need to unpack the CParameters collection in the ROOT record for the job in DS_JOBOBJECTS, and then decode the enumerated parameter type.
Parameter types are in the multi-valued field #19 in this record. From JOBCONTROL.H you can learn that the value 1 is associated with Encrypted as a parameter type, and that there are fewer than 10 types so a search for "1" alone should suffice. Therefore we have a shortcut available. Something like the following should do it.
Code: Select all
SELECT NAME FMT '32L', CATEGORY FMT '40L'
FROM DS_JOBS
WHERE JOBNO IN
(
SELECT OBJIDNO
FROM DS_JOBOBJECTS
WHERE OBJNAME = 'ROOT'
AND EVAL "INDEX(@RECORD<19>,'1',1)" > '0'
);
Jobs With Database Stages
This one is slightly easier, as the inner query can be driven directly off OLETYPE. You want ODBC, UV and Custom stage types (in server jobs database stages are all in the CCustomStage class).
Code: Select all
SELECT NAME FMT '32L', CATEGORY FMT '40L'
FROM DS_JOBS
WHERE JOBNO IN
(
SELECT OBJIDNO
FROM DS_JOBOBJECTS
WHERE OLETYPE LIKE 'CODBC%'
OR OLETYPE LIKE 'CUv%'
OR OLETYPE LIKE 'CCustom%'
);
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Ray,
Thanks for your qucik reply.For example in a oracle enterprise stage or odbc stage we have a Password property there i defined #TargetPassword# and if there is any way i can find out what is the exact parameter name i defined in this property for all datasbase stages and then i use this parameter name to check if it is encrypted or not.
1)first i will check in a job if we have any database stages (odbc,oracle,db2).
2)I need to find what is parameter name i defined in the password property of the stage.
3)Use that paramater name and check if it is encrypted or not.
Thanks
dstest
Thanks for your qucik reply.For example in a oracle enterprise stage or odbc stage we have a Password property there i defined #TargetPassword# and if there is any way i can find out what is the exact parameter name i defined in this property for all datasbase stages and then i use this parameter name to check if it is encrypted or not.
1)first i will check in a job if we have any database stages (odbc,oracle,db2).
2)I need to find what is parameter name i defined in the password property of the stage.
3)Use that paramater name and check if it is encrypted or not.
Thanks
dstest
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can do what I suggested above - simply form the intersection of the sub-queries.
If you want to attack it from the other end (that is, beginning with a particular parameter name) that's a fish of an entirely different colour, and a rather more complex query. Indeed, one that would take more time to develop than I have free at the moment. Yes it's do-able, but I only have so many unpaid hours I can devote to these kinds of things.
If you want to attack it from the other end (that is, beginning with a particular parameter name) that's a fish of an entirely different colour, and a rather more complex query. Indeed, one that would take more time to develop than I have free at the moment. Yes it's do-able, but I only have so many unpaid hours I can devote to these kinds of things.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: