Grants On Remote User Tables.., Table does not exist


i'm working with 9i AS, R2 .. i hav two databases located in two servers
i have user b in database b that user have grants and read some of the user a in database a tables ..
the two databases in linked through Database Link...

when i import user B tables to database B an error occurs.

[QUOTE]IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist[QUOTE]

how can i virsualy mounted the remote user in Local database...


this is all the log>>>

[CODE]Username:
Connected to: Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by PAY, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing PAY's objects into CVPAY
. . importing table "ACCOUNTS" 137 rows imported
. . importing table "ALLDED_DEDUCTIONS" 260 rows imported
. . importing table "ALLOWS" 1949 rows imported
. . importing table "ALLSDEDS" 282 rows imported
. . importing table "BANKS" 10 rows imported
. . importing table "CENTER" 10 rows imported
. . importing table "DEDUCTS" 1478 rows imported
. . importing table "DMASTER" 1174 rows imported
. . importing table "DTRUNCATE" 0 rows imported
. . importing table "ECASH" 0 rows imported
. . importing table "EMPALLDED" 67702 rows imported
. . importing table "EMPS" 2 rows imported
. . importing table "EMPS1" 0 rows imported
. . importing table "EMP_ALL_DEDS" 1538 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UPDATE ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT DELETE ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
. . importing table "EMP_JOB" 1 rows imported
. . importing table "FACTORS" 232 rows imported
. . importing table "FACTOR_TYPES" 22 rows imported
. . importing table "FORM_HEDAER" 0 rows imported
. . importing table "GRADE" 33 rows imported
. . importing table "HDMASTER" 0 rows imported
. . importing table "HFACTORS" 1215 rows imported
. . importing table "HLOANS_DETS" 5678 rows imported
. . importing table "H_ALLDED" 283 rows imported
. . importing table "H_ALLO" 126 rows imported
. . importing table "H_ALL_DED" 8192 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT DELETE ON "H_ALL_DED" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "H_ALL_DED" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UPDATE ON "H_ALL_DED" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
. . importing table "H_DEDS" 157 rows imported
. . importing table "JOBS" 0 rows imported
. . importing table "LABOUR" 0 rows imported
. . importing table "LOANSES" 47 rows imported
. . importing table "LOANS_DETS" 492 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "LOANS_DETS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UPDATE ON "LOANS_DETS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT DELETE ON "LOANS_DETS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
. . importing table "MANAGE" 171 rows imported
. . importing table "MASTER" 171 rows imported
. . importing table "OVER" 0 rows imported
. . importing table "OVER1" 161 rows imported
. . importing table "OVERTAX" 158 rows imported
. . importing table "PARM" 116 rows imported
. . importing table "SALARY" 124832 rows imported
. . importing table "SALARY1" 12036 rows imported
. . importing table "SALHAF" 512 rows imported
. . importing table "SALNET" 512 rows imported
. . importing table "SALNET_H" 0 rows imported
. . importing table "SAL_HAF_H" 0 rows imported
. . importing table "SAS" 1 rows imported
. . importing table "SECTIONS" 25 rows imported
. . importing table "SECU" 1 rows imported
. . importing table "TAFKITAS" 33 rows imported
. . importing table "TAXSES" 0 rows imported
. . importing table "TAXZAKS" 512 rows imported
. . importing table "TRUNCATES" 0 rows imported
. . importing table "UNIT" 0 rows imported
. . importing table "WINDOWS" 3 rows imported
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "FACTORS" ADD FOREIGN KEY ("EMP_NO") REFERENCES "PERSONEL"."EMP"
"LOYEES" ("EMP_NO") ENABLE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "H_ALL_DED" ADD FOREIGN KEY ("EMP_NO") REFERENCES "PERSONEL"."E"
"MPLOYEES" ("EMP_NO") ENABLE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "ECASH" ADD FOREIGN KEY ("EMP_NO") REFERENCES "PERSONEL"."EMPLO"
"YEES" ("EMP_NO") ENABLE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_002" BEFORE INSERT ON PAY.H_ALLO REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" INSERT INTO H_ALLDED(GRADE,CODE,NAME,TAX_IND,DAY_IND,VAL,ACC_NO,FLAG)"
" VALUES (:NEW.GRADE,:NEW.CODE,:NEW.NAME,:NEW.TAX_IND,:NEW.DAY_IND,:NEW.VAL"
",:NEW.ACC_NO,:NEW.FLAG);"
"END;"
""
""
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_003" BEFORE UPDATE ON PAY.H_ALLO REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW BEGIN"
"UPDATE H_ALLDED SET VAL=:NEW.VAL,NAME=:NEW.NAME,TAX_IND=:NEW.TAX_IND,DAY_IN"
"D=:NEW.DAY_IND,FLAG=:NEW.FLAG "
"WHERE GRADE=:NEW.GRADE AND CODE=:NEW.CODE AND ACC_NO=:NEW.ACC_NO;"
"END;"
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_004" BEFORE DELETE ON PAY.H_ALLO REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW BEGIN"
" DELETE FROM H_ALLDED"
" WHERE GRADE=:OLD.GRADE AND CODE=:OLD.CODE AND ACC_NO=:OLD.ACC_NO;"
"END;"
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_005" AFTER DELETE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
"DELETE FROM PAY.EMP_ALL_DEDS "
"WHERE EMP_NO = :NEW.EMP_NO AND :NEW.BAN_CODE=0;"
"END;"
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_006" AFTER DELETE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
"DELETE FROM PAY.LOANS_DETS "
"WHERE EMP_NO = :NEW.EMP_NO AND :NEW.BAN_CODE=0;"
"END;"
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_007" AFTER DELETE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
"DELETE FROM PAY.H_ALL_DED "
"WHERE EMP_NO = :NEW.EMP_NO AND :NEW.BAN_CODE=0;"
"END;"
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_108" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'ä' THEN"
"UPDATE PAY.LOANS_DETS SET IND = 'ä'"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_109" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'á' THEN"
"UPDATE PAY.LOANS_DETS SET IND = ' '"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_110" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'ä' THEN"
"UPDATE PAY.EMP_ALL_DEDS SET IND = 'ä'"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_112" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'á' THEN"
"UPDATE PAY.EMP_ALL_DEDS SET IND = ' '"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_228" BEFORE INSERT ON PAY.H_DEDS REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" INSERT INTO H_ALLDED(GRADE,CODE,NAME,VAL,ACC_NO,FLAG)"
" VALUES (:NEW.GRADE,:NEW.CODE,:NEW.NAME,:NEW.VAL,:NEW.ACC_NO,:NEW.FLAG);"
"END;"
""
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_231" BEFORE UPDATE ON PAY.H_DEDS REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW "
""
""
""
"BEGIN"
" UPDATE H_ALLDED SET VAL=:NEW.VAL,NAME=:NEW.NAME,FLAG=:NEW.FLAG"
" WHERE GRADE=:NEW.GRADE AND CODE=:NEW.CODE AND ACC_NO=:NEW.ACC_NO;"
"END;"
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_232" BEFORE DELETE ON PAY.H_DEDS REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW "
""
""
"BEGIN"
" DELETE FROM H_ALLDED"
" WHERE GRADE=:OLD.GRADE AND CODE=:OLD.CODE AND ACC_NO=:OLD.ACC_NO;"
"END;"
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
Import terminated successfully with warnings.
[CODE]

thanks for help...

sorry it seems i post in the wrong place...

Regards
LVL 4
linuxoAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
Database links allow two (or more) different Oracle databases to share information with each other, but you cannot grant access on a table in database "a" to a user (or role) that only exists in database "b".  Grants can only be given to users or roles within each database.

When you use a database link, like from database "a" to get to tables in database "b", you have to connect to database "b" with a username that is valid in database "b".  Through the link then, you will be able to have the same access as that uses does when that user connects locally to database "b".

It is not clear to me why you are doing an export and import.  If you plan to use the link, you don't need to export the data from one database to the other, right?
0
 
Ivo StoykovCommented:
Hello linuxo,

error is streightforward I think.

you have either to specify existing schema or to create one with this name...

HTH

I
0
 
linuxoAuthor Commented:
Hello,

the question is who can i specify a remote schema ...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Ivo StoykovCommented:
Hi linuxo,

you cannot import tables localy and use a remote shcema. You could link tables (grant) to remote schema or remote object to local schema, but the owner cannot be a remote user - if I understood correctly the question.

regards

I
0
 
linuxoAuthor Commented:
Hello,

the Idea is to create a new user in database b with the same  database user in (a) schema and relations.
but in a the primary schema is existed what make everything goes fine, user B should use the primary user a in a database...

ok letme c.

0
 
linuxoAuthor Commented:
hi,

the problem is solved with....

I use the Database link and create synonym for schema i want to by available...
every thing is fine now..
when i import the DUMP file everything is ok...


regards
0
 
Mark GeerlingsDatabase AdministratorCommented:
So, can you accept one (or more) of the comments here as the "answer" to close this question?  Or, do you plan to ask Community Support to cancel this question and refund your points?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.