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
CREATE TABLE "WORK"."ACCEPTLOG"
( "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),
"SENDDATE" DATE NOT NULL ENABLE,
"CREATEDATE" DATE, --time stamp
"NOTE" VARCHAR2(200),
CONSTRAINT "PK_ACCEPTLOG" PRIMARY KEY ("ID"))
--Set up trigger
create or replace trigger trg_pub_acceptlog
before insert on acceptlog
for each row
declare
-- local variables here
begin
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;
--view
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;
--Jurisdiction
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;