主页 / dba / 问题 / 214804
Accepted
Nick Chammas
Asked: 2018-08-14 08:37:02 +0800 CST

ALL_TABLES 和 TABLES 之间有什么区别？

  • 772

文档没有启发性：

  • 所有表：

    提供有关 Vertica 数据库中表的摘要信息。

  • 表格：

    提供有关数据库中所有表的信息。

我问的原因是用户对为什么他们可以在一个表中看到某些表而在另一个表中看不到感到困惑。它与权限有关，但不清楚具体如何。

例如，一个用户告诉我他们有权查询一个表。但是，该表未显示在 中TABLES，其列也未显示在 中COLUMNS，但该表确实显示在 中ALL_TABLES。

因此，这导致了以下问题：

  1. ALL_TABLES和 和有什么不一样TABLES？
  2. 用户如何查看他们有权查询的所有表（和列！）的列表？
vertica

1 个回答

  1. Best Answer

    ALL_TABLES 显示表和视图。TABLES 仅显示表格：

    dbadmin=> create or replace view test as select now() ;
CREATE VIEW
dbadmin=> 
dbadmin=> select * from test;
              now              
-------------------------------
 2018-08-13 13:04:24.500297-04
(1 row)

dbadmin=> 
dbadmin=> select * from all_tables where table_type = 'VIEW';
 schema_name |     table_id      | table_name | table_type | remarks 
-------------+-------------------+------------+------------+---------
 public      | 45035996273742356 | test       | VIEW       | 
(1 row)

dbadmin=> 
dbadmin=> 
dbadmin=> 
dbadmin=> select table_name, owner_name from tables where table_name = 'test' ;
 table_name | owner_name 
------------+------------
(0 rows)

dbadmin=> 
dbadmin=>

    还有一个名为视图的系统表：

    dbadmin=> 
dbadmin=> 
dbadmin=> 
dbadmin=> select table_name, owner_name from views ;
 table_name | owner_name 
------------+------------
 test       | dbadmin
(1 row)

    用户可以从表、all_tables 和视图中进行选择，以查看他对哪些表和视图具有权限。我相信我是从 http://www.vertica-forums.com/viewtopic.php?t=726得到的

    这是我用作 DBADMIN 的工具来总结权限：

     SELECT X.object_type,
        X.object_schema,
        X.object_name,
        X.permissions,
        X.grantor,
        X.owner,
        X."grant_sql (for permissions that have '*')"
 FROM ( SELECT 1 AS ordinal,
        g.object_schema,
        g.object_name,
        g.object_type,
        CASE WHEN (g.privileges_description = 'USAGE'::varchar(5)) THEN (g.privileges_description || ' (READONLY access) '::varchar(19)) WHEN (g.privileges_description ~~ '%CREATE%'::varchar(8)) THEN (g.privileges_description || ' (can read, create & drop tables) '::varchar(34)) ELSE NULL END AS permissions,
        g.grantor,
        s.schema_owner AS owner,
        CASE WHEN (g.privileges_description ~~ '%*%'::varchar(3)) THEN (('grant <INSERT, SELECT, UPDATE, DELETE, REFERENCES> on '::varchar(54) || g.object_name) || ' to <user_name> ; '::varchar(18)) ELSE NULL END AS "grant_sql (for permissions that have '*')"
 FROM (v_catalog.grants g JOIN v_catalog.schemata s ON ((g.object_name = s.schema_name)))
 WHERE (g.object_type = 'SCHEMA'::varchar(6)) UNION  SELECT 2 AS ordinal,
        g.object_schema,
        ((t.table_schema || '.'::varchar(1)) || t.table_name) AS object_name,
        g.object_type,
        g.privileges_description AS permissions,
        g.grantor,
        t.owner_name AS owner,
        CASE WHEN (g.privileges_description ~~ '%*%'::varchar(3)) THEN (('grant <usage, create> on '::varchar(25) || g.object_name) || ' to <user_name> ; '::varchar(18)) ELSE NULL END AS "grant_sql (for permissions that have '*')"
 FROM (v_catalog.grants g JOIN v_catalog.tables t ON (((g.object_schema = t.table_schema) AND (g.object_name = t.table_name))))
 WHERE (g.object_type = 'TABLE'::varchar(5))) X
 ORDER BY X.ordinal,
          X.object_name;
    • 2

相关问题