Authorizing User Access
Types of Vector Users
In most installations, there are four types of users:
Installation Owner
The installation owner is typically an account named "actian", but the actian name is not required.
By default, this user has the Security privilege and most of the other privileges. Some of the privileges, however, can be revoked from this user and the system will still operate correctly. In a good production system, this user performs only administrative tasks on the system (such as startup and shutdown).
System Administrator
The system administrator is sometimes the "root" account. This account is commonly owned by the Information Technology (IT) department, but is also commonly owned by a user who has been defined as the Vector System Administrator.
In a large production environment, there may be one or a few of these users. These users have the Security privilege, which allows them to use the -u flag on commands to imitate other users, and usually possess other privileges such as maintain_locations and maintain_users; if security auditing is enabled, they will also typically have auditor and maintain_audit privileges. The responsibility of this user is to perform administrative tasks that affect the entire Vector instance such as creating and destroying Vector users, allowing Vector to use new disk drives, and monitoring the Vector security audit logs.
In smaller environments, the system administrator and the installation owner may be the same user.
Database Administrator (DBA)
The DBA typically has only the Createdb privilege. DBAs can use the -u flag in their own databases only.
Typically, the DBA is not the installation owner, and in a good production system, does not have the Security privilege. The definition of the primary DBA for any given database is the user who ran the createdb command to create that database. Additional DBAs can be defined for a database by granting (see
The GRANT Statement) them the db_admin privilege for that database.
End User
The end user typically has no privileges and cannot create a database.
How to Establish User Access
Be default, Vector uses DBMS Authentication for user access.
The process for establishing access to Vector is as follows:
1. (Optional) The system administrator defines user accounts in the operating system. Accounts are needed for local users and for remote users who access the product through a local account.
Note: This step is optional:
• If an installation password is defined, in which case the user accesses Vector directly, without having to go through a local account.
• For remote users, if the DBMS Server is configured to use DBMS authentication. (This is the default for Vector.)
Accounts can be set up before or after Vector is installed (except for the installation owner account, which is set up during installation, belongs to the system administrator, and is assigned maximum privileges to perform all operations).
2. The database administrator defines user objects.
A database administrator or system administrator starts Vector and defines user objects. Part of the user object definition is a user ID.
If OS authentication is used, the user ID must correspond to the user ID used to log on to the operating system.
Typically, the system administrator sets up a user object for the database administrator, who in turn sets up user objects for other users.
Users and Profiles
Users are defined using user objects and, optionally, profile objects.
A user object is a definition that specifies the user’s name, default group, default profile, subject privileges, and several other attributes.
A profile is a template that defines a set of subject privileges and other attributes that can be applied to one or more users. The user authorization process can be streamlined by using profiles.
Working with User Objects
You can perform the following basic operations on user objects:
• Create and alter user objects
• View existing user objects, including the detailed properties of each object
• Drop user objects
These tasks can be accomplished using Actian Director, VDBA, or the accessdb forms-based utility.
In SQL, you can use the CREATE USER, ALTER USER, and DROP USER statements when working in a session connected to the iidbdb database.
Note: Many of the features associated with a user object, such as subject privileges, password, expiration date, and security auditing, are security-related features, described later in this guide.
Create a New User with Accessdb
You must have maintain_users permission to authorize users. Using the accessdb utility, you can add, modify, or delete users and grant them database access permissions.
To authorize a new user
1. Start accessdb by issuing the following command at the operating system prompt:
accessdb
The accessdb main menu appears.
2. Select Users from the accessdb main menu.
The Users Catalog screen appears.
3. Select Create.
The Create a User screen appears.
4. Type the user information into the following fields:
User Name
Login name of the user. The name can be a regular or delimited identifier.
For example, to use a numeric user ID, the name must be delimited (enclosed in double quotes, as in "888282").
(For details on delimited identifiers, see the SQL Language Guide.)
Profile for User
(Optional) Default profile for the user.
Default Group
(Optional) Default group (see
Groups) the user is assigned to.
Expire Date
Note: After you save the user definition, you can assign a
User Password. A user password is optional unless the user will require DBMS authentication.
5. In the Permissions section, change the default subject privilege settings for the user by tabbing to the desired field and typing the appropriate value:
y
Grants the privilege
n
Denies the privilege
r
Makes the privilege requestable. For details on requestable privileges, see SET SESSION in the SQL Language Guide.
6. Select Save from the menu.
The user entry is saved.
7. Repeat steps 3-6 for each new user you want to authorize.
8. Select End twice.
You are returned to the accessdb main menu.
9. Select Quit.
Note: If you do not see the Quit function listed, press ESC to scroll through the menu options.
The accessdb utility ends.
User Expiration Date
The user expiration date is an optional part of the user definition. It determines the date after which the user can no longer access Vector.
An expiration date can be specified as any valid Vector date or as a date or time interval. For example, you might specify an interval of ‘1 month’ or ‘1 year,’ or an absolute date, such as ‘5‑jan‑2007.’
The user expiration date is checked each time the user connects to the DBMS Server. If the expiration date has passed, then access is denied.
To enable an expired user to connect, the associated user (or profile) object must be modified to reset the expiration date.
User Password
A password can be specified as part of the user definition.
How the password is used depends on whether DBMS authentication is enabled. If it is enabled, the user name and password supplied by the connection attempt must match this user name and password. If no password is supplied by the connecting application, a remote connection attempt fails. A local connection attempt with no password will succeed, assuming that the user was not defined with DBMS_AUTHENTICATION = 'REQUIRED'.
If DBMS authentication is not enabled, the DBMS password operates as a second level of password after the initial connection is established (using the configured GCF security mechanism, such as authentication against an OS user and password). In this case, the application sends the DBMS password in the clear, after the connection is established. If no DBMS password is provided by the application, the DBMS server asks the client libraries to prompt for one if possible; or, the connection attempt fails if no prompting is possible.
When a session requires a password and one is not specified, a prompt requests a password. For security reasons, a password prompt is issued if either a required password is missing or the user name is unknown or illegal. This behavior is consistent with that of operating systems during logon.
Note: If a user with the Security privilege starts a session using the –u flag to impersonate another user, the real user’s password—not the impersonator’s—is required.
A user with the Change_Password privilege is permitted to change his own password; to do so, however, he must supply his old password. A user with the maintain_users privilege can change the password of another user, in addition to changing the method of password validation or removing the password altogether.
Note: Passwords also apply to roles.
Authorize Multiple Users with SQLscript
Using accessdb you can create a file of the users at your installation and their corresponding permissions. This file is useful for copying installations.
To create a file of users
1. From the accessdb main menu choose Users.
The Users Catalog screen appears.
2. Choose the SQLscript menu item.
The accessdb utility creates an SQL script and displays an SQLscript message indicating the file location.
3. Press Return.
The message is cleared from the screen.
4. Select End.
Note: If you do not see the End function listed, press ESC to scroll through the menu options.
You are returned to the accessdb main menu.
Note: The SQLscript function creates users only, not the profiles, groups, or roles associated with each user. Roles and groups must be unloaded and reloaded for the script to generate the expected results.
Working with Profile Objects
You can perform the following basic operations on profile objects:
• Create and alter user objects
• View existing user objects, including the detailed properties of each object
• Drop user objects
In SQL, you can use the CREATE PROFILE, ALTER PROFILE, and DROP PROFILE statements when working in a session connected to the iidbdb database.
You can work with profile objects in Actian Director and VDBA.
Example of Using a Profile
After a profile is created, it can be associated with a new or existing user object as the default profile for that user. By doing so, the attributes defined in the profile are associated with the user, and the user’s attributes are updated whenever the profile is modified.
Attributes can also be set directly at the user level to override settings at the profile level.
For example, a company conducts an analysis of the tasks and responsibilities of its database operators at multiple sites. They find three tasks that are common to this type of user: database and file location maintenance, debugging, and database backups.
They create a profile for maintaining databases called dbop (database operator) with the appropriate subject privileges:
• maintain_locations
• trace
• operator
Whenever the company hires a new database operator, the database administrator can associate the dbop profile with that new user. Doing so automatically assigns the maintain_locations, trace, and operator privileges to the user.
If the company alters the dbop profile to include the maintain_users privilege, the change automatically affects any user currently using the profile.
Because the dbop profile did not specify the option to audit the query text associated with user queries, users associated with this profile are not audited for query text. To audit the query text for only one of the users associated with the dbop profile, this option can be turned on at the user level (by using the ALTER USER statement). This overrides the default for that particular user, without affecting any other users of the dbop profile.
Default Profile
A default profile is the profile initially assigned to a user if one is not explicitly assigned.
The default profile specifies the following:
• No default group
• No subject privileges or default privileges
• No expiration date
• No security audit options (that is, default events are audited)
Notes:
• You can alter the default profile but you cannot drop it.
• Altering the default profile will alter privilege attributes of all users that have not been given a specific profile.
You can change the default profile using the ALTER DEFAULT PROFILE statement, Actian Director, or VDBA.
