Isolating Users from the Database Using Views
This topic presents a well-designed database structure based on a structure formulated and used by a Teradata customer.
The design incorporates different task-oriented profiles that access only a series of views and macros defined within several task-oriented databases.
Only these task-oriented access definition databases can access the base tables defined for the database. This design structure builds a virtual firewall between all users and the base tables they access.
Flow Diagram of the Database Structure
The following graphic illustrates the work flows and individual task-oriented profiles and database objects defined for this database structure.
Note that Tables_Database contains only base tables and their associated index subtables.
The example database defines three types of end user profile. The following table describes those types.
End User Profiles
| Profile | Description | Permitted SQL Statements and Utility Commands |
|---|---|---|
| Query_Profile | Defines privileges to the views, macros, and stored procedures defined in Query_Database. |
|
| Upsert_Profile | Defines privileges to the views, macros, and stored procedures defined in Upsert_Database. |
|
| Batch_Profile | Defines privileges to the tables defined in Tables_Database. |
|
Rules for End User Profiles
- All users belong to one or more task profiles.
- Users inherit their privileges from the task profiles to which they belong.
- A user can belong to more than one task profile.
- Privileges are granted at DATABASE or USER levels only.
- Query_Profile and Upsert_Profile only have access to databases that contain macros, stored procedure definitions, and views exclusively.
- Batch_Profile is the only profile that permits the base tables in Tables_Database to be accessed directly.
Rules for Batch_Profile
- Users inherit their privileges from Batch_Profile.
- Privileges are granted at Database or User levels only.
- Batch_Profile has direct access to the base tables in Tables_Database because of the functions performed using it.
- Batch_Profile users can perform the following functions against Tables_Database
- CHECKPOINT transactions
- CREATE TABLEs
- DROP TABLEs
- DUMP databases
- RESTORE databases
Rules for Query_Database
Note that Query_Database objects are organized in such a way that the entire database can be archived in one operation. You can also archive Upsert_Database in the same operation if you choose to do so.
- Both Query_Profile and Upsert_Profile users can perform the following functions against Query_Database.
- CALL permitted stored procedures
- EXECUTE permitted macros
- SELECT rows from Tables_Database objects through views
- The database contains only views, stored procedure definitions, and macros that permit an end user to query or grant privileges on Tables_Database objects indirectly.
Rules for Upsert_Database
Note that Upsert_Database objects are organized in such a way that the entire database can be archived in one operation. You can also archive Query_Database in the same operation if you choose to do so.
The word upsert derives from update-insert and implicitly refers to delete as well.
- CALL permitted stored procedures
- DELETE rows in Tables_Database objects through views
- EXECUTE permitted macros
- INSERT rows into Tables_Database objects through views
- SELECT rows from Tables_Database objects through views
- UPDATE rows in Tables_Database objects through views
- DELETE rows
- INSERT rows
- SELECT rows
- UPDATE rows
- GRANT privileges on database objects