Chapter 9 - Access Rights

“Never engage in a battle of wits with an unarmed person.”

- Anonymous

The Objects That Require Access Rights

 

 

The above objects require access rights in order to access them.

Objects and Available Access Rights

Objects

The above objects require access rights in order to access them. These rights are listed as well and above you can also see an example of how rights can be granted.

A Few Examples to Get You Started

TeraTom can now use Select queries on the Employee_Table.

 

 

TeraTom can now use Select queries on the Employee_Table and he can grant this right to others.

These examples are designed to teach you the mere basics of rights on objects.

There are Three Types of Access Rights

Access Rights consist of Automatic Rights, Implicit Rights and Explicit Rights. Here is an explanation of each:

1.  Automatic Rights are often referred to as Default Rights because these are privileges that are automatically given to creators and, in the case of users and databases, each of their created objects.

2.  Implicit Rights are sometimes called Ownership Rights. Anybody above you in the Teradata hierarchy is considered your parent or owner and a parent has an implied right over their children. So these Owners (Parents) have the implicit right to grant rights on their owned objects (Children), either to themselves or to any other user or database. Ownership rights cannot be taken away unless ownership is transferred.

3.  Explicit rights are privileges a user explicitly grants to another user with an actual GRANT statement. This statement inserts a new rows into the DBC.AccessRights table. Explicit rights can be removed by explicitly using the REVOKE statement.

 

When a user submits a CREATE statement, new rows are inserted in the table DBC.AccessRights, and they are removed for an object if it is dropped.

There are Three Types of Access Rights

 

Implicit rights belong to the owners of objects. Owners don't require rows in the DBC.AccessRights table to grant privileges on owned objects. Ownership rights can't by revoked. A parent or owner has the implied right to GRANT privileges over their children. DBC and SysDBA hold implicit roles on all the other databases above.

Automatic rights happen whenever a CREATE statement is submitted and new rows are automatically added to the DBC.AccessRights table. When the databases above were created, they automatically received all but four access rights on themselves. Automatic rights are removed with REVOKE or DROP statements.

Explicit rights are completely controlled by when a user explicitly and literally submits a GRANT or REVOKE statement. The GRANT statement adds new rows to the DBC.AccessRights table, and the rights are granted. The REVOKE removes them.

There are Three Types of Access Rights

 

Explicit rights can only happen when a user explicitly (and literally) submits a GRANT or REVOKE statement. The GRANT statement adds new rows to the AccessRights table in DBC, and the rights are granted. The REVOKE removes them from the table.

DBC has Implicit Rights over SysDBA, and both of them have Implicit Rights over the Marketing, Finance, Sales, IT and HR databases. No rights are needed in the table DBC.AccessRights because it is obvious to Teradata who are parents Vs. children.

The databases Marketing, Finance, Sales, IT and HR were given Automatic rights when their CREATE statements were submitted. Each database received all rights except for four (CREATE Database, CREATE User, DROP Database and DROP User). SysDBA who created them were given all rights to them including the four exceptions.

A Dinner Invitation of Access Rights

Sometimes mom and dad just show up around dinner time unannounced with that hungry look on their face, and, of course, they are always welcome. This is implied because they brought you into this world, and (in their mind) they can take you out!

When your children eat dinner at their seats, they have automatic rights because you legally have to feed them. The children can't Create or Drop the meal unless you grant them permission.

When your children invite one of their friends over for dinner, it is allowed because you gave them permission to do so with a WITH GRANT OPTION. Your children then granted their friend an explicit right to come to your house. Of course, you or your children might change your mind and revoke the offer if something else comes up.

You have automatic rights in your own kitchen including Create meal, Drop meal, Create silverware, Drop silverware, because mom and dad granted you permission when you turned 18.

And of course sometimes grandma or grandpa can't keep their mouth shut and it is implied that they can grant themselves permission to give their advice to everyone at the table! It is also implied that you can chime in your two cents to your kids and their friends also.

One of the Problems with Access Rights

One of the issues with Access Rights is they take up so much room in the DBC.AccessRights table. This is a major reason to use Roles.

 

Below, our SysDBA user is going to create TeraTom. The following page will show the automatic rights attained by both SysDBA and TeraTom.

 

Now that SysDBA has just created TeraTom, turn the page and see the Access Rights.

The Rights for SysDBA and TeraTom

After SysDBA creates the user TeraTom, the following Access Rights are given to both SYSDBA and TeraTom over the user TeraTom:

SysDBA is also given these additional rights on TeraTom

As you can see above, just by creating a new user a lot of Access Rights go into the DBC.AccessRights table. One row is inserted for each and every right for both SysDBA and TeraTom. Roles actually help control the size and complexity of this table. Make sure you implement roles as your Access Rights strategy.

The GRANT Statement

The GRANT statement is used when you want to explicitly give to users, databases, or roles one or more privileges on an objects such as a database, user, table, view, macro, trigger, stored procedure, or user-defined function (UDF). To grant a privilege, you must have the privilege itself and have GRANT authority or be an owner (in which you then have automatic rights).

The recipient of an explicitly granted privilege may be:

• Specific user(s) or database(s)
• Specified role or roles
• Keyword PUBLIC which means every user in the system
• Keyword ALL DBC which means every user in the system
• Keyword ALL username or database which is the username or database and ALL descendants

The GRANT statement is used as an explicit right to grant access rights. The next few pages will teach you all of the fundamentals and you major options.

Create A Role and then Assign that Role It's Access Rights

Example

When a role is first created, it does not have any associated rights until grants are made to it.

 

We first create a role and it has no associated rights with it. Then, we grant the rights on the database or objects we want to that role. You can use the Grant or Revoke commands to add or take away rights for a role. Above, we created a role name Biz_Users_Role and then granted SELECT and EXECUTE on objects in that database.

GRANT to PUBLIC

GRANT SELECT ON BIZ TO PUBLIC ;

 

Database Biz

All current and future users have SELECT access to the database biz.

 

The PUBLIC keyword places on row per PUBLIC right in the DBC.AccessRights table. This is much more efficient than the way Teradata did it before their earlier V2.R5 release which placed one row per user per right thus flooding the table with rows. The new changes welcome the user of the PUBLIC keyword.

GRANT To ALL DBC

GRANT SELECT ON BIZ TO ALL DBC ;

 

Database Biz

All current and future users have SELECT access to the database biz.

 

The PUBLIC keyword places on row per PUBLIC right in the DBC.AccessRights table, and this is the exact same thing as using the ALL DBC keywords.

GRANT Using the ALL Keyword

We just granted all current and future users in sales and any current and future descendants below sales (such as Execs) SELECT access on the database Mrkt_Tables.

 

The ALL keyword is more “old school” because that is the way it was done before the implementation of roles. Now with roles, this technique isn't used as much.

GRANT Database Strategy for Users, Views and Tables

 

We just gave all the views in our Database S_Views SELECT WITH GRANT OPTION to their associated tables in the S_Tables database. Then, we used the ALL keyword to GRANT the users SELECT access to the views in the S_Views database. Brilliant!

Inheriting Access Rights

You may inherit access rights by the placement of your user in the hierarchy. The key is to set up access rights so that any new object added to an existing user or database automatically inherits specific access rights. This is done with the keyword “ALL”.

The immediate owner (user or database) of a view or table that is referenced by another must have the right on the referenced object that is specified and must have that right including the WITH GRANT OPTION keywords.

GRANT at the Column Level

Before Teradata 13.0, only the UPDATE and REFERENCES privileges were granted at both the table level and column level. After Teradata 13.0, the SELECT, INSERT, UPDATE, and REFERENCES privileges can be granted at the table or column level. For performance reasons, it is often better to use views instead of column level grants.

GRANT for the Ability to CREATE Secondary Indexes

If you are the owner of a table, you can create secondary indexes, but if you are not you will need explicit rights to create secondary indexes. The INDEX privilege must be granted at the table level to permit the creating of secondary indexes.

Access Rights to CREATE Triggers

To CREATE or REPLACE a trigger, you need to have specific Access Rights.

Access Rights to CREATE Triggers:

CREATE TRIGGER privilege on the table or at the database level.
SELECT privilege on any column referenced in a WHEN clause or a triggered SQL statement subquery.
INSERT, UPDATE, or DELETE privileges on the triggered SQL statement table, but this does depend on the triggered SQL statement.

Access Rights to REPLACE Triggers:

DROP TRIGGER privilege on the table or at the database level. The exception is when you use the REPLACE TRIGGER statement when no target trigger exists and you instead create a completely new trigger.
SELECT privilege on any column referenced in a WHEN clause or on a triggered SQL statement subquery.
INSERT, UPDATE, or DELETE privileges on the triggered SQL statement table, but this depends on the actual triggered SQL statement.

The REVOKE Command

The REVOKE statement can remove privileges from a specific named user, PUBLIC, the ALL username, and for a specified role. To REVOKE a privilege, the user must either be an owner of the object or have the privilege granted, and hold GRANT authority on the privilege.

The REVOKE Command is not as automatic as it might seem. It is termed passive because Teradata feels the need for speed so it does NOT automatically delete rows from the DBC.AccessRights table when a user is dropped. Here are the fundamental thoughts:

Never adds rows to the DBC.AccessRights table for a REVOKE command. Only deletes!
Only removes rows if the specific privileges exist.
Does not cascade through the hierarchy unless you specify the ALL keyword.
The REVOKE statement always removes rights inserted by a CREATE statement.
It will also remove explicit rights inserted in the table by an explicit GRANT statement.
A REVOKE statement at the object level can't remove privileges that were granted at the database or user level because there is no row in the DBC.AccessRights table for the individual object.

DBC Tables for AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV

lists all rights granted to users in the system.

 

lists all rights granted to roles in the system.

 

lists all rights granted to the current user.

 

Shows all the Access Rights for user SQL00

 

Show the number of Access Rights that you explicitly granted

 

Shows all the Access Rights that you have explicitly granted

Find out everything you wanted to know about Access Rights.

The GIVE Statement

The GIVE statement above gave user TeraTom to M_Users and the user transferred. The GIVE statement does not alter DBC.AccessRights. No rights on the given database or user are granted to the new ownership hierarchy either. The database or user that you GIVE does not receive any access rights from its new owner. The new owner gains implicit access rights over the transferred object and the old owner loses them.

A DROP User can be Better than a GIVE Statement

A DROP can be cleaner than a GIVE. Why? TeraTom won't be taking his space and access rights with him! The DROP will cause TeraTom's access rights to be removed from S_Users, and the CREATE allows TeraTom to inherit access rights from M_Users.

Removing a Level in the Teradata Hierarchy

Our goal here was to drop the M_Admin user, but as you can see from the example on the top left, M_Admin had descendants of Mandy and Mark. The GIVE statement was used to give Mandy to Marketing and thus Mark went with her. Then, M_Admin had no more descendants (example top right) as we could then delete and drop user M_Admin.