[oracle 10g]Access rights after import
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;
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 !
no, dont grant to public - that is bad, do it properly
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.
this space intentionally left blank
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.
Click Here to Expand Forum to Full Width