【DB笔试面试627】在Oracle中,对表执行COMMENT(注释)操作需要什么权限?

题目部分

在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程序员面试笔试宝典》,作者:李华荣。

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-08-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券