I am new to Teradata and little confused with giving the permissions for a view to a user. I have a user abc who has reand/insert permission on databases PQR and XYZ. Now i have created table aaa on PQR and a view bbb on XYZ which points to PQR.aaa and when i try to insert data(running insert statement after logging in as abc), i am getting an error "The owner referenced by user does not have insert with GRANT Option". What am i missing here? Any help would be highly appreciated.
Thanks in Advance!!!
norlammy we give select with grant access to the view.
I am not sure if the above method worked, if it did, then good.
else what you can do is :
to to TD admin tool, select the view and give it select with grant access.
If you are inserting into Table PQR.aaa via View XYZ.bbb, then Database XYZ should have Insert access on PQR.aaa, Please execute the below SQL and try inserting the records,
"GRANT INSERT ON PQR.aaa TO XYZ"
i have view say DB.v1 with 3 fields say
REPLACE VIEW DB.view1
AS LOCKING ROW FOR ACCESS SELECT f1,f2,f3 FROM DB.Table1 ;
and i have given the grants at column level
GRANT Select (f1) ON DB.view1 TO NO_PII_ROLE1 ;
GRANT Select (f2) ON DB.view1 TO NO_PII_ROLE2 ;
GRANT Select (f3) ON DB.view1 TO NO_PII_ROLE3 ;
1) i did not know that the Grants which i assign is internally linked to column id- pls correct me if i am wrong.
2) I tried to add a new field in between f2 and f3 say f1,f2,f2_1,f3 - i observer that the column f3 grants is getting dropped. Why is it? and how do i handle it?
Vijay Mani, Typically granting the access at the column level is not done as it becomes difficult to maintain and manage if there are a huge number of databases and table. it will be either at the table/view level or database level. To restrict the access to specific columns, a view is built with the columns excluded(which are not required to be shown).
When you added the column how was it added. is it through the add column phrase?. if so the column would ideally be the last column Please check the DBC.ALLROLERIGHTS to verify the rights for the roles.
I have created a view as REPLACE VIEW abcV.xyz as SELECT * from defV.xyz. I am getting error as 'USER DOES NOT HAVE SELECT WITH GRANT OPTION ACCESS on defV.xyz'. Where as i am able to do direct select on SELECT * FROM defV.xyz.
Please suggest on how to proceed ?
Execute the below statement, Your view database needs select access on referenced objects in the SQL
Grant select on defV to abcV with grant option;