Home » SQL & PL/SQL » SQL & PL/SQL » schema
schema [message #345563] Thu, 04 September 2008 03:58 Go to next message
maxis
Messages: 17
Registered: September 2008
Junior Member
I am just confuse about

how to make a single schema available to all users log on to database

i try alter session set current_schema=maxis

and issue cammand select * from tab

but schema object not available

Re: schema [message #345569 is a reply to message #345563] Thu, 04 September 2008 04:07 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please post what have you tried.

I think you missing some privileges.

Regards,
Rajat
Re: schema [message #345570 is a reply to message #345563] Thu, 04 September 2008 04:12 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe you shall open the documentation and read about CURRENT_SCHEMA session parameter. All documentation books are available e.g. online on http://tahiti.oracle.com/.

Corresponding 10gR2 section is available here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#sthref4525
Quote:
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.
Re: schema [message #345576 is a reply to message #345570] Thu, 04 September 2008 04:27 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
It's worth sharing the use of it.

In a database you can have many schema that corresponds
to different applications data.

Now we can create a special user that has access to
all schema tables but don't have any privileges
to create or drop objects.

This can be done to have a user that has access to many applications with restricted privileges as you get in production.i.e Readonly kind of user.

I am considering that the special user is Rajat.

I connect to the databse via Rajat.

Now to access other schema objects i can access it like this:-

select * from schema1.table1
select * from schema2.table2

or 
alter session set current_schema=schema1
select * from table1

alter session set current_schema=schema2
select * from table2

select user from dual
will return Rajat not schema1 or schema2.

Hope this clarifies.

Regards,
Rajat
Re: schema [message #345596 is a reply to message #345576] Thu, 04 September 2008 04:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So everyone logs on as that single user?
Great! Gives me the freedom to do whatever I want to, without leaving any trace that it was me who did it..
Re: schema [message #345600 is a reply to message #345596] Thu, 04 September 2008 04:57 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I said to read not to do anything frank.
That i clearly mentioned in my post.
Quote:

This can be done to have a user that has access to many applications with restricted privileges as you get in production.i.e Readonly kind of user.



Regards,
Rajat

[Updated on: Thu, 04 September 2008 04:58]

Report message to a moderator

Re: schema [message #345602 is a reply to message #345600] Thu, 04 September 2008 04:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, you said the user had no privs to create or drop objects. No mentioning (as far as I read it) about contents of tables.
Re: schema [message #345605 is a reply to message #345602] Thu, 04 September 2008 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm petty sure that this is what Public Synonyms were invented for.
Just create a public synonym for each object in that users table, and grant Select on the objects to Public.

There's really no need to get complicated about this.
Re: schema [message #345606 is a reply to message #345602] Thu, 04 September 2008 05:07 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
May be i have missed something or you have misinterpreted
me.

I had simply given a scnerio in which we need a schema
with readonly privileges.
That we get in production.

For this we can create a schema.
Grant only select privileges on
diffrent schema tables to view the data.

This kind of user is created in production to view data
in the production nothing else.

Please let me know if my approach is wrong or is thier
any better way to do this.

Your suggestions are welcome.

Regards,
Rajat
Re: schema [message #345609 is a reply to message #345606] Thu, 04 September 2008 05:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member

JRowbottom wrote on Thu, 04 September 2008 05:06


I'm petty sure that this is what Public Synonyms were invented 
for.Just create a public synonym for each object in that users 
table,and grant Select on the objects to Public.

There's really no need to get complicated about this. 


No that is a security risk.
I don't agree.


Regards,
Rajat
Re: schema [message #345610 is a reply to message #345609] Thu, 04 September 2008 05:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How on earth can this pose a bigger risk than sharing a single user?
At least using different users, you will be able to audit every single user's actions!
Re: schema [message #345613 is a reply to message #345563] Thu, 04 September 2008 05:22 Go to previous messageGo to next message
maxis
Messages: 17
Registered: September 2008
Junior Member
Just Littel more about

when i use current_schema = rajat then

i can create table but permit to select

explain Sir thanx
Re: schema [message #345614 is a reply to message #345610] Thu, 04 September 2008 05:24 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I tell you the reason for this.

Suppose you have granted privileges to public.

Now a hacker can create an account in your database and
can get access to that data because it's public.

But if you create a special user for this.You can manage
it very well.You can change it's password ondaily basis.

And granting things to public is not a good approach.

Why you wan't to audit.What will you achieve by auditing it.

Regards,
Rajat
Re: schema [message #345616 is a reply to message #345614] Thu, 04 September 2008 05:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Just Littel more about

when i use current_schema = rajat then
i can create table but permit to select

explain Sir thanx



That depends on what privileges you have given to Rajat.
Give only select privileges no create.

Regards,
Rajat
Re: schema [message #345617 is a reply to message #345609] Thu, 04 September 2008 05:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No that is a security risk.
I don't agree.


Do please enlighten me as to how a public synonym and a GRANT SELECT can constitute a security risk.
What loopholes does it open up?

The only conceivable risk I can think of is that it lets any user who logs on to the database see the data, but as the originl requirement was:
how to make a single schema available to all users log on to database
, then that is obviously inherent in any solution.
Re: schema [message #345618 is a reply to message #345563] Thu, 04 September 2008 05:29 Go to previous messageGo to next message
maxis
Messages: 17
Registered: September 2008
Junior Member
is that not a knowledgeable task?

now in advance business approach audit is most preferable context as for as computer application concern.

Thanx Sir
Re: schema [message #345619 is a reply to message #345614] Thu, 04 September 2008 05:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajatratewal wrote on Thu, 04 September 2008 12:24
I tell you the reason for this.

Suppose you have granted privileges to public.

Now a hacker can create an account in your database and
can get access to that data because it's public.

But if you create a special user for this.You can manage
it very well.You can change it's password ondaily basis.

And granting things to public is not a good approach.

Why you wan't to audit.What will you achieve by auditing it.

Regards,
Rajat

If a hacker is able to create an account, don't you think that he will also be able to grant select privs?
Having a user that is shared amongst several people cries out "write down password, so everyone can see it", especially if you have a password of the day..
Re: schema [message #345620 is a reply to message #345605] Thu, 04 September 2008 05:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@JRowbottom: Unless you have tables with the same name in different schemas and you want to share all of them under the same name.

But maxis did not mention that; this case was included only in rajatratewal's scenario, which may be close/far/totally different than maxis's needs.
Re: schema [message #345621 is a reply to message #345614] Thu, 04 September 2008 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Now a hacker can create an account in your database and
can get access to that data because it's public.

If you read the first post, you will see that the OP WANTS the data to be available to everyone who logs onto the database.
Quote:
how to make a single schema available to all users log on to database


Additionally, if your security has been penetrated to the point where people can connect to your database and create their own accounts, it would take an act of god to stop them having access to your data anyway.
Re: schema [message #345622 is a reply to message #345617] Thu, 04 September 2008 05:31 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes you are right JRowbottom.You have given my answer.

Quote:

The only conceivable risk I can think of is that it lets any
user who logs on to the database see the data



This is the biggest threat myfriend.
Suppose you are working in a bank.Anybody can steal your
customer data.Thier name,address.

For you it can be smaller but this is the biggest threat
in my view.

Regards,
Rajat
Re: schema [message #345623 is a reply to message #345622] Thu, 04 September 2008 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Iappreciate that I repeat myself again, but the OP WANTS the data to be available to everyone on the database.

That's the purpose of this thread - totell him how to achiee that.

Any solution that achiees this aim will suffer from the same issue.
Re: schema [message #345624 is a reply to message #345614] Thu, 04 September 2008 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why you wan't to audit.What will you achieve by auditing it.

By auditing, you can see WHO has been looking at the data. That way, if there is a leak of information, you can narrow down which users have had access to the data.
Re: schema [message #345625 is a reply to message #345622] Thu, 04 September 2008 05:37 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

If a hacker is able to create an account,
don't you think that he will also be able to grant select privs?
Having a user that is shared amongst several people cries out "write down password, so everyone can see it", especially if you have a password of the day..



Both are different things.If you can create user it does not mean you can grant privileges.

I hope we are off the track now and we should stop this
discussion here.It's of no use to argue whether i am right
or you are right.I have given my comments.You can give
your.OP problem is solved.

Let's solve another problem.

So cheers Frank.

Regards,
Rajat
Re: schema [message #345633 is a reply to message #345613] Thu, 04 September 2008 06:03 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
maxis wrote on Thu, 04 September 2008 12:22
Just Littel more about

when i use current_schema = rajat then

i can create table but permit to select

explain Sir thanx


When you have privilege to create any table, you may do so in any schema.
When you do not have privilege to select any table, you may fail with "ORA-00942: table or view does not exist" error.

Examine output of the following queries:
SELECT * FROM user_sys_privs;
SELECT * FROM user_role_privs;
SELECT * FROM user_tab_privs;
and compare it with the documented ones, available in the link I posted earlier.
Previous Topic: Date - Date
Next Topic: update statement
Goto Forum:
  


Current Time: Wed Dec 07 20:31:27 CST 2016

Total time taken to generate the page: 0.23982 seconds