• Questions
  • Password change of remote database user by dblink but showing error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another database 'SYS_DBALINK"

Breadcrumb

Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, raghunadhan.

Asked: November 28, 2019 - 3:03 am UTC

Answered by: Chris Saxon - Last updated: December 09, 2019 - 10:44 am UTC

Category: Application Express - Version: 19.1.0.00.15

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Upgrade with no effort

You Asked

HI,i created a form in Oracle Apex and when i click the submit button then Stored Procedure runs that changes the remote database user password over dblink but iam getting the error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another database 'SYS_DBALINK". Please find my code

Version : Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Below SP runs in  local database that calls different database dynamically over dblink

DECLARE
  SQL_STRING VARCHAR2(1000);
BEGIN
SQL_STRING:=' begin accout_change@'||:P2_DATABASE||'LINK('''||:P2_USERAME||''','''||:P2_PASSWORD||'''); end; ';
    EXECUTE IMMEDIATE SQL_STRING;
-- dbms_output.put_line(SQL_STRING);
END;
 
Below SP runs in remote database

create or replace PROCEDURE accout_change (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
BEGIN
    sql_string := 'alter user '
                  || P2_USERAME
                  || ' '
                  || 'identified by '
                  || P2_PASSWORD;
   EXECUTE IMMEDIATE sql_string;
    -- dbms_output.put_line('string ' || sql_string);
    -- dbms_output.put_line('Success');
EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END;

and we said...

BEWARE! BEWARE! This code has MASSIVE SQL injection possibilities.

Stop using string concatenation.

Use bind variables.

Note: this is tricky with the alter user - you can't bind identifiers (usernames, table names, etc.) - so you'll need to use dbms_assert to ensure your code is safe.

To learn about this read:

https://www.oracle.com/technetwork/database/features/plsql/overview/how-to-write-injection-proof-plsql-1-129572.pdf

DO THIS NOW!

If this code is already live, remove it until it's safe.

Anyway, onto your question:

It looks like the link itself isn't configured right. What happens when you run:
select * from dual@SYS_DBALINK


?

What about the procedure on the remote database? Can you call it on that database? What about manually calling it from SQL*plus/cl/dev over the db link?

and you rated our response

  (9 ratings)

Reviews

Reply to your question

November 29, 2019 - 1:55 am UTC

Reviewer: A reader from India

Hi Chris,
I was asked to develop an app for password change of selected database and below select query works when called from sqlworkshop of oracle apex .
select * from dual@SYS_DBALINK

DUMMY
X


The full error when called when submitting button is below

PLS-00352: Unable to access another database 'SYS_DBALINK' ORA-06550: line 1, column 8: PLS-00201: identifier 'ACCOUT_CHANGE@SYS_DBALINK' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored
ora_sqlcode: -6550
Chris Saxon

Followup  

November 29, 2019 - 10:08 am UTC

Have you changed the code to use bind variables yet?

Response to your question

November 30, 2019 - 1:55 pm UTC

Reviewer: A reader

I changed mycode to bind variable as below but showing error as

ORA-06550: line 1, column 22:
PLS-00103: Encountered the symbol "" when expecting one of the following:
@ <an identifier> <a double-quoted delimited-identifier>
ORA-06550: line 1, column 35:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( , * % & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk
1. begin
2. PASSWORD_CHANGE_1('SYS_DBA','TestDB','TestDB1');
3. END

My code is
create or replace PROCEDURE PASSWORD_CHANGE_1(P2_DATABASE VARCHAR2,P2_USERAME VARCHAR2 ,P2_PASSWORD VARCHAR2) IS
SQL_STRING VARCHAR(500);
 
BEGIN
 SQL_STRING:=' begin accout_change@:1LINK(:2,:3); end; ';
EXECUTE IMMEDIATE SQL_STRING using in P2_DATABASE ,P2_USERAME,P2_PASSWORD;

dbms_output.put_line(SQL_STRING);
END;
create or replace PROCEDURE accout_change_1 (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
BEGIN
    sql_string := 'alter user :1 identified by :2 ';
   EXECUTE IMMEDIATE sql_string USING P2_USERAME, P2_PASSWORD ;

    -- dbms_output.put_line('string ' || sql_string);
    -- dbms_output.put_line('Success');
EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END;










Chris Saxon

Followup  

December 05, 2019 - 9:06 am UTC

Nice start.

But as noted below, you can't bind identifiers (the username, database link name, & password).

So you still need to concatenate these - AFTER passing them to dbms_assert.

Another Issue

December 01, 2019 - 4:03 am UTC

Reviewer: A reader from India

Also getting the below error when executing below error .Please help me out solving in this db password change

ORA-01935: missing user or role name

create or replace PROCEDURE accout_change_1 (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
BEGIN
    sql_string := 'alter user :1 identified by :2 ';
   EXECUTE IMMEDIATE sql_string USING P2_USERAME, P2_PASSWORD ;

    -- dbms_output.put_line('string ' || sql_string);
    -- dbms_output.put_line('Success');
EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END;

Followup

December 03, 2019 - 12:59 pm UTC

Reviewer: A reader

Hi Chris ,Can you please help me

To "A reader from India"

December 04, 2019 - 6:00 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

Hi - PMFJI, but afaik please look at it is NOT possible to use BINDs for identifiers in DDLs, and ALTER USER is a DDL command, so imho your cod will NOT fly : please look at https://asktom.oracle.com/pls/apex/asktom.search?tag=bind-variables-in-ddl-with-execute-immediate , http://www.orafaq.com/forum/t/150992/ , https://stackoverflow.com/questions/10315170/execute-immediate-alter-user-bind-variable and https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:588045400346317188 for examples/references...
So, in your procedure you MUST concat the strings for username and password, and NOT to bind them ... OF COURSE, to minimize SQL injection risks you WILL sanitize your inputs , maybe with DBMS_ASSERT and other techniques....

Regards,

Chiappa

P.S. :

1. of course, many languages/programming tools have the capacity for changing passwords, https://www.php.net/manual/en/function.oci-password-change.php show how to do it in php : check if the programming language/tool in use for your application have it , if yes you could write something OUTSIDE the database, inside yor app...

2. you always can call the OCI function OCIPasswordChange, maybe via EXTERNAL PROCEDURES : check My Oracle Support note "Using OCIPasswordChange() to Change Password" (Doc ID 99457.1) for an example...

Chris Saxon

Followup  

December 05, 2019 - 9:09 am UTC

Good links, thanks.

Reply

December 05, 2019 - 1:58 am UTC

Reviewer: A reader

I used the same way as you told but the getting error
ORA-00922: missing or invalid option


create or replace PROCEDURE accout_change_3 (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
    usr VARCHAR2(50);
    pwd VARCHAR2(50);
BEGIN
    sql_string := 'alter user dbms_assert.SCHEMA_NAME(:username)
 identified by dbms_assert.ENQUOTE_NAME(:password);';
   EXECUTE IMMEDIATE sql_string using P2_USERAME,P2_PASSWORD;
   

END

Chris Saxon

Followup  

December 05, 2019 - 9:08 am UTC

You need to concatenate the output of dbms_assert into your statement:

    sql_string := 'alter user ' || dbms_assert.SCHEMA_NAME(username) || 
' identified by ' || dbms_assert.ENQUOTE_LITERAL(password) || ';';

Reply

December 06, 2019 - 1:55 am UTC

Reviewer: A reader

Hi chris thanks,
When i used dbms_assert.ENQUOTE_literal it shows error as "missing or invalid passowrds"
so I used dbms_assert.ENQUOTE_name,now it shows error as invalid character

please find my code and execution

create or replace PROCEDURE accout_change_test (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS

sql_string VARCHAR2(500) ;
BEGIN
   sql_string := 'alter user ' || dbms_assert.SCHEMA_NAME(P2_USERAME) || 
      ' identified by ' || dbms_assert.ENQUOTE_name(P2_PASSWORD)||';' ;
dbms_output.put_line(sql_string);
EXECUTE IMMEDIATE sql_string using  P2_USERAME,P2_PASSWORD ;

END;

begin 
accout_change_test('TESTDB','TestDB1');
end;

Chris Saxon

Followup  

December 06, 2019 - 2:46 pm UTC

See the reply below.

You do not need the extra ";"

December 06, 2019 - 5:45 am UTC

Reviewer: A reader

You do not need the extra ";" when executing commands using Execute Immediate. 

Hence the SQL string should be:

 sql_string := 'alter user ' || dbms_assert.SCHEMA_NAME(P2_USERAME) || 
      ' identified by ' || dbms_assert.ENQUOTE_name(P2_PASSWORD);



Cheers!
Chris Saxon

Followup  

December 06, 2019 - 2:53 pm UTC

Yep, that should do the trick.

Reply

December 08, 2019 - 2:31 am UTC

Reviewer: A reader

Hi Chris,
Thank you so much .its working
Chris Saxon

Followup  

December 09, 2019 - 10:44 am UTC

Great :)