Home » SQL & PL/SQL » SQL & PL/SQL » Reg the DDL trigger to avoid altering a table (merged)
Reg the DDL trigger to avoid altering a table (merged) [message #350800] Fri, 26 September 2008 07:02 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Is there is possible to revoke the alter privilege from a particular user

here i need to make the ALTER command not work in a particular user is there is any way to make the alter unuse ,if means pls guide over this

but i gave the below command

REVOKE alter  ON scott.EXT_AWBDATA_TEMP_UPDATED FROM scott
Error at line 1
ORA-01927: cannot REVOKE privileges you did not grant



Thanks,

[Updated on: Fri, 26 September 2008 07:14]

Report message to a moderator

Re: Reg Privileges in oracle [message #350803 is a reply to message #350800] Fri, 26 September 2008 07:17 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
ram anand wrote on Fri, 26 September 2008 14:02

REVOKE alter  ON scott.EXT_AWBDATA_TEMP_UPDATED FROM scott
Error at line 1
ORA-01927: cannot REVOKE privileges you did not grant



The owner can always alter his tables, with no grants needed.

You can create the table on a different schema, lock the account or revoke the create session privilege to do it.

Bye Alessandro

Re: Reg Privileges in oracle [message #350833 is a reply to message #350803] Fri, 26 September 2008 11:05 Go to previous messageGo to next message
gkbiswal
Messages: 5
Registered: August 2008
Location: Mumbai
Junior Member
You can create ALTER DDL Trigger on the schema to restrict user to use ALTER Command.

Regards,
Gk
Re: Reg Privileges in oracle [message #351044 is a reply to message #350833] Mon, 29 September 2008 01:55 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

I have tried the below trigger which used to raise an error before altering the table on a particular schema,but when i tried to alter a particular table i could find the trigger is unfired ,so can any one pls guide over this

CREATE OR REPLACE trigger cargoops.trig1
    BEFORE alter OR DROP ON SCHEMA
DECLARE
    v_sysevent VARCHAR2(120);
    v_obj_type  VARCHAR2(120);
    v_obj_name VARCHAR2(120);
    v_user VARCHAR2(30);
    past_due EXCEPTION;
    --PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    SELECT ora_sysevent,user 
    INTO v_sysevent,v_user  FROM dual;
    
    IF (v_sysevent = 'ALTER') 
     THEN 
        
    Raise past_due;
        --Raise_application_error (-21000,'You cannot perform this Action.');
       -- dbms_output.put_line('ddl Canceld !');
    END IF; 
    
EXCEPTION
      when past_due then
    DBMS_OUTPUT.PUT_LINE('You cannot perform this Action.');     

END trig1;
/
sho err


Thanks,
Re: Reg Privileges in oracle [message #351048 is a reply to message #351044] Mon, 29 September 2008 02:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is plain stupid.
Now you will need an admin-user in order to upgrade your application.
You just fixed the wrong problem. The root-cause of your problem was not that the owner was able to alter his objects, but that apparently too many people had access to the owner-schema.
Reg the DDL trigger to avoid altering a table [message #351213 is a reply to message #350800] Mon, 29 September 2008 23:31 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can any one provide an example for DDL triggers to avoid altering the table (before) , the trigger gets fire whenever an user attempts to alter the table in the particular schema

Thanks,
Re: Reg the DDL trigger to avoid altering a table [message #351214 is a reply to message #351213] Mon, 29 September 2008 23:46 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Don't give privileges to do that.

Why you need a trigger for this.

./fa/456/0/ Google.I found answers on first page.

CREATE OR REPLACE TRIGGER alter_trigger
BEFORE  ALTER ON SCHEMA
BEGIN
  dbms_output.put_line('Altered');
END alter_trigger;
/


Regards,
Rajat

[Updated on: Tue, 30 September 2008 00:14]

Report message to a moderator

Re: Reg the DDL trigger to avoid altering a table [message #351217 is a reply to message #351214] Tue, 30 September 2008 00:15 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi,

Even after revoking the below privilege, the table gets altered

revoke alter on schema.tablename  from user 


normally i think so after creating the user the default privilege are set as it is we cannot revoke for the specfied
privileges ,so can u provide me the exact privilege to avoid altering the table before

Thanks,
Re: Reg the DDL trigger to avoid altering a table [message #351218 is a reply to message #351217] Tue, 30 September 2008 00:20 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

normally i think so after creating the user the default privilege are set as it is we cannot revoke for the specfied
privileges



No Default.

I have edited my previous post to show a trigger that get fired before alter statement in a schema.

Regards,
Rajat
Re: Reg the DDL trigger to avoid altering a table [message #351222 is a reply to message #351218] Tue, 30 September 2008 00:31 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

The trigger fails to fire , but the concept is i need to raise an caution exception when some one tries to alter any table in that schema
I have tried one trigger which is shown below can u tell me whats wrong in the trigger

CREATE OR REPLACE TRIGGER trig1
    BEFORE ALTER OR DROP ON SCHEMA
DECLARE
    v_sysevent VARCHAR2(120);
    v_obj_type  VARCHAR2(120);
    v_obj_name VARCHAR2(120);
    v_user VARCHAR2(30);
    past_due EXCEPTION;
    --PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT ora_sysevent, ora_dict_obj_type, ora_dict_obj_name, user 
    	INTO v_sysevent, v_obj_type, v_obj_name, v_user  FROM dual;
   
  	IF (v_sysevent = 'alter') AND (v_obj_type = 'TABLE') THEN 
    
      Raise past_due;
        --Raise_application_error (-21000,'You cannot perform this Action.');
       -- dbms_output.put_line('ddl Canceld !');
    END IF; 
    
EXCEPTION
      when past_due then
    DBMS_OUTPUT.PUT_LINE('You cannot perform this Action.');     

END trig1;



Here the above trigger fails to fire

thanks,
Re: Reg the DDL trigger to avoid altering a table [message #351226 is a reply to message #351222] Tue, 30 September 2008 00:40 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
IF (v_sysevent = 'alter')


You know string comparison is case sensitive.

Try this:-

IF (v_sysevent = 'ALTER')


Regards,
Rajat
Re: Reg the DDL trigger to avoid altering a table [message #351228 is a reply to message #351222] Tue, 30 September 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, you don't to select Oracle variables to put them into local ones, you can directly use them:
if ora_sysevent='ALTER' ...

Regards
Michel
Re: Reg the DDL trigger to avoid altering a table [message #351239 is a reply to message #351228] Tue, 30 September 2008 01:13 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi,
I have changed the trigger as

   CREATE OR REPLACE TRIGGER trig1
    BEFORE ALTER OR DROP ON SCHEMA
DECLARE
    v_sysevent VARCHAR2(120);
    v_obj_type  VARCHAR2(120);
    v_obj_name VARCHAR2(120);
    v_user VARCHAR2(30);
    past_due EXCEPTION;
    --PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--  SELECT ora_sysevent, ora_dict_obj_type, ora_dict_obj_name, user 
--  --INTO v_sysevent, v_obj_type, v_obj_name, v_user  
--  FROM dual;
   
  	IF (ora_sysevent = 'ALTER') AND (ora_dict_obj_type = 'TABLE') THEN 
    
      Raise past_due;
        --Raise_application_error (-21000,'You cannot perform this Action.');
       -- dbms_output.put_line('ddl Canceld !');
    END IF; 
    
EXCEPTION
      when past_due then
    DBMS_OUTPUT.PUT_LINE('You cannot perform this Action.');     

END trig1;
/



but now also the trigger fails ,there is no way to avoid before altering a table ,my suggestion reg this is to create the user as read only is there is any solution can u pls help me reg this

Thanks,

Re: Reg the DDL trigger to avoid altering a table [message #351241 is a reply to message #351239] Tue, 30 September 2008 01:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Boss you need to Halt Execution When Exception Occurs.

CREATE OR REPLACE TRIGGER trig1 BEFORE ALTER OR DROP ON SCHEMA
BEGIN
IF (ora_sysevent = 'ALTER') AND (ora_dict_obj_type = 'TABLE') THEN
  RAISE_APPLICATION_ERROR(-20001,'You cannot perform this');
END IF; 
END trig1;


When you raise exception it get handled in exception
block and table is altered.

You don't have to handle exception otherwise it will
continue execution.

Regards,
Rajat
Re: Reg the DDL trigger to avoid altering a table [message #351243 is a reply to message #351241] Tue, 30 September 2008 01:28 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi Rajat,

Thanks a lot for your kind replys now its work fine ,with my small mistake reg handle the exception

Thanks,
Re: Reg the DDL trigger to avoid altering a table [message #351253 is a reply to message #351213] Tue, 30 September 2008 01:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ram anand wrote on Tue, 30 September 2008 06:31
Hi,
Can any one provide an example for DDL triggers to avoid altering the table (before) , the trigger gets fire whenever an user attempts to alter the table in the particular schema


How is this different from your previous question? Why did you decide to start it all over again?
Re: Reg the DDL trigger to avoid altering a table [message #351254 is a reply to message #351239] Tue, 30 September 2008 02:03 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my suggestion reg this is to create the user as read only

Don't give it the privilege to do something you don't want it does.

Regards
Michel

[I merged the two topics]

[Updated on: Tue, 30 September 2008 02:05]

Report message to a moderator

Previous Topic: ORA-00937: not a single-group group function
Next Topic: Handling single quotes in parameter passing
Goto Forum:
  


Current Time: Fri Dec 09 00:09:32 CST 2016

Total time taken to generate the page: 0.12268 seconds