Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

giving user rights to access another schema

Status
Not open for further replies.

rk68

Programmer
Jul 15, 2003
171
IN
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
 
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
 
Try something like this:

Code:
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
[3eyes]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top