Dear All,
I have what I hope is a simple problem, and would appreciate any advice on how I could resolve it.
I have raised an SR with Oracle and searched forums and the web. The answer may be so simple that no one
bothers to write about it. We have an Oracle cloud Database and associated Cloud compute server running Forms and WLS
12 (exact editions below), and one reason for this is so we can offer a single database for a set of external partner groups, and home-working specialists.
The question I have is this. How can I block/rollback any update, delete or Insert DML attempt, if it does not come from one of our Forms 12c
sessions? I do want users to carry out such DML in the forms themselves and assign object privileges to roles, and grant those roles to my users to allow
that. However I, do not want to allow any global DML statements run on the tables, should someone attempt to do so from an SQL Command prompt.
Since we make use of local TNSNAMES for running data query exports to Excel, the IP address, and port could be determined, so someone could configure an SQL
connection if they wished.
Things tried so far
- Revoke all roles from users except CONNECT and PUBLIC SELECT, and then assign roles to them as they start a forms session or possibly as they enter individual forms. Tried assigning a role to a named user
from within a PRE-FORM trigger and it does not compile. Should this be possible ?
GRANT C##ENVIRO TO C##GEN_USER;
2 Revoke all roles from users except CONNECT and PUBLIC SELECT, and use the CONNECT_SCHEMA command in a WHEN-NEW-FORM trigger to allow the forms session to use a SCHEMA who does have permission to perform the DML
on the objects
ALTER SESSION SET
CURRENT_SCHEMA=C##CAN_RUN_DML
Problem with this is that when tested form the command line, it does not cause the current session to enjoy the privileges that the new schema has (which is what I would expect). In any
event I could not get the statement to compile within a forms 12 trigger. I understand that SET CURRENT_SCHEMA is more about avoiding the use of object synonyms rather
than transporting privileges
3 Can I block global DML using an ON DELETE, INSERT OR UPDATE trigger to count the number of rows that will be effected, and if it is over a certain number,
rollback that attempt and inform the user? Oracle support warned against this approach due to unacceptable overhead which I didn’t quite understand (our entire Oracle estate is minute ).
Should this approach work in principle or is it a bad idea?
4. Could I limit DML to connections solely made form a forms session? The view V$SESSION holds this detail
select schemaname,program,module,sid from v$session where program='frmweb.exe';
C##REPORT frmweb.exe REG_PHOTO_TEST 30
C##NJOHNSTON frmweb.exe REG_PHOTO_TEST 42
C##SHERBERTSON frmweb.exe REG_DRAW 272
But how can I use this information to block non-form originated delete, insert or update statements (while allowing select) ?
This would be the neatest solution I think.
5 Can I limit DML from particular connections by IP address? Since we have a cloud compute server running our forms (and users cannot create SQL sessions on it) then any
DML requests coming from its IP to our database could be allowed based on a modified SQLNET.ORA file
- tcp.validnode_checking = yes
This turns on the hostname/IP checking for the listeners, and I can then supply a lists of nodes to enable/disable, as such:
- tcp.invited_nodes = (hostname1, hostname2)
- tcp.excluded_nodes= (192.168.10.3)
The problem with this is that I need to allow specialist to access the database from home both to initiate a forms session, and
access the Oracle data directly to run select queries. I would be very grateful for advice on the best way to achieve what I need. I am not a full time DBA and am new to the forum
and hope I have given a thorough description of the problem.
Thanks
Pete
Oracle Standard DBAAS 12. 1.0.2.0 (On Oracle LINUX Cloud) with single pluggable database
Forms 12 12.2.1.1.0 on Oracle Cloud
Compute server Windows Server 2012 R2
Web Logic Server 12.2.1.1.0