Hello All,
I observed a strange behaviour by the system tables.
I have a database "A"
schema "t_schema"
users - a1,a2,a3
I expect the v_catalog.tables should show me all the tables in the database "A" irrespective of the owner they are created from
Suppose user a1 creates a table , he can see the table in the v_catalog.tables, but user a2 cannot see the table in v_catalog.tables.
Suppose user a2 creates a table, he cannot see the table in v_catalog.tables niether can a1 see the table created by user a2 in the v_catalog.tables.
User a1 can see tables created by x,y,z owners in the v_catalog.tables
When granted access to user a2 by user a1, a2 can access the table in the select clause.
Can anybody explain this behaviour. please
strange behaviour by system tables
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: strange behaviour by system tables
Hi,
I believe this is expected behavior. Users can only see tables in the system table TABLES in which they own or have been granted access privileges. A super user (those with dbadmin role) can see all tables all the time.
It works the same in Oracle...
I believe this is expected behavior. Users can only see tables in the system table TABLES in which they own or have been granted access privileges. A super user (those with dbadmin role) can see all tables all the time.
It works the same in Oracle...
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: strange behaviour by system tables
Hello knicely,
I thought the same, and tried giving access to user a2 for a table created by user a1
Doing this, user a2 was able to query table created by user a1 but still he couldn't see the table in his system_tables v_catalog.tables
When user a1 revoked the permissions from a2, user a2 was not able to query the table created by user a1 again.
So, even if user a2 had access privileges on table created by user a1, still he couldn't find it in the system tables.
Moreover, user a1 can see table created by some other users in the system tables v_catalog.tables
I thought the same, and tried giving access to user a2 for a table created by user a1
Code: Select all
Grant all on table_name to a2;
When user a1 revoked the permissions from a2, user a2 was not able to query the table created by user a1 again.
So, even if user a2 had access privileges on table created by user a1, still he couldn't find it in the system tables.
Moreover, user a1 can see table created by some other users in the system tables v_catalog.tables
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: strange behaviour by system tables
In the following example, notice how the user JANE cannot see the table named JIM_SCHEMA.JIM_TABLE in the V_CATALOG.TABLES system table until she is granted a privilege on JIM_SCHEMA.JIM_TABLE...
And the same goes if user JANE creates a table. User JIM can only see the table JANE creates in the V_CATALOG.TABLES system table if he has been given a privilege on JANE's table...
Code: Select all
dbadmin=> create schema jim_schema;
CREATE SCHEMA
dbadmin=> create user jim;
CREATE USER
dbadmin=> grant all on schema jim_schema to jim;
GRANT PRIVILEGE
dbadmin=> create user jane;
CREATE USER
dbadmin=> grant usage on schema jim_schema to jane;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> create table jim_schema.jim_table (c1 int);
CREATE TABLE
dbadmin=> insert into jim_schema.jim_table values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select table_name from v_catalog.tables;
table_name
------------
jim_table
(1 row)
dbadmin=> \c dbadmin jane;
You are now connected to database "dbadmin" as user "jane".
dbadmin=> select table_name from v_catalog.tables;
table_name
------------
(0 rows)
dbadmin=> select * from jim_schema.jim_table;
ERROR 4367: Permission denied for relation jim_table
dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> grant select on jim_schema.jim_table to jane;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jane;
You are now connected to database "dbadmin" as user "jane".
dbadmin=> select table_name from v_catalog.tables;
table_name
------------
jim_table
(1 row)
dbadmin=> select * from jim_schema.jim_table;
c1
----
1
(1 row)
Code: Select all
dbadmin=> \c dbadmin dbadmin
You are now connected to database "dbadmin" as user "dbadmin".
dbadmin=> grant all on schema jim_schema to jane;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jane
You are now connected to database "dbadmin" as user "jane".
dbadmin=> create table jim_schema.jane_table (c1 int);
CREATE TABLE
dbadmin=> insert into jim_schema.jane_table values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select table_name from v_catalog.tables;
table_name
------------
jim_table
jane_table
(2 rows)
dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> select table_name from v_catalog.tables;
table_name
------------
jim_table
(1 row)
dbadmin=> select * from jim_schema.jane_table;
ERROR 4367: Permission denied for relation jane_table
dbadmin=> \c dbadmin jane
You are now connected to database "dbadmin" as user "jane".
dbadmin=> grant delete on jim_schema.jane_table to jim;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jim
You are now connected to database "dbadmin" as user "jim".
dbadmin=> select table_name from v_catalog.tables;
table_name
------------
jim_table
jane_table
(2 rows)
dbadmin=> select * from jim_schema.jane_table;
ERROR 4367: Permission denied for relation jane_table
dbadmin=> delete from jim_schema.jane_table;
OUTPUT
--------
1
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: strange behaviour by system tables
Hello knicely,
Thanks for a detailed explaination on this issue.
I tried this with two different users and this works very good.
But the problem still exists with the users a1 and a2.
I checked their privileges and they are identical. Still when user a1 grants user a2 'select' access to a table, The user a2 can only \d+ table and cannot see the table in the v_catalog.tables.
Thanks for a detailed explaination on this issue.
I tried this with two different users and this works very good.
But the problem still exists with the users a1 and a2.
I checked their privileges and they are identical. Still when user a1 grants user a2 'select' access to a table, The user a2 can only \d+ table and cannot see the table in the v_catalog.tables.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: strange behaviour by system tables
Sorry, but I cannot reproduce your problem
I tried to again, but my database us acting as expected:
I tried to again, but my database us acting as expected:
Code: Select all
dbadmin=> create user a1;
CREATE USER
dbadmin=> create user a2;
CREATE USER
dbadmin=> create schema t_schema;
CREATE SCHEMA
dbadmin=> grant all on schema t_schema to a1;
GRANT PRIVILEGE
dbadmin=> grant usage on schema t_schema to a2;
GRANT PRIVILEGE
dbadmin=> \c dbadmin a1;
You are now connected to database "dbadmin" as user "a1".
dbadmin=> create table t_schema.a1 (c1 int);
CREATE TABLE
dbadmin=> insert into t_schema.a1 values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> \c dbadmin a2;
You are now connected to database "dbadmin" as user "a2".
dbadmin=> select * from t_schema.a1;
ERROR 4367: Permission denied for relation a1
dbadmin=> \d t_schema.a1;
Did not find any relation.
dbadmin=> select * from v_catalog.tables where table_name = 'a1';
table_schema_id | table_schema | table_id | table_name | owner_id | owner_name | is_temp_table | is_system_table | system_table_creator | partition_expression | create_time | table_definition
-----------------+--------------+----------+------------+----------+------------+---------------+-----------------+----------------------+----------------------+-------------+------------------
(0 rows)
dbadmin=> \c dbadmin a1
You are now connected to database "dbadmin" as user "a1".
dbadmin=> grant select on t_schema.a1 to a2;
GRANT PRIVILEGE
dbadmin=> \c dbadmin a2;
You are now connected to database "dbadmin" as user "a2".
dbadmin=> select * from t_schema.a1;
c1
----
1
(1 row)
dbadmin=> \d t_schema.a1;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------+------+------+---------+----------+-------------+-------------
t_schema | a1 | c1 | int | 8 | | f | f |
(1 row)
dbadmin=> \x
Expanded display is on.
dbadmin=> select * from v_catalog.tables where table_name = 'a1';
-[ RECORD 1 ]--------+------------------------------
table_schema_id | 45035996273892722
table_schema | t_schema
table_id | 45035996273892728
table_name | a1
owner_id | 45035996273892714
owner_name | a1
is_temp_table | f
is_system_table | f
system_table_creator |
partition_expression |
create_time | 2013-07-09 11:13:34.347909+00
table_definition |
dbadmin=> select * from grants where object_name = 'a1';
-[ RECORD 1 ]----------+------------------
grant_id | 45035996273892858
grantor_id | 45035996273892714
grantor | a1
privileges_description | SELECT
object_schema | t_schema
object_name | a1
object_id | 45035996273892728
object_type | TABLE
grantee_id | 45035996273892718
grantee | a2
dbadmin=> \c dbadmin dbadmin
You are now connected to database "dbadmin" as user "dbadmin".
dbadmin=> select * from grants where object_name = 'a1';
-[ RECORD 1 ]----------+------------------------------------------------
grant_id | 45035996273892856
grantor_id | 45035996273892714
grantor | a1
privileges_description | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*
object_schema | t_schema
object_name | a1
object_id | 45035996273892728
object_type | TABLE
grantee_id | 45035996273892714
grantee | a1
-[ RECORD 2 ]----------+------------------------------------------------
grant_id | 45035996273892858
grantor_id | 45035996273892714
grantor | a1
privileges_description | SELECT
object_schema | t_schema
object_name | a1
object_id | 45035996273892728
object_type | TABLE
grantee_id | 45035996273892718
grantee | a2
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.