Solved

revoking drop/alter table privileges from a user

Posted on 2001-08-31
6
3,329 Views
Last Modified: 2011-10-03
Dear Experts,I have created a user and granted the create session,create any table priv.i assigned default tablespace system quota 50m for this user.I connected to that user and tried to create a table .it created a table.I revoked create any table priv. from that user by connecting at system/manager.Again I connected to that user and tried to create a table.it is displaying the message insufficient priv. this is fine.I want to revoke the drop and alter table priv.from that user also.how to do that
Madhavi
0
Comment
Question by:madhavi_vsr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Expert Comment

by:renuraj
ID: 6444757
REVOKE DROP TABLE FROM <user name>;
REVOKE ALTER TABLE FROM <user name>;

Regards
0
 

Expert Comment

by:mareyalare
ID: 6444963
You may also want revoke the privileges on a specific object.
REVOKE DROP TABLE ON <TABLENAME> FROM <USERNAME>;
REVOKE ALTER TABLE ON <TABLENAME> FROM <USERNAME>;
If you use the clause CASCADE CONSTRAINTS, it would drop any referential integrity constraints defined by the user.

Regards

0
 
LVL 3

Expert Comment

by:ramkb
ID: 6445362

Hi,

Just like 'CREATE ANY TABLE' there are 'ALTER ANY TABLE' and 'DROP ANY TABLE' system privileges in Oracle.

You have to connect as SYSTEM (or any DBA USER) and do:
REVOKE ALTER ANY TABLE FROM <USER>;
REVOKE DROP ANY TABLE FROM <USER>;

Cheers,
Ramesh
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Accepted Solution

by:
Jankovsky earned 50 total points
ID: 6445390
Hi,
I'm sorry, but it doesn't work.
You can't revoke from user DROP and ALTER its own table.

...ANY TABLE means like operations in another schemas.

Usualy We create two different users: as database objects (tables) owner and second user, who has granted access to owner's objects and specified synonyms.

Regards.
0
 

Author Comment

by:madhavi_vsr
ID: 6447157
hey, this statement
REVOKE DROP TABLE ON <TABLENAME> FROM <USERNAME>;
giving the error
ORA-00990: missing or invalid privilege
ths statement
REVOKE ALTER ANY TABLE FROM <USER>; giving
ORA-01952: system privileges not granted to <user>
0
 

Expert Comment

by:ksskanth
ID: 6449624
Hi

You have given only 2 privilegs

1) Create session
2) Create any table

Also Drop privilege & alter table privilege is given to the user for any table create by him which cannot revoked.

To give only selective privileges create a role with all the privileges and grant that role to the user. Also create table with some other user to make him as owner and give only privileges to other users.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question