Oracle教程 2023-02-25

Oracle 视图 DBA_COL_PRIVS 官方解释，作用，如何使用详细说明

Oracle视图dba_col_privs用来查看哪些用户/角色拥有对某一表特定列的权限，这些权限可以是SELECT、UPDATE、DELETE、INSERT等。

它包含六个字段：

– GRANTEE：授权者
– TABLE_SCHEMA：表所属Schema
– TABLE_NAME：表名
– COLUMN_NAME：特定列名
– PRIVILEGE：拥有的特定列的权限
– GRANTABLE：能否将权限授权给其他用户

使用方法：

1. 查询某一列的所有的权限：

SELECT * FROM dba_col_privs WHERE column_name = ‘列名’;

2. 查询特定用户拥有某一列的指定权限：

SELECT * FROM dba_col_privs WHERE column_name = ‘列名’ AND grantee = ‘用户名’ AND privilege = ‘权限’；

3. 查询能够授权某一列特定权限的用户：

SELECT * FROM dba_col_privs WHERE column_name = ‘列名’ AND granbable = ‘YES’ AND privilege = ‘权限’；

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	Datatype	Description
`GRANTEE`	`VARCHAR2(128)`	Name of the user or role to whom access was granted
`OWNER`	`VARCHAR2(128)`	Owner of the object
`TABLE_NAME`	`VARCHAR2(128)`	Name of the object
`COLUMN_NAME`	`VARCHAR2(128)`	Name of the column
`GRANTOR`	`VARCHAR2(128)`	Name of the user who performed the grant
`PRIVILEGE`	`VARCHAR2(40)`	Privilege on the column
`GRANTABLE`	`VARCHAR2(3)`	Indicates whether the privilege was granted with the `GRANT OPTION` (`YES`) or not (`NO`)
`COMMON`	`VARCHAR2(3)`	Indicates how the grant was made. Possible values: `YES` if the privilege was granted commonly (`CONTAINER=ALL` was used) `NO` if the privilege was granted locally (`CONTAINER=ALL` was not used)
`INHERITED`	`VARCHAR2(3)`	Indicates whether the privilege grant was inherited from another container (`YES`) or not (`NO`)