Restricting User access 1

[hilbertl3]

I have an application with an Access front end and an Oracle back end. In Oracle, I've set up permissions for my users so that some tables can be apppended to and deleted from, others only selected etc. etc. I would like to prevent users from using a tool like TOAD or SQL plus to log into the Oracle database where my application's tables are housed and manipulate the data in those tables through adhoc SQL statements. In short, I want the users to only interact with my application's tables through my front end. Is it possible to implement a security policy like this?

hilbertl

 

[LKBrwnDBA]

You could write a system/database event trigger that checks the PROGRAM or MODULE columns in V$SESSION.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[hilbertl3]

I'm new to Oracle. Could you provide a little more detail?

 

[LKBrwnDBA]

Did you click on the link I provided?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Specially the section 'database_event' LOGON trigger.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Or look at similar post in thread759-1109662

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Or you can code something like this:

CREATE OR REPLACE TRIGGER AFTERLOGON
AFTER LOGON ON DATABASE
DECLARE V_PROG VARCHAR2(48);
BEGIN
  SELECT MODULE INTO V_PROG FROM V_$SESSION;
  IF UPPER(V_PROG) LIKE 'TOAD%' OR
     UPPER(V_PROG) LIKE 'SQL*PLUS%'
  THEN
    RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to use TOAD or SQL*Plus.');
  END IF;
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[hilbertl3]

Thank you very much.

 

[Turkbear]

Hi,
But..( there is always a but, it seems), that trigger will prevent ANYONE ( including you ) from using those tools..Are you sure you want to limit yourself?
Perhaps checking the USER before prohibiting the tools would be better..





To Paraphrase:"The Help you get is proportional to the Help you give.."