Skip to main content

CROSS VISIBILITY of objects in schema "PUBLIC" among users

  • July 15, 2017
  • 2 replies
  • 16 views
MaciejPaliwoda
Forum|alt.badge.img+2

Hi
I would like to achieve situation that every table or view created by the user is **automaticaly ** visible to all other users having access to that schema in my particular case PUBLIC.

I was trying such script :

__CREATE USER user1 IDENTIFIED BY 'password';
CREATE USER user2 IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON SCHEMA PUBLIC TO PUBLIC

ALTER SCHEMA "mpdb.public" DEFAULT INCLUDE SCHEMA PRIVILEGES;

CREATE ROLE Business;
GRANT ALL PRIVILEGES ON SCHEMA "public" TO Business;
GRANT SELECT ON SCHEMA PUBLIC TO Business;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "mpdb.public" to Business;
GRANT Business TO user1, user2;
ALTER USER user1 DEFAULT ROLE ALL;
ALTER USER user1 DEFAULT ROLE ALL;

---for user 1
--vsql -U user1 -w password -c "create table t2(a int);"
--CREATE TABLE
-- for user 2
--vsql -U user2 -w password -c "select * from all_tables where table_name='t2'"
/*
schema_name | table_id | table_name | table_type | remarks
-------------+----------+------------+------------+---------
(0 rows)
/__

But without success. What am I doing wrong?
Thx
M.

    2 replies

    TomM
    Forum|alt.badge.img
    • TomM
    • Participating Frequently
    • July 15, 2017

    It looks like you GRANTed all to the PUBLIC role in the PUBLIC schema, and I wonder if user2 needs to SET ROLE to PUBLIC to see what user1 created, even though you set DEFAULT ROLE ALL I've never seen that command and I wonder if it's working. I see in the docs a function labeled HAS_ROLE. I wonder if that would help you figure this out. https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/HAS_ROLE.htm

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • July 17, 2017

    @MaciejPaliwoda: You never set the default role for user2.

    This works:

    [dbadmin@s18384357 ~]$ vsql -c "CREATE USER user1 IDENTIFIED BY 'password';"
CREATE USER
[dbadmin@s18384357 ~]$ vsql -c "CREATE USER user2 IDENTIFIED BY 'password';"
CREATE USER
[dbadmin@s18384357 ~]$ vsql -c "ALTER SCHEMA \"sfdc.public\" DEFAULT INCLUDE SCHEMA PRIVILEGES;"
ALTER SCHEMA
[dbadmin@s18384357 ~]$ vsql -c "CREATE ROLE Business;"
CREATE ROLE
[dbadmin@s18384357 ~]$ vsql -c "GRANT CREATE ON SCHEMA public TO Business;"
GRANT PRIVILEGE
[dbadmin@s18384357 ~]$ vsql -c "GRANT SELECT ON SCHEMA public to Business;"
GRANT PRIVILEGE
[dbadmin@s18384357 ~]$ vsql -c "GRANT Business TO user1, user2;"
GRANT ROLE
[dbadmin@s18384357 ~]$ vsql -c "ALTER USER user1 DEFAULT ROLE ALL;"
ALTER USER
[dbadmin@s18384357 ~]$ vsql -c "ALTER USER user2 DEFAULT ROLE ALL;"
ALTER USER
[dbadmin@s18384357 ~]$ # Run as user1
[dbadmin@s18384357 ~]$ vsql -U user1 -w password -c "create table user1_tab (c1 int);"
WARNING 6978:  Table "user1_tab" will include privileges from schema "public"
CREATE TABLE
[dbadmin@s18384357 ~]$
[dbadmin@s18384357 ~]$ # Run as user2
[dbadmin@s18384357 ~]$ vsql -U user2 -w password -c "select * from user1_tab;"
 c1
----
(0 rows)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement