Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 69 70 71 72 73 74 < 75 > 76 77 78 79 80 81 .. 218 >> Next


5 - 10

Teradata RDBMS Database Administration Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

Implementing Roles and Profiles

Administrative Procedures

IF you want to .

THEN .

control access rights at the group level

follow this procedure:

Step Action
1 Log on as user DBC and grant to your database administration user (such as DBAdmin) the CREATE ROLE privilege, including the WITH GRANT OPTION. For example: GRANT CREATE ROLE TO DBAdmin WITH GRANT OPTION;
2 Log off as user DBC and log on again as your database administration user.
3 Use CREATE ROLE rolename statements to define one role for each set of access rights per group.
4 Grant the appropriate access rights to each role.
5 Grant one or more roles to one or more users until all users in all groups have the necessary privileges.

let another user grant or drop roles

include the WITH ADMIN OPTION when you grant the role: GRANT rolenameA TO username WITH ADMIN OPTION;

assign a role as the standard session default for a user

use the DEFAULT ROLE=rolename/NONE/NULL option of the CREATE USER or MODIFY USER statement

Note: The role must have already been explicitly granted to the receiver, except when the user submitting the CREATE or MODIFY statement has been granted that role, or has an active role (current role plus nested roles) which has been grated that role, with the WITH ADMIN OPTION. (This also must be the case to assign a role at user creation time.)

MODIFY USER username AS DEFAULT ROLE=NULL;

A newly assigned default role does not affect the current role of an active session; it takes affect when the user next logs on.

find out what role is current for this session

use the SELECT ROLE statement: SELECT ROLE;

5 - 10 Teradata RDBMS Database Administration

Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

Implementing Roles and Profiles

IF you want to . THEN .
disable or change your current role for this session use the SET ROLE rolename/NULL/NONE statement. For example, to disable the default role for the rest of this session: SET ROLE NONE; If a role is specified, the role must exist and have already been granted to you. Note: The SET ROLE statement is treated as a DDL statement. It is permitted if it is the only statement or the last statement of a transaction. It is not supported within a stored procedure.
remove all the privileges of a role from all members use the DROP ROLE rolename statement.
remove a user from membership in a role use the REVOKE rolename FROM username statement.
find out about role and user GRANT activity query the views: DBC.AllRoleRights DBC.UserRoleRights (For column details, see the Teradata RDBMS Data Dictionary)

5 - 10

Teradata RDBMS Database Administration Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

Implementing Roles and Profiles

Implementing Profiles

The assignment of a profile to a group of users is a way of ensuring that all members of a group operate with a common set of parameters. Therefore, the values in a profile always take precedence over values defined for a user via the CREATE and MODIFY USER statements.

Profiles enable you to manage the following common parameters:

Password attributes, including:

Expiration

Composition (length, digits, and special characters)

Allowable logon attempts

Duration of user lockout (indefinite or elapsed time)

Re-use

Account strings, including ASE codes and Performance Groups

Default database

Spool space

Temporary space

All members inherit changed profile parameters. The impact is immediate, or in response to a SET SESSION statement, or upon next logon, depending on the parameter:

SPOOL and TEMP space allocations are imposed immediately. This will affect the current session of any member who is logged on at the time his or her user definition is modified.

Caution: In a profile, the SPOOL and TEMP limits may not exceed the current space limits of the user submitting the CREATE/MODIFY PROFILE statement.

Password attributes take effect upon next logon. (For attribute details, see "Customizing Your Password Controls" on page 6-21.)

Account IDs and a default database are considered at next logon unless the member submits a SET SESSION DATABASE or SET SESSION ACCOUNT statement, in which case the specified databasename or account ID must agree with a profile definition. (For further information on accounts, see "Defining Accounts" on page 5-20.)

5 - 10 Teradata RDBMS Database Administration

Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

Implementing Roles and Profiles

Procedure to Create and Assign a Profile

To create and assign a profile, follow this procedure:

Step Action
1 Log on as user DBC.
2 Grant to your database administration user (such as SYSDBA): The CREATE PROFILE privilege Include the WITH GRANT OPTION For example: GRANT CREATE PROFILE TO SYSDBA WITH GRANT OPTION;
Previous << 1 .. 69 70 71 72 73 74 < 75 > 76 77 78 79 80 81 .. 218 >> Next