OraFAQ Forum: Security » REVOKE INSERT,UPDATE,DELETE rights from owner himself

Home » RDBMS Server » Security » REVOKE INSERT,UPDATE,DELETE rights from owner himself (Oracle 10g (10.2.0.1.0),Windows Server 2003, 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389649]

Tue, 03 March 2009 00:43

rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan

Member

Hello everybody,

I have created a user for qadb, i gave dba and connect grants to this user initially and after importing its dump i revoked the dba grant.

Now i also want to revoke the INSERT, UPDATE and DELETE privs from this user's tables in its own schema.

By default i think owner has the DML rights in its own schema where the only grant given is CONNECT

Is it possible to do something like

connected as system/pass

REVOKE UPDATE ON MW6QA.REP_SETUP FROM MW6QA;

Error comes
ORA-01927: Cannot REVOKE privileges you did not grant

please guide me as i want all dml ops to be revoked from this qadb user.

Report message to a moderator

Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389653 is a reply to message #389649]

Tue, 03 March 2009 00:46

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Now i also want to revoke the INSERT, UPDATE and DELETE privs from this user's tables in its own schema.

A owner can always do what it wants with its objects.
Lock the account and use another one to manage the data.

Regards
Michel

Report message to a moderator

Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389654 is a reply to message #389649]

Tue, 03 March 2009 00:48

rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan

Member

I cannot lock the accout as it is used by the qa resources for testing,

and at the same time for qa team's issues findings and fixations , the development team also queries its tables.

But i want the development team only to view its tabels, but no DMLs should be given

Report message to a moderator

Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389663 is a reply to message #389654]

Tue, 03 March 2009 01:04

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Then;

1. You should need to create synonyms to other used based on your root user.

2. As per your requirement you can grant/revoke to/from user.

Report message to a moderator

Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389666 is a reply to message #389654]

Tue, 03 March 2009 01:26

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I cannot lock the accout as it is used by the qa resources for testing,

QA should be the image of production, so you should not use this account but an end user one.

Quote:

the development team also queries its tables.

In development, there should be no problem to be able to insert/delete/update data.

You have an organization problem not an Oracle one.
Trying to solve an organization issue with a technical solution will just lead to bigger problems.

Regards
Michel

Report message to a moderator

Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389671 is a reply to message #389649]

Tue, 03 March 2009 02:16

rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan

Member

I have fixed the issue

1. Create another user for development team devtest

2. Granted SELECT privs over all tables of QADB tables to devtest

3. Created views in devtest schema for all tables that map to qadb tables

4. Create synonyms in devtest schema for all functions and procedures that map to functions and procedures of qadb objects

5. Granted execute any procedure to this new devtest user

That is how i am satisfied with the qadb and dev team accessing those same objects with their respective rights.

Thanks everybody for sharing...

Report message to a moderator

Previous Topic:	Usage of _tsenc_tracing variable
Next Topic:	Howto: Privilegs based on foreign key of a column??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 01 19:45:52 CDT 2025