Oracle 常用脚本之查看用户、权限、资源配置等信息

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目录

        查看用户信息
        查看用户的权限
            查看用户的系统权限
            查看用户的对象权限
            查看用户的字段权限
            查看用户的角色权限
        查看用户的资源配置
        查看用户的表空间配额
        查看用户的创建语句

大家好，我是只谈技术不剪发的 Tony 老师。今天我们来介绍一下如何查看 Oracle 数据库中模式用户的相关信息以及如何生成创建用户的语句。

在 Oracle 中，模式（schema）和用户（user）可以看作是相同的概念。当我们创建一个用户时，相当于创建了一个同名的模式。
查看用户信息

数据字典视图 dba_users 包含了当前数据库中的所有模式用户信息，需要 DBA 权限才能查看：

select username, created, account_status, default_tablespace, temporary_tablespace, profile, last_login
from dba_users
order by username;



其中：

    USERNAME 是用户名；
    CREATED 是用户的创建时间；
    ACCOUNT_STATUS 是用户的状态，例如 OPEN、EXPIRED、LOCKED 等；
    DEFAULT_TABLESPACE 是用于存储数据的默认表空间；
    TEMPORARY_TABLESPACE 是默认的临时表空间；
    PROFILE 是用户的资源配置文件；
    LAST_LOGIN 是用户最后一次登录时间。

    📝关于视图 dba_users 更多的字段说明可以参考官方文档。

另外，我们也可以通过 all_users 视图查看当前用户可见的所有用户，或者使用 user_users 视图查看当前登录用户的信息。

select username, created
from all_users
order by username;

select username, created, account_status, default_tablespace, temporary_tablespace
from user_users
order by username;



    📝Oracle 中的许多数据字典表都存在 3 个相应的视图：ALL_* 视图包含了当前用户可以访问的对象信息；DBA_* 视图包含了数据库中的所有相关信息，需要 DBA 或者数据字典查看权限才能访问；USER_* 视图包含了当前用户模式下的对象信息。

非系统用户是指不是由 Oracle 维护的用户，可以通过以下语句进行查看：

select *
from dba_users
where oracle_maintained = 'N'
order by username;


如果将 N 改成 Y，返回的就是由 Oracle 维护的系统用户。
查看用户的权限

Oracle 用户的权限包括系统权限、对象权限、字段权限以及通过角色获得的权限。
查看用户的系统权限

视图 dba_sys_privs 包含了用户和角色的系统权限信息：

select grantee, privilege, admin_option
from dba_sys_privs
where grantee = :user_name; -- 用户名或者角色名



其中，

    GRANTEE 是用户或者角色的名称；
    PRIVILEGE 是系统权限的名称；
    ADMIN_OPTION 表示用户或者角色是否可以将该权限授权其他用户（ADMIN 选项）。

另外，视图 user_sys_privs 包含了当前用户的系统权限信息。
查看用户的对象权限

用户的对象权限可以通过视图 dba_tab_privs 进行查询：

select grantee, owner, table_name, grantor, privilege, grantable, type
from dba_tab_privs
where grantee = :user_name; -- 用户名或者角色名



其中，

    GRANTEE 是被授予该对象权限的用户或者角色；
    OWNER 是该对象的拥有者；
    TABLE_NAME 是对象的名称，不一定是表名；
    GRANTOR 是执行授权操作的用户；
    PRIVILEGE 是权限名；
    GRANTABLE 表示授权时是否使用了 GRANT OPTION 选项；
    TYPE 是对象的类型，可以是表、索引、程序包、序列等。

另外，视图 user_tab_privs 包含了与当前用户相关的对象权限，也就是 GRANTEE、OWNER 或者 GRANTOR 为当前用户。视图 all_tab_privs 包含了 user_tab_privs 中的数据，以及授予任何启用的角色或者 PUBLIC 的权限。
查看用户的字段权限

用户的字段对象权限可以通过视图 dba_col_privs 进行查询：

select grantee, owner, table_name, column_name, grantor, privilege, grantable
from dba_col_privs
where grantee = :user_name; -- 用户名或者角色名



其中，COLUMN_NAME 是字段的名称；其他字段和 dba_tab_privs 相同。

另外，视图 user_col_privs 包含了与当前用户相关的字段对象权限，也就是 GRANTEE、OWNER 或者 GRANTOR 为当前用户。视图 all_col_privs 包含了 user_col_privs 中的数据，以及授予任何启用的角色或者 PUBLIC 的权限。
查看用户的角色权限

角色（role）是一组权限的集合，可以用于简化权限的管理。视图 dba_role_privs 包含了授予用户和其他角色的角色信息。

select grantee, granted_role, admin_option, delegate_option, default_role
from dba_role_privs
where grantee = :user_name; -- 用户名或者角色名



其中，

    GRANTEE 是被授予该角色的用户或者其他角色；
    GRANTED_ROLE 是被授予的角色；
    ADMIN_OPTION 表示授予角色时是否使用了 ADMIN OPTION 选项；
    DELEGATE_OPTION 表示授予角色时是否使用了 DELEGATE OPTION 选项；
    DEFAULT_ROLE 表示该角色是否是用户的默认角色（登录后自动生效）。

另外，视图 user_role_privs 包含了当前用户被授予的角色。视图 role_role_privs 包含了被授予其他角色的角色。

通过关联用户的角色和角色的系统权限或者对象权限，可以获得用户通过角色得到的权限。例如：

-- 用户通过角色得到的系统权限
select rp.grantee, rp.granted_role, sp.privilege, sp.admin_option
from dba_sys_privs sp
join dba_role_privs rp on (rp.granted_role = sp.grantee)
where rp.grantee = :user_name; -- 用户名或者角色名

-- 用户通过角色得到的对象权限
select rp.grantee, granted_role, owner, table_name, grantor, privilege, grantable, type
from dba_tab_privs tp
join dba_role_privs rp on (rp.granted_role = tp.grantee)
where rp.grantee = :user_name; -- 用户名或者角色名



查看用户的资源配置

资源配置文件（profile）是针对数据库资源的使用限制，包括 CPU 时间、并发会话数量、密码管理策略等。使用以下语句查看用户的资源配置文件和相应的资源配额：

select u.username, u.profile, p.resource_name,p.resource_type, p.limit
from dba_profiles p
join dba_users u on (u.profile = p.profile)
where u.username = :user_name;



其中，

    RESOURCE_NAME 是资源的名称；
    RESOURCE_TYPE 是资源的类型，可以是 KERNEL 或者 PASSWORD；
    LIMIT 是资源的配额限制。

查看用户的表空间配额

视图 dba_ts_quotas 包含了所有用户的表空间配额信息。

select tablespace_name, username, bytes, max_bytes, blocks, max_blocks, dropped
from dba_ts_quotas
where username = :user_name; -- 用户名或者角色名


其中，

    TABLESPACE_NAME 是表空间的名称；
    USERNAME 是用户名；
    BYTES 是已经分配给用户的空间，单位为字节；
    MAX_BYTES 是用户的配额，单位为字节，-1 表示没有限制；
    BLOCKS 是已经分配给用户的空间，单位为数据块；
    MAX_BLOCKS 是用户的配额，单位为数据块，-1 表示没有限制；
    DROPPED 表示表空间是否已经被删除。

视图 user_ts_quotas 包含了当前用户的表空间配额信息。
查看用户的创建语句

Oracle 系统程序包 dbms_metadata 可以用于获取各种数据字典信息以及对象的 DDL，我们可以使用以下查询返回完整的用户定义语句：

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = :user_name
and rownum = 1
union all -- 获取系统权限
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :user_name
and rownum = 1
union all -- 获取对象权限
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', op.grantee) AS ddl
from (
    select tp.grantee
    from dba_tab_privs tp
    where tp.grantee = :user_name
    union all
    select cp.grantee
    from dba_col_privs cp
    where cp.grantee = :user_name
) op
where rownum = 1
union all -- 获取用户角色
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :user_name
and rownum = 1
union all -- 获取默认角色
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :user_name
and rp.default_role = 'YES'
and rownum = 1
union all -- 获取表空间配额
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = :user_name
and rownum = 1
union all -- 获取非默认的资源配置
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = :user_name
and u.profile <> 'DEFAULT'
and rownum = 1;


如果觉得文章对你有用，欢迎关注❤️、评论📝、点赞👍！