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