I have below 2 questions with regards to TD DBAs :
1. I want to understad the meaning of "select with grant option". Say i have a db mydata in which i create a table and a db myview in which i create a view referrring tables in mydata. I have a user ABC to which i have given select rights on DB myview and another user DEF whom i have given select with grant access on myviews. Now, i also have given "select with grant option" to myviews on my data.
Now, user DEF has a select with grant option on myviews can give a select access to mydata to any other user, as he has with grant rights on myviews that has grant rights on mydata in turn. But user ABC cant give such access as he doesn't have a with grant access to myviews.
Please let me know if my understanding is correct here any any inputs here ? Any link to a reference material will be helpful here.
2. We have created a dba reaonly role and have given read rights to all objects in all DB in the system to the role. Now, this role is assigned to the individual DBAs ID , who get rights to read all objects in all databases. But, when a dba logs to the sql assistent and queries "sel * from dbc.users", he can only see just himself, no one else. Whereas, if he logs in with sysdbaadmin id, he can see all users when fires the same query. why is this so and how can this be removed ? i want that the DBA should see all users when he logs in with indidual id ?
I will try to answer what I know from my understanding , someone correct me if I am wrong.
1 Select with grant option works exactly as it sounds, a user who has a select with grant option can give the access to the objects to the other user, so your undertstanding is correct.
2. If you do a show view, you would probably see where username = user, this is put in to ensure that you see only your ID's or ID's which are a part of your hierarchy .