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