Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Revoke privilege for ddl command from a schema owner

SOLVED
Go to solution
Chris Fung
Frequent Advisor

Revoke privilege for ddl command from a schema owner

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
6 REPLIES
Tom Jackson
Valued Contributor

Re: Revoke privilege for ddl command from a schema owner

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
Honored Contributor

Re: Revoke privilege for ddl command from a schema owner

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
Honored Contributor

Re: Revoke privilege for ddl command from a schema owner

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
Honored Contributor
Solution

Re: Revoke privilege for ddl command from a schema owner

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Revoke privilege for ddl command from a schema owner

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Chris Fung
Frequent Advisor

Re: Revoke privilege for ddl command from a schema owner

Thank you all your inputs especially for Yogeeraj !!

You save my life again !!

Cheers,

Chris,