OraFAQ Forum: SQL & PL/SQL » Revoke insert and Update access from schema owner

Home » SQL & PL/SQL » SQL & PL/SQL » Revoke insert and Update access from schema owner (11.0.2.10)

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

Revoke insert and Update access from schema owner [message #650838]

Thu, 05 May 2016 03:49

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Hi All,

I need to stop application access to update or insert on a certain schema and limit the application right to read only (a temporary step before stopping the application). Unfortunately this application uses a user who is the creator/owner of those tables.

My questions are:
1- Is this possible?
2- Is it possible to change the owner? is this recommended?
3- Currently I am trying to either create a new schema and import all table into it and create a new user with right privilege on all table and modify the application to use this user... but its very lengthy as its a legacy application and very difficult to modify.
4- What is the shortest way to do this?
5- what is the recommended practice?

Thanks
Ferro

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650839 is a reply to message #650838]

Thu, 05 May 2016 03:56

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Additional solution: or I can create a new user on this schema with read only access on all its tables and modify the application to use this user.

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650841 is a reply to message #650839]

Thu, 05 May 2016 04:58

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

Senior Member
Account Moderator

The "additional solution" IS the way to go.
No one should have access to application owner and this account should locked but during upgrades.

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650846 is a reply to message #650838]

Thu, 05 May 2016 06:47

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

Would this

ALTER TABLE...READ ONLY;

or this

ALTER TABLESPACE..READ ONLY;

be sufficient?

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650851 is a reply to message #650846]

Thu, 05 May 2016 07:59

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

Senior Member
Account Moderator

SQL> create table tt (id int) tablespace ts_d01;

Table created.

SQL> alter tablespace ts_d01 read only;

Tablespace altered.

SQL> drop table tt ;

Table dropped.

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650852 is a reply to message #650851]

Thu, 05 May 2016 08:05

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

Senior Member
Account Moderator

Or better:

SQL> create table tt (id int) segment creation immediate tablespace ts_d01;

Table created.

SQL> alter tablespace ts_d01 read only;

Tablespace altered.

SQL> alter table tt add val varchar2(100);

Table altered.

SQL> drop table tt purge;

Table dropped.

SQL> alter tablespace ts_d01 read write;

Tablespace altered.

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650856 is a reply to message #650852]

Thu, 05 May 2016 09:11

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

I'm glad I don't have you as one of my end users, Michel Smile

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650870 is a reply to message #650856]

Thu, 05 May 2016 13:25

Bill B
Messages: 1971
Registered: December 2004

Senior Member

As a general rule (and something I always do), create your tables in one schema, and use a a different schema(s) for the application. That way you have total control on how the application can access the data. For example one application at my site has all the data in 4 schemas and almost 60000 other schemas accessing the data. You control by giving the appropriate rights to a role and then grant the role to the application users who typically have no local objects.

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650951 is a reply to message #650870]

Sat, 07 May 2016 23:04

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Thanks Michel and all for the useful advice/rule of thumb,

I tried my solution but was forced to go else way as the application (a legacy case tool called IEF) did not accept linking to db objects that it cannot access directly (i.e. it can access MyTable but cannot access MyReadOnlySchema.MyTable).

Unfortunately, I had to enforce the read only rules by a combination of table instead of triggers and some application security.

Thanks a lot,
Ferro

Report message to a moderator

Re: Revoke insert and Update access from schema owner [message #650953 is a reply to message #650951]

Sun, 08 May 2016 00:25

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

For future reference, the use of synonyms or ALTER SESSION SET CURRENT SCHEMA might have been easier alternatives.
How do you create INSTEAD OF triggers on tables? I thought they existed only for views.

Report message to a moderator

Previous Topic:	select based on ratio from second table
Next Topic:	What is the best method to fetch data from a table and write into a csv file.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 17 08:47:01 CDT 2025