On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,632 Users
  • 2,269,769 Discussions
  • 7,916,800 Comments

Discussions

Password reset with session

user12840908
user12840908 Member Posts: 35
edited Sep 23, 2017 4:50PM in Database Ideas - Ideas

Hi,

One of the tasks of production DBA is to guide developers with read-only access during the incident period to view some application data.

This requires DBA to share readonly user password with application team and then resetting it after few hours (generally automated) based on your organization policies.

As we all know changing the Oracle schema password does not terminate existing connections. This process is always followed by executing the procedure to kill all session for the user.

Ofcourse there are situations where we may not want sessions to be killed as they may from from application layer. So this is very much an optional step.

Can we not have a command which provides a feature to terminate existing sessions? It can easily be done by DBA’s but will be great if we have some command like:

ALTER USER USERNAME IDENTIFIED BY STRONGPASSWORD TERMINATE SESSION;

Oracle can consider this option as added security feature!

Regards!

user12840908
4 votes

Active · Last Updated

Comments

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    This seems to me like an idea for your specific shop. In all the shops I've worked in, this hasn't been a problem I've needed solving.

  • This seems to me like an idea for your specific shop. In all the shops I've worked in, this hasn't been a problem I've needed solving.

    Hey thanks for your reply and inputs.

    Firstly this is not a problem, it is just a way to combine multiple steps into one.

    So if you could please tell what happens in your shop?

    If i understand it could be only one for following things:

    • Permenently give readonly acces to developers in production - not allowed in our shop
    • After resetting password let continue with any connected session - not allowed in our shop
    • After resetting password kill the sessions

    There are options to do via proxy grant, but  again that would require termination.

    Regards!

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    Hey thanks for your reply and inputs.

    Firstly this is not a problem, it is just a way to combine multiple steps into one.

    So if you could please tell what happens in your shop?

    If i understand it could be only one for following things:

    • Permenently give readonly acces to developers in production - not allowed in our shop
    • After resetting password let continue with any connected session - not allowed in our shop
    • After resetting password kill the sessions

    There are options to do via proxy grant, but  again that would require termination.

    Regards!

    In my shop, we do not give developers read-only access to things in production, definitely not with a shared account. All users of our production database have individual accounts. If a dev needs access to a table or two in production for researching a bug, we grant their individual account SELECT privileges on the table(s) they need. Then revoke when they are done with their task.

    Shared accounts in production environments are a bad thing and are typically frowned up by the auditors.

  • In my shop, we do not give developers read-only access to things in production, definitely not with a shared account. All users of our production database have individual accounts. If a dev needs access to a table or two in production for researching a bug, we grant their individual account SELECT privileges on the table(s) they need. Then revoke when they are done with their task.

    Shared accounts in production environments are a bad thing and are typically frowned up by the auditors.

    Thanks for your reply.

    Yes, I completely agree with your approach and I too have been into these environments. But in my current organization (it's a large org and is among top 10 customers of Oracle, can't name it due to obvious reason) we have a separate team working globally from multiple regions, each and every group can have 100's of DBA's , APP DBA's etc. Now they have a couple of accounts for separate teams, and an in-house built application where anyone requiring password goes and request for it. Post approval user can see the password from the system and it's valid for a couple of hours.So here application takes care of who accessed/retrieved password at what time.

    But unfortunately, it doesn't kill session once the password is reset (may be they trust DBA's integrity). So for our systems, we end up going and killing the same.

    So it was just a thought, that's what this forum is about :-)

    Thanks again! Nice discussing with you.

    Regards!