This discussion is archived
9 Replies Latest reply: Apr 25, 2013 1:11 PM by rp0428 RSS

Make session read only depending on ip

User477708-OC Journeyer
Currently Being Moderated
how to make a session read only via logon trigger. We have a development environment that "may" logon to production by accident in a program, its not ideal but we have to leave connection open from clients as they infrequently do need to access production so instead Im thinking make a session readonly if a session comes in from that development subnet from that program.

create logon trigger blah
if ( sys_context('userenv','ip_address') = 'IP ADDRESS OF BAD HOST' ) and program='program I want to make read-only conenctions.exe"
then
"set session read only which I think has to be done by issuing set transaction read only but if anyone knows a better way please suggest"
end if;
  • 1. Re: Make session read only depending on ip
    JohnWatson Guru
    Currently Being Moderated
    Create a role with SELECT privileges only.
    Grant the role to the user, and then ALTER USER .... DEFAULT ROLE ALL EXCEPT <the_select_role> ;
    In your trigger, EXECUTE IMMEDIATE 'SET ROLE <the_select_role' ;

    That should do it.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
  • 2. Re: Make session read only depending on ip
    sybrand_b Guru
    Currently Being Moderated
    IP addresses can be spoofed.
    Executable names can be changed.

    You don't mention a version, but in sqlnet.ora on the server you can configured tcp.invited_nodes or tcp.excluded_nodes.
    Also Connection Manager can do this.
    You can configure ranges of IP-addresses there.

    Your approach is doomed to fail. Miserably!

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: Make session read only depending on ip
    User477708-OC Journeyer
    Currently Being Moderated
    sybrand_b wrote:
    IP addresses can be spoofed.
    Executable names can be changed.
    Yes and yes but not to go into details, its not a concern here.
    You don't mention a version, but in sqlnet.ora on the server you can configured tcp.invited_nodes or tcp.excluded_nodes.
    Also Connection Manager can do this.
    You can configure ranges of IP-addresses there.

    Your approach is doomed to fail. Miserably!
    You make it sound so...final. :)

    I do know where youre coming from and fully accept point, in theory, developers should not have access to production however in reality on this site, Read only access quite frequently and sometimes urgent write access required to fix a production issue which has financial implications. I dont want to be playing with tcp exclusion nodes/iptables/firewall rules or whatever blocking mechanism in this case.
  • 4. Re: Make session read only depending on ip
    sybrand_b Guru
    Currently Being Moderated
     Read only access quite frequently and sometimes urgent write access required to fix a production issue which has financial implications.
    Seems your organisation (which I hope is not a bank) has the door wide open to numerous malversations.
    The best thing you can do is to resign, assuming you have any integrity, or have them waive your responsibility for any debacle.
    However, when the debacle happens, they will shoot the DBA.
    Think about it!

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: Make session read only depending on ip
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    961469 wrote:

    I do know where youre coming from and fully accept point, in theory, developers should not have access to production however in reality on this site, Read only access quite frequently and sometimes urgent write access required to fix a production issue which has financial implications. I dont want to be playing with tcp exclusion nodes/iptables/firewall rules or whatever blocking mechanism in this case.
    IP based security in an application layer (especially when you want to protect financial data), is flawed. And IMO, fatally so.

    The correct place for IP based security is something like iptables - and not a hack in application code to apply some security condition.
  • 6. Re: Make session read only depending on ip
    User477708-OC Journeyer
    Currently Being Moderated
    Billy  Verreynne  wrote:
    961469 wrote:

    I do know where youre coming from and fully accept point, in theory, developers should not have access to production however in reality on this site, Read only access quite frequently and sometimes urgent write access required to fix a production issue which has financial implications. I dont want to be playing with tcp exclusion nodes/iptables/firewall rules or whatever blocking mechanism in this case.
    IP based security in an application layer (especially when you want to protect financial data), is flawed. And IMO, fatally so.

    The correct place for IP based security is something like iptables - and not a hack in application code to apply some security condition.
    Im not disagreeing with this principle of securing the database, I'll repeat though, some of the developers at times need access to production to resolve issue (financial related) so we cannot cut off connection completely with an ip route or firewall change. Its very infrequent but when RW access is needed, its needed fast.

    The principle has been thrashed out by several very competent DBAs , managers and dev leads. we all understand this is not the ideal way to go, I cant go into specifics its just the way it has to be, management have signed off on it after I spelled out risks so I have a pass if anything bad happens.
  • 7. Re: Make session read only depending on ip
    EdStevens Guru
    Currently Being Moderated
    961469 wrote:
    Billy  Verreynne  wrote:
    961469 wrote:

    I do know where youre coming from and fully accept point, in theory, developers should not have access to production however in reality on this site, Read only access quite frequently and sometimes urgent write access required to fix a production issue which has financial implications. I dont want to be playing with tcp exclusion nodes/iptables/firewall rules or whatever blocking mechanism in this case.
    IP based security in an application layer (especially when you want to protect financial data), is flawed. And IMO, fatally so.

    The correct place for IP based security is something like iptables - and not a hack in application code to apply some security condition.
    Im not disagreeing with this principle of securing the database, I'll repeat though, some of the developers at times need access to production to resolve issue (financial related) so we cannot cut off connection completely with an ip route or firewall change. Its very infrequent but when RW access is needed, its needed fast.

    The principle has been thrashed out by several very competent DBAs , managers and dev leads. we all understand this is not the ideal way to go, I cant go into specifics its just the way it has to be, management have signed off on it after I spelled out risks so I have a pass if anything bad happens.
    Why not just grant them the necessary additional priv on an 'as needed' basis, in response to a documented request and for a finite period of time? Sure, this will require a little intervention and involvement by the DBA, but so what? If something like that gets to be a 'full time job' for a dba, then you've got bigger issues that need to be addressed.
  • 8. Re: Make session read only depending on ip
    Alex Fatkulin Explorer
    Currently Being Moderated
    1. Set transaction read only will absolutely not work because it's a setting for the current transaction, all it takes is a commit/rollback and the setting is gone.

    2. Set role (for a regular role) will not work too because what stops me from connecting and enabling any role that was granted to me? You will likely be looking into secure application roles which can be enabled by the authorized package. That package can be called by the on-logon trigger and enable roles based on the session information you want (bearing in mind all the shortcoming others have identified). Obviously the package should be protected from modifications. Of course that also means that you can't be connected as table(s) owner.

    3. Another option will be looking into VPD and have a policy for insert/update/delete which will rely on the information you want. That way you don't care whether you're connected as an owner or not.
  • 9. Re: Make session read only depending on ip
    rp0428 Guru
    Currently Being Moderated
    >
    I'll repeat though, some of the developers at times need access to production to resolve issue
    >
    Now you are changing your story. In the beginning you said this:
    >
    We have a development environment that "may" logon to production by accident in a program
    >
    'by accident'? Give us a break. If you have code that can connect to production 'by accident' then the developers that wrote that code, and the team that did the code review, should have a serious talking to about what will happen if they do that again.
    >
    The principle has been thrashed out by several very competent DBAs , managers and dev leads. we all understand this is not the ideal way to go, I cant go into specifics its just the way it has to be, management have signed off on it after I spelled out risks so I have a pass if anything bad happens.
    >
    Here's a principle that has been thrashed out by some of the best minds in the business: Access should be based on a demontrated NEED TO KNOW.

    You don't provide ANY access to production or sensitive data without a need to know.

    Your developers DO NOT need read only access via a role, or any other means, every time they connect.

    The proper way to implement your business requirement is to create that read-only role and a read-only user with a password only known to the DBAs or management.

    When a developer needs access (documented in some fashion - even email) the DBA alters that user to change the password and provides the username and password to the developer. From that time forward that developer is responsible for that account.

    After the developers work is complete they notify the DBA and the DBA alters the user to change the password the developers do not know. That secures the account.

    Problem solved - developers have access to an account that can access production when they need to and do NOT have access to that account when they don't need it.

    You need to implement a solution that prevents those 'accidents' from happening.

Legend

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