giving user rights to access another schema

[rk68]

Hi,
I have created a USER and want to give rights to this USER to access tables, procedures from another schema.
The user should have rights only to read, write, delete, insert records, execute procedure.
User should not be allowed to create/drop table.

How can I do this as I am new to DBA.

TIA,
RAJ

 

[Beilstwh]

you have to grant the rights on every object in the other schema. The grants must be issued by the other schema user

grant select,insert,update,delete on my_table to new_user;
grant select on my_sequence to new_user;
grant execute on my_procedure to new_user;


Bill
Lead Application Developer
New York State, USA

 

[LKBrwnDBA]

Try something like this:

DEF Owner_='SCOTT'
DEF Newusr='NewUser'
SET LIN 120 PAGES 0 TERM OFF ECHO OFF VER OFF
SPO Grant_ddl.sql
SELECT CASE
            WHEN Otype = 'TABL'
            THEN
                  'GRANT SELECT, INSERT,UPDATE,DELETE ON '
                || Owner|| '.'|| Oname|| ' TO &&NewUsr;'
            WHEN Otype IN ( 'VIEW', 'SEQU' )
            THEN
               'GRANT SELECT ON ' || Owner || '.' || Oname || ' TO &&NewUsr;'
            WHEN Otype IN ( 'PROC', 'FUNC' )
            THEN
               'GRANT EXECUTE ON ' || Owner || '.' || Oname || ' TO &&NewUsr;'
         END
            AS Ddl
  FROM ( SELECT Owner, SUBSTR ( Object_Type, 1, 4 ) Otype, Object_Name Oname
           FROM Dba_Objects
          WHERE Owner = 'OWNER_'
            AND ( Object_Type IN ( 'TABLE', 'VIEW', 'SEQUENCE' )
             OR SUBSTR ( Object_Type, 1, 4 ) IN ( 'PROC', 'PACK', 'FUNC' ) ) )
ORDER BY 1 Desc;
SPO OFF
SET ECHO ON
@@Grant_ddl

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb