Oracle 常用腳本之查看用戶、權(quán)限、資源配置等信息

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        查看用戶信息
        查看用戶的權(quán)限
            查看用戶的系統(tǒng)權(quán)限
            查看用戶的對(duì)象權(quán)限
            查看用戶的字段權(quán)限
            查看用戶的角色權(quán)限
        查看用戶的資源配置
        查看用戶的表空間配額
        查看用戶的創(chuàng)建語(yǔ)句

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來(lái)介紹一下如何查看 Oracle 數(shù)據(jù)庫(kù)中模式用戶的相關(guān)信息以及如何生成創(chuàng)建用戶的語(yǔ)句。

在 Oracle 中，模式（schema）和用戶（user）可以看作是相同的概念。當(dāng)我們創(chuàng)建一個(gè)用戶時(shí)，相當(dāng)于創(chuàng)建了一個(gè)同名的模式。
查看用戶信息

數(shù)據(jù)字典視圖 dba_users 包含了當(dāng)前數(shù)據(jù)庫(kù)中的所有模式用戶信息，需要 DBA 權(quán)限才能查看：

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



其中：

    USERNAME 是用戶名；
    CREATED 是用戶的創(chuàng)建時(shí)間；
    ACCOUNT_STATUS 是用戶的狀態(tài)，例如 OPEN、EXPIRED、LOCKED 等；
    DEFAULT_TABLESPACE 是用于存儲(chǔ)數(shù)據(jù)的默認(rèn)表空間；
    TEMPORARY_TABLESPACE 是默認(rèn)的臨時(shí)表空間；
    PROFILE 是用戶的資源配置文件；
    LAST_LOGIN 是用戶最后一次登錄時(shí)間。

    ??關(guān)于視圖 dba_users 更多的字段說(shuō)明可以參考官方文檔。

另外，我們也可以通過(guò) all_users 視圖查看當(dāng)前用戶可見(jiàn)的所有用戶，或者使用 user_users 視圖查看當(dāng)前登錄用戶的信息。

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 中的許多數(shù)據(jù)字典表都存在 3 個(gè)相應(yīng)的視圖：ALL_* 視圖包含了當(dāng)前用戶可以訪問(wèn)的對(duì)象信息；DBA_* 視圖包含了數(shù)據(jù)庫(kù)中的所有相關(guān)信息，需要 DBA 或者數(shù)據(jù)字典查看權(quán)限才能訪問(wèn)；USER_* 視圖包含了當(dāng)前用戶模式下的對(duì)象信息。

非系統(tǒng)用戶是指不是由 Oracle 維護(hù)的用戶，可以通過(guò)以下語(yǔ)句進(jìn)行查看：

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


如果將 N 改成 Y，返回的就是由 Oracle 維護(hù)的系統(tǒng)用戶。
查看用戶的權(quán)限

Oracle 用戶的權(quán)限包括系統(tǒng)權(quán)限、對(duì)象權(quán)限、字段權(quán)限以及通過(guò)角色獲得的權(quán)限。
查看用戶的系統(tǒng)權(quán)限

視圖 dba_sys_privs 包含了用戶和角色的系統(tǒng)權(quán)限信息：

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



其中，

    GRANTEE 是用戶或者角色的名稱；
    PRIVILEGE 是系統(tǒng)權(quán)限的名稱；
    ADMIN_OPTION 表示用戶或者角色是否可以將該權(quán)限授權(quán)其他用戶（ADMIN 選項(xiàng)）。

另外，視圖 user_sys_privs 包含了當(dāng)前用戶的系統(tǒng)權(quán)限信息。
查看用戶的對(duì)象權(quán)限

用戶的對(duì)象權(quán)限可以通過(guò)視圖 dba_tab_privs 進(jìn)行查詢：

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



其中，

    GRANTEE 是被授予該對(duì)象權(quán)限的用戶或者角色；
    OWNER 是該對(duì)象的擁有者；
    TABLE_NAME 是對(duì)象的名稱，不一定是表名；
    GRANTOR 是執(zhí)行授權(quán)操作的用戶；
    PRIVILEGE 是權(quán)限名；
    GRANTABLE 表示授權(quán)時(shí)是否使用了 GRANT OPTION 選項(xiàng)；
    TYPE 是對(duì)象的類型，可以是表、索引、程序包、序列等。

另外，視圖 user_tab_privs 包含了與當(dāng)前用戶相關(guān)的對(duì)象權(quán)限，也就是 GRANTEE、OWNER 或者 GRANTOR 為當(dāng)前用戶。視圖 all_tab_privs 包含了 user_tab_privs 中的數(shù)據(jù)，以及授予任何啟用的角色或者 PUBLIC 的權(quán)限。
查看用戶的字段權(quán)限

用戶的字段對(duì)象權(quán)限可以通過(guò)視圖 dba_col_privs 進(jìn)行查詢：

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 包含了與當(dāng)前用戶相關(guān)的字段對(duì)象權(quán)限，也就是 GRANTEE、OWNER 或者 GRANTOR 為當(dāng)前用戶。視圖 all_col_privs 包含了 user_col_privs 中的數(shù)據(jù)，以及授予任何啟用的角色或者 PUBLIC 的權(quán)限。
查看用戶的角色權(quán)限

角色（role）是一組權(quán)限的集合，可以用于簡(jiǎn)化權(quán)限的管理。視圖 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 表示授予角色時(shí)是否使用了 ADMIN OPTION 選項(xiàng)；
    DELEGATE_OPTION 表示授予角色時(shí)是否使用了 DELEGATE OPTION 選項(xiàng)；
    DEFAULT_ROLE 表示該角色是否是用戶的默認(rèn)角色（登錄后自動(dòng)生效）。

另外，視圖 user_role_privs 包含了當(dāng)前用戶被授予的角色。視圖 role_role_privs 包含了被授予其他角色的角色。

通過(guò)關(guān)聯(lián)用戶的角色和角色的系統(tǒng)權(quán)限或者對(duì)象權(quán)限，可以獲得用戶通過(guò)角色得到的權(quán)限。例如：

-- 用戶通過(guò)角色得到的系統(tǒng)權(quán)限
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; -- 用戶名或者角色名

-- 用戶通過(guò)角色得到的對(duì)象權(quán)限
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）是針對(duì)數(shù)據(jù)庫(kù)資源的使用限制，包括 CPU 時(shí)間、并發(fā)會(huì)話數(shù)量、密碼管理策略等。使用以下語(yǔ)句查看用戶的資源配置文件和相應(yīng)的資源配額：

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 是已經(jīng)分配給用戶的空間，單位為字節(jié)；
    MAX_BYTES 是用戶的配額，單位為字節(jié)，-1 表示沒(méi)有限制；
    BLOCKS 是已經(jīng)分配給用戶的空間，單位為數(shù)據(jù)塊；
    MAX_BLOCKS 是用戶的配額，單位為數(shù)據(jù)塊，-1 表示沒(méi)有限制；
    DROPPED 表示表空間是否已經(jīng)被刪除。

視圖 user_ts_quotas 包含了當(dāng)前用戶的表空間配額信息。
查看用戶的創(chuàng)建語(yǔ)句

Oracle 系統(tǒng)程序包 dbms_metadata 可以用于獲取各種數(shù)據(jù)字典信息以及對(duì)象的 DDL，我們可以使用以下查詢返回完整的用戶定義語(yǔ)句：

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = :user_name
and rownum = 1
union all -- 獲取系統(tǒng)權(quán)限
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 -- 獲取對(duì)象權(quán)限
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 -- 獲取默認(rèn)角色
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 -- 獲取非默認(rèn)的資源配置
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;


如果覺(jué)得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??！