Home » RDBMS Server » Security » REVOKE INSERT,UPDATE,DELETE rights from owner himself (Oracle 10g (10.2.0.1.0),Windows Server 2003, 64 bit)
REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389649] Tue, 03 March 2009 00:43 Go to next message
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.
Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389653 is a reply to message #389649] Tue, 03 March 2009 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389654 is a reply to message #389649] Tue, 03 March 2009 00:48 Go to previous messageGo to next message
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
Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389663 is a reply to message #389654] Tue, 03 March 2009 01:04 Go to previous messageGo to next message
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.

Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389666 is a reply to message #389654] Tue, 03 March 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: REVOKE INSERT,UPDATE,DELETE rights from owner himself [message #389671 is a reply to message #389649] Tue, 03 March 2009 02:16 Go to previous message
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...
Previous Topic: Usage of _tsenc_tracing variable
Next Topic: Howto: Privilegs based on foreign key of a column??
Goto Forum:
  


Current Time: Thu Mar 28 20:14:15 CDT 2024