CREATE THREE DIFFERENT USER AND SCHEMA i.e. where access should be limited to their own scehma only

Hello Everyone

I wonder how can i do the following CREATION ?

 

My problem here is CREATE and DROP is performing publicly which means if i create any table from user 1 then user 2 can access it and drop it but i want CREATE and DROP activities on own schema privately where if user 1 create any tables then user 2 should not see and neither could drop the table. Current situation is Right now i am able to drop it from other user as well.For example if create table named yy from hmstg_0405xx_data user then hcstg_0405xx_config can access it and also able to drop. But my requirement is i don't want any of the below mentioned three user access any other's users tables but all user user should have CREATE AND DROP.  Please HELP me.


create three schema with following name in vertica

“pm_0405xx_config”, “pm_0405xx_data” and “pm_0405xx_analytics with following password.

USER                            PASSWORD

---------------------------------------------

hmstg_0405xx_data                        add@t@201

hcstg_0405xx_config                     cat@t@201

antstg_0405xx_analytics                mouse@t@201

 

 

and the access should be limited to  their own schema only .

 They should be able to create and  drop object inside their own schema  only .




GRANT USAGE ON SCHEMA PUBLIC to pm_0405xx_config;


GRANT USAGE ON SCHEMA pm_0405xx_data TO Joe;


 create schema if not exists pm_0405xx_config;
 

hmstg_0405xx_data                        add@t@201
hcstg_0405xx_config                     cat@t@201
antstg_0405xx_analytics                mouse@t@201
create user hmstg_0405xx_data identified by 'add@t@201';
create user hcstg_0405xx_config identified by 'cat@t@201';
create user antstg_0405xx_analytics identified by 'mouse@t@201';
 

 


GRANTING USER TO PARTICULAR SCHEMA ONLY.FOR EXAMPLE

GRANT USAGE ON SCHEMA pm_0405xx_data TO Joe;

GRANT USAGE ON SCHEMA PUBLIC to pm_0405xx_config;
 create schema if not EXISTS pm_0405xx_data
 

 create schema if not EXISTS pm_0405xx_analytics
 
CREATE ROLE NOW
Create role PM_role;
grant usage on schema hell to hmstg_0405xx_data;
Grant  create on hell.TEST_SAS_MONTHLY_20730131_2073131 to PM_role;
Grant usage on a particular schema to the Read only role
Grant usage on schema pm_0405xx_config, pm_0405xx_data, pm_0405xx_analytics to PM_role;
 

Grant select privileges on some or all tables of a particular schema to the Read only role
ROLLBACK 4123:  No user or role
Grant SELECT,CREATE,DROP  to PM_role;
============================
“pm_0405xx_config”, “pm_0405xx_data” and “pm_0405xx_analytics with following password.
USER                            PASSWORD
---------------------------------------------
hmstg_0405xx_data                        add@t@201
hcstg_0405xx_config                     cat@t@201
antstg_0405xx_analytics                mouse@t@201
GRANT CREATE ON SCHEMA PUBLIC to hmstg_0405xx_data;
GRANT USAGE ON SCHEMA hell to hmstg_0405xx_data;
GRANT USAGE ON SCHEMA hell to hcstg_0405xx_config ;
GRANT USAGE ON SCHEMA hell to antstg_0405xx_analytics;
 

GRANT USAGE ON SCHEMA pm_0405xx_data to hmstg_0405xx_data;
 

GRANT create on TestAqua TO hmstg_0405xx_data WITH GRANT OPTION;
grant all on schema  pm_0405xx_data  to hmstg_0405xx_data;
grant all on schema  pm_0405xx_config to hcstg_0405xx_config;
grant all on schema  pm_0405xx_analytics  to antstg_0405xx_analytics;

hell>>> GRANT ALL PRIVILEGES ON TABLE hell. TEST_SAS_MONTHLY_20730131_2073131 TO hmstg_0405xx_data;
hell>>> GRANT ALL PRIVILEGES ON TABLE hell.TEST_SAS_MONTHLY_20730131_2073131 TO hcstg_0405xx_config;
hell>>> GRANT ALL PRIVILEGES ON TABLE hell.TEST_SAS_MONTHLY_20730131_2073131 TO antstg_0405xx_analytics;

hell>>> GRANT CREATE ON TABLE hell. TEST_SAS_MONTHLY_20730131_2073131 TO hmstg_0405xx_data;
dbadmin>>> GRANT CREATE ON DATABASE TestAQA TO hmstg_0405xx_data;
REVOKE ALL PRIVILEGES ON TABLE INT_STG_TB_DW_OCS_SUBFNLIST TO hmstg_0405xx_data;
 

dbadmin=> GRANT USAGE ON SCHEMA hell TO hcstg_0405xx_config;
GRANT PRIVILEGE
dbadmin=> GRANT USAGE ON SCHEMA antstg_0405xx_analytics TO antstg_0405xx_analytics;
ROLLBACK 4650:  Schema "antstg_0405xx_analytics" does not exist
dbadmin=> GRANT USAGE ON SCHEMA pm_0405xx_analytics TO antstg_0405xx_analytics;
GRANT PRIVILEGE
dbadmin=>

GRANTING CREATE AND DROP TO USER
hmstg_0405xx_data                        add@t@201
hcstg_0405xx_config                     cat@t@201
antstg_0405xx_analytics                mouse@t@201
DBADMIN>>>>GRANT CREATE ON SCHEMA PUBLIC to hmstg_0405xx_data;
DBADMIN>>>>GRANT CREATE ON SCHEMA PUBLIC to hcstg_0405xx_config;
DBADMIN>>>>GRANT CREATE ON SCHEMA PUBLIC to antstg_0405xx_analytics;
REVOKE PUBLIC ON SCHEMA pm_0405xx_data to hmstg_0405xx_data;
“pm_0405xx_config”, “pm_0405xx_data” and “pm_0405xx_analytics with following password.
 

====================================================
GRANT CREATE ON PARTICLUAR SCHEMA
REVOKE PUBLIC ON SCHEMApm_0405xx_data FROM hmstg_0405xx_data;
“pm_0405xx_config”, “pm_0405xx_data” and “pm_0405xx_analytics with following password.
USER                            PASSWORD
---------------------------------------------
hmstg_0405xx_data                        add@t@201
hcstg_0405xx_config                     cat@t@201
antstg_0405xx_analytics                mouse@t@201
dbadmin=> GRANT CREATE ON SCHEMA pm_0405xx_analytics to antstg_0405xx_analytics;
GRANT PRIVILEGE
dbadmin=> GRANT CREATE ON SCHEMA pm_0405xx_data to hmstg_0405xx_data;

dbadmin=> GRANT CREATE ON SCHEMA pm_0405xx_configto hcstg_0405xx_config;

 

Comments

  •  Hi ,

     

    Just to make this clear ! 

     

    You have 3 users and you don`t want them to have access to any other objects but theirs ? (correct?)

     

    Well from what i see below it should work. 

     

    But i do an example here : 

     

    Create two schemas:

     

    select dba.add_schema('schema1');
    select dba.add_schema('schema2');

    Create two users :

     

     

    create user usr_schema1 identified by 'usr1';
    create user usr_schema2 identified by 'usr2';

    See who has grants until now on the created schemas:

     

     

    select 
    privileges_description,
    object_name,
    object_type,
    grantee
    from grants
    where object_type='SCHEMA' and
    object_name in ('schema1','schema2')
    and grantee !='dbadmin';
    privileges_description object_name object_type grantee
    ---------------------- ----------- ----------- -------------------
    SELECT schema1 SCHEMA schema1_datareader
    USAGE schema1 SCHEMA schema1_schemausage
    INSERT, UPDATE, DELETE schema1 SCHEMA schema1_datawriter
    SELECT schema2 SCHEMA schema2_datareader
    USAGE schema2 SCHEMA schema2_schemausage
    INSERT, UPDATE, DELETE schema2 SCHEMA schema2_datawriter

    Note :

     

    - my UDP dba.add_schema creates the schema + the datareader + datawriter + schemausage roles.

    Check the post about how to create one like this here.

     

    Next we grant acces to the roles acordingly :

     grant schema1_datareader,schema1_schemausage to usr_schema1;

    Check agian the access to the schemas and see we don`t have users having direct access, all is done thru roles:

    select 
    privileges_description,
    object_name,
    object_type,
    grantee
    from grants
    where object_type='SCHEMA' and
    object_name in ('schema1','schema2')
    and grantee !='dbadmin';
    privileges_description object_name object_type grantee
    ---------------------- ----------- ----------- -------------------
    SELECT schema1 SCHEMA schema1_datareader
    USAGE schema1 SCHEMA schema1_schemausage
    INSERT, UPDATE, DELETE schema1 SCHEMA schema1_datawriter
    SELECT schema2 SCHEMA schema2_datareader
    USAGE schema2 SCHEMA schema2_schemausage
    INSERT, UPDATE, DELETE schema2 SCHEMA schema2_datawriter

    Set the default roles into user definition:

     alter user usr_schema1 default role schema1_datareader,schema1_schemausage;

    - this ti avois set role syntax.

     

    Now create an object in that schema try to access it with the user1(who we know we gave him access) and then with user2(who was just created).

     create table schema1.TBL1(id int) ;

    [dbadmin@aodba ~]$ vsql -U usr_schema1
    Password:
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type: \h or \? for help with vsql commands
    \g or terminate with semicolon to execute query
    \q to quit

    usr_schema1=> \dt
    List of tables
    Schema | Name | Kind | Owner | Comment
    ---------+------+-------+---------+---------
    schema1 | TBL1 | table | dbadmin |
    (1 row)



    -- switch to user2

    [dbadmin@prodvert01 ~]$ vsql -U usr_schema2
    Password:
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type: \h or \? for help with vsql commands
    \g or terminate with semicolon to execute query
    \q to quit

    usr_schema2=> \dt
    No relations found.

    -- he cannot see nothing

    usr_schema2=> drop table schema1.TBL1;
    ROLLBACK 3989: Must be owner of relation TBL1

    -- he cannot drop the tbl


    usr_schema2=> select * from schema1.TBL1;
    ERROR 3580: Insufficient privilege: USAGE on SCHEMA 'schema1' not granted for current user

     

    Was this what you were looking for ? 

     

  •   I may be wrong in my previous answer as your question is not very well formulated :)

    Put some work into making questions so other can get the idea.

     

      If you wanna avoid that other users can see other users objects in the public schema you have to make sure you revoke select on the public schema from them! this way they will see only their objects.

     

  • Hi Adrian

    First of all I heartly would like to thank you for the response. Also yes this is exact one i am looking every user should have seprate schema for each and should have thier own access only which means limited access.

     

    I tried creating two schemas like the below one but i could not create. 

     

    dbadmin=> select dba.add_schema('schema1');
    ERROR 4650: Schema "dba" does not exist

     

    HERE TESTAQUA is my database
    dbadmin=> select Tesaqua.add_schema('schema1');
    ERROR 4650: Schema "Testaqua" does not exist

     

    Since the error 4650 means SCHEMA NAME does not exist i even tried to gave  dbadmin as schema.
    dbadmin=> select dbadmin.add_schema('schema1');
    ERROR 4650: Schema "dbadmin" does not exist

     

    I have another schema name AQUA , i tried giving that schema name too but it did not work
    dbadmin=> select aqua.add_schema('schema1');
    ERROR 3457: Function aqua.add_schema(unknown) does not exist, or permission is denied for aqua.add_schema(unknown)
    HINT: No function matches the given name and argument types. You may need to add explicit type casts
    dbadmin=>

     

    I wonder where I am doing mistakes ? Can you guide me please. From the above query i gave schema name, database name  both but it did not work. From the below statement which you have mentioned under CREATE TWO SCHEMAS, My understanding is dba means database name and you have create new schema named SCHEMA 1 via ADD SCHEMA

     

    Create two schemas:

     

    select dba.add_schema('schema1');
    select dba.add_schema('schema2');

     

     

  • Hi,

     

    :) , ok this add_schema does not exists in  Vertica , this is something i created for my self. 

     

    See the link here on hoe to build the same for you. 

     

    http://www.aodba.com/create-vertica-schema-fly/

     

    Is just UDP, easy to install and is very useful in time .

     

     

     

  • Hi Adrian

    As per the above url. I followed the instruction accordingly from where i was able to CREATE SCHEMA and ROLE accordingly but I could not get the output on RIGHT GRANTS. For more clear i would like to put the every steps that i have trigger and the output as well so that you can suggest where i am lacking and not been able to RIGHT GRANTS ? PLEASE check the output of NINTH : CHECK IF ROLE HAVE RIGHT GRANTS ON NEW SCHEMA. Here i could not get any RIGHT GRANT OUTPUT.. PLEASE ADVISE

     

    FIRST : I created .profile file inside /home/dbadmin/.profile

     

    [dbadmin@vertica-aqua1 Catalog]$ more /home/dbadmin/.profile
    #!/bin/bash

    username=dbadmin
    password=************
    export username
    export password
    [dbadmin@vertica-testsrv1 Catalog]$

     

     

    SECOND : I CREATED  add_schema.sh file inside 

    /home/dbadmin/TestAqua/v_testaqua_node0001_catalog/Catalog

     

    #!/bin/bash
    . /home/dbadmin/.profile

    /opt/vertica/bin/vsql -U dbadmin -w vertica -t -c"
    create schema schema1;
    --add inheritance
    ALTER SCHEMA SCHEMA1 DEFAULT INCLUDE PRIVILEGES;

    --create roles
    create role schema1_datareader;
    create role schema1_datawriter;
    create role schema1_schemausage;

    --grant specific access to role
    grant select on schema schema1 to schema1_datareader;
    grant usage on schema schema1 to schema1_schemausage;
    grant insert,update,delete on schema schema1 to schema1_datawriter;
    "

    THIRD : I trigger following command

    chmod 4750 add_schema.sh

     

    FOURTH : I opened admintools, went through CONFIGURATION MENU >> Install External Procedure >>

    SELECT DATABASE NAME, CLICK OK

    A window will appear which asked for --->Select external procedure file to install. ( I selected add_schema.sh)  as /home/dbadmin/TestAqua/v_testaqua_node0001_catalog/Catalog/add_schema.sh

     

    After giving add_schema.sh full path , I clicked on OK and a message called  External procedure installed appeared. After then I exit from admintools.

     

    OR Instead of going through admintools it can be created via command as following :-

    admintools -t install_procedure -d TestAqua -f /home/dbadmin/TestAqua/v_testaqua_node0001_catalog/Catalog/add_schema.sh -p *********

     

    FIFTH : I created a schema name DBA

    create schema dba;

     

    SIXTH : I trigger following query 

     

    dbadmin-> /opt/vertica/bin/vsql -U dbadmin -w vertica-c "CREATE PROCEDURE dba.add_schema(schema_name varchar) AS 'add_schema.sh' LANGUAGE 'external' USER 'dbadmin';"


    dbadmin-> \q


    [dbadmin@vertica-testsrv1 Catalog]$ /opt/vertica/bin/vsql -U dbadmin -w vertica-c "CREATE PROCEDURE dba.add_schema(schema_name varchar) AS 'add_schema.sh' LANGUAGE 'external' USER 'dbadmin';"
    vsql: FATAL 2983: Database "CREATE PROCEDURE dba.add_schema(schema_name varchar) AS 'add_schema.sh' LANGUAGE 'external' USER 'dbadmin';" does not exist

     

    SEVENTH : CHECK IF SCHEMA CREATED . Schema is created

     

    dbadmin=> select schema_name,schema_owner from schemata where schema_name ilike '%schema%';
    schema_name | schema_owner
    -------------+--------------
    schema1 | dbadmin
    (1 row)

    dbadmin=>

     

    EIGHTH : CHECK IF ROLES HAS BEEN CREATED

    dbadmin=> select * from roles where name ilike '%schema%';
    role_id | name | assigned_roles
    -------------------+---------------------+----------------
    45035996290581228 | schema1_datareader |
    45035996290581230 | schema1_datawriter |
    45035996290581232 | schema1_schemausage |
    (3 rows)

    dbadmin=>

     

     

    NINTH : CHECK IF ROLE HAVE RIGHT GRANTS ON NEW SCHEMA

     

     

    dbadmin=> select grantor,privileges_description,object_name,object_type,grantee
    dbadmin-> from grants where grantee in (select name from roles where name ilike '%schema%');
    grantor | privileges_description | object_name | object_type | grantee
    ---------+------------------------+-------------+-------------+---------
    (0 rows)

    dbadmin=>

  • Hi ,

     

    You need to put your add_schema.sh in your procedure node folder (all the nodes)

  •  

    Regarding your grants question ! 

     

    Do you have ny objects  created in that schema ? you need to have tables in there to be able show up.

     

  • Hi Adrian

    add_schema.sh is present on all nodes. I re-checked it and find that this file is present on all node

     

    [dbadmin@vertica-testsrv1 TestDB]$ cd procedures/
    [dbadmin@vertica-testsrv1 procedures]$ ls
    add_schema.sh
    [dbadmin@vertica-testsrv1 procedures]$ pwd
    /home/aqua/TestDB/procedures
    [dbadmin@vertica-testsrv1 procedures]$

     

     

     

  • Do you have ny objects  created in that schema ? you need to have tables in there to be able show up.

    Question : Here do you mean like. I have to create table inside new schema which is mentioned above named SCHEMA 1 ? Please advise

  • Ok i am bit confused :)

     

    1 - Did the creation of thew UDP worked ? 

     

    2 - In order to have anything shown in the grants table as granted to the roles, you need to create the table in those schemas, they will inherit the grants becouse of the inheritage option we used to create the schema.

    Once the tables are creaed you dont need to explicitly grant access to them.

     

    Thx 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file