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

UJJWAL_RANA · September 2016

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;

Adrian_Oprea_1 · September 2016

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 ?

Adrian_Oprea_1 · September 2016

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.

UJJWAL_RANA · September 2016

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');

Adrian_Oprea_1 · September 2016

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 .

UJJWAL_RANA · September 2016

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

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

Adrian_Oprea_1 · September 2016

Hi ,

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

Adrian_Oprea_1 · September 2016

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.

UJJWAL_RANA · September 2016

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]$

UJJWAL_RANA · September 2016

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

Adrian_Oprea_1 · September 2016

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

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

Comments

Leave a Comment