This discussion is archived
6 Replies Latest reply: Apr 1, 2013 7:36 AM by rp0428 RSS

Can I block all table changes?

999970 Newbie
Currently Being Moderated
At work I have several "Connections" with one being very sensitive, and I want to find a way to keep it locked down. I do need to make changes to the tables there once in a great while, but 99% of the time it's just for viewing. Is there a way to block all changes to all tables in this connection? I'm looking for an easy to way to maybe lock, then unlock it, when I need to just to keep it safe.
  • 1. Re: Can I block all table changes?
    Irian Pro
    Currently Being Moderated
    This kind of check should not be done using SQLDeveloper or any other database access tool, it should be managed at the database level by permissions.

    If I were in you I'd create a read only user with select only rights for the schema you want to protect, and use that for the SQLDeveloper connection, when the need arises to actually make some changes you can change the user name on the fly, make your changes and then revert to the read only user (this works better if you do not save the password for your connection).

    This obviously assumes you have the right to create users on your database, if this is not the case then you should contact the DBA and he will be able to help you.
  • 2. Re: Can I block all table changes?
    999970 Newbie
    Currently Being Moderated
    Thanks. I'll let him know. :)
  • 3. Re: Can I block all table changes?
    999970 Newbie
    Currently Being Moderated
    Thanks. I'll let him know. :)
  • 4. Re: Can I block all table changes?
    rp0428 Guru
    Currently Being Moderated
    >
    At work I have several "Connections" with one being very sensitive, and I want to find a way to keep it locked down. I do need to make changes to the tables there once in a great while, but 99% of the time it's just for viewing. Is there a way to block all changes to all tables in this connection? I'm looking for an easy to way to maybe lock, then unlock it, when I need to just to keep it safe.
    >
    What database? Your options depend on the database you are using. Since you didn't say I'll just assume it's Oracle.

    "Connections" do not have privileges. Users (schemas) have privileges. And they ONLY have the privileges that have been granted to them.

    So one solution is DON'T grant privileges to that user that you don't want them to have. Then grant them those privileges when they need them; revoke them when they don't need them.

    The easiest way to implement solution #1 is to create two roles for that user. The first role (perhaps named 'SCOTT_READ_ONLY_ROLE') only has SELECT privileges to the tables. The second role (SCOTT_ALL_PRIVILEGES_ROLE) has a different set of privileges, perhaps ALL privileges to the tables.

    When you want the user to only have READ privileges to the SCOTT tables grant the user the 'SCOTT_READ_ONLY_ROLE' and revoke any other roles. Then when you need to revoke the read only role and grant them the SCOTT_ALL_PRIVILEGES_ROLE.

    The second, and better solution is to use two different users. The first user only has SELECT privileges granted via the SCOTT_READ_ONLY_ROLE. The second user has all privileges granted via the SCOTT_ALL_PRIVILEGES_ROLE.

    Don't tell anyone the second password. When someone needs to use the second user change the password to something new, tell them the password and let them use the account. When they are done change the password back to the original or any other password that no one knows.
  • 5. Re: Can I block all table changes?
    999970 Newbie
    Currently Being Moderated
    Thanks for that detailed suggestion! It's very well appreciated.
  • 6. Re: Can I block all table changes?
    rp0428 Guru
    Currently Being Moderated
    Please mark your questions ANSWERED once they have been.

Legend

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