SQL User Acces to Schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL User Acces to Schema

  1. #1
    Join Date
    Oct 2000
    Posts
    21

    Red face

    Hello
    Could anyone tell how to give to access to one schema
    I mean I have schema name APPS , I want create sql user
    give him access to APPS schema tables only
    Thankx
    Sanjay
    sanjay

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    1. create role called apps_user
    2. grant select, insert, update, delete on tablename to apps_user (for every table in apps schema);
    3. create user new_user_name
    4. grant connect, apps_user to new_user_name
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    21

    Angry

    Well this not easy APPS schema itself is composed or say have access to another 10 schema GL PO AP ( This is Oracle Applications) so APPS schema might have more than 1000 tables This approach u said seems very difficult any other suggestions
    Thaks
    Sanjay
    sanjay

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    What's so difficult? Create a script off dba_tables, run the script, you're done.

    set trimspool on
    set pages 0
    set linesize 222
    spool grants.sql
    select 'grant select, insert, update, delete on ' || table_name || ' to apps_user;'
    from user_tables;
    spool off
    <edit grants.sql to get rid of extra stuff>
    @grants.sql

    If you spend 5 minutes on it, it will be a lot.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width