- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Revoke privilege for ddl command from a schema own...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2003 04:44 AM
тАО03-05-2003 04:44 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2003 04:53 AM
тАО03-05-2003 04:53 AM
Re: Revoke privilege for ddl command from a schema owner
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2003 04:57 AM
тАО03-05-2003 04:57 AM
Re: Revoke privilege for ddl command from a schema owner
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2003 02:37 PM
тАО03-05-2003 02:37 PM
Re: Revoke privilege for ddl command from a schema owner
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2003 08:18 PM
тАО03-05-2003 08:18 PM
SolutionHere'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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2003 08:28 PM
тАО03-05-2003 08:28 PM
Re: Revoke privilege for ddl command from a schema owner
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-07-2003 10:25 AM
тАО03-07-2003 10:25 AM
Re: Revoke privilege for ddl command from a schema owner
You save my life again !!
Cheers,
Chris,