Oracle許可權（二）許可權相關的動態效能檢視與資料字典檢視

路途中的人2012發表於2016-05-19
Oracle

001 DBA_USERS

DBA_USERS 描述了資料庫中所有的使用者資訊。

相關檢視：

USER_USERS 描述了當前使用者的資訊。該檢視不包括 PASSWORD, PROFILE, PASSWORD_VERSIONS, EDITIONS_ENABLED, 和AUTHENTICATION_TYPE 列.

Column Description
USERNAME 使用者名稱
USER_ID 使用者ID
PASSWORD 為了支援AUTHENTICATION_TYPE 列，該列已被棄用
ACCOUNT_STATUS 帳號（使用者）狀態，一共9種:

  • OPEN

  • EXPIRED

  • EXPIRED(GRACE)

  • LOCKED(TIMED)

  • LOCKED

  • EXPIRED & LOCKED(TIMED)

  • EXPIRED(GRACE) & LOCKED(TIMED)

  • EXPIRED & LOCKED

  • EXPIRED(GRACE) & LOCKED

LOCK_DATE 帳號被鎖定的日期（如果帳號處於鎖定狀態）
EXPIRY_DATE 帳號過期的日期
DEFAULT_TABLESPACE 資料的預設表空間
TEMPORARY_TABLESPACE 預設的臨時表空間名或臨時表空間組名
CREATED 使用者建立日期
PROFILE 使用者資源概要檔名
INITIAL_RSRC_CONSUMER_GROUP 使用者的初始資源消費者組
EXTERNAL_NAME 使用者外部名
PASSWORD_VERSIONS Shows the list of versions of the password hashes (also known as "verifiers") existing for the account.

The PASSWORD_VERSIONS column value includes 10G if an old case-insensitive ORCL hash exists and 11G if a SHA-1 hash exists.

Note that one or both of these verifiers can exist for any given account.

EDITIONS_ENABLED Indicates whether editions have been enabled for the corresponding user (Y) or not (N)
AUTHENTICATION_TYPE Indicates the authentication mechanism for the user:

  • EXTERNAL - CREATE USER user1 IDENTIFIED EXTERNALLY;

  • GLOBAL - CREATE USER user2 IDENTIFIED GLOBALLY;

  • PASSWORD - CREATE USER user3 IDENTIFIED BY user3;


002 DBA_SYS_PRIVS

DBA_SYS_PRIVS 描述了授予給使用者和角色的系統許可權。

Column Description
GRANTEE Grantee name, user, or role receiving the grant
PRIVILEGE System privilege
ADMIN_OPTION Indicates whether the grant was with the ADMIN option (YES) or not (NO)

003 ROLE_ROLE_PRIVS

ROLE_ROLE_PRIVS describes the roles granted to other roles. Information is provided only about roles to which the user has access.

Column Description
ROLE Name of the role
GRANTED_ROLE Role that was granted
ADMIN_OPTION Signifies that the role was granted with ADMIN option


004 ROLE_SYS_PRIVS

ROLE_SYS_PRIVS describes system privileges granted to roles. Information is provided only about roles to which the user has access.

Column Description
ROLE Name of the role
PRIVILEGE System privilege granted to the role
ADMIN_OPTION Indicates whether the grant was with the ADMIN option (YES) or not (NO)


005 ROLE_TAB_PRIVS

ROLE_TAB_PRIVS describes table privileges granted to roles. Information is provided only about roles to which the user has access.

Column Description
ROLE Name of the role
OWNER Owner of the object
TABLE_NAME Name of the object
COLUMN_NAME Name of the column, if applicable
PRIVILEGE Object privilege granted to the role
GRANTABLE YES if the role was granted with ADMIN OPTION; otherwise NO

 

006 DBA_TAB_PRIVS

DBA_TAB_PRIVS describes all object grants in the database.

Related View

USER_TAB_PRIVS describes the object grants for which the current user is the object owner, grantor, or grantee.

Column Description
GRANTEE Name of the user or role to whom access was granted
OWNER Owner of the object
TABLE_NAME Name of the object. The object can be any object, including tables, packages, indexes, sequences, and so on.
GRANTOR Name of the user who performed the grant
PRIVILEGE Privilege on the object
GRANTABLE Indicates whether the privilege was granted with the GRANT OPTION(YES) or not (NO)
HIERARCHY Indicates whether the privilege was granted with the HIERARCHY OPTION (YES) or not (NO)


007 DBA_COL_PRIVS

DBA_COL_PRIVS describes all column object grants in the database.

Related View

USER_COL_PRIVS describes the column object grants for which the current user is the object owner, grantor, or grantee.

Column Description
GRANTEE Name of the user or role to whom access was granted
OWNER Owner of the object
TABLE_NAME Name of the object
COLUMN_NAME Name of the column
GRANTOR Name of the user who performed the grant
PRIVILEGE Privilege on the column
GRANTABLE Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO)


008 DBA_ROLES

DBA_ROLES describes all roles in the database.

Column Description
ROLE Name of the role
PASSWORD_REQUIRED This column is deprecated in favor of the AUTHENTICATION_TYPEcolumn
AUTHENTICATION_TYPE Indicates the authentication mechanism for the role:

  • NONE - CREATE ROLE role1;

  • EXTERNAL - CREATE ROLE role2 IDENTIFIED EXTERNALLY;

  • GLOBAL - CREATE ROLE role3 IDENTIFIED GLOBALLY;

  • APPLICATION - CREATE ROLE role4 IDENTIFIED USINGschema.package;

  • PASSWORD - CREATE ROLE role5 IDENTIFIED BY role5;


009 DBA_ROLE_PRIVS

DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.

Related View

USER_ROLE_PRIVS describes the roles granted to the current user.

Column Description
GRANTEE Name of the user or role receiving the grant
GRANTED_ROLE Granted role name
ADMIN_OPTION Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO)
DEFAULT_ROLE Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO)


010 V$PWFILE_USERS

V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, and SYSASM privileges.

Column Description
USERNAME Name of the user that is contained in the password file
SYSDBA Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE)
SYSOPER Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE)
SYSASM Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)



未完待續

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2103454/，如需轉載，請註明出處，否則將追究法律責任。

相關文章