Home » SQL & PL/SQL » Client Tools » grant user schema to another user (11.2.0.1)
grant user schema to another user [message #493664] Wed, 09 February 2011 05:11 Go to next message
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 #493665 is a reply to message #493664] Wed, 09 February 2011 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From user A execute:
select 'grant select on '||table_name||' to B;' from user_tables;

Copy the result and execute it.
Do the same thing for procedural object and execute privilege (querying user_objects).

Regards
Michel
Re: grant user schema to another user [message #493668 is a reply to message #493665] Wed, 09 February 2011 05:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
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

Re: grant user schema to another user [message #493796 is a reply to message #493672] Wed, 09 February 2011 23:54 Go to previous message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you for all who are all supported for me for solving the issue.
Previous Topic: SQPLUS - How to to see specs/body of package procedures ?
Next Topic: dbms_metadata.get_ddl in Sql*Plus
Goto Forum:
  


Current Time: Sat Feb 24 23:48:06 CST 2018

Total time taken to generate the page: 0.24328 seconds