[oracle 10g]Access rights after import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: [oracle 10g]Access rights after import

  1. #1
    Join Date
    Sep 2008
    Posts
    3

    [oracle 10g]Access rights after import

    Hello,

    I am a beginner with Oracle so I have a newbee question ...

    One of our customer send us an dump of his database (DMP file created with command EXP). In his database, tables are owned by user AAA.

    I created a user AAA and a user BBB then I (successfully) imported the dump in our database with IMP command.

    Now, when I run the command "select * from tableXX;" with user AAA, it shows the right result.
    However when I run the same command from user BBB or SYS, I got the following error: "ORA-00942: table or view does not exist"

    How can I grant the right privilegies to BBB ??

    Thanks for the support !

    For information I created both others with the same script :

    create user AAA identified by empjob
    default tablespace TS_1
    temporary tablespace TS_TEMP;


    grant create session, alter session to AAA;
    grant create cluster, create database link, create synonym to AAA;
    grant create sequence, create table, create view to AAA;
    grant create public database link, drop public database link to AAA;

    grant create public synonym, drop public synonym to AAA;

    grant create trigger, drop ANY trigger to AAA;

    grant restricted session to AAA;

    alter user AAA quota unlimited on TS_1;
    alter user AAA quota unlimited on TS_2;

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Talking Select any table

    1) You could GRANT SELECT ANY TABLE to BBB;

    2) OR you can login as user AAA and "grant select on {table name} to BBB;" for every table and view;



    PS: When login as BBB you will need to code your queries prefixing the table names with the schema name like this:

    SELECT * FROM AAA.TableXX;

    (Unless you create public or private synonyms for each table or view).
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Sep 2008
    Posts
    3
    LKBrwn_DBA,

    Thanks for the quick and helpful answer !

    I guess I will put all tables "PUBLIC" and create a public synonyms.
    That may not be the safest but it will be easier for testing third party access.

    Thanks again for the support !

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no, dont grant to public - that is bad, do it properly

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    What Davey means is that you should create a role with all of the privileges that you want to
    grant and then grant that role to another user i.e. "grant aaa_user to bbb;"

    I included a script that you can use to create the role with
    all of the grants. You can edit it it to do what you want.
    Attached Files Attached Files
    this space intentionally left blank

  6. #6
    Join Date
    Sep 2008
    Posts
    3
    gandolf989,

    Thank you for the script, I will try to understand it and modify it to match my needs.

    I tested with "public", it works fine.
    I will try your way too and let you know.

    Regards

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