Revoke privilege for ddl command from a schema owner

[Chris Fung]

Hi All,

Scenario:

1. userA (with create session and resource privileges)login to Oracle and create tableA in his owner schema

2. userA inserts some records to tableA.

3. Revoke the resource privilege from userA

=> Now the results will be userA can no longer create database objects inside his schema.....but he still can insert, update, detete records from tableA......and even he can drop tableA.

What I am trying to do is to create a user who originally own some tables and then revoke all the drop/create table privilege from him....so that he only has authorities to insert, update, delete and truncate his original tables....but not "Drop"

Could anyone tell me how to accomplish this task (without make use of other user accounts....what I mean is create the table with userB..and then grant all the required tables and associated privilege from userB to userA).

Thanks and Regards,

Chris

[Tom Jackson]

Hi Chris:

Can't you just create the table with B, then as userB:

grant insert on tableA to userA
grant update on tableA to userA
grant delete on tableA to userA

I'm not sure about truncating, you may need to grant alter.

Tom

[Christian Gebhardt]

Hi

sorry, but no chance. "drop table" is no system privileg that you can revoke.

A user can manipulate his own objects in every way, so he always can do a "drop table" even he misses the "create table pr??vileg"

Chris

[Brian Crabtree]

Chris,

Christian is correct, you cannot revoke the "drop table" privilege for a user owned table. One possiblilty would be to create an admin user that could own the original tables, and grant the roles needed (with synonyms). Then, create a procedure that would create the table, and synonym, and grant table privileges to userA. This would disallow userA to drop or create tables, but would still allow selects against it.

Brian

[Yogeeraj_1 · Accepted solution]

Hey you can!!

Here's the piece of magic:
============================================================
SQL> create user ydtest identified by ydtest;

User created.

SQL> grant connect, resource to ydtest;

Grant succeeded.

SQL> grant create trigger to ydtest;

Grant succeeded.

SQL> connect ydtest/ydtest
Connected.

SQL> create or replace trigger ddl_trigger
2 before drop on SCHEMA
3 declare
4 l_sysevent varchar2(25);
5 begin
6 select ora_sysevent into l_sysevent from dual;
7
8 if ( l_sysevent = 'DROP' )
9 then
10 RAISE_APPLICATION_ERROR(-20001,'Youare not allowed to drop any tables
');
11 end if;
12 end;
13 /

Trigger created.

SQL> insert into test
2 select rownum from all_objects
3 where rownum <10;

9 rows created.

SQL> commit;

Commit complete.

SQL> delete from test where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> truncate table test;

Table truncated.

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Youare not allowed to drop any tables
ORA-06512: at line 8


SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER

SQL>



============================================================

;)

hope this helps!

Cheers
Yogeeraj

[Yogeeraj_1]

oops! missed the last part...

after you have revoked all privileges.

============================================================
SQL> connect system
Enter password:
Connected.
SQL> revoke connect, resource from ydtest;

Revoke succeeded.

SQL> revoke create trigger from ydtest;

Revoke succeeded.

SQL> grant create session to ydtest;

Grant succeeded.

SQL> connect ydtest/ydtest
Connected.
SQL> truncate table test;

Table truncated.

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Youare not allowed to drop any tables
ORA-06512: at line 8


SQL> create table test2 ( col1 number);
create table test2 ( col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>
============================================================

Best Regards
Yogeeraj

[Chris Fung]

Thank you all your inputs especially for Yogeeraj !!

You save my life again !!

Cheers,

Chris,