Home » RDBMS Server » Security » Need to make read only schema (Oracle10g,Linux)
Need to make read only schema [message #377502] Tue, 23 December 2008 04:57 Go to next message
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.
Re: Need to make read only schema [message #377511 is a reply to message #377502] Tue, 23 December 2008 05:12 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Create your database objects in one schema and grant SELECT on them to the other (read-only) schemas.
Re: Need to make read only schema [message #377512 is a reply to message #377511] Tue, 23 December 2008 05:18 Go to previous messageGo to next message
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.
Re: Need to make read only schema [message #377514 is a reply to message #377512] Tue, 23 December 2008 05:21 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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.
Re: Need to make read only schema [message #377517 is a reply to message #377514] Tue, 23 December 2008 05:24 Go to previous messageGo to next message
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.
Re: Need to make read only schema [message #377519 is a reply to message #377517] Tue, 23 December 2008 05:28 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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.
Re: Need to make read only schema [message #382726 is a reply to message #377502] Fri, 23 January 2009 14:59 Go to previous messageGo to next message
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

Re: Need to make read only schema [message #382727 is a reply to message #377502] Fri, 23 January 2009 15:05 Go to previous messageGo to next message
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.
Re: Need to make read only schema [message #382744 is a reply to message #382726] Sat, 24 January 2009 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to grant privilege for each table.

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

Regards
Michel
Re: Need to make read only schema [message #382746 is a reply to message #382726] Sat, 24 January 2009 01:04 Go to previous message
Littlefoot
Messages: 21806
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).
Previous Topic: security issues..?
Next Topic: Alert in Enterprise manager regarding SYS login locally
Goto Forum:
  


Current Time: Fri Mar 29 08:46:15 CDT 2024