Oracle & making a reference to ALL_USERS(USERNAME)

Question

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

Answer 1

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

Answer 2

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;