【DB笔试面试627】在Oracle中，对表执行COMMENT（注释）操作需要什么权限？
【DB笔试面试627】在Oracle中，对表执行COMMENT（注释）操作需要什么权限？
小麦苗DBA宝典
发布于 2019-09-29 16:16:38
发布于 2019-09-29 16:16:38
题目部分
在Oracle中，对表执行COMMENT（注释）操作需要什么权限？
♣
答案部分
Oracle的COMMENT语句可以给一个列、表、视图或快照添加一个最多2K字节的注释。注释被存储在数据字典中，并且可以通过数据字典视图DBA_COL_COMMENTS（列的注释）和DBA_TAB_COMMENTS（表的注释）查看COMMENTS列。COMMENT语句的语法：
COMMENT ON TABLE tb | COLUMN tb.cols IS 'text';
其中，tb是表的名字，cols是表中列的名字，text是注释的文本。可以用设置注释为空串（''）的办法从数据库中删除一个注释，例如：
COMMENT ON TABLE employees IS '';
COMMENT ON TABLE HR.employees IS 'Employee Information';
COMMENT ON COLUMN HR.employees.FIRST_NAME IS 'Employee FIRST_NAME';
对于SYS用户下的表，只有拥有ALTER权限的普通用户才可以对其进行COMMENT操作。对于普通用户下的表，拥有“COMMENT ANY TABLE”或ALTER权限的普通用户都可以执行COMMENT操作。示例如下：
SYS@test18c> create table t as select * from dual;
Table created.
SYS@test18c> create user lhr33 identified by lhr;
User created.
SYS@test18c> grant create session to lhr33;
Grant succeeded.
SYS@test18c> GRANT SELECT ON sys.t to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c>
LHR33@test18c> select * from sys.t;
DU
--
X
LHR33@test18c> comment on column sys.t.dummy is 'aa';
comment on column sys.t.dummy is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> comment on table sys.t is 'aa';
comment on table sys.t is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant alter on sys.t to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c>
LHR33@test18c> comment on table sys.t is 'aa';
Comment created.
LHR33@test18c> comment on column sys.t.dummy is 'aa';
Comment created.
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> revoke alter on sys.t from lhr33;
Revoke succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on column sys.t.dummy is 'aa';
comment on column sys.t.dummy is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant COMMENT ANY TABLE to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on column sys.t.dummy is 'aa';
comment on column sys.t.dummy is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> comment on table sys.t is 'aa';
comment on table sys.t is 'aa'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant alter on sys.t to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on table sys.t is 'aa';
Comment created.
对于普通用户下的表，拥有“COMMENT ANY TABLE”或ALTER权限的用户都可以执行COMMENT操作：
LHR33@test18c> conn lhr/lhr
Connected.
LHR@test18c> create table bb as select * from dual;
Table created.
LHR@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on table lhr.bb is 'bb';
Comment created.
LHR33@test18c> select * from lhr.bb;
select * from lhr.bb
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> revoke COMMENT ANY TABLE from lhr33;
Revoke succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on table lhr.bb is 'bb';
comment on table lhr.bb is 'bb'
*
ERROR at line 1:
ORA-00942: table or view does not exist
LHR33@test18c> conn / as sysdba
Connected.
SYS@test18c> grant select on lhr.bb to lhr33;
Grant succeeded.
SYS@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on table lhr.bb is 'bb';
comment on table lhr.bb is 'bb'
*
ERROR at line 1:
ORA-01031: insufficient privileges
LHR33@test18c> conn lhr/lhr
Connected.
LHR@test18c> grant alter on lhr.bb to lhr33;
Grant succeeded.
LHR@test18c> conn lhr33/lhr
Connected.
LHR33@test18c> comment on table lhr.bb is 'bb';
Comment created.
视图DBA_COL_COMMENTS和DBA_TAB_COMMENTS在做开发时非常实用，举例如下：
create table SCOTT.G_PROD_USER_CONF
(
func_type VARCHAR2(20) not null,
func_sub_type VARCHAR2(20) not null,
userid VARCHAR2(20) not null,
username VARCHAR2(50) not null,
sendtype VARCHAR2(20) not null,
email_address VARCHAR2(500)
);
-- Add comments to the table
comment on table SCOTT.G_PROD_USER_CONF is '系统功能人员配置';
-- Add comments to the columns
comment on column SCOTT.G_PROD_USER_CONF.func_type is '功能类型 ';
comment on column SCOTT.G_PROD_USER_CONF.func_sub_type is '功能子类型 1=收件人 2=抄送人 3=密送人 4=发件人';
comment on column SCOTT.G_PROD_USER_CONF.userid is '员工工号';
comment on column SCOTT.G_PROD_USER_CONF.username is '员工姓名';
comment on column SCOTT.G_PROD_USER_CONF.sendtype is '发送类型1：短信2：邮件3：通知公告';
comment on column SCOTT.G_PROD_USER_CONF.email_address is '电子邮箱';
-- Grant/Revoke object privileges
grant select, insert, update, delete, references, alter, index on SCOTT.G_PROD_USER_CONF to PUBLIC;
通过视图可以查询出一些有用的SQL语句：
SELECT * FROM DBA_TAB_COMMENTS D WHERE D.TABLE_NAME = 'G_PROD_USER_CONF';
SELECT 'A.' || D.COLUMN_NAME || ',',
'--' || D.COMMENTS,
'P_' || DTC.COLUMN_NAME || ' ' || DTC.DATA_TYPE || ',',
'P_' || DTC.COLUMN_NAME || ' ' || DTC.DATA_TYPE || ',' || ' --' ||
D.COMMENTS 入参,
'A.' || D.COLUMN_NAME || ' ' || D.COLUMN_NAME || ',' || '--' ||
D.COMMENTS 查询,
'P_' || D.COLUMN_NAME || ',' || '--' || D.COMMENTS 插入,
'A.' || D.COLUMN_NAME || '=' || 'P_' || DTC.COLUMN_NAME || ', --' ||
D.COMMENTS 更新,
D.COLUMN_NAME || ', --' || D.COMMENTS,
D.COMMENTS,
DECODE(DTC.DATA_TYPE, 'DATE', 'DATE', '') DATA_TYPE,
'--' || D.COMMENTS || CHR(10) ||
' v_sql := v_sql || fun_sqlparam(p_' || D.COLUMN_NAME ||
', '' and A.' || D.COLUMN_NAME || (CASE
WHEN DTC.DATA_TYPE = 'VARCHAR2' THEN
Q'[ = ''{0}'' '); ]'
ELSE
Q'[ = {0} ');]'
END) WHERE条件
FROM DBA_COL_COMMENTS D, DBA_TAB_COLS DTC
WHERE D.TABLE_NAME = DTC.TABLE_NAME
AND D.COLUMN_NAME = DTC.COLUMN_NAME
AND D.OWNER = DTC.OWNER
AND D.TABLE_NAME = 'G_PROD_USER_CONF'
AND D.OWNER = 'SCOTT'
ORDER BY DTC.COLUMN_ID;
返回结果，只列举部分：
& 说明：
有关COMMENT的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2642615/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-08-29，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
短信
腾讯云短信（Short Message Service，SMS）可为广大企业级用户提供稳定可靠，安全合规的短信触达服务。用户可快速接入，调用 API / SDK 或者通过控制台即可发送，支持发送验证码、通知类短信和营销短信。国内验证短信秒级触达，99%到达率；国际/港澳台短信覆盖全球200+国家/地区，全球多服务站点，稳定可靠。