Chapter 8 – Roles

“I have had dreams, and I have had nightmares. I overcame my nightmares because of my dreams”

- Author Unknown

Roles

Roles simplify database administration by assigning access rights to tables and other objects, and then groups of people with similar job functions (or roles) can access these objects.

It is as simple as creating different roles for different job functions and responsibilities, and then granting specific privileges (access rights) on database objects to these roles, then granting a role or roles to users who share the same privileges.

images

Imagine a thousand users accessing certain databases, tables, views, macros or stored procedures and having to grant each individual user access rights to these objects. Why not, instead, create a role that provides access to these objects, and then assign everyone needing this access that role? Roles simplify this very important security assignment.

Getting Started for Role Creation

To create a role, a user needs to be given CREATE and DROP Role Rights. Only DBC is given these rights initially. User DBC can GRANT others this right.

images

When the system first arrives, only DBC can create and drop roles. DBC can, however, grant others these rights. In the above examples DBC has granted both SysDBA and TeraTom the ability to create and drop roles. Notice that TeraTom has been given the ability to grant others these rights because of the WITH GRANT OPTION.

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

Example

CREATE ROLE Biz_Users_Role ;

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

 

GRANT SELECT ON BIZ TO Biz_Users_Role ;

images

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 we granted SELECT access to all objects in that database.

Create a User and Assign them a Default Role

CREATE USER Biz_User01
      FROM TeraTom
   AS
      PASSWORD=abc111
      PERM=0
      Profile=Biz_Profile
      DEFAULT ROLE = Biz_Users_Role ;

images

Let's recap. We gave TeraTom the right to Create and Drop roles. Then, we created a role called Biz_Users_Role. Then, we assigned the SELECT right to the Biz database to the Biz_Users_Role. Above, we created a user named Biz_User01 and assigned them a default role of Biz_Users_Role. Now, our user (Biz_User01) can run SELECT statements (queries) on all tables (objects) in the Biz database.

A Role vs. a Profile

CREATE USER Biz_User01
      FROM TeraTom
   AS
      PASSWORD=abc123
      PERM=0
      SPOOL=5000000
      TEMPORARY=2000000
      ACCOUNT='$Low'
      DEFAULT DATABASE=Biz ;

GRANT SELECT on Biz to Biz_User01 ;

CREATE USER Biz_User01
      FROM TeraTom
   AS
      PASSWORD=abc111
      PERM=0
      Profile=Biz_Profile
      DEFAULT ROLE = Biz_Users_Role ;

 

On the left, we created a user and assigned that user spool and temp space, an account, and a default database. On the right, we did the same thing by first creating a Biz_Profile with those exact parameters.

On the left, after we created the user we had to individually grant them SELECT access on the database Biz. On the right, we did the same thing by first creating the Biz_Users_Role, then granting Select access to the Biz database on that role.

The beauty of profiles and roles is that their created once and then they can be applied to thousands of users. Profiles are for people's parameters and roles are for their access rights.

Granting a Role to a Current User

GRANT Biz_Users_Role TO Biz_User03 ;

A role can be granted to a rent user.

 

 

MODIFY USER Biz_User03 AS
Default Role = Biz_Users_Role ;

Only a user with a current role can be modified with a new Default Role.

 

The user executing the MODIFY USER command with the DEFAULT ROLE option must also have ADMIN privileges on a specified role, and the new default role must have first been directly granted to the user before modifying the DEFAULT ROLE with the MODIFY USER command.

Active Roles

With Teradata V2R5.0, only one role may be the session's current role and this complies with the ANSI standard. With Teradata V2R5.1 and beyond, the SET ROLE ALL option is available, and this allows a user to have all valid roles (for that user) to be active.

At logon, the current role is determined by the DEFAULT ROLE value for the user. A specific role can established as the current role so a user then has access rights of the current role plus any nested roles.

A user may change roles at any time during a session by executing the following command within their session.

SET Role Role_Name

 

An example of this might be:

SET Role Biz_Users_Role ;

Setting Your Active Role to ALL

A user may change roles to ALL roles they have been assigned by executing the following command within their session.

 

SET ROLE ALL ;

 

SET ROLE ALL allows a user to have all valid roles (for that user) to be active. This can be set during the session, used in the MODIFY USER command or done when the user is created with the CREATE USER statement.

Roles and Valid Objects

Once a new role is created, access rights can be added with the GRANT statement or withdrawn or taken away with the REVOKE statement on the following nine objects:

  1. Database
  2. Table
  3. Column
  4. View
  5. Macro
  6. Stored procedure
  7. Trigger
  8. Join index
  9. Hash index

 

 

Above, you can see the varying objects in which access rights can be granted or revoked for all roles.

Roles and Invalid Commands

Roles may not be granted on the following eight functions or access rights.

  1. CREATE ROLE
  2. DROP ROLE
  3. CREATE PROFILE
  4. DROP PROFILE
  5. CREATE USER
  6. DROP USER
  7. CREATE DATABASE
  8. DROP DATABASE

A role cannot have descendants so the ALL option of a GRANT/REVOKE statement cannot be applied to a role. The following example would be prohibited.

GRANT SELECT ON SQL_CLASS TO ALL Sales_Role;

ANSI also prohibits a right to be granted to a role using the WITH GRANT OPTION keywords. The statement below is also prohibited.

GRANT SELECT ON SQL_CLASS TO Sales_Role WITH GRANT OPTION;

Above, you can see the varying objects in which access rights can NOT be granted.

Nesting of Roles

Teradata allows one to grant a role to another role. This is referred to as “nesting roles”. Teradata supports only one level of nesting.

If a role has another role as a member (a role has been granted to a role) and the role is the active role for a user, then a user gets additional access to all the objects that the nested role has privileges to.

Assume Biz_Users_Role and Sales_Users_Role is granted to Role_Biz_Sales and assume that Role_Biz_Sales is the current role of a user. The user then has the following access rights:

  1. Access rights directly assigned to the user
  2. Access rights assigned to Biz_Users_Role
  3. Access rights assigned to Sales_Users_Role
  4. Access rights assigned to Role_Biz_Sales

 

The next three pages will be dedicated to understanding nesting of roles. You will see both logically and syntactically what makes sense and is valid, and then you will see what is invalid. Turn the next couple of pages, and get ready to be amazed!

Nesting of Roles in Action (1 of 3)

images

Some Marketing users will just have the Mrkt_Role, and some Sales users will only have the Sales_Role. But, some could have access to each with the nested MandS_Role. Some Consulting users will just have the Cons_Role, and some IT users will have just the IT_Role. But, some could have access to both with the nested CandI_Role.

Nesting of Roles in Action (2 of 3)

images

Nesting of Roles in Action (3 of 3)

images

Quiz – What Databases Does Mandy Have Access To?

images

 

CREATE ROLE Mrkt_Role;  
CREATE ROLE Sales_Role; GRANT SELECT ON I_Tables to MandyMarketing;
CREATE ROLE Exec_Role; GRANT SELECT, EXECUTE ON M_Tables TO Mrkt_Role;
CREATE ROLE Cons_Role; GRANT SELECT ON S_Tables TO Sales_Role;
CREATE ROLE IT_Role; GRANT Mrkt_Role TO MandS_Role;
CREATE ROLE MandS_Role; GRANT Sales_Role TO MandS_Role;
CREATE ROLE CandI_Role; GRANT MandS_Role TO MandyMarketing ;
CREATE ROLE Executive_Role;  

After the SQL runs above, what privileges on what databases does “MandyMarketing” have?

Answer – What Databases Does Mandy Have Access To?

images

 

CREATE ROLE Mrkt_Role;  
CREATE ROLE Sales_Role; GRANT SELECT ON I_Tables to MandyMarketing;
CREATE ROLE Exec_Role; GRANT SELECT, EXECUTE ON M_Tables TO Mrkt_Role;
CREATE ROLE Cons_Role; GRANT SELECT ON S_Tables TO Sales_Role;
CREATE ROLE IT_Role; GRANT Mrkt_Role TO MandS_Role;
CREATE ROLE MandS_Role; GRANT Sales_Role TO MandS_Role;
CREATE ROLE CandI_Role; GRANT MandS_Role TO MandyMarketing ;
CREATE ROLE Executive_Role;  

 

“MandyMarketing” has SELECT access rights to all tables in the I_Tables database, M_Tables database, and S_Tables database, plus EXECUTE in the M_Tables database.

GRANT WITH ADMIN OPTION Command

The following two syntax examples are how you can grant a role to a user or a role using the WITH ADMIN OPTION:

images

REVOKE ADMIN OPTION FOR Command

The following two syntax examples are how you can revoke a role to a user or a role using the ADMIN OPTION FOR command:

images

RoleMembers, RoleInfo, UserRoleRights and ProfileInfoVX,

SELECT * FROM
DBC.RoleMembersVX;

Check to see your role names, the grantor and the date it was granted

 

SELECT RoleName, DatabaseName, TableName, ColumnName, AccessRight
FROM DBC.UserRoleRightsV
ORDER BY 1;

lists all rights granted to the enabled roles of the user.

 

SELECT COUNT(*)
FROM DBC.RoleInfoVX;

Check to see all the roles that you personally have created

 

SELECT * FROM
DBC.ProfileInfoVX;

Show all information about your profiles

 

SELECT Role;

Check to see if you have a Role

 

SELECT Profile;

Check to see if you have a profile

The RoleMembers, UserRoleRights, RoleInfoVX and ProfileInfoVX views show you what you need to know about Roles and Profiles. Remember that Roles are for “Rights” and Profiles are for “People”.

DBC Tables for AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV

SELECT COUNT(*)
FROM DBC.AllRightsV;

lists all rights granted to users in the system.

 

SELECT * FROM
DBC.AllRoleRightsV;

lists all rights granted to roles in the system.

 

SELECT COUNT(*)
FROM DBC.UserRightsV;

lists all rights granted to the current user.

 

SELECT * FROM DBC.UserRightsV
WHERE Databasename = 'sql00';

Shows all the Access Rights for user SQL00

 

SELECT Count(*) FROM
DBC.UserGrantedRightsV;

Show the number of Access Rights that you explicitly granted

 

SELECT * FROM
DBC.UserGrantedRightsV;

Shows all the Access Rights that you have explicitly granted

Find out everything you wanted to know about Access Rights.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset