ALL_TABLES 和 TABLES 有什麼區別？

這些文件令人費解：

• 所有表：

提供有關 Vertica 數據庫中表的摘要資訊。

• 表格：

提供有關數據庫中所有表的資訊。

我問的原因是使用者對為什麼他們可以在一個中看到一些表而在另一個中看不到的原因感到困惑。它與權限有關，但不清楚具體如何。

例如，使用者告訴我他們有權查詢表。但是，該表沒有出現在 中TABLES，其列也沒有出現在 中COLUMNS，但該表確實出現在 中ALL_TABLES。

因此，這會導致以下問題：

1. ALL_TABLES和有什麼區別TABLES？
2. 使用者如何查看他們有權查詢的所有表（和列！）的列表？

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=> 

還有一個叫做views的系統表：

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;

引用自：https://dba.stackexchange.com/questions/214804