Our database contains various tables created by various staff. What I need to do is control access to tables that I create so that I can authorize only 'x' number of staff to access and/or view only the data it contains. Can access control be incorporated at table creation time using SQL? I.e. Create Table psuser.MotorVehicle , No Fallback , No Before Journal , No after Journal -- Set access permission for userid x, y ,z etc. (col1 , col2 , col3 etc.) primary index (Col1); Once data is inserted then only x, y, & z staff would have access to table. I know that the dbc access right tables contain all our userid's but how to ensure that only the staff whom you want access to the table have that right? Any assistance would be greatly appreciated. Thanks
I’m not sure of your set up but when you create a table in a database no one has access to the table unless you grant a blanked access to everything in the database to everyone. So normally restricting access to a table or view is not an issue.
There is no way to set the permissions on a table as part of the create table statement. You have to use subsequent GRANT statements to allow access to a table. The permissions will determine what they can and what they can’t do with a table. In addition views can further restrict the access to what a particular user can see. So this can all be done.
I would suggest you take a look at setting ROLEs as part of your access control. You give access control to a role and that role is used to determine what tables and views can be accessed. Then you can grant the role to specific staff to give them different kind of access depending on your and their needs. With roles you don’t have to reassign all of the access rights to an individual user, if for example they are new, you just grant them a particular role that has all of the rights they need instead.
Thanks Supreme Being; I am aware of the dbc role tables and have also created views in the past. Appreciate your comments. I am however having difficulty in finding information on the Grants statement and how to apply it. From what I know all staff using our database have access to everyone elses tables etc. As I am not the administrator of the database and therefore unable to manipulate the dbc role and access rights tables, would it be a great impost upon your supremeness to steer me in the right direction in using the Grants statement? thanks muchly. If nothing else would you know the electronic version of the teradata manual that references the Grant statement? thanks.
Let’s assume you have database ‘Michael’ and you are the owner of that database. Assuming that other staff logon to the database as separate users they don’t have access to Michael database. The only way to grant them select access to a table in that database is with a grant statement. Suppose user Jim needs to access data in your table called MotorVehicle and this table is defined in the Michael database. The GRANT statement to give them select access to that table would be:
GRANT SELECT ON Michael.MotorVehicle TO Jim;
That would allow user Jim to have select access to table MotorVehicle. If you only want Jim to have access to selected data in that table then create a view that will only return information that you allow Jim to see. Give Jim select access on that view only and not the base table.