|
|
|
|
This function allows to check if a user/password combination is valid on a given server. This is usually not possible from within PL/SQL and Oracle does not offer any build-in mechanism or workaround to do so.
set echo on
set feedback on
/**********************************************************************************************************/
/* */
/* Procedure: CheckPassword */
/* Description: This function allows to check if a user/password combination is valid on a given server. */
/* This is usually not possible from within PL/SQL and Oracle does not offer any build-in */
/* mechanism or workaround to do so. */
/* */
/* The following parameter can be used: */
/* 1. theUsername: the name of the database user. */
/* 2. thePassword: the password of the user. */
/* 3. theServer: the server name. */
/* 4. theDebugFlag: if set to one debug messages are send using pipe messages in a pipe */
/* called 'plsql_debug'. Use the Meterial Dreams tool PlSqlTrace to */
/* retrieve the debug messages. */
/* */
/* Version: 1.0.0 */
/* */
/* Required: Oracle Server Version 7.3 or higher, this script should be executed as user SYS or SYSTEM */
/* or with full DBA rights granted. */
/* If you want to use the debugging features you should laso use the PL/SQL-Trace utility */
/* that can be found on the Material Dreams web site. */
/* */
/* Example: */
/* */
/* BEGIN */
/* DECLARE */
/* aRetCode INTEGER; */
/* aText VARCHAR2(8000); */
/* BEGIN */
/* aRetCode := CheckPassword('scott', 'tiger', 'TEST', 1); */
/* aText := 'CheckPassword returned [' || aRetCode || ']'; */
/* dbms_pipe.pack_message(LENGTH(aText)); */
/* dbms_pipe.pack_message(aText); */
/* aRetCode := dbms_pipe.send_message('plsql_debug', dbms_pipe.maxwait, 1024*1024); */
/* END; */
/* END; */
/* */
/* Written by: Material Dreams */
/* EMail: info@materialdreams.com */
/* WWW: http://www.materialdreams.com/oracle */
/* */
/* License: This script can be freely distributed as long as this header will not be removed and */
/* improvements and changes to this script will be reported to the author. */
/* */
/* Copyright (c) 1995-2004 by Material Dreams. All Rights Reserved. */
/* */
/**********************************************************************************************************/
CREATE OR REPLACE
FUNCTION CheckPassword (
theUsername IN VARCHAR2,
thePassword IN VARCHAR2,
theServer IN VARCHAR2,
theDebugFlag IN INTEGER DEFAULT 0
) RETURN NUMBER
IS
aLinkName VARCHAR2(10) := 'TestLink';
aText VARCHAR2(8000);
aCursor INTEGER;
aRetCode INTEGER;
BEGIN
aCursor := sys.dbms_sql.open_cursor;
BEGIN
aText := 'drop database link ' || aLinkName;
dbms_sql.parse(aCursor, aText, DBMS_SQL.NATIVE);
aRetCode := dbms_sql.execute(aCursor);
EXCEPTION
WHEN OTHERS THEN
IF (theDebugFlag > 0) THEN
aText := 'Unable to drop the database link ' || aLinkName || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
dbms_pipe.pack_message(LENGTH(aText));
dbms_pipe.pack_message(aText);
aRetCode := dbms_pipe.send_message('plsql_debug', dbms_pipe.maxwait, 1024*1024);
END IF;
dbms_sql.close_cursor(aCursor);
RETURN -3;
END;
BEGIN
aText := 'create database link ' || aLinkName ||
' connect to ' || theUsername ||
' identified by ' || thePassword ||
' using ''' || theServer || '''';
dbms_sql.parse(aCursor, aText, DBMS_SQL.NATIVE);
aRetCode := dbms_sql.execute(aCursor);
EXCEPTION
WHEN OTHERS THEN
IF (theDebugFlag > 0) THEN
aText := 'Unable to create the database link ' || aLinkName || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
dbms_pipe.pack_message(LENGTH(aText));
dbms_pipe.pack_message(aText);
aRetCode := dbms_pipe.send_message('plsql_debug', dbms_pipe.maxwait, 1024*1024);
END IF;
dbms_sql.close_cursor(aCursor);
RETURN -1;
END;
BEGIN
dbms_sql.parse(aCursor, 'SELECT 1 FROM dual@' || aLinkName, DBMS_SQL.NATIVE);
dbms_sql.define_column(aCursor, 1, aRetCode);
aRetCode := dbms_sql.execute(aCursor);
aRetCode := dbms_sql.execute_and_fetch(aCursor);
EXCEPTION
WHEN OTHERS THEN
IF (theDebugFlag > 0) THEN
aText := 'Unable to select from link ' || aLinkName || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
dbms_pipe.pack_message(LENGTH(aText));
dbms_pipe.pack_message(aText);
aRetCode := dbms_pipe.send_message('plsql_debug', dbms_pipe.maxwait, 1024*1024);
END IF;
dbms_sql.close_cursor(aCursor);
RETURN -2;
END;
BEGIN
aText := 'drop database link ' || aLinkName;
dbms_sql.parse(aCursor, aText, DBMS_SQL.NATIVE);
aRetCode := dbms_sql.execute(aCursor);
EXCEPTION
WHEN OTHERS THEN
IF (theDebugFlag > 0) THEN
aText := 'Unable to drop the database link ' || aLinkName || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
dbms_pipe.pack_message(LENGTH(aText));
dbms_pipe.pack_message(aText);
aRetCode := dbms_pipe.send_message('plsql_debug', dbms_pipe.maxwait, 1024*1024);
END IF;
dbms_sql.close_cursor(aCursor);
RETURN -3;
END;
dbms_sql.close_cursor(aCursor);
IF (theDebugFlag > 0) THEN
aText := 'Successfully selected from link TestLink';
dbms_pipe.pack_message(LENGTH(aText));
dbms_pipe.pack_message(aText);
aRetCode := dbms_pipe.send_message('plsql_debug', dbms_pipe.maxwait, 1024*1024);
END IF;
RETURN 1;
END CheckPassword;
/