Password

[Friend33]

Hi Tharg

Thanks for letting me know the answers to my silly questions.

Have 3 short questions. Hope you would answer them::

1. How can we have a password generated by the system ?
(Not manually)

2. Can you provide some inputs regarding 'Password
History' built-in functionality ?

3. And, how can we have a script to check for user
expiration after 45 days ?

Please do answer them when you have free time.

Thank You,

Ravi

 

[Thargy]

Ravi,

as before, I am working on the assumption that you are doing your own security. Hence the following:-

/******************************************************************************
******************************************************************************/
   FUNCTION new_password (p_username IN AUTHENTICATION_INFO.user_name%TYPE)
      RETURN VARCHAR2
--Based on the user's name, generate a password and make it valid for the user
   IS
      only_alpha_characters   CONSTANT VARCHAR2 (1) := 'A';
   BEGIN
      --Provide hard-coded password for test purposes
      IF c_test_mode THEN
         RETURN c_test_mode_password;
      ELSE
         --Get a random alphanumeric string 10 characters long
         DBMS_RANDOM.SEED (TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
         RETURN DBMS_RANDOM.STRING (only_alpha_characters, 10);
      --N.B. no error handling, so any errors propagate out, and calling function
      --fails safe.
      END IF;
   END new_password;

/******************************************************************************
******************************************************************************/

The above will generate a pseudo-random string ten characters long. Note that I have built into it a test_mode, which allows developers and testers to bypass the security when needed. This should obviously NOT be available to anyone other than authorised persons.

To check on the users password history and date of expiration you have no choice but to store a timestamp, showing when the password was created. Every time a user logs on, check that you are not >45 days after the timestamp.

With regard to history, you also have no choice but to store old passwords for comparison. However, this is no great hardship, as at 45 day per password, you'll need less than 8 per annum. This is a triflingly small amount of data to store, so it shouldn't be a problem.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Ravi,

Welcome to Tek-Tips.com! We're glad to have you here.

I'm responding here with a few thoughts, in this thread since it is your latest of six threads regarding the topic of "Passwords" since your joining us ten days ago. (Please don't take offence at my observations, since I intend no offence. I just want you to feel comfortable here and to have a long, happy, and mutually rewarding relationship amongst all of us colleagues here on Tek-Tips.com.)

I've noticed that our friend, Tharg, has spent, what must have been, many hours so far, by offering excellent methods and code by which you can accomplish your objectives. Please keep in mind that all of us here are volunteers...you don't pay money for the help/postings...our currency here is Purple Stars, which you have kindly awarded to Tharg on a couple of threads so far. I'm sure he appreciates your awarding him stars.

Also, the intent of this site is to (generally) provide specific answers to specific technical questions, rather than to offer free consulting and coding services.

I'm also aware that Tharg has a day job. I believe Tharg can also provide after-hours consulting services on many Oracle-related topics, for a reasonable consulting fee. At the point at which he has been providing virtually exclusive consulting services to you, consuming a great deal of time, via your Tek-Tips posts, I believe that his level of effort in your behalf has just about exceeded "comfort level". Therefore, if you would like to pursue a consulting arrangement with Tharg in your behalf, (if Tharg concurs), I can separately provide you with contact methods to reach Tharg to discuss a consulting arrangement with him.

If your job depends on your becoming an expert on password-related issues (as it appears it does), and your needs on this topic extend further, I suggest/propose that you either:

1) contact Tharg for direct (remunerated) assistance,
2) refer to Google sources for documentation and examples,
3) peruse Oracle's documentation on this topic, or
4) take either on on-line or instructor-led class that deals with this topic.

Very best holiday wishes and regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.