Lock Table Vs Rename

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Lock Table Vs Rename

Hi,

 

1) I have requirement to lock tables in Production environment to restrict all the users accessing particular tables in multiple databases and kindly help me on how we can achieve this ?

 

The below is the response from my DBA:

Access are not grated on table level, they are usually at Role, database level. Rename approach is the most used method

 

2) Does the Statistics of the Table changes if we Rename the Table ? I believe there should not be any changes in the Statistics & Indexes if the tables are Renamed, Kindly confirm.

 

Thanks,

Arun

2 REPLIES
Enthusiast

Re: Lock Table Vs Rename

Appreciate if anyone can help on this topic.

 

Thanks,

Arun

Teradata Employee

Re: Lock Table Vs Rename

From the SQL Data Definition Language Detailed Topics manual:

 

Tables That Have Hash or Join Indexes Cannot Be Renamed

  You cannot rename a table on which a hash or join index is defined. You must first drop the

  index, and then you can rename the table.

Function of RENAME Table Requests

  When you rename a table, Teradata Database only changes the table name. All statistics and

  privileges belonging to the table remain with it under the new name. [Indexes other than join indexes are unchanged.]

 

 

Users will receive a "does not exist" error if they reference the renamed object with the old name. If they figure out the new name, they would be able to access it with the new name.

 

If the restriction on the object is to be temporary, another choice would be to add a rule(s) to workload management to restrict certain users from using certain objects or entire databases.

If the restriction is to be permanent, it would be better to remove the access rights.