Home » RDBMS Server » Server Administration » Access to another schema
Access to another schema [message #188964] Tue, 22 August 2006 10:35 Go to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Is it possible to give access to the user to another schema ?
The user should have the complete access to the schema. Can this be done ?
Re: Access to another schema [message #188994 is a reply to message #188964] Tue, 22 August 2006 12:48 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Either that schema owner or one with DBA rights will have to use the GRANT command to give access for each object to the schema you want to access from.
Re: Access to another schema [message #189987 is a reply to message #188994] Mon, 28 August 2006 23:03 Go to previous messageGo to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Thanks for the reply.

But I have more than 100 tables in the schema. It will be bit difficult to grant access to each and every table. Is there any way that we can give access using a single command or a stored procedure?
Re: Access to another schema [message #190091 is a reply to message #189987] Tue, 29 August 2006 04:47 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
There is way to do this.

You have to create script which is very easy....

You have to create spool file with .sql extension.

connect as user on whose object we have to give complete access to other user

run below script

Select 'grant select on' ||Tname|| ' to <username who need access>;' from tab ;

e.g Select 'grant select on' ||Tname|| 'to scott;' from tab;

you will get result of above query in .sql (extension) spool file, now run this spool file.

Now connect with the user to whom you want to give access... to check that user got access or not..

cheers..!
sunilkumar

[Updated on: Tue, 29 August 2006 05:07]

Report message to a moderator

Re: Access to another schema [message #190112 is a reply to message #190091] Tue, 29 August 2006 05:21 Go to previous message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Thanks Sunil. It was a good idea.

It worked with the following command

select 'grant select on '|| tname || ' to Scott;'from tab;

[Updated on: Tue, 29 August 2006 05:21]

Report message to a moderator

Previous Topic: writing by DBWn
Next Topic: REF dependency
Goto Forum:
  


Current Time: Thu Dec 08 18:24:22 CST 2016

Total time taken to generate the page: 0.11074 seconds