Totally a noob when it comes to Teradata. But I need to know how to perform three tasks.
I just setup the Teradata Express VM for vmware. I am trying to create a NEW user who can do the following:
* select * from dbc.users
* modify any user's password up to and including DBC
* select * from dbcinfo
I am having a heck of a time figuring out the correct permissions or using the dbc user to grant various permissions to a new user.
Any help would be much appreciated.
To MODIFY another user's password, or to have dbc.UsersV return info for another user, the new user must have DROP USER permission on that other user. (So to meet your stated goal, you would need to grant DROP USER on all other users to that new user.)
But you can't grant DROP USER ON DBC. Only DBC can modify DBC's password.
By default SELECT is granted to PUBLIC for many of the DBC views.
Blowing the dust off of this old post...
I wish to grant access for two users to be able to reset a password for any user in a specific database that holds our non-ETL/app users. They have CREATE USER and DROP USER access in that database, but hit an error when attempting to MODIFY USER to reset a password as they don't have access to the user. Is there a way to automatically grant them DROP USER to any new user created in that database automatically when the new user is created. Otherwise we will have to grant the access to them every time a new user is created and that is an extra step we don't wish to add to the workflow.
Just to make sure that I've understood your user structure and requirements, I think your user hierarchy structure is something like the following.
Your non-ETL/App users are all owned by a single user.
- Non_ETL_App_user_owner -- USR_01 -- USR_02
You want two 'admin' users to be able to reset passwords on USR_01, USR02 etc.
Your two 'admin' userid's have DROP USER on 'Non-ETL_App_user_owner' but not on USR_01, USR_02 etc.
Assuming the above is correct...
Your 'admin' users can only change the password for USR_01 etc. if they have DROP USER on USR_01 or if they are an owner of USR_01.
So you could move your 'admin' users in the hierarchy to be an owner (not necessarily the immediate owner) of USR_01 etc. They might have to then grant themselves the DROP USER right, but being an owner, they can.
- moving your 'admin' users may not be possible in your organisation.
The other approach (I think) is to create a stored procedure in database 'Non_ETL_App_user_owner' using the SQL SECURITY OWNER option and then allow your 'admin' users to CALL that procedure. The procedure builds and executes the 'modify user' command.
- I am definitely not an expert on stored procedures but I think that will work.
@DaveWellman You understood it perfectly. These two users are also users under the same database. While I can give them OWNER privileges of USR_01 and USR_02 I don't really want to. I have a lot of users under that database and I don't want to have explicitly grant extra permissions for these two each time a new user is created (or dropped and recreated even).
However... that procedure idea may have some merit. Then I can keep their security out of it (except for EXECUTE PROCEDURE) and then see that the parent database has all the necessary rights to perform this that a SQL SECURITY OWNER proc could do the job. I feel like I may run into the same issues with the parent database's rights with new users, but I may try it anyway.
The only other thing I could think of was some sort of hamfisted script that runs every 5 minutes to grant this access to a role for any users found in this database. That just sounds like a recipe for disaster debugging though.