| System Setup and Security [message #185866] |
Thu, 03 August 2006 21:27  |
droopa
Messages: 1
Registered: August 2006
Location: Australia
|
Junior Member |
|
|
Hi All,
Have complex issue trying to solve. For certification process need to ensure that no one outside system (application process) can alter a table.
So have process on windows or solaris that runs as a user. That user connects to DB via SSO. Revoke std login of that user to DB server machine.
Then need to prevent access via Oracle client somehow from other machines? and also prevent DBA from accessing or altering data - so assume only have one user account in DB and that is this user?
This this will make backups hard I know.... Has anyone done this sort of thing before?
|
|
|
|
| Re: System Setup and Security [message #186132 is a reply to message #185866] |
Sun, 06 August 2006 06:42  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Do not create a public synonym for that table. Do not grant select, insert, update, delete for yourinstance.table to any user except the one user you want to see the table.
If you login as the table owner then you will be able to see the table. Then no one else will be able to see the table.
Perhaps you are looking for VPD? (Virtual Private Database). I *think* everything is seen via a view on the table and you leave out the stuff you don't want public.
You can also put triggers on the table to not allow particular changes. It would rollback rather than commit any changes. A trigger fired before commit that issued a rollback?
You can have a table trigger see what application is running and on what O/S to determine what to do with changes. Check the 'create trigger' documentation. (ie create or replace trigger )
http://members.tripod.com/mdameryk/CreateOrclTrgr.htm
Backups do not nessarally need to be complex in this case. Just make sure your backups can see that table.
Here is an example that I use, check the documentation to see what items are available:
Create or replace trigger AUDIT_NAME_TABLE_DI
after DELETE OR INSERT on name_table
for each row
DECLARE
TIME_NOW DATE;
TERMINAL CHAR(10);
APPLIC_DATE DATE;
begin
/* this trigger is fired UPON NAME_TABLE DELETES OR INSERTS */
TIME_NOW := SYSDATE;
TERMINAL := USERENV('TERMINAL');
IF INSERTING THEN
SELECT APPLICATION_DATE INTO APPLIC_DATE FROM STUDENT
WHERE PERSON_ID= :NEW.ENTITY_ID;
IF TRUNC(TIME_NOW) <> TRUNC(APPLIC_DATE) THEN
insert into AUDIT_name_table values
(TIME_NOW, user, TERMINAL, 'I',
:NEW.entity_id,
' ',' ', ' ',
:NEW.name_type, :NEW.last_name, :NEW.first_name);
END IF;
ELSIF DELETING THEN
insert into AUDIT_name_table values
(TIME_NOW, user, TERMINAL, 'D',
:old.entity_id,
:OLD.name_type,:OLD.last_name, :OLD.first_name,
' ', ' ', ' ');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
end;
Create or replace trigger AUDIT_NAME_TABLE_U
after update
of last_name, first_name, name_type on name_table
for each row
DECLARE
TIME_NOW DATE;
TERMINAL CHAR(10);
begin
/* this trigger is fired upon NAME_TABLE UPDATES */
TIME_NOW := SYSDATE;
TERMINAL := USERENV('TERMINAL');
IF UPDATING THEN
IF (:OLD.NAME_TYPE <> :NEW.NAME_TYPE)
OR (:OLD.LAST_NAME <> :NEW.LAST_NAME)
OR (:OLD.FIRST_NAME <> :NEW.FIRST_NAME)
THEN
insert into AUDIT_name_table values
(TIME_NOW, user(this is the user), TERMINAL(this is the o/s), 'U', :NEW.entity_id,
:OLD.name_type, :OLD.last_name, :OLD.first_name,
:NEW.name_type, :NEW.last_name, :NEW.first_name);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
end;
|
|
|
|
| Re: System Setup and Security [message #205793 is a reply to message #185866] |
Mon, 27 November 2006 10:16 |
bwetkstr
Messages: 114
Registered: August 2005
|
Senior Member |
|
|
| droopa wrote on Fri, 04 August 2006 04:27 | Hi All,
Have complex issue trying to solve. For certification process need to ensure that no one outside system (application process) can alter a table.
So have process on windows or solaris that runs as a user. That user connects to DB via SSO. Revoke std login of that user to DB server machine.
Then need to prevent access via Oracle client somehow from other machines? and also prevent DBA from accessing or altering data - so assume only have one user account in DB and that is this user?
This this will make backups hard I know.... Has anyone done this sort of thing before?
|
Isn't external authentication a solution?
[Updated on: Mon, 27 November 2006 10:16] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
