cancel
Showing results for 
Search instead for 
Did you mean: 

how change password of user mapping in oci_dblink???

SOLVED
Gold Adventurer

how change password of user mapping in oci_dblink???

hi all, i have a FDW to acces oracle from EDB 

CREATE SERVER mi_oracle
FOREIGN DATA WRAPPER oci_dblink
OPTIONS (connstr '//10.1.0.1:1521/db');


CREATE USER MAPPING 
FOR public
SERVER mi_oracle
OPTIONS (password 'password',user 'system');





i want to change the password but return some error : 

 

ALTER USER MAPPING FOR public SERVER psvs_oracle OPTIONS (SET password 'nueva_pass');

ERROR:  option "password" not found

********** Error **********

ERROR: option "password" not found
SQL state: 42704

i am missing something? 

Regards

Tags (1)
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
EDB Team Member

Re: how change password of user mapping in oci_dblink???

Hi Asotolongo,

 

From the output it says that syntax error and same we have reproduce in our local environment. Could please use the correct syntax to update the password which is shown below.

 

edb=# alter user MAPPING FOR test SERVER mi_oracle OPTIONS (SET password 'pwd');
ERROR:  option "password" not found

 

----------------------------------------------------------------------------------------------------------------------------------------

edb=# alter user MAPPING FOR test SERVER mi_oracle OPTIONS (SET user 'test', password 'pwd');
ALTER USER MAPPING
----------------------------------------------------------------------------------------------------------------------------------------
 
Please let us know if you need any other help/info on this, if not could you please mark as Accept as Solution.
EDB Team Member

Re: how change password of user mapping in oci_dblink???

Hi asotolongo,

 

Your issue related to user mapping is resolved in the latest version of EPAS10 :

 

EDB Technical Update EPAS 10.8.16

13 REPLIES 13
Level 3 Adventurer

Re: how change password of user mapping in oci_dblink???

Hi, 

 

Can you please specify the database versions you are using?

 

--

Regards

Ajinkya

Gold Adventurer

Re: how change password of user mapping in oci_dblink???

hi @ajinkya , the EDB version is:

 

EnterpriseDB 10.4.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

 

Regards

EDB Team Member

Re: how change password of user mapping in oci_dblink???

Hi @asotolongo,

 

From the syntax, it looks like you are trying to create public mapping, if so please use the PUBLIC (capital) as keyword :

 

CREATE USER MAPPING
FOR PUBLIC
SERVER mi_oracle
OPTIONS (password 'password',user 'system');


If you are trying to create mapping for only system user, use command as below :

 

CREATE USER MAPPING
FOR system
SERVER mi_oracle
OPTIONS (password 'password',user 'system');

 

Please test it again with above commands and let us know in case of any issues/queries.

 

Regards,

Sudhir

Gold Adventurer

Re: how change password of user mapping in oci_dblink???

hi @slonkar , thanks for your response, i need that my user mapping be public for all user 

i only want to change the password of user mapping (because in oracle change)

 

 

and CAPITAL letter not function:

ALTER USER MAPPING FOR PUBLIC SERVER mi_oracle OPTIONS (SET password 'nueva_pass');

ERROR:  option "password" not found

********** Error **********

ERROR: option "password" not found
SQL state: 42704

regards!!!

Highlighted
EDB Team Member

Re: how change password of user mapping in oci_dblink???

Hi Asotolongo,

 

From the output it says that syntax error and same we have reproduce in our local environment. Could please use the correct syntax to update the password which is shown below.

 

edb=# alter user MAPPING FOR test SERVER mi_oracle OPTIONS (SET password 'pwd');
ERROR:  option "password" not found

 

----------------------------------------------------------------------------------------------------------------------------------------

edb=# alter user MAPPING FOR test SERVER mi_oracle OPTIONS (SET user 'test', password 'pwd');
ALTER USER MAPPING
----------------------------------------------------------------------------------------------------------------------------------------
 
Please let us know if you need any other help/info on this, if not could you please mark as Accept as Solution.
Gold Adventurer

Re: how change password of user mapping in oci_dblink???

hi @kapil, yes your solution function OK, but in DDL definition i get tow obfuscated_password, the old and new, that is to say that is adding attributes obfuscated_password, 

here is the DDL after an ALTER

CREATE USER MAPPING 
   FOR public
   SERVER mi_oracle
  OPTIONS (obfuscated_password 'Nd5EQGQ/3Lj46TQ+cfKBdg',user 'system',obfuscated_password 'efgPzUYh03PK3k6DJie09g');

Thanks 

Regards

EDB Team Member

Re: how change password of user mapping in oci_dblink???

Hi Asotolongo,

Good to hear that the solution given worked.

Could you please elaborate your concern regarding the DDL definition mentioned in your response?

Gold Adventurer

Re: how change password of user mapping in oci_dblink???

hi @Rohit , here you are the link with  a video to see the concern about the DDL after do the "ALTER USER"  command

 

https://drive.google.com/open?id=1-mdcwfx3XgxylSsW2TcINqH1behWjPox

 

let me know if you need more information

 

Regards

Level 3 Adventurer

Re: how change password of user mapping in oci_dblink???

Hi,

 

We have tested your concern on our local environment, we are also receiving the similar create script.

Whenever we are modifying the password a new entry is getting added in encrypted format. The test case is attached.

We will discuss it internally whether it is expected behaviour or not and get back to you.