revoking drop/alter table privileges from a user

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
madhavi_vsrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JankovskyConnect With a Mentor Commented:
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
 
renurajCommented:
REVOKE DROP TABLE FROM <user name>;
REVOKE ALTER TABLE FROM <user name>;

Regards
0
 
mareyalareCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ramkbCommented:

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
 
madhavi_vsrAuthor Commented:
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
 
ksskanthCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.