甲骨文公司引用 ALL_USERS(USERNAME)
所以我需要从员工表（idEmployee、姓名等）到创建帐户的真实用户进行映射。我决定添加一个表 Mapping_Employee_User(idEmployee, userName) 如下所示
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)
);
但我收到“ORA01031 权限不足”原因：尝试更改当前用户名或密码...”但我是其实我并没有这么做，只是想提供一个参考。
请注意：我对这个
以 SYS 身份登录的用户拥有完全权限，我可以看到实际的表名为“USER$”，但我找不到表 ALL_USERS...无论如何，我该如何进行这种引用？
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??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
ALL_USERS和USER$都是系统表/视图。它们由 Oracle 本身维持在较低水平。水平太低，无法执行这些限制。你根本无法做你想做的事。（这样想：如果您尝试
DROP USER bob，会发生什么？您希望 Oracle 强制执行您的外键约束吗？如果您的用户表空间脱机，会发生什么？）编辑：我建议你忽略 userName 上的外键。您可能需要安排一些作业来比较
Mapping_Employee_User与DBA_USERS中的用户，以确保它们保持同步。或者，您可能希望使用 LDAP（我听说这是可能的）等方式来管理您的 Oracle 用户。ALL_USERSandUSER$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_Uservs.DBA_USERSto make sure they stay in sync. Alternatively, you may want to manage your Oracle users with, say, LDAP (which I hear is possible).ALL_USERS 是一个视图，本身并不是一个表。
grant select on all_users to USERNAME;就足够了。如果您仍然遇到 ORA-01031，可能是因为用户没有 CREATE TABLE 权限：
grant create table to USERNAME;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;