2

So i need to do a mapping from a Employee table (idEmployee, name, etc..) to a real user with a account created. I decided to add a table Mapping_Employee_User(idEmployee, userName) like below

CREATE TABLE Mapping_Employee_User( 
    idEmployee NUMBER(6)
        CONSTRAINT FK_Mapping_Employee_User1 REFERENCES Employee (idEmployee),
    userName VARCHAR2(30 BYTE)
        CONSTRAINT FK_Mapping_Employee_User2 REFERENCES ALL_USERS(USERNAME),
    CONSTRAINT PK_Mapping_Employee_User PRIMARY KEY (idEmployee, userName)
);

But i am getting a "ORA01031 insufficient privileges Cause: An attempt was made to change the current username or password..." But I am not actually doing that, I just want to make a reference.

As a note: I have full rights with this user

Logged as SYS I can see that the actual table is named "USER$", and I cant find table ALL_USERS...anyway how do I do this kind of reference??

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Iftimie Vlad
  • 199
  • 1
  • 2
  • 12

2 Answers2

5

ALL_USERS and USER$ are both system tables/views. They are maintained at a low level by Oracle itself. At a level too low to enforce those constraints. You simply can't do what you're trying to do.

(Think of it this way: what'd happen if you tried to DROP USER bob? Do you expect Oracle to enforce your foreign key constraint? What'd happen if your user tablespace is offline?)

edit: I suggest you just leave off the foreign key on userName. You may want to schedule some job to compare the users in Mapping_Employee_User vs. DBA_USERS to make sure they stay in sync. Alternatively, you may want to manage your Oracle users with, say, LDAP (which I hear is possible).

derobert
  • 49,731
  • 15
  • 94
  • 124
  • I've changed one line and now get a smaller error: CONSTRAINT CONSTRAINT FK_Mapping_Employee_User2 REFERENCES SYS.USER$(NAME) . Now it just says table does not exist, but this select works just fine: SELECT NAME FROM SYS.USER$... So what? How do i skip this one? – Iftimie Vlad Dec 02 '11 at 21:54
  • @IftimieVlad: The documentation says you're not supposed to touch SYS.USER$ at all. I doubt there is a way around that error. At least not a way sane enough to think about (I mean, as long as you're mucking with the data dictionary, maybe you can find how constraints are stored and insert it directly. But don't do that.). – derobert Dec 02 '11 at 22:06
  • Yes you're right... I decided to use the table without the referencing part. – Iftimie Vlad Dec 02 '11 at 22:36
0

ALL_USERS is a view and not a table by itself.

grant select on all_users to USERNAME;

should suffice. if you are still getting ORA-01031 it's probably because the user doesn't have the CREATE TABLE privilege:

grant create table to USERNAME;

Daniel Haviv
  • 1,036
  • 8
  • 16