OraFAQ Forum: Security » HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER

Home » RDBMS Server » Security » HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER [message #304488]

Wed, 05 March 2008 06:45

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

I only want to stop Column drop, Column additions need to be allowed

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304521 is a reply to message #304517]

Wed, 05 March 2008 08:39

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just modify you trigger on ALTER and check if it is ADD or DROP column.

Regards
Michel

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304526 is a reply to message #304521]

Wed, 05 March 2008 08:57

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Responding to System Events through Triggers

ora_sql_txt contains SQL text.

Regards
Michel

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304546 is a reply to message #304527]

Wed, 05 March 2008 10:30

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

Thank you very much, it is done

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304548 is a reply to message #304546]

Wed, 05 March 2008 10:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please post the code for others.

Regards
Michel

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304755 is a reply to message #304548]

Thu, 06 March 2008 08:54

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;

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304769 is a reply to message #304755]

Thu, 06 March 2008 09:49

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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;

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304789 is a reply to message #304786]

Thu, 06 March 2008 11:50

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re:How to apply restriction on trigger of Alter [message #304937 is a reply to message #304789]

Fri, 07 March 2008 04:35

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;

Report message to a moderator

Previous Topic:	HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER
Next Topic:	Could the default maximum password length be changed ??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 03:22:37 CST 2024