如何获取共享给其他用户的数据库对象的DDL？ [英] How to get DDL for database objects shared to other user?

问题描述

我们正在提取Sybase ASE 15.5版的数据库DDL。我们在Java中创建了一个执行ddl命令的示例。我们使用Sybase提供的ddlgen实用程序通过以下命令获得ddl：

We are working on extracting database DDL of Sybase ASE 15.5 version. We have one sample created in java that execute ddl commands. We got ddl using ddlgen utility provided by Sybase with commands :

java -cp "mypath/lib/jconn4.jar;mypath/lib/dsparser.jar;mypath/lib/DDLGen.jar" com.sybase.ddlgen.DDLGenerator -Usa -Pmypassword -S192.123.13.111:5000 -Dmaster

上面的命令为用户 sa 中存在的所有数据库对象生成DDL，我们还获得了共享给其他用户的对象列表由用户 sa 设置为：

Above command generate DDL for all database objects exist in user sa, also we get list of objects shared to other user by user sa as :

Grant Select on dbo.mytable1 to anotheruserthansa
go
Grant Select on dbo.mytable2 to anotheruserthansa
go 

现在我们想要按用户名共享对象的DDL，例如在我的情况下，用户是另一个用户thansa

Now we want DDL of shared objects by username, like in my case user is anotheruserthansa

我们需要一些命令可以为用户生成DDL的密码，我们可能没有密码。 我们需要使用用户sa，密码和模式名称获取DDL的内容： anotheruserthansa 我们需要为用户共享的所有数据库对象生成ddl。 sa 到另一个用户 。

We need some command that can generate DDL for user, we might not have its password. What we need to get DDL using my user sa, its password and with schema name : anotheruserthansa we need to generate ddl for all the database object those are shared by user sa to anotheruserthansa.

像我们在 Oracle 中执行以下查询：

Like we do in Oracle with query :

select  object_type from dba_objects where owner = ''anotheruserthansa' and object_name = 'anotheruserthansa'

如何使用其架构为共享对象获取DDL？

How can we get DDL for shared object using its schema?

推荐答案

这很可能是通过将SQL查询与参数组合后完成的，然后将参数传递给 ddlgen

This would likely be done with a combination of a SQL Query, with parameters then passed to ddlgen

SELECT db_name()
     , su.name
     , so.type
     , so.name
 FROM sysusers su, sysobjects so
WHERE su.uid = so.uid
 AND su.name = "[name of user you are looking for]"

然后您可以传递dbname，对象类型，和对象名称放入 ddlgen 生成DDL。

You can then pass the dbname, object type, and object name into ddlgen to generate the DDL.

ddlgen -Usa -Sservername -Tobject_type -Nobject_name -Ddbname ...etc

仅供参考-Sybase / SAP最佳做法建议反对允许用户拥有数据库中的对象。建议所有数据库对象都由 dbo 拥有。不要与Oracle和SQL Server使用的架构概念混淆。 ASE不使用这种逻辑除法。

FYI - Sybase/SAP best practices recommends against allowing users to own objects within the database. It's recommended that all database objects be owned by dbo This is not to be confused with the concept of schemas used by Oracle and SQL Server. ASE does not use those kinds of logical divisions.

这篇关于如何获取共享给其他用户的数据库对象的DDL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！