在plsql中检查用户密码是否有效 [英] Check user's password is valid or not in plsql

问题描述

我需要检查数据库用户是否有效.

I need to check if a database user is valid.

我将允许用户使用 ALTER 命令更改密码，但是在我这样做之前，我想验证其当前密码，以确保他们是他们所说的真实身份.是否可以在pl/sql内部获取用户名/密码组合并检查其是否有效?

I will allow users to change their passwords using the ALTER command, but before I do I want to validate their current password to make sure they are the who they say they are. Is it possible inside of pl/sql to take the user/password combination and check if it is valid or not?

推荐答案

我认为Oracle数据库没有为您提供执行此操作的API.您可能要使用的一种方法是使用回送数据库链接进行检查.像这样:

I don't think Oracle database gives you an API for doing this. One kludge you could use would be to use a loopback database link to check it. Like this:

DECLARE
  p_username        VARCHAR2 (30) := 'VXSMIMMCP';  -- Change to the user whose password you are validating
  p_test_password   VARCHAR2 (30) := 'NOT_MY_PASSWORD';  
  p_loopback_connection_string VARCHAR2(80) := 'STRQA2';  -- Change this for your environment
BEGIN

  BEGIN
    EXECUTE IMMEDIATE q'[drop database link password_test_loopback]';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  EXECUTE IMMEDIATE
    'create database link password_test_loopback connect to "' || p_username || '" identified by "' || p_test_password || '" using ''' || p_loopback_connection_string || '''';

  EXECUTE IMMEDIATE q'[SELECT * FROM dual@password_test_loopback]';

  EXECUTE IMMEDIATE q'[drop database link password_test_loopback]';

  dbms_output.put_line('Password is good');
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE = -1017 THEN
    DBMS_OUTPUT.PUT_LINE('Password is wrong');
  ELSE
    raise;
  END IF;
END;

更多高级选项是找到一种使用单点登录/LDAP进行Oracle身份验证的方法.然后，您的问题就变成了-我该如何在LDAP字典中验证用户名/密码，这要容易得多.

More advanced options would be to find a way to use single signon / LDAP for your Oracle authentication. Then, your problem becomes -- how do I validate a username/password in my LDAP dictionary, which is much easier.

这篇关于在plsql中检查用户密码是否有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！