英文:
Why is CREATE table missing from DDL export script in SQL Developer?
问题
I'm trying to retrieve the CREATE table statement for multiple tables from oracle SQL Developer so I can run it in SQL Management to create new tables.
However, when highlighting multiple tables and right clicking > Quick DLL> Save to File, my file looks like this:
GRANT INSERT ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT UPDATE ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT SELECT ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT DELETE ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
GRANT INSERT ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
GRANT SELECT ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
GRANT UPDATE ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
Why is there no CREATE table statements in here?
英文:
I'm trying to retrieve the CREATE table statement for multiple tables from oracle SQL Developer so I can run it in SQL Management to create new tables.
However, when highlighting multiple tables and right clicking > Quick DLL> Save to File, my file looks like this:
GRANT INSERT ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT UPDATE ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT SELECT ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT DELETE ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
GRANT INSERT ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
GRANT SELECT ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
GRANT UPDATE ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
Why is there no CREATE table statements in here?
答案1
得分: 1
我已连接为Opsweb，我只能看到OPSR用户下的表格。
无法查看其他用户对象的DDL。SQL Developer在后台使用dbms_metadata，根据文档的描述：
Oracle元数据模型的对象视图实施安全性如下：
- 非特权用户只能查看自己对象的元数据。
- 非特权用户还可以检索授予他们的公共同义词、系统权限以及授予他们或由他们授予他人的对象权限。这也包括授予PUBLIC的权限。
- 如果调用者请求他们没有特权检索的对象，不会引发异常；对象只是不会被检索。
- 如果非特权用户被授予对其他模式中对象的某种访问权限，他们将能够通过Metadata API检索授予规范，但不能检索对象的实际元数据。
正如上述的最后一条所说，你可以获取授权信息（这就是你现在看到的），但无法获取实际的元数据。
如果你的用户被授予select_catalog_role，你就能够获取OPSR用户对象的DDL，但你需要向你的DBA请求权限，或者更容易的方法可能是连接为该用户，或者请求有权限的其他人执行提取操作。
英文:
> I'm connected as Opsweb and the only tables I can see are under the OPSR user.
You can't see the create DDL for other user's objects. SQL Developer is using dbms_metadata in the background, and from the documentation:
> The object views of the Oracle metadata model implement security as follows:
> - Nonprivileged users can see the metadata of only their own objects.
> - Nonprivileged users can also retrieve public synonyms, system privileges granted to them, and object privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.
> - If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.
> - If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.
and so on. As the last bullet above says, you cen get the grants - which is what you are seeing now - but not the actual metadata.
If your user was granted the select_catalog_role you would be able to get the DDL for OPSR's objects, but you'd have to ask your DBA for that and it would probably be easier to connect as that user, or ask someone else who can to do that to perform the extract for you.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论