Database Users and Roles

Database Users and Roles

Vine database security is maintained with the following roles:

VINEYARDDB

VINEYARDDB, as the schema owner, has full access to all Vine database objects. It can create other roles, tables, procedures and other basic database elements.

All the product, object set and user group roles are granted to VINEYARDDB with ADMIN option.

User Group Roles

A user group role "VYUG{UserGroupName}ROLE" is created for each license and object set group (and subgroups of those). A group role has roles of all object sets assigned to it. (Please note that in Vine DB earlier than 4.5 a role was created for each group even if that role was not really used).

NOTE: There are no object set and user group roles in the default Vine installation, i.e. when only the API object set is present and it is granted to 'ALL' users.

An object set can be assigned to a group in Vine Object Manager.

Object Set Roles

An object set role "VY{ObjectSetName}ROLE" is created for each object set.

When a set is assigned to a group then the object set's role is granted to the group's role. For instance, if the API and Custom object sets are assigned to the "Management" group, then the VYAPIROLE and VYCUSTOMROLE object set roles are granted to the VYUGMANAGEMENTROLE.

Note: In case an object set is assigned to ALL, then the object set role is granted to VINEYARDUSER.

An object set role gets the following object grants:

• SELECT privilege on custom object and parameter views (see Standard Object Views in the Vine Server Database chapter),
• INSERT and UPDATE privileges on custom object tables.
• EXECUTE privilege on custom object PL/SQL packages (see Object Packages in the SQL API chapter)

All users included into the set's user group get this set's role. This way the set's role effectively closes access to the set's data for all users not having the role granted to them.

VINEYARDUSER Role

The VINEYARDUSER role has all the basic rights needed for users of Vine for Windows and Vine Web, such as:

• SELECT rights on all object and parameter table views,

• INSERT and UPDATE rights on all standard object tables,

• EXECUTE rights on PL/SQL user packages.

• In addition this role gets object set roles granted for all object sets assigned to ALL.

VINTAGEROLE and VINTAGEWEB roles get VINEYARDUSER granted.

Product Roles

Each client product usually has a corresponding end-user role, e.g. Vine for Windows - VINTAGEROLE, Vine Web - VINTAGEWEBROLE.

VINTAGEROLE

VINTAGEROLE is the Vine for Windows product role granted to all users of Vine for Windows.

VINTAGEROLE is granted those privileges which are needed for Vine for Windows users but not included in the VINEYARDUSER role.

When a user account or usergroup is added to the Vine for Windows Users group, it gets the VINTAGEROLE.

VINEYARDTABLES Role

The VINEYARDTABLES role grants full rights to all Vine object tables, standard and custom. It is used to give access to Vine tables to external custom interfaces.

Object Data Protection with Roles

Vine user group and object set roles support the following scheme of object data protection for the Vine database:

• Standard fields of any standard API object are always open to any Oracle user with the VINEYARDUSER role.

• All fields of any custom object are available only to users of its object set (the group role or the VINEYARDUSER role get the object set role granted).

• A custom field of any standard API object can be specifically protected and made available only to the users of the object set. This type of a separate field protection should be avoided for performance reasons (see the "Views" section below).

NOTE: Protection with Oracle roles is mainly important for limiting access to the Vine data from third party tools, like Oracle SQL tools and the like. Vine products maintain its own data protection & distribution logic which is supported with Vine user groups.

Integration users

Two types of integration users can exist in a Vine database:

• ASP Integration user - a user with full access to all data inside one organization and no access whatsoever to data of other organizations.

• Full-rights integration user - always manually made and has access to only tables and views specified manually.

ASP/MyVine Integration users

An ASP Integration user:

• must be in the VY_USER table.

• must be in the "MyVine Integration users" group and have its role. The role has the CONNECT and VINEYARDUSER roles granted.

• must be in only one organization group and have its role.

• cannot be a Vine for Windows/Web user.

Recommended username format: INTEGRATION.<Organization>.

Use the “Integration User” tool in Admin Console to handle integration users (create, enable/disable and change the password).

Database functionality

• VintageUtilAPI.IsUserInGroup functions identify if the connected user belongs to the MyVine Integration users group.

• Object views in ASP-mode are modified so that integration user can see all data of its organization. (data is visible if showgroup is any group or user in the Integration user’s organization.)

• Object triggers in ASP-mode are modified so that integration user can insert and update all data for its organization. The triggers check EDITGROUP / DELETEGROUP / ADDCONNECTIONGROUP / DELETECONNECTIONGROUP fields. If the user is a member of “MyVine Integration users” group then any object belonging to the Organization or any user of the Organization can be edited. Default visibility of inserted rows is set to Organization ID.

Full-Rights Integration User

The full-rights integration user is supported in non-ASP mode databases. It has full-rights to the Vine database, bypassing all visibility and editing rights:

• Username must begin with “VINEINTEGRATION”

• Username cannot be in VY_USER table.

• Username cannot contain “@” or “.” characters.

• Database user is created manually and all object privileges granted manually.