得到用户创建脚本 metadata ddl（未经测试）-凯发app官方网站

set long 9999
set pages 999
1、创建 profile脚本
select dbms_metadata.get_ddl('profile',name) p from sys.profname$ where flags!=8 order by name;
2、创建用户脚本
select dbms_metadata.get_ddl('user', username) u from dba_users where oracle_maintained='n' order by username;
3、创建表空间限额
select dbms_metadata.get_granted_ddl('tablespace_quota',username) q from dba_users where username in(select username from dba_ts_quotas where username in (select username from dba_users where oracle_maintained='n')) order by username;
4、创建角色
select dbms_metadata.get_granted_ddl ('role_grant',username) r from dba_users where username in(select grantee from dba_role_privs where username in (select username from dba_users where oracle_maintained='n')) order by username;
如果是业务创建的角色
select 'grant '||granted_role||' to '||role||';' r from role_role_privs where role='approle1';

select 'grant '||privilege||' to '||role||';' r from role_sys_privs where role='approle1';

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||role||';' r from role_tab_privs where role='approle1';

这里未考虑admin、字段权限等细节，也有可能创建的role没有给任何用户使用，
这样的可以考虑放弃（扔了）。
5、系统权限
select dbms_metadata.get_granted_ddl ('system_grant', username) s from dba_users where username in(select grantee from dba_sys_privs ) and username in (select username from dba_users where oracle_maintained='n') order by username;
6、对象权限
select dbms_metadata.get_granted_ddl ('object_grant', username) t from dba_users where username in(select grantee from dba_tab_privs) and username in (select username from dba_users where oracle_maintained='n') order by username;

type name	meaning	attributes	notes
aq_queue	queues	snd	dependent on table
aq_queue_table	additional metadata for queue tables	nd	dependent on table
aq_transform	transforms	sn	none
association	associate statistics	d	none
audit	audits of sql statements	dg	modeled as dependent, granted object. the base object name is the statement audit option name (for example, alter system). there is no base object schema. the grantee is the user or proxy whose statements are audited.
audit_obj	audits of schema objects	d	none
cluster	clusters	sn	none
comment	comments	d	none
constraint	constraints	snd	constraints do not include the following: primary key constraint for iot column not null constraints certain ref scope and with rowid constraints for tables with ref columns
context	application contexts	n	none
database_export	all metadata objects in a database	h	corresponds to a full database export
db_link	database links	sn	modeled as schema objects because they have owners. for public links, the owner is public. for private links, the creator is the owner.
default_role	default roles	g	granted to a user by alter user
dimension	dimensions	sn	none
directory	directories	n	none
fga_policy	fine-grained audit policies	d	not modeled as named object because policy names are not unique.
function	stored functions	sn	none
index_statistics	precomputed statistics on indexes	d	the base object is the index's table.
index	indexes	snd	none
indextype	indextypes	sn	none
java_source	java sources	sn	none
job	jobs	s	none
library	external procedure libraries	sn	none
materialized_view	materialized views	sn	none
materialized_view_log	materialized view logs	d	none
object_grant	object grants	dg	none
on_user_grant	grants	g	modeled as user grants. grants the privileges of one user to other user in the form grant ... on user .... the grantee is the user. example: grant inherit privileges on user "user1" to "user2".
operator	operators	sn	none
package	stored packages	sn	by default, both package specification and package body are retrieved. see "".
package_spec	package specifications	sn	none
package_body	package bodies	sn	none
procedure	stored procedures	sn	none
profile	profiles	n	none
proxy	proxy authentications	g	granted to a user by alter user
ref_constraint	referential constraint	snd	none
refresh_group	refresh groups	sn	none
resource_cost	resource cost info	h	none
rls_context	driving contexts for enforcement of fine-grained access-control policies	d	corresponds to the dbms_rls.add_policy_content procedure
rls_group	fine-grained access-control policy groups	d	corresponds to the dbms_rls.create_group procedure
rls_policy	fine-grained access-control policies	d	corresponds to dbms_rls.add_grouped_policy. not modeled as named objects because policy names are not unique.
rmgr_consumer_group	resource consumer groups	sn	oracle data pump does not use these object types. instead, it exports resource manager objects as procedural objects.
rmgr_intitial_consumer_group	assign initial consumer groups to users	g	none
rmgr_plan	resource plans	sn	none
rmgr_plan_directive	resource plan directives	d	dependent on resource plan
role	roles	n	none
role_grant	role grants	g	none
rollback_segment	rollback segments	n	none
schema_export	all metadata objects in a schema	h	corresponds to user-mode export.
sequence	sequences	sn	none
sql_domain	sql domains	see notes	a domain is a high-level dictionary object that belongs to a schema and encapsulates a set of properties and constraints. the attributes and constraints are defined and managed only for the domain, and are automatically applied to all the columns of the given domain.
synonym	synonyms	see notes	private synonyms are schema objects. public synonyms are not, but for the purposes of this api, their schema name is public. the name of a synonym is considered to be the synonym itself. for example, in create public synonym foo for bar, the resultant object is considered to have name foo and schema public.
system_grant	system privilege grants	g	none
table	tables	sn	none
table_data	metadata describing row data for a table, nested table, or partition	snd	for partitions, the object name is the partition name. for nested tables, the object name is the storage table name. the base object is the top-level table to which the table data belongs. for nested tables and partitioning, this is the top-level table (not the parent table or partition). for nonpartitioned tables and non-nested tables this is the table itself.
table_export	metadata for a table and its associated objects	h	corresponds to table-mode export
table_statistics	precomputed statistics on tables	d	none
tablespace	tablespaces	n	none
tablespace_quota	tablespace quotas	g	granted with alter user
transportable_export	metadata for objects in a transportable tablespace set	h	corresponds to transportable tablespace export
trigger	triggers	snd	none
trusted_db_link	trusted links	n	none
type	user-defined types	sn	by default, both type and type body are retrieved. see "set_filter procedure". .
type_spec	type specifications	sn	none
type_body	type bodies	sn	none
user	users	n	none
view	views	sn	none
xmlschema	xml schema	sn	the object's name is its url (which can be longer than 30 characters). its schema is the user who registered it.
xs_user	real application security (ras) user	n	corresponds to ras users
xs_role	real application security (ras) role	n	corresponds to ras roles
xs_roleset	real application security (ras) rolesets	n	corresponds to ras rolesets
xs_role_grant	real application security (ras) role grants	n	corresponds to ras role grants
xs_security_class	real application security (ras) security class	sn	corresponds to ras security classes
xs_data_security	real application security (ras) data security policy	sn	corresponds to ras data security policies
xs_acl	real application security (ras) acl	sn	corresponds to ras access control lists (acls) and associated access control entries (aces)
xs_acl_param	real application security (ras) acl parameter	n	corresponds to ras access control lists (acl) parameters
xs_namespace	real application security (ras) namespace	n	corresponds to ras namespaces.

所有对象类型。

https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms_metadata.html#guid-312afc59-fd7b-4274-adb6-3c1f752681c0

阅读(1066) | 评论(0) | 转发(0) |

上一篇：oracle linux 7 上禁用透明大页 thp

下一篇：asm 实例管理命令

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们