Home » RDBMS Server » Security » HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER
HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER [message #304488] Wed, 05 March 2008 06:45 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
How to stop drop column in whole schema in oracle. Adding of column needs to be allowed
Re: HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER [message #304502 is a reply to message #304488] Wed, 05 March 2008 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
STOP PUTTING YOUR TITLE IN UPPER CASE.

The answer is the same as your previous question.

Regards
Michel

[Updated on: Wed, 05 March 2008 07:20]

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304517 is a reply to message #304502] Wed, 05 March 2008 08:16 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
I only want to stop Column drop, Column additions need to be allowed
Re:How to apply restriction on trigger of Alter [message #304521 is a reply to message #304517] Wed, 05 March 2008 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just modify you trigger on ALTER and check if it is ADD or DROP column.

Regards
Michel
Re:How to apply restriction on trigger of Alter [message #304526 is a reply to message #304521] Wed, 05 March 2008 08:57 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Folowing is the trigger.

CREATE OR REPLACE TRIGGER ALTER_trigger
BEFORE ALTER ON <schema name>.SCHEMA

BEGIN

--if DROP_COLUMN IS TRUE then
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot Alter table');
--end if;
END;

Could you please tell me that how do i parse the command

[Updated on: Wed, 05 March 2008 08:58]

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304527 is a reply to message #304526] Wed, 05 March 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Responding to System Events through Triggers

ora_sql_txt contains SQL text.

Regards
Michel
Re:How to apply restriction on trigger of Alter [message #304546 is a reply to message #304527] Wed, 05 March 2008 10:30 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Thank you very much, it is done
Re:How to apply restriction on trigger of Alter [message #304548 is a reply to message #304546] Wed, 05 March 2008 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the code for others.

Regards
Michel
Re:How to apply restriction on trigger of Alter [message #304755 is a reply to message #304548] Thu, 06 March 2008 08:54 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
CREATE OR REPLACE TRIGGER ALTER_trigger
BEFORE ALTER ON <SCHEMA NAME>.SCHEMA
declare
sql_text ora_name_list_t;
v_stmt VARCHAR2(2000);
v_t varchar2(100);
v_t2 varchar2(100);
n number;
BEGIN


n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;

select SUBSTR(v_stmt,INSTR(v_stmt,'drop column',1),11) into v_t from dual;

if v_t='drop column' then
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot Alter table');

ELSE

select SUBSTR(v_stmt,INSTR(v_stmt,'rename column',1),13) into v_t from dual;

if v_t='rename column' then
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot Alter table');


end if;

end if;




END;
Re:How to apply restriction on trigger of Alter [message #304769 is a reply to message #304755] Thu, 06 March 2008 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, "How to format your post?" section.

There are several caveats in your code.
You don't need to select from dual, just check if "instr" returns 0 or not.
You don't need else and second if, use AND.
You have to take care of case. What if DROP COLUMN if written in capitals?

Regards
Michel

[Updated on: Thu, 06 March 2008 09:49]

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304786 is a reply to message #304769] Thu, 06 March 2008 11:32 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Thank you very much, i have changed my trigger as follows

CREATE OR REPLACE TRIGGER ALTER_trigger
BEFORE ALTER ON <SCHEMA NAME>.SCHEMA
declare
sql_text ora_name_list_t;
v_stmt VARCHAR2(2000);
v_t varchar2(100);
n number;
BEGIN

n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;

if INSTR(lower(v_stmt),'drop column',1)<>0 OR INSTR(lower(v_stmt),'rename column',1)<>0 then
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot Alter table');

end if;

END;
Re:How to apply restriction on trigger of Alter [message #304789 is a reply to message #304786] Thu, 06 March 2008 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think also add "set unused" which is logically the same thing as "drop column" and why not "rename column" or "modify" a column?

Regards
Michel
Re:How to apply restriction on trigger of Alter [message #304937 is a reply to message #304789] Fri, 07 March 2008 04:35 Go to previous message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
CREATE OR REPLACE TRIGGER ALTER_trigger
BEFORE ALTER ON <schemaname>.SCHEMA
declare
sql_text ora_name_list_t;
v_stmt VARCHAR2(2000);
v_t varchar2(100);
n number;
BEGIN
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;

if INSTR(lower(v_stmt),'drop column',1)<>0 OR INSTR(lower(v_stmt),'rename column',1)<>0
OR INSTR(lower(v_stmt),'set unused',1)<>0 OR INSTR(lower(v_stmt),'drop unused',1)<>0
OR INSTR(lower(v_stmt),'modify',1)<>0
then
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot Alter table');

end if;
END;
Previous Topic: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER
Next Topic: Could the default maximum password length be changed ??
Goto Forum:
  


Current Time: Thu Apr 24 04:59:30 CDT 2014

Total time taken to generate the page: 0.11534 seconds