Professional Web Applications Themes

Changing the default schema for a user - Oracle Server

Greetings All, I have created a oracle instance in ORACLE 9i. I have created a user "admin" and create a lot of tables and object belongs to user "admin". And now, I need to create another user "operator" who just only can SELECT, INSERT, UPDATE and DELETE on all tables of schema "admin". Therefore, I grant SELECT, INSERT, UPDATE and DELETE of all objects under "admin" to "operator" and using the following command when "operator" login: ALTER SESSION SET CURRENT_SCHEMA= admin; it is successfully the "operator" operate on all tables of "admin" but cannot ALTER tables, but it is very ...

  1. #1

    Default Changing the default schema for a user

    Greetings All,

    I have created a oracle instance in ORACLE 9i.
    I have created a user "admin" and create a lot of tables and object
    belongs to user "admin". And now, I need to create another user
    "operator" who just only can SELECT, INSERT, UPDATE and DELETE on all
    tables of schema "admin".

    Therefore, I grant SELECT, INSERT, UPDATE and DELETE of all objects
    under "admin" to "operator" and using the following command when
    "operator" login:

    ALTER SESSION SET CURRENT_SCHEMA= admin;

    it is successfully the "operator" operate on all tables of "admin" but
    cannot ALTER tables, but it is very trouble that everyone login as
    "operator" needs to ALTER SESSION. Can I change the "operator" login's
    default schema as "admin"!?
    or is there any other methods to solve my scenario, e.g. synonym (I
    don't know how to use synonym) !?

    Thomas Lam
    Thomas Guest

  2. #2

    Default Re: Changing the default schema for a user

    Comments and answers are inline.

    "Thomas Lam" <com> a écrit dans le message news:
    google.com... 

    Good practice.
     
     

    That's it ! Use synonyms.
    The way to use them is fairly simple :
    log as operator
    CREATE SYNONYM my_admin_table for admin.my_admin_table;

    This way operator can refer to admin's tables as if they were in is own
    schema
    with the security policy demanded (only DML, no DDL)

    If multiple users have to refer to admin's tables, either you create PUBLIC
    synonyms (logged as admin
    and CREATE PUBLIC SYNONYM, take a look a SQL reference)
    or you create a set of synonyms for each user as you did for operator.

    Hope this helps.

    Alkos


    Alkos Guest

  3. #3

    Default Re: Changing the default schema for a user

    Hello!

    You can set current schema in the after logon trigger

    example

    CREATE OR REPLACE TRIGGER afterlogon
    AFTER LOGON ON DATABASE
    BEGIN
    IF USER ='OPERATOR' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=ADMIN';
    END IF;
    END;

    Note that current schema is "admin"
    select sys_context( 'userenv', 'current_schema' ) from dual;


    but you are still the user "operator"
    select sys_context( 'userenv', 'username' ) from dual;


    You can also solve it with synonyms as alkos said but i personally avoid
    using them.


    Regards,
    Janne!


    Thomas Lam wrote:
     

    Jan Guest

  4. #4

    Default Re: Changing the default schema for a user

    com (Thomas Lam) wrote in message > 

    create or replace trigger after_logon_operator
    after logon on operator.schema
    begin
    execute immediate 'alter session set current_schema = admin';
    end;
    /

    No need for synonyms in this case!

    jan
    Jan Guest

  5. #5

    Default Re: Changing the default schema for a user

     


    Additionally -besides the two mentioned methods- you can of course
    qualify the tables with the schema.object notation like so:

    select * from admin.table_name where bla='foo';


    Also, operator is a reserved word and I'd refrain from using it as a
    user's name:

    select * from v$reserved_words where keyword='OPERATOR'


    hth,
    Rene


    --
    Rene Nyffenegger
    http://www.adp-gmbh.ch
    Rene Guest

  6. #6

    Default Re: Changing the default schema for a user


    "Rene Nyffenegger" <ch> a écrit dans le message de
    news: bphpqp$1ohjv8$news.uni-berlin.de... 
    >
    >
    > Additionally -besides the two mentioned methods- you can of course
    > qualify the tables with the schema.object notation like so:
    >
    > select * from admin.table_name where bla='foo';
    >
    >
    > Also, operator is a reserved word and I'd refrain from using it as a
    > user's name:
    >
    > select * from v$reserved_words where keyword='OPERATOR'
    >
    >
    > hth,
    > Rene
    >
    >
    > --
    > Rene Nyffenegger
    > http://www.adp-gmbh.ch[/ref]

    Well spotted, Rene.
    Guess you're from Switzerland, am i right ?
    People there are wellknown for their accuracy.

    Cheers,
    Alkos





    Alkos Guest

  7. #7

    Default Re: Changing the default schema for a user

     
    >>
    >>
    >> Additionally -besides the two mentioned methods- you can of course
    >> qualify the tables with the schema.object notation like so:
    >>
    >> select * from admin.table_name where bla='foo';
    >>
    >>
    >> Also, operator is a reserved word and I'd refrain from using it as a
    >> user's name:
    >>
    >> select * from v$reserved_words where keyword='OPERATOR'
    >>
    >>
    >> hth,
    >> Rene
    >>
    >>
    >> --
    >> Rene Nyffenegger
    >> http://www.adp-gmbh.ch[/ref]
    >
    > Well spotted, Rene.
    > Guess you're from Switzerland, am i right ?
    > People there are wellknown for their accuracy.[/ref]


    Yes, I am swiss. Don't know if that makes me any more accurate as anyone
    else, though.

    Rene


    --
    Rene Nyffenegger
    http://www.adp-gmbh.ch
    Rene Guest

  8. #8

    Default Re: Changing the default schema for a user


    "Rene Nyffenegger" <ch> a écrit dans le message news:
    bq9ui1$20fvn8$news.uni-berlin.de... [/ref][/ref]
    but [/ref][/ref]
    login's 
    > >
    > > Well spotted, Rene.
    > > Guess you're from Switzerland, am i right ?
    > > People there are wellknown for their accuracy.[/ref]
    >
    >
    > Yes, I am swiss. Don't know if that makes me any more accurate as anyone
    > else, though.
    >
    > Rene
    >
    >
    > --
    > Rene Nyffenegger
    > http://www.adp-gmbh.ch[/ref]

    Nevermind, it was just an attempt to be funny . . .
    I was refering William Tell's famous way to slice apples.

    Cheers
    Alkos


    Alkos Guest

  9. #9

    Default Re: Changing the default schema for a user

    According to Oracle ...
    "This setting changes the current schema, but it does not change the
    session user or the current user, nor does it give you any additional
    system or object privileges for the session. "

    So you will still need to grant appropriate object privileges.

    You may also be interested in
    http://www.ixora.com.au/q+a/0105/05154451.htm
    Mike


    Jan Korecki <se> wrote in message news:<3fbb5cf3$0$97839$bahnhof.se>... [/ref]
    Michael Guest

Similar Threads

  1. changing default ssh shell?
    By G. in forum Mac Applications & Software
    Replies: 6
    Last Post: October 12th, 04:37 PM
  2. Changing default browser
    By Tamara T in forum Adobe Photoshop Elements
    Replies: 2
    Last Post: September 21st, 02:25 PM
  3. Start Menu - All Programs - All users/Current user (Default user)
    By Gary in forum Windows Setup, Administration & Security
    Replies: 2
    Last Post: August 11th, 05:52 AM
  4. Replies: 3
    Last Post: July 11th, 04:26 PM
  5. Replies: 0
    Last Post: July 9th, 11:13 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139