1
0
The IMPORTED PRIVILEGES works only on a shared database. Note that for shares, you can’t grant permissions on individual objects ,instead permission is granted to entire Schema of shared database. Thus, Granting individual privileges on imported database is not allowed..
Use GRANT IMPORTED PRIVILEGES when granting a table privilege to a role.
Since this is a shared database (reader account or consumer account), one can only provide IMPORTED PRIVILEGES to access the objects and apart from this, no other can be granted. If you want to only share one or few tables from a shared database to a particular role, then you will need a separate share for this role with just that table.
GRANT IMPORTED PRIVILEGES ON DATABASE TO ROLE –
Sharing provides read access on ‘all’ objects in the database to a role, instead IMPORTED PRIVILEGES to limit read access to subset of objects in database instead of all objects in database.
Consider a scenario where we have 4 tables in a shared database, On the consumer side Read access would be available on ALL objects and we cannot directly grant subsets of privileges on objects, to roles created on a shared database. We want these tables to be shared to the consumer account but the access should be provided in below hierarchy.
T1,T2 à Access to Role FLM
T3,T4 à Access to Role OPS
So in Provider Account, We will create two shares
create share SHARE1 ;
create share SHARE2 ;
grant select on table T1,T2 in schema DEMO_DB..PUBLIC to share SHARE1;
grant select on table T3,T4 in schema DEMO_DB..PUBLIC to share SHARE2;
Create the READER Account and assign these SHARES to READER ACCOUNT
In the Consumer side:
create database FLM_DB from share bza02481.SHARE1;
create database OPS_DB from share bza02481.SHARE2;
create role FLM;
create role OPS;
create user FLM password='Thakurji@1234' default_role = FLM must_change_password = false;
create user OPS password='Thakurji@1234' default_role = OPS must_change_password = false;
grant imported privileges on database FLM_DB to role FLM;
grant imported privileges on database OPS_DB to role OPS;;
LOGIN to FLM: We will be able to see only ST1 and ST2 tables:
LOGIN to OPS:
IMPORTED PRIVILEGE applies to only shared databases. It grants ability to enable roles other than the owning role to access a shared database.
