grant user schema to another user [message #493664] |
Wed, 09 February 2011 05:11 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have two users say A and B. I have all the tables,views,indexes, types,procedures,packages etc. User B wants to access all the objects from user A.
Please help me.
|
|
|
|
Re: grant user schema to another user [message #493668 is a reply to message #493665] |
Wed, 09 February 2011 05:23 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Thank you michel.
I have done same thing which u said.
create or replace procedure p1 is
BEGIN
FOR Rec IN (SELECT object_name, object_type FROM all_objects
WHERE owner='userA'
AND object_type IN ('TABLE','VIEW','TYPE','TYPE BODY','INDEX','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') ) LOOP
IF Rec.object_type IN ('TABLE','VIEW','TYPE','TYPE BODY','INDEX') THEN
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON <userA>.'||Rec.object_name||' TO userB';
ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') THEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON <userA>.'||Rec.object_name||' TO userB';
END IF;
END LOOP;
END;
/
I created the above procedure and successfully created but I am not able access the packages becoz i m picking the data from packages and it is giving invalid name pattern for types.
I want to access the tables,packages without giving the schema name from UserB.
Did i miss anything or need to do anything from my side.
Please advice.
|
|
|
Re: grant user schema to another user [message #493672 is a reply to message #493668] |
Wed, 09 February 2011 05:27 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Having privileges on an object doesn't mean you don't need to specify the schema when referencing the object, they are seperate concepts.
If you want to avoid using the schema name you can either:
a) create synonyms for each object
b) use alter session set current_schema
EDIT: typo
[Updated on: Thu, 10 February 2011 03:38] Report message to a moderator
|
|
|
|