Skip to Main Content
  • 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

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 1000+ times

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

  (10 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 :)

well first thing from Tom

January 16, 2020 - 12:53 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Well, the first thing from Tom for this question - would be highlighting his "i hate your code" url like this.

EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END; 


I hate your code:

https://www.google.com/search?q=site%3Aasktom.oracle.com+"i+hate+your+code"

https://asktom.oracle.com/pls/apex/asktom.search?tag=is-there-an-easy-way-to-make-a-ctas#1535781400346575552

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database