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 .. 80 81 82 83 84 85 < 86 > 87 88 89 90 91 92 .. 218 >> Next

DELETE objectname DELETE . WHERE Remove an object in a database or user Remove rows from a table, directly or through a view Delete a permanent journal DML
DROP Remove a database, user, role, profile, table, view, macro, journal table, stored procedure, index (secondary), join index, hash index, or trigger. DDL
DUMP Archive an AMP, AMP range, or AMP cluster, or one, several, or a range of databases, data tables, journal tables, and/or users. ARC andASF2
EXECUTE Execute a macro DML
EXECUTE PROCEDURE Refers to the corresponding CALL statement
GRANT CREATE objecttype TO . [WITH GRANT/ ADMIN OPTION] where objectype is a user, database, role, or profile. (For indexes, see the INDEX privilege.) Grants both the CREATE and DROP or CREATE and EXECUTE privileges on that type of object to the specified user. The WITH ADMIN OPTION is valid only when granting a ROLE. DCL
GRANT MONITOR PRIVILEGES TO . Grant MONITOR privileges to a user or role. DCL

6 - 10 Teradata RDBMS Database Administration

Chapter 6: Controlling Access

Controlling Inherited and Group Privileges

Privilege Purpose SQL Sublanguage/ Client (Host) Utility
GRANT . WITH GRANT OPTION Grant to a user the GRANT privilege itself, thus enabling the user to grant privileges on the specified object to other users. DCL
INDEX (table level only) Privilege that includes the: CREATE INDEX and DROP INDEX privileges COLLECT STATISTICS and DROP STATISTICS privileges DDL
INSERT Load new rows in a permanent table, directly or through a view. DML
REFERENCES columnlist REFERENCES ALL BUT columnlist In CREATE TABLE or ALTER TABLE in a FOREIGN key clause, defines PK/FK relations. DDL
REPLACE Replace an existing macro, procedure, or view with the object defined by the immediately following CREATE statement. DDL
RESTORE Restore by AMP, by database or user, or by journal or data table. Also permits execution of: - DELETE JOURNAL Drops a permanent journal - ROLLBACK Uses a before-image permanent journal to restore tables (that write to that journal) to their state before a modification. - ROLLFORWARD Uses an after-image permanent journal to update tables (that write to that journal) to reflect a modification. ARC and ASF2
SELECT Select the information in one, many, or all columns from a table or view. DML
UPDATE Modify column values in a table, directly or through a view. DML

6 - 10

Teradata RDBMS Database Administration Chapter 6: Controlling Access

Privileges To and From PUBLIC

Privileges To and From PUBLIC

Any GRANT statement containing the phrase TO ALL DBC is translated internally into a single GRANT ... TO PUBLIC statement. Every user now and in the future can immediately use a public right on the object.

This prevents an excessive number of access rights rows from accumulating in DBC.AccessRights.

Granting Access Rights to PUBLIC

When you grant one or more access rights on an object to PUBLIC, one row (indicating the public-object pair) is inserted in the DBC.AccessRights table for each granted right.

You can use the PUBLIC keyword any time you want to grant a right to all users.

Revoking Granted Rights from PUBLIC

You can revoke one or more granted rights from PUBLIC, but you cannot selectively revoke a public right from an individual user.

Caution: The statement REVOKE . FROM ALL DBC is not translated to REVOKE . FROM PUBLIC. The ALL DBC form of REVOKE is used only to delete DBC.AccessRights rows for public rights granted in pre-V2R5.0 releases of Teradata RDBMS.

Teradata RDBMS Database Administration

6 - 7 Chapter 6: Controlling Access

Access Rights Validation

Access Rights Validation

During a session, access rights validation uses the following process:

Step Process
1 Searches the cache or DBC.AccessRights for a userID-objectID pair entry for the required right.
2 If not found and user has a current role, searches the cache or DBC.AccessRights for roleID-objectID pair entry for the required right.
3 If not found, retrieves from the cache or DBC.RoleGrants all roles nested within the current role.
4 For each nested role, searches the cache or DBC.AccessRights for roleID-objectID pair entry for the required right.
5 If not found, searches the cache or DBC.AccessRights for a publicuserID-objectID pair entry for the required right.

6 - 8

Teradata RDBMS Database Administration Chapter 6: Controlling Access

Controlling Inherited and Group Privileges

Viewing Granted Privileges

The system stores privileges as rows in the system tables when a user submits a GRANT or CREATE statement.

Several views offer information about the privileges granted to a user either automatically or explicitly. These include:

IF you want information about ... THEN use the view named .
all privileges that have been automatically or explicitly granted DBC.AllRights
privileges the requesting user has explicitly granted to other users DBC.UserGrantedRights
Previous << 1 .. 80 81 82 83 84 85 < 86 > 87 88 89 90 91 92 .. 218 >> Next