OraFAQ Forum: Security » Need to make read only schema

Home » RDBMS Server » Security » Need to make read only schema (Oracle10g,Linux)

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

Need to make read only schema [message #377502]

Tue, 23 December 2008 04:57

sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai

Senior Member

Hi,

I need to make read only schema for non system users.

I am pl/sql developer.I only to create database objects.The other users are able to only select data in one scheama not to create a any database objects.

How can resolve this sceanrio?

Thanks,
Michael.

Report message to a moderator

Re: Need to make read only schema [message #377511 is a reply to message #377502]

Tue, 23 December 2008 05:12

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Create your database objects in one schema and grant SELECT on them to the other (read-only) schemas.

Report message to a moderator

Re: Need to make read only schema [message #377512 is a reply to message #377511]

Tue, 23 December 2008 05:18

sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai

Senior Member

Thanks for guideness.

Suppose, i have create one table million records in user (a. Suddely,it must be affected on other scheam(user b).

Is it possible?

Thanks,
Michael.

Report message to a moderator

Re: Need to make read only schema [message #377514 is a reply to message #377512]

Tue, 23 December 2008 05:21

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Yes, all the users with SELECT on the table will see the same data. However, note that they will not see uncommitted data.

Report message to a moderator

Re: Need to make read only schema [message #377517 is a reply to message #377514]

Tue, 23 December 2008 05:24

sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai

Senior Member

the schema owner represents the Oracle user that owns all your database objects, while application users are Oracle users that need access to those schema objects.

Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far to many privileges, which can easily result in damage to your data and the objects themselves and also i have create one columns in table.For these select privilages,it will not replicate in user(B).

Thanks,
Michael.

Report message to a moderator

Re: Need to make read only schema [message #377519 is a reply to message #377517]

Tue, 23 December 2008 05:28

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

I'm not sure if you're making a statement or asking a question. However, if you need other schemas to only see certain columns, you will have to create views and then grant SELECT on those views.

Report message to a moderator

Re: Need to make read only schema [message #382726 is a reply to message #377502]

Fri, 23 January 2009 14:59

sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai

Senior Member

Hi,

I have created synonyms for all tables and give select privilege to it.i able to select the data from other users.

Is any possible to select any tables from one user to other user?

I tried like this.

grant select,insert,update,delete any tables from usera to userb:

It shows a error like ORA-00990: missing or invalid privilege error.

Is it possible?

thanks,
Michael

Report message to a moderator

Re: Need to make read only schema [message #382727 is a reply to message #377502]

Fri, 23 January 2009 15:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is it possible?
Yes, when you use valid syntax as describe below

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Report message to a moderator

Re: Need to make read only schema [message #382744 is a reply to message #382726]

Sat, 24 January 2009 00:21

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

Senior Member
Account Moderator

You have to grant privilege for each table.

Remark: insert,update,delete are not read-only.

Regards
Michel

Report message to a moderator

Re: Need to make read only schema [message #382746 is a reply to message #382726]

Sat, 24 January 2009 01:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Just in case you didn't get the point:

grant select,insert,update,delete any tables from usera to userb:

any tables is wrong (if you really used that syntax) as you'll have to grant privileges for every single table in its own GRANT statement:

GRANT SELECT ON emp TO user_b;
GRANT SELECT ON dept TO user_b;
etc.

Of course, it might be painful if there are many tables; that's why you may write a SQL which will create these statements for you. If you are interested in such a solution, search the board - there've already been such questions (and answers).

Report message to a moderator

Previous Topic:	security issues..?
Next Topic:	Alert in Enterprise manager regarding SYS login locally

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 02:11:33 CST 2025