Restricting users from updating columns

Database

Restricting users from updating columns

Hi,

I need a mechanism by which certain users are empowered to update some of the columns in a (very small) table, but not all columns. Also, the users should not be able to insert to/delete from this table. Is there an easy way to achieve this?

The users will likely use MS Access via ODBC connection to access the Teradata table.

Thanks,
Nikhil
4 REPLIES
Enthusiast

Re: Restricting users from updating columns

Do you need the users to be able to view all columns for the table? If not, then you can set up a view layer that only exposes those columns these users are authorized to interact with. Then grant users SELECT/UPDATE access to the VIEW, not the table, and you should be good to go.

If you need them to be able to view all columns of the table but only update some, you should probably look into column-level access rights.

Re: Restricting users from updating columns

Thanks for the response.

Yes, the users should be able to view all columns. The DBA seems to think that column-level access permission is not possible in Teradata and suggested that this may have to be implemented through procedures (To update the columns the users may have to call a stored procedure with parameters specifying the column and values etc). This seems to me like asking too much from the user. So I was wondering if there is an easier way.

Thanks.
Enthusiast

Re: Restricting users from updating columns

Column level access rights are possible in Teradata. The syntax for the command would look like this:

GRANT UPDATE("ColumnName") ON "DatabaseName"."TableName" TO "UserName/RoleName";

I'd recommend creating a role and assigning the rights to update the specific columns to that role. Then you can control which users get the access through role memberships.
Enthusiast

Re: Restricting users from updating columns

I suggest considering using a Stored Procedure with a built in update of an audit table so that you can keep track of who did what update when.