This discussion is archived
14 Replies Latest reply: Sep 14, 2013 8:17 PM by jgarry RSS

Is there a way to identify user accounts that need to be locked?

Jon L Newbie
Currently Being Moderated

Hi,

 

I am trying to write a script that will lock user accounts for employees that are being outprocessed (e.g. quit, fired, went to a different project).  The trouble I'm having is that the way I'm notified is by email from security that a person (first and last name provided in the email) is being outprocessed.  However, that individual may have multiple accounts and the account names don't always follow the same format like 'first initial last name'.  For example, I may have a user named John Doe with accounts like jdoe_sensor1, jdoe_sensor2, etc.  Then there could be a user Alice Smith with account like alice_s_sensor1, alice_s_sensor2, etc.  I know I can use OEM to lock users, but there are two main problems with that.  1 -- Finding the users, then clicking on each user and then locking them one by one.  And 2 -- I may not need to lock them right away.  For example, the email from security may say "Lock all accounts for FIRSTNAME LASTNAME at the end of the day on a certain date.  So I was hoping to write a script to identify the accounts, lock the user, and then verify they were locked and run it in cron, so the accounts get locked when they're supposed to.  An example of the SQL statements I'm thinking of are:

 

SELECT username, user_id, account_status FROM dba_users WHERE username like upper ('%$user%');

ALTER user $user ACCOUNT LOCK;

SELECT username, user_id, account_status FROM dba_users WHERE username like upper ('%$user%');

 

So basically, I need a way to find out what the possible combinations are for $user.  Is there a view besides dba_users which has more detailed information like first name and last name?  I'm thinking if there is, then I can query that and find out all the accounts that user has and then plug those into the lock script.    

 

Thanks!

 

Jon

  • 1. Re: Is there a way to identify user accounts that need to be locked?
    Eduardo Legatti Explorer
    Currently Being Moderated

    Hi,

     

    Is there a view besides dba_users which has more detailed information like first name and last name?

    No. I think you will need to create your own table and then combine the Oracle users with your table...

     

    eg:

     

    REAL_NAME SCHEMA_NAME
    ------------------- -----------------------------
    John Doe       jdoe_sensor1  
    John Doe       jdoe_sensor2

     

    Cheers

     

    Legatti

  • 2. Re: Is there a way to identify user accounts that need to be locked?
    Jon L Newbie
    Currently Being Moderated

    Hi Legatti,

     

    No. I think you will need to create your own table and then combine the Oracle users with your table...

     

     

    I was afraid of that.   Actually, it shouldn't be that bad.  I kind of figured there wasn't anything in the dictionary when I tried using OEM to create a user and saw it doesn't offer the chance to capture any of that additional information (e.g. first name, last name, phone number/extension).  Oh well.  Thanks for the reply!

     

    Jon

  • 3. Re: Is there a way to identify user accounts that need to be locked?
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    You can do something like this

    SQL> !cat a.sql

    SET VERIFY OFF

    SELECT username, user_id, account_status FROM dba_users WHERE username like upper ('%&&own%');

    ALTER user &&own ACCOUNT LOCK;

    SELECT username, user_id, account_status FROM dba_users WHERE username like upper ('%&&own%');

     

     

    SQL> @a.sql

    Enter value for own: ME

     

     

    USERNAME                          USER_ID ACCOUNT_STATUS

    ------------------------------ ---------- --------------------------------

    ME                                     40 OPEN

     

     

     

     

    User altered.

     

     

     

     

    USERNAME                          USER_ID ACCOUNT_STATUS

    ------------------------------ ---------- --------------------------------

    ME                                     40 LOCKED

     

    Hope this help

  • 4. Re: Is there a way to identify user accounts that need to be locked?
    Eduardo Legatti Explorer
    Currently Being Moderated

    Hi,

     

    first name, last name, phone number/extension

     

    Where exactly you enter this kind of information within OEM?

     

    Cheers

     

    Legatti

  • 5. Re: Is there a way to identify user accounts that need to be locked?
    sb92075 Guru
    Currently Being Moderated

    application must not be 3-tier

     

    post complete results from SQL below

     

    SELECT * FROM V$VERSION;

  • 6. Re: Is there a way to identify user accounts that need to be locked?
    EdStevens Guru
    Currently Being Moderated

    JonL wrote:

     

    Hi Legatti,

     

    No. I think you will need to create your own table and then combine the Oracle users with your table...

     

     

    I was afraid of that.   Actually, it shouldn't be that bad.  I kind of figured there wasn't anything in the dictionary when I tried using OEM to create a user and saw it doesn't offer the chance to capture any of that additional information (e.g. first name, last name, phone number/extension).  Oh well.  Thanks for the reply!

     

    Jon

    As far as oracle is concerned, a username is just an account authorized (or not) for various database operations.  It is of no concern to the database that the account is (or is not) assigned to a human being, and so it is not is not concerned with information related to that supposed human being.

     

    You could create a table with that information, and a procedure for creating (human) users that populates that table at the same time it creates the account in the database.  Then your table could include a column for the database account.

    Do you get so many people leaving that dropping them manually (once you determine the account name) is so onerous as to need an automated method?

     

    Hmm. I guess this custom user table could include a column for 'drop_date', default null.  Or maybe just a 'drop_at_cob  number(1) default 0).  Then when you get a request use the GUI app you will write and select to either 'drop now' or 'drop at end of day'.  If 'drop now', drop now.  If at cob, set the flag to 1, and have a daily job read the table and drop all the flagged accounts.

  • 7. Re: Is there a way to identify user accounts that need to be locked?
    Jon L Newbie
    Currently Being Moderated

    Hi Legatti,

     

    Sorry, I didn't mean I can enter that kind of info -- I cannot.  So, since I am unable to enter that kind of information when using OEM to create a user, it's not being stored anywhere.  So my assumption is that this information doesn't exist in the dictionary, and since OEM does NOT offer the user a chance to enter, it probably doesn't exist anywhere.  That is just my assumption though.  Your suggestion of creating a separate table and entering that info manually is what one of my colleagues at work suggested, too.  It's kind of a pain, but it's not that bad.

     

    Thanks!

     

    Jon

  • 8. Re: Is there a way to identify user accounts that need to be locked?
    Greg.Spall Expert
    Currently Being Moderated

    There is a very large problem with being given only a person's name and not their user ids.

     

    For example, if you have two people with same (or similar) name, then what?

     

    John Doe

    John J. Doe

     

    This seems to be very common, and even more so with some very common names:

     

    Smith

    Chin

    etc

     

    So even if you have a lookup table:

     

    NameUserid
    John Doejohndoe
    John Doejdoe
    John J. Doejohnd
    J. Doejdoe2
    John Djohn_d
    Jon Doejond
    Jim Doejidoe
    Johnny Doejonydoe

     

    Really, nowadays, with different policies, practices, etc, I've seen all manner of userids. When you're given somebody to "close down", you should really press them to provide userids, not just first name, last name.

     

    After all, if they tell you to lock all "John Doe's" accounts, how do you know that the id "johnd" isn't supposed to be locked? or even "jond" ??  You really have no idea. Did security mean "John J. Doe" and didn't provide his initial? What if they both happen to have J middle initial, but once's just registered with the company because the other one existed?

     

    My thought: If you're not given the specific userid(s), you're running a pretty good risk (at some point in time) that you will lock an id you shouldn't, or not lock an id you should.

  • 9. Re: Is there a way to identify user accounts that need to be locked?
    Jon L Newbie
    Currently Being Moderated

    Do you get so many people leaving that dropping them manually (once you determine the account name) is so onerous as to need an automated method?

     

    Hi Ed,

     

    Unfortunately, due to recent budget cuts, there has been a rash of outprocessing.  However, even if it was once every couple months, the problem is many of the users in my databases have upwards of a dozen schemas.  So really the problem is I'm lazy.  If I use OEM to lock a dozen accounts, then I have to go to the users page, find the user, click the radio button, click Lock User from the drop down and then click Go. If those users weren't on page 1, go to the page that shows them.  Repeat 11 times.  That is really tedious and annoying and ripe for automation.  Unfortunately, when you choose the "Multiple" type of selections on the Users page which turns the radio buttons into check boxes, the only option is to delete the user.  The other reason to automate is to put it in cron so I don't have to be present for the locking of the account to occur.

     

    I will create necessary table and back populate with the needed info so I can do this.  Again, I'm lazy, was just hoping I could avoid doing this if the info already existed in the dictionary.

     

    Thanks!

     

    Jon

  • 10. Re: Is there a way to identify user accounts that need to be locked?
    Jon L Newbie
    Currently Being Moderated

    After all, if they tell you to lock all "John Doe's" accounts, how do you know that the id "johnd" isn't supposed to be locked? or even "jond" ??  You really have no idea. Did security mean "John J. Doe" and didn't provide his initial? What if they both happen to have J middle initial, but once's just registered with the company because the other one existed?

     

    My thought: If you're not given the specific userid(s), you're running a pretty good risk (at some point in time) that you will lock an id you shouldn't, or not lock an id you should.

     

     

    That's an excellent point.  I'm not ready to implement any of this yet, just trying to do some research and see what information is available that can be used.

     

    Thanks!

     

    Jon

  • 11. Re: Is there a way to identify user accounts that need to be locked?
    EdStevens Guru
    Currently Being Moderated

    JonL wrote:

     

    Do you get so many people leaving that dropping them manually (once you determine the account name) is so onerous as to need an automated method?

     

    Hi Ed,

     

    Unfortunately, due to recent budget cuts, there has been a rash of outprocessing.  However, even if it was once every couple months, the problem is many of the users in my databases have upwards of a dozen schemas.  So really the problem is I'm lazy.  If I use OEM to lock a dozen accounts, then I have to go to the users page, find the user, click the radio button, click Lock User from the drop down and then click Go. If those users weren't on page 1, go to the page that shows them.  Repeat 11 times.  That is really tedious and annoying and ripe for automation.  Unfortunately, when you choose the "Multiple" type of selections on the Users page which turns the radio buttons into check boxes, the only option is to delete the user.  The other reason to automate is to put it in cron so I don't have to be present for the locking of the account to occur.

     

    I will create necessary table and back populate with the needed info so I can do this.  Again, I'm lazy, was just hoping I could avoid doing this if the info already existed in the dictionary.

     

    Thanks!

     

    Jon

     

    Nothing wrong with lazy (in certain contexts!) .  That's what drives creation of better tools.

     

    I am a bit bothered by your statement "many of the users in my databases have upwards of a dozen schemas".  In oracle, a 'user' is synonymous with 'schema'.  So what you are saying is that a given human employee may have multiple user accounts in the database.  That in itself sounds to me like a system that is out of control.  Why would a person need multiple database accounts?   Something is seriously amiss here.  May be political.  May be technical.  May be both.

  • 12. Re: Is there a way to identify user accounts that need to be locked?
    Jon L Newbie
    Currently Being Moderated

    Nothing wrong with lazy (in certain contexts!) .  That's what drives creation of better tools.

     

    Indeed - laziness breeds ingenuity! 

     

     

    I am a bit bothered by your statement "many of the users in my databases have upwards of a dozen schemas".  In oracle, a 'user' is synonymous with 'schema'.  So what you are saying is that a given human employee may have multiple user accounts in the database.  That in itself sounds to me like a system that is out of control.  Why would a person need multiple database accounts?   Something is seriously amiss here.  May be political.  May be technical.  May be both.

     

    Understandable -- our PROD systems (non-human accounts) have multiple schemas (was a design choice years ago).  However, we have human developers that need to develop this system.  Each developer's environment in DEV is laid out the same as the "system's".  Otherwise, "normal" human users like an operator trying to login and run a report or something only have one account.  Bottomline - there is a method to the madness. 

     

    Thanks!

     

    Jon

  • 13. Re: Is there a way to identify user accounts that need to be locked?
    Greg.Spall Expert
    Currently Being Moderated

    JonL wrote:

     

    Indeed - laziness breeds ingenuity! 

     

     

    I always thought it was the other way around

  • 14. Re: Is there a way to identify user accounts that need to be locked?
    jgarry Guru
    Currently Being Moderated

    You didn't mention the OS(s) this is on, but perhaps there is some sysadmin or netadmin who has already had to deal with this problem, and you could coordinate with them to have this extra information in one place.  Or do you have a modern HR system you could leach onto?  This is a matter of coordination among many groups.  I know I find it very helpful to know things like computer names and IP's and phone numbers when Bill has Fred run Sally's program incorrectly, munching a whole cpu for hours.

     

    I've seen people leave, then notice they are still running stuff.  Frightening, but so far has always been just some project they completed and is perfectly innocent.  File it under "bad release management," but still something to take into account before locking and having people confused as to why their program stops working.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points