Permissions to a view

Database
Enthusiast

Permissions to a view

Hello All,

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

9 REPLIES
Supporter

Re: Permissions to a view

The database XYZ need select and inserts rights on either PQR or PQR.aaa with grant options.

Enthusiast

Re: Permissions to a view

Thanks!!

Enthusiast

Re: Permissions to a view

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.

Re: Permissions to a view

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"

Re: Permissions to a view

grant select on <tablesdb> to <viewsdb> with grant option;

the above one will work for this

Enthusiast

Re: Permissions to a view

i have view say DB.v1 with 3 fields say

REPLACE VIEW DB.view1

(

f1,f2,f3

)

 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           ;

-----------------------------------My question.

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?

Enthusiast

Re: Permissions to a view

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. 

Enthusiast

Re: Permissions to a view

Hi Guys,

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 ?

Thanks.

Enthusiast

Re: Permissions to a view

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;