Credentials - Oracle

[Former Member]

Dear CPS developers

I am new to this forum and new to version 7 and I am struggling with credentials - especially with Oracle credentials. What needs to match where information wise that such a credential would work? How could a test credentials efficiently.

I am trying to set up a small test with a cronacle script that looks like that:

create or replace script "xxx"."xxx_S_ADMIN_REMOTE_SQL"

( "JCS_RUNUSER" in varchar2(30) null

description 'Use this credentials for the JCS_RUNDB job'

nocmdfile

default expr('<db_user>' )

, "JCS_RUNDB" in varchar2(64) null

description 'Perform job in database'

nocmdfile

default expr('db_name' )

)

application "SYSJCS"."xxx"

format "SYSJCS"."GENERIC"

queue "SYSJCS"."xxx"

no verify

as declare

lv_sysdate varchar2(10);

begin

select to_char(sysdate, 'dd-mm-yyyy') into lv_sysdate from dual;

dbms_output.put_line('ln_sysdate: '||lv_sysdate);

end;

/

and a credential that looks like that:

create or replace credential "SYSJCS"."ORACLE"."<db_name>"."<db_user>"

encoded password ''

/

Somehow I always get this error:

ORA-20800: JCS-02370: SYSJCS has no access to ORACLE credentials for <db_user> on system <db_name>

ORA-06512: at "SYSJCS.DDL", line 7556

ORA-06512: at line 3

JCS-00215: in statement RSO_GET_PASSWORD

I tried different versions and it doesn't work.

Many thanks for your support & regards

Stephan

[Former Member]

Hi Stephan,

I think you're approaching this incorrectly. The credentials provide a means of storing a user/pwd combinations for logging into the OS of a particular scheduler host. This enables you to run jobs as if you have logged in as a particular user.

To create a job to run pl/sql in a remote oracle db then you have two options. You could use the JCS_RUNDB System parameter to change the db name used by the job. (Create a Script with the command text 'echo :USRPWD' and submit it and see the results, i.e. the oracle connect string is displayed 'sysjcs/fred@repository', now edit the script and add the JCS_RUNDB param with a default value for the target db and see how the target db gets changed in the connect string 'sysjcs/fred@remotedb'). The downside to this method is that you need to have a user account/pwd combination the same as in the Repository in the target remote db. If this is not a problem then this method works fine. (if you have a specific user in the remote db you can create the same user in the repository and create a script owned by that user and set the script account property in the script)

Credentials provide futher flexibility in that Scripts that need to run as particular OS accounts can be owned by a common user in SAP CPS and when submitted an OS user (Credential) can be specified. So in your scenario if your user in the remote db was externally identifed you could have you Script/Job effectively logging into OS as the required user and then connect to the target db and execute your pl/sql. Your script effectively becomes a SQLPlus script containing PL/SQL. If not externally identified other options exist too.

Regards,

Simon

[Former Member]

Hi Stephan,

Your approach is correct, in V7 CPS also supports credentials for Oracle connections to eliminate the restriction that Simon is describing with the need for the same username and password.

The thing to note is though that these credentials are case sensitive, so you need to specify the database tnsnames alias in JCS_RUNDB and the username in JCS_RUNUSER in exact the same case as you specified them when creating the credential, otherwise you may get the indicated error.

I'd recommend to use everything in uppercase.

Regards,

Anton.

[Former Member]

Thanks Simon and Anton for your input ... Sorry, it took me so long to reply ...

Best regards, Stephan