Home » SQL & PL/SQL » SQL & PL/SQL » trigger for restricting insertion into table through the SQL*plus (Oracle 10g, XP)
trigger for restricting insertion into table through the SQL*plus [message #317562] Thu, 01 May 2008 23:38 Go to next message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
Deal All,
i want to create a trigger for restricting insertion through sql*plus or Oracle SQL Developer.
pls help me
Thanks in advance
Re: trigger for restricting insertion into table through the SQL*plus [message #317567 is a reply to message #317562] Fri, 02 May 2008 00:02 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

restricting insertion through sql*plus or Oracle SQL Developer


Triggers are implicitely called by oracle when a certain condition satisfies like before insert or after insert.it has nothing to do with Sql* plus or Sql developer.

Study before or after insert triggers(row level or statement level)
Re: trigger for restricting insertion into table through the SQL*plus [message #317568 is a reply to message #317567] Fri, 02 May 2008 00:11 Go to previous messageGo to next message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
Dear rajatrataval,

pls see this code
CREATE OR REPLACE TRIGGER triggername
BEFORE INSERT OR DELETE OR UPDATE ON SUN 
DECLARE
rowcnt number(10);
BEGIN
SELECT count(*) INTO rowcnt
FROM v$session 
WHERE module in('SQL Developer','Oracle SQL developer','SQL*Plus');

IF rowcnt > 0  THEN
begin

  if inserting then 
  RAISE SOME APPLICATION ERROR
  elsif deleting then
  RAISE SOME APPLICATION ERROR
  elsif updating then
  RAISE SOME APPLICATION ERROR
  else
  RAISE SOME APPLICATION ERROR
  end if;
end;
end if;
END;


but it is giving ORA-04098 error
one thing can assure that it is possible to retrive session module ( i.e whether SQL*plus or Oracle SQL Developer )through above query.. check it
Re: trigger for restricting insertion into table through the SQL*plus [message #317571 is a reply to message #317568] Fri, 02 May 2008 00:33 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
At what line you are getting error.

Try drop first and then create it again
Re: trigger for restricting insertion into table through the SQL*plus [message #317572 is a reply to message #317562] Fri, 02 May 2008 00:36 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
From http://ora-04098.ora-code.com/:
Quote:
ORA-04098: trigger 'string.string' is invalid and failed re-validation
Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.

Your trigger has error(s), you shall correct them. To know details about it, issue SHOW ERRORS command in SQL*Plus or query USER_ERRORS from anywhere.

From a brief look, you have only one BEGIN statements, but two END statements. Also raising the error does not have the syntax you posted.

By the way, V$SESSION includes information about ALL session connected to DB. For information about your current session, you shall use SYS_CONTEXT function.
Re: trigger for restricting insertion into table through the SQL*plus [message #317598 is a reply to message #317568] Fri, 02 May 2008 02:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
suneelvarma_27 wrote on Fri, 02 May 2008 07:11

if inserting then
RAISE SOME APPLICATION ERROR
[/code]

Did you copy this code from someone who told you how to do this?
"SOME APPLICATION ERROR" should be replaced with, well, some application error. It's an example, a placeolder where you must use your own application error.

Further, remove the begin and end from the if construction. Those don't add anyting.

Finally, it is quite easy to change the name of the program I use to connect to the database. That way I can still use SQL*Plus or whatever I want.
Take a look at application roles to find a more secure way to shield off access to your db.

[Updated on: Fri, 02 May 2008 02:44]

Report message to a moderator

Re: trigger for restricting insertion into table through the SQL*plus [message #317602 is a reply to message #317568] Fri, 02 May 2008 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
All you would have to do to get round this trigger is to rename your Sql*Plus executable to 'Fred' and this trigger will let you do whatever you like.
Re: trigger for restricting insertion into table through the SQL*plus [message #317618 is a reply to message #317598] Fri, 02 May 2008 03:40 Go to previous messageGo to next message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
i have given SOME APPLICATION ERROR as example...
ok place dbms_output.put_line('u can not insert into table through SQL*plus);

even though it is not working
Re: trigger for restricting insertion into table through the SQL*plus [message #317619 is a reply to message #317598] Fri, 02 May 2008 03:42 Go to previous messageGo to next message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
Dear JRowBottom,

cud you pls explain it indetail.i could not get u'
thanks

Re: trigger for restricting insertion into table through the SQL*plus [message #317620 is a reply to message #317618] Fri, 02 May 2008 03:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You could use an application context or package variable. Set it in the application and check it in the triggers. If the context/variable is not set (e.g. through SQL*Plus), prevent DML.

At one of my past projects, all was done through PL/SQL packages. No regular user had direct access to the tables or package source.

Here's a link to the Oracle docs that might be interesting.

MHE

[Updated on: Fri, 02 May 2008 03:53]

Report message to a moderator

Re: trigger for restricting insertion into table through the SQL*plus [message #317624 is a reply to message #317602] Fri, 02 May 2008 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Fri, 02 May 2008 09:53
All you would have to do to get round this trigger is to rename your Sql*Plus executable to 'Fred' and this trigger will let you do whatever you like.

Not true for the first part but yes true in the principle.
SQL*Plus itself set the module (not program name):
C:\>copy %ORACLE_HOME%\bin\sqlplus.exe fred.exe
        1 fichier(s) copiť(s).

C:\>fred michel/michel

SQL*Plus: Release 11.1.0.6.0 - Production on Fri May 2 10:42:31 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> set serveroutput on
SQL> exec dbms_output.put_line(sys_context('USERENV','MODULE'));
SQL*Plus

But of course this can be modified:
SQL> exec dbms_application_info.set_module('Michel','');

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(sys_context('USERENV','MODULE'));
Michel

PL/SQL procedure successfully completed.

As Maarten said, the solution is in the opposite, allowing only if a (secret) condition is met.

Or better, use a secure application role that enables the write privileges inside the application only; by default, the user has no (not wanted) privilege.

Regards
Michel

Re: trigger for restricting insertion into table through the SQL*plus [message #317632 is a reply to message #317619] Fri, 02 May 2008 04:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, I'm getting things a bit mixed up.
It's the 'PROGRAM' column in v$session that is set to the name of the executable that you run.

The module column that you're checking can be set by anyone with access to DBMS_APPLICATION_INFO, and set to anything that they like.

[I see my mistake has already been spotted... Embarassed ]

[Updated on: Fri, 02 May 2008 04:21]

Report message to a moderator

Re: trigger for restricting insertion into table through the SQL*plus [message #317669 is a reply to message #317618] Fri, 02 May 2008 07:42 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
suneelvarma_27 wrote on Fri, 02 May 2008 04:40
i have
ok place dbms_output.put_line('u can not insert into table through SQL*plus);

even though it is not working


dbms_output has no meaning in a trigger. And would you really want a manager or VP to see a message that has the spelling level of a 1st grader?
Re: trigger for restricting insertion into table through the SQL*plus [message #317702 is a reply to message #317618] Fri, 02 May 2008 11:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
suneelvarma_27 wrote on Fri, 02 May 2008 10:40
i have given SOME APPLICATION ERROR as example...


Don't you think we might be in a better position to help you out if you posted your real code, instead of some mockup, without even telling us?
After all, you ask us to help you on a syntactical error!
Re: trigger for restricting insertion into table through the SQL*plus [message #317783 is a reply to message #317669] Sat, 03 May 2008 09:18 Go to previous messageGo to next message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
this is the trainee level forum...
if you are a VP, then do not see the messages that are posted by trainees
Re: trigger for restricting insertion into table through the SQL*plus [message #317804 is a reply to message #317562] Sat, 03 May 2008 14:57 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Rhetotical question -
What happens if I rename sqlplus to myclient & connect to your DB?

How does your proposed "solution" prevent me from writing some simple PERL code using DBI/DBD to access your database?

The real & complete solution is to make sure that only authorized folks have valid usernames & passwords to connect to your DB.

[Updated on: Sat, 03 May 2008 15:01] by Moderator

Report message to a moderator

Re: trigger for restricting insertion into table through the SQL*plus [message #317826 is a reply to message #317562] Sat, 03 May 2008 22:51 Go to previous messageGo to next message
spsonkusare
Messages: 3
Registered: May 2008
Junior Member
Hi,

To restrict operations issued at SQL*PLUS
+++++++++++++++++++++++++++++++++++++++++
Rather using trigger, try this

Example: To disable DROP command through SQL*PLUS

INSERT INTO PRODUCT_USER_PROFILE(PRODUCT, USERID, ATTRIBUTE, CHAR_VALUE)
VALUES('SQL*PLUS','SCOTT','DROP','DISABLED')

(Make sure you have appropriate privileges for this)
Re: trigger for restricting insertion into table through the SQL*plus [message #317832 is a reply to message #317826] Sun, 04 May 2008 01:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
spsonkusare wrote on Sun, 04 May 2008 05:51
Hi,

To restrict operations issued at SQL*PLUS
+++++++++++++++++++++++++++++++++++++++++
Rather using trigger, try this

Example: To disable DROP command through SQL*PLUS

INSERT INTO PRODUCT_USER_PROFILE(PRODUCT, USERID, ATTRIBUTE, CHAR_VALUE)
VALUES('SQL*PLUS','SCOTT','DROP','DISABLED')

Easy to circumvent:
SQL> conn system
Enter password:
Connected.
SQL> INSERT INTO PRODUCT_USER_PROFILE(PRODUCT, USERID, ATTRIBUTE, CHAR_VALUE)
  2  VALUES('SQL*PLUS','SCOTT','DROP','DISABLED');

1 row created.

SQL> commit;

Commit complete.

SQL> conn scott/tiger
Connected.
SQL> drop table drop_test;
SP2-0544: Command "drop" disabled in Product User Profile
Drop command is shielded from sqlplus as intended. Let's see if Michel's trick works:
SQL> exec dbms_application_info.set_module('Fake', '');

PL/SQL procedure successfully completed.
SQL> drop table drop_test;
SP2-0544: Command "drop" disabled in Product User Profile

Nice. Protection against this trick as well.
Let's rename our sqlplus binary:
C:\oraclexe\app\oracle\product\10.2.0\server\BIN>copy sqlplus.exe fakeplus.exe
        1 file(s) copied.

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>fakeplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 4 08:51:10 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> drop table drop_test;
SP2-0544: Command "drop" disabled in Product User Profile

Good!
Now, how would the backend know this is SQL*Plus? Must be something in the executable that announces itself as such. Let's use a Hex Editor and scan the contents and edit along the way..
C:\oraclexe\app\oracle\product\10.2.0\server\BIN>HexEdit fakeplus.exe

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>fakeplus /nolog

FakePlus: Release 10.2.0.1.0 - Production on Sun May 4 08:45:06 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/tiger
Connected.
SQL> drop table drop_test;

Table dropped.

[Updated on: Sun, 04 May 2008 01:56]

Report message to a moderator

Re: trigger for restricting insertion into table through the SQL*plus [message #317833 is a reply to message #317826] Sun, 04 May 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question was not just about SQL*Plus but also SQL Developer and as this is an example, I think all other SQL tools like TOAD, DB Artisan and so on are also involved.

As I said, the best way to ensure that a user does not use another tool than the application is to give him privileges through a secure application role and no other role or privilege.

Regards
Michel
Re: trigger for restricting insertion into table through the SQL*plus [message #317837 is a reply to message #317833] Sun, 04 May 2008 02:18 Go to previous message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
thanks to all...
Previous Topic: how can i identified very recent DML transaction
Next Topic: can we able to create a view with conditional statement
Goto Forum:
  


Current Time: Wed Dec 07 18:36:35 CST 2016

Total time taken to generate the page: 0.12570 seconds