Open database table let the third party operate the solution directly

Posted by PcGeniusProductions on Sun, 03 May 2020 02:08:49 +0200

In the project, a third-party company pushes the files to our file server and records the logs at the same time. This log, of course, is also saved into our database.

The most natural idea of this log implementation is to tune the interface. We provide the interface, which is called by the third party. But we all feel troublesome. The owner decided to let the third party write the database directly.

This involves issues such as authority. The authority shall be able to:

1. It can only be inserted, not modified or deleted
2. For convenience and safety, only some fields can be inserted
3. For the third party, the fields that can be inserted are visible, otherwise transparent

The ideas are as follows:

1. Set up the view, including only some fields, and let the third party operate the view
2. For uninserted fields, such as primary key ID and time stamp, the trigger is used to automatically complete them
3. Establish a special database account for the third party, assign the minimum necessary permissions, and use aliases, mask table space and other details

The details are as follows:

1. Create third party tablespaces and users

CREATE USER pub identified by pub;--No need to create a default tablespace
grant connect to pub;

2. Create triggers and views under tablespace work

--Log table
   (    "ID" VARCHAR2(64) NOT NULL ENABLE, --Primary key
    "F1" VARCHAR2(50), 
    "F2" VARCHAR2(50), 
    "F3" VARCHAR2(50), 
    "FILENAME" VARCHAR2(100), 
    "SUFFIX" VARCHAR2(10), 
    "FILESIZE" NUMBER(9,0), 
    "CREATEDATE" DATE, --time stamp
    "NOTE" VARCHAR2(200), 

--Set up trigger
create or replace trigger trg_pub_acceptlog
  before insert on acceptlog
  for each row
  -- local variables here
  select sys_guid() into :NEW.ID from dual;--Fill in automatically ID
  select sysdate into :NEW.createdate from dual;--Auto time stamp
end trg_pub_acceptlog;

create or replace view v_acceptlog as
  select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,filename,suffix,filesize,senddate from work.acceptlog;

grant select on work.v_acceptlog to pub;
grant insert on work.v_acceptlog to pub;
--Create alias permission
grant create synonym  to pub;

3. Log in with pub

--Create an alias to trick third parties into thinking it's a physical table, not a view
create or replace synonym acceptlog for work.v_acceptlog;

--such,Third party companies can use it directly acceptlog Data insertion
insert into acceptlog(f1,senddate) values('source',sysdate);
select * from acceptlog;

Topics: Database