Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nancy.

Asked: February 13, 2005 - 8:48 pm UTC

Last updated: July 22, 2005 - 8:45 am UTC

Version: 9.0.1

Viewed 1000+ times

You Asked

1. Is there a way that a user can not drop a table even he has previlige to do so . actullay I want to prevent only one table from user schema.
Can a trigger do that.

And my 2nd question is can i find table size of a schema using a select. Like how much emp table uses space in scott schema.

Thanks

and Tom said...

ops$tkyte@ORA9IR2> create or replace trigger no_dropping_for_you
2 before drop on database
3 begin
4 if (user = 'SCOTT')
5 then
6 raise_application_error( -20001, 'No dropping allowed...' );
7 end if;
8 end;
9 /

Trigger created.

ops$tkyte@ORA9IR2> connect scott/tiger
Connected.
ops$tkyte@ORA9IR2> drop table t;
drop table t
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No dropping allowed...
ORA-06512: at line 4


ops$tkyte@ORA9IR2> desc t
Name Null? Type
---------------------------------------- -------- ----------------------------
X NOT NULL NUMBER(38)
Y NUMBER(38)



see:

</code> http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm#998000 <code>

for all of the functions available to you in that trigger (who is trying to drop what type of object named whatever -- you can get all of that information and use that to prevent the dropping of whatever you wish)




ops$tkyte@ORA9IR2> select bytes from dba_segments where segment_name = 'EMP' and owner = 'SCOTT';

BYTES
----------
65536


shows the space currently allocated to the SCOTT.EMP table.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Access rights

Raghav, February 14, 2005 - 8:18 am UTC

Hi Tom,

I have a table having the records of all the users who operate the particular schema. Can I prevent / assign them particular rights like only query allowed / insert allowed to particular set of users (from my user ) table.

Thanks in advance
Raghav

Tom Kyte
February 14, 2005 - 8:39 am UTC

not really clear on your goal here.

you can certainly write a program, in plsql perhaps, that reads your tables and issues the appropriate grants.



how to log user's info before drop

Jerome, July 21, 2005 - 7:29 am UTC

Hi Tom:

I use this trigger to prevent user truncate table and record the user's information.

CREATE TABLE LOG_TAB
(MACHINE VARCHAR2(100),USERNAME VARCHAR2(100), IP VARCHAR2(100)) ;


create or replace trigger TRG_NO_TRUNCATE_TABLE
before truncate on USER.SCHEMA

declare
v_machine varchar2(100);
V_ip VARCHAR2(100);
V_user VARCHAR(100);

begin

select SYS_CONTEXT('USERENV', 'TERMINAL'),
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV', 'OS_USER')
into v_machine, V_IP, V_USER from dual;

insert into log_tab values (v_machine, v_ip, v_user);

RAISE_APPLICATION_ERROR(num => -20998,
msg => 'truncate table is not allowed !! pls contact DBA.');

end;

When I try to truncat a table. It did show application error but I can't find any data in log_tab and no other error messages.
Why the trigger can't insert a row?
How to record some log data before it show application error?

Thanks

Jerome



Tom Kyte
July 21, 2005 - 8:03 am UTC

missing a commit.

drop table

Hitender-, July 21, 2005 - 10:42 am UTC

Since we cannot have commit in trigger and we are failing the transaction. We need to make it autonomus transaction by giving

create or replace trigger TRG_NO_TRUNCATE_TABLE
before truncate on USER.SCHEMA
pragma autonomous_transaction ;

ORA-04079

jerome, July 21, 2005 - 8:32 pm UTC

I have tried to use "pragma autonous_trancaction" before. When the trigger was compiled it will show error "ORA-04079 invalid trigger specification".
So I guess may be we can't do any trancaction before raise application error or it will be rollback.

Tom Kyte
July 22, 2005 - 8:45 am UTC 

It works, you must have made a typo

ops$tkyte@ORA9IR2> CREATE TABLE LOG_TAB
  2   (MACHINE VARCHAR2(100),USERNAME VARCHAR2(100), IP VARCHAR2(100)) ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger TRG_NO_TRUNCATE_TABLE
  2  before truncate on SCHEMA
  3  declare
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  begin
  6    insert into log_tab (machine,ip, username)
  7    values ( SYS_CONTEXT('USERENV', 'TERMINAL'),
  8             SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
  9             SYS_CONTEXT('USERENV', 'OS_USER') );
 10    commit;
 11
 12    RAISE_APPLICATION_ERROR
 13    (-20998, 'truncate table is not allowed !! pls contact DBA.');
 14  end;
 15  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table t;
truncate table t
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998: truncate table is not allowed !! pls contact DBA.
ORA-06512: at line 10
 
 
ops$tkyte@ORA9IR2> select * from log_tab;
 
MACHINE    USERNAME   IP
---------- ---------- ----------
           tkyte      127.0.0.1

A reader, July 22, 2005 - 11:05 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library