Archives of the TeradataForum

Message Posted: Fri, 16 Jan 2004 @ 11:46:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Patrick,

I understand the confusion ! Let me try an explain AccessRights and the Teradata hierarchy.

Firstly, an AllNessFlag vale of "Y" means that this AccessRight will be inherited by a user that is created lower down in this branch of the database/user hierarchy. As an example, assume that:

-- there is a row in dbc.accessrights with AllNessFlag = "Y", username="DevUsers", Databasename="DB1", TableName="All", accessright = "Select"

-- there is a row in dbc.accessrights with AllNessFlag = "N", username="DevUsers", Databasename="DB1", TableName="All", accessright = "Update"

-- a new user is created with the command "Create user Devuser2 from DevUsers......." (this puts DevUser2 under DevUsers in the hierarchy)

As a result of the "create user" command being executed (and ONLY thinking about the accessrights mentioned above), the accessrights table will contain the following row

-- AllNessFlag = "N", username= "DevUser2", Databasename="DB1", TableName="All", accessright = "Select" <== this accessright has been 'inherited' by DevUser2 from DevUsers.

Note that the accessright mentioned above which grants Update privileges NOT inherited - because the AllNessFlag = "N".

Secondly, having created user DevUser2 under DevUsers in the hierarchy, if we issue the command "Grant Delete on DB1 to All DevUsers", the following rows will be inserted into the dbc.accessrights table;

-- AllNessFlag = "Y", username= "DevUsers", Databasename="DB1", TableName="All", accessright = "Delete"

-- AllNessFlag = "N", username= "DevUser2", Databasename="DB1", TableName="All", accessright = "Delete"

Finally, I'm not sure about the problems that you've described, but I think something else was done because what you've described (a higher level user granting themselves privileges) shouldn't - and doesn't in my experience - "wipe out" lower level users' privileges.

In Teradata, a user has all rights over any object lower down in the hierarchy (hence DBC has ALL rights over ALL objects). However, a higher level user may need to grant itself a privilege before it can execute a command. Thus, even logged on as DBC you may get a return code which says "security violation - you can't do this", but you ARE allowed to grant yourself the privilege to execute the command.

Using the example from before, with DevUsers being at a higher level in the hierarchy to Devuser2, assume DevUser2 has created a table T1 in it's database.

-- user DevUsers may issue "Select * from devuser2.T1..." and receive a "security violation" error, BUT

-- user DevUsers can then issue "Grant select on devuser2.T1 to devusers;" and then re-issue the Select.

By DevUsers issuing the Grant command for itself, NO OTHER user's should have their accessrights to this table affected. If that is really happening then I think you need to talk to NCR, it's not right.

Hope that helps.

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>