You can set permissions on specific columns/fields on a table, not just the entire table. The JDM uses the same allow/deny concept as SQL Server with three possible states:
- Not set (grey dot): The database role is not allowed to access the object, but are not explicitly denied access.
- Grant (green with white checkmark): The database role is granted access to the object. However, if a user is a member of another database role that is denied access, he will not be able to access the object.
- Deny (red with white bar): The database role is denied access to the object. Even if a user is a member of another database role that is allowed access, he will still be denied access.
In addition to the three states described above, a table can have different mixed states depending on the column level permissions set on the table. The mixed states are:
- Partially Granted (green and grey icon). The database role is granted access to some columns on the table. Note that you will also see this icon if the database role is granted access to all columns on a table since this will not automatically set Allow on the table level.
- Partially Denied (red and grey icon): The database role is denied access to some columns on the table. Note that you will also see this icon if the database role is denied access to all columns on a table since this will not automatically set Deny on the table level.
- Mixed Grant/Deny (red and green icon): The database role is allowed access to some columns and denied access to other columns on the table.
Assigning Object or Column Level Permissions to Database Roles
To assign object level permissions, or column level permissions on tables, to database roles, follow the steps below.
On the Data tab, in the project tree, under Data Warehouses and the relevant data warehouse, right click on Security and click on Object Security Setup. The Object Security Setup window opens.
Click on Tables, Views or Schemas in the left-hand column to choose the type of object you wish to set up access for. Expand Tables and click on an individual table to assign column level permissions for that table.
In the right-hand column, the table shows object names in the left-most column and database roles in the following columns. Click on icon in the intersection between the object name and the database role to change the permission for the database role on that object. Note that if you set column level permissions on a table, this will overwrite any current object level permissions set and vice versa.
(Optional) Click on Add Role, Edit Role or Delete Role to add, edit or delete database roles as needed.
Click on OK to save changes and close the window.