force oracle database to allow write operation only by application hibernate connections (spring boo-CodePudding

I Want To Prevent Oracle Database User To Write Operation On Specific Column ( Only Hibernate Connections Allowed To Write On That column) In Other Words If You Want To Change Specific Column Only Spring Transactions Allowed To Do That Not Database User

update:
I Dont Want A Database User Allowed To Write On Column Because In My App Every Thing Should Work Under System Transactions

Exception : God Database User Is Ok :)

CodePudding user response：

Then you have to create another user for the Hibernate connection and grant access to that column.

That's how column level security works in Oracle:

grant update (col1, col2) on mytab to fred;

CodePudding user response：

Triggers can allow only specific programs to modify specific columns. But there are a lot of caveats in the below solution. You almost certainly want to use Simon's solution if you're trying to protect a large amount of secure data. But if you're only trying to enforce best practices on a small number of columns, this trigger solution should work.

First, you need to find some session metadata that can be used to uniquely identify Hibernate. I can't help you much with this part - the values probably depends on your configuration and settings. But there's likely a relevant value or two in the SYS_CONTEXT function that can uniquely identify the connections you care about.

For example, run queries like this to find relevant metadata:

SQL> select sys_context('userenv', 'client_program_name') program from dual;

PROGRAM
-----------
sqlplus.exe

But beware: some of those values can be imitated by client programs. For example, a user can create a JDBC program that sets it's "program" to "Hibernate". You will have to play around with different combinations of metadata to find something that is secure enough.

Below is the code to create a simple schema and a trigger that only allows one column to be updated by "plsqldev.exe" (my preferred IDE).

--drop table test_table;
create table test_table(anyone_can_modify number, only_hibernate_can_modify number);
insert into test_table values(1,1);
commit;

create or replace trigger protect_test_table_columns
before update of only_hibernate_can_modify on test_table
begin
    --Only allow Hibernate to update this column.
    --TODO: Add similar protections for INSERT and DELETE?
    --TODO: Figure out the real CLIENT_PROGRAM_NAME, or other metadata that identifies Hibernate.
    --Note: DBA users can ignore trigger errors and still change the values.
    if sys_context('userenv', 'client_program_name') = 'plsqldev.exe' then
        null;
    else
        raise_application_error(-20000, 'Only Hibernate can modify this column.');
    end if;
end;
/

I can update the columns fine in my IDE, but the updates fail when I use a different program:

SQL> update test_table set anyone_can_modify = 2;

1 row updated.

SQL> update test_table set only_hibernate_can_modify = 2;
update test_table set only_hibernate_can_modify = 2
       *
ERROR at line 1:
ORA-20000: Only Hibernate can modify this column.
ORA-06512: at "JHELLER.PROTECT_TEST_TABLE_COLUMNS", line 8
ORA-04088: error during execution of trigger
'JHELLER.PROTECT_TEST_TABLE_COLUMNS'