[oracle 10g]Access rights after import

[JFE]

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;

[LKBrwn_DBA]

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).

[JFE]

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 !

[davey23uk]

no, dont grant to public - that is bad, do it properly

[gandolf989]

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.

[JFE]

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