To other users, How to hide the Oracle object owner of a table

user_9793369 0
Viewed 152

There is an original "A" table owned by DBA account "X" , And provides a triggered temporary "B" table that is inserted into the "A" table to the third party account "Y". Because you can not give "Y" the DML privilege of table "A" When the owner of the table of "B" is created as "X" How can the owner of "B" hide "X" from "Y"?

If I can not hide, The owner of the "A" table is "X", The owner of the "B" table is "Y", Without giving the DML privilege of the "A" table to "Y", Give "Y" only the DML privilege of the "B" table,   I want to create an Insert trigger on the "B" table. Insert "A" into the table. That is, in any case "Y" should not know the name of "X". Is it possible?

database-administration oracle11g oracle10g permissions oracle
1 Answers

Create a trigger ON Y.B, but have the trigger owned by X. That way only the trigger can perform the INSERT and the user Y has no other access to the other table.

This code creates the trigger:

create or replace trigger x.b_trg
after insert on y.b
for each row
begin
    insert into x.a values(:new.some_column);
end;
/

Below is an example of the user Y being able to insert into Y.B (which then gets copied into X.A), but cannot directly insert into X.A.

SQL> insert into y.b values(1);

1 row created.

SQL> insert into x.a values(2);
insert into x.a values(2)
              *
ERROR at line 1:
ORA-00942: table or view does not exist

The above solution prevents access but doesn't completely "hide" it. Hiding code is difficult in Oracle. We can obfuscate code, but that obfuscation is easily reversed with online utilities. And there are other ways to figure out the dependencies using the data dictionary.

user_4091720
Related