英文:
Importing sql file to a new user throws insufficient privilege error in sqldeveloper
问题
我已在AWS Oracle RDS中创建了一个用户，并拥有我的表、函数和所有其他代码。
由于我需要与此数据库结构相同的副本，我使用SQL Developer将其导出到SQL文件中。
创建了另一个用户，现在想将SQL文件导入到这个新用户，但它报告了权限不足的错误。
ORA-01031: 权限不足
- 00000 - "权限不足"
*原因：尝试执行数据库操作而没有必要的权限。
*操作：请向您的数据库管理员或指定的安全管理员申请授予您必要的权限。
我已经授予了用户权限。这是因为在创建表语句中存在"user1"吗？
CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )
请您帮忙看一下。
谢谢，
Manju
英文:
I have created a user in AWS oracle RDS and have my tables,functions and all other code.
As I needed the same replica of this DB structure, I exported it to an sql file using SQL Developer.
Created another user, and now want to import the sql file to this new user, but it is throwing insufficient privilege error.
> ORA-01031: insufficient privileges
>
> 01031. 00000 - "insufficient privileges"
>
> *Cause: An attempt was made to perform a database operation without
> the necessary privileges.
>
> *Action: Ask your database administrator or designated security
> administrator to grant you the necessary privileges
I have granted the privileges to the user. Is it because of the user1 which is present in the create table statement?
CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )
Could you please help.
Thank you,
Manju
答案1
得分: 2
CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )
这个语句在 user1 模式下创建了一张表。如果当前模式不是 user1 或者当前用户没有CREATE ANY TABLE权限，该命令将失败。
如何让不同用户拥有相同模式但不同的数据？
一个模式是由一个用户拥有的一组对象组成的。一个模式的名称与拥有它的用户的名称相同。为了实现你的目标，你只需要从所有的SQL语句中移除 user1.，然后以USER2（或其他用户）的身份运行脚本。
以后参考，SQL Developer导出DDL工具有一个用于包含模式名称的复选框；默认情况下是选中的（即包括），但如果我们想要以不同的用户身份运行脚本，我们可以取消选中它。
英文:
> CREATE TABLE "user1"."MBOMCOMPONENTS" ( "COMPONENTNO" VARCHAR2(14 BYTE), "PLANT" VARCHAR2(50 BYTE) )`
This statement builds a table in the "user1" schema. The command will fail if the current schema is not "user1" and the current user lacks the CREATE ANY TABLE privilege.
> how can we have separate users with same schema and different data?
A schema is a set of objects owned by a user. A schema has the same name as the user which owns it. To achieve your aim all you need to do is remove "user1". from all the SQL statements, then run the script as USER2 (or whoever).
For future reference, the SQL Developer Export DDL tool has a checkbox for including the schema name; by default it is ticked (i.e. include) but we can unset it if we will want to run the scripts as different users.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论