SQL Data Service Permissions
End users can connect to an SQL data service through a JDBC or ODBC client tool. After connecting, users can run SQL queries against virtual tables in an SQL data service, or users can run a virtual stored procedure in an SQL data service. Permissions control the level of access that a user has to an SQL data service.
You can assign permissions to users and groups on the following SQL data service objects:
- •SQL data service
- •Virtual table
- •Virtual stored procedure
When you assign permissions on an SQL data service object, the user or group inherits the same permissions on all objects that belong to the SQL data service object. For example, you assign a user select permission on an SQL data service. The user inherits select permission on all virtual tables in the SQL data service.
You can deny permissions to users and groups on some SQL data service objects. When you deny permissions, you configure exceptions to the permissions that users and groups might already have. For example, you cannot assign permissions to a column in a virtual table, but you can deny a user from running an SQL SELECT statement that includes the column.
Types of SQL Data Service Permissions
You can assign the following permissions to users and groups:
- •Grant permission. Users can grant and revoke permissions on the SQL data service objects using the Administrator tool or using the infacmd command line program.
- •Execute permission. Users can run virtual stored procedures in the SQL data service using a JDBC or ODBC client tool.
- •Select permission. Users can run SQL SELECT statements on virtual tables in the SQL data service using a JDBC or ODBC client tool.
Some permissions are not applicable for all SQL data service objects.
The following table describes the permissions for each SQL data service object:
Object | Grant Permission | Execute Permission | Select Permission |
|---|
SQL data service | Grant and revoke permission on the SQL data service and all objects within the SQL data service. | Run all virtual stored procedures in the SQL data service. | Run SQL SELECT statements on all virtual tables in the SQL data service. |
Virtual table | Grant and revoke permission on the virtual table. | - | Run SQL SELECT statements on the virtual table. |
Virtual stored procedure | Grant and revoke permission on the virtual stored procedure. | Run the virtual stored procedure. | - |
Assigning Permissions on an SQL Data Service
When you assign permissions on an SQL data service object, you define the level of access a user or group has to the object.
1. On the Manage tab, select the Services and Nodes view.
2. In the Navigator, select a Data Integration Service.
3. In the contents panel, select the Applications view.
4. Select the SQL data service object.
5. In the details panel, select the Group Permissions or User Permissions view.
6. Click the Assign Permission button.
The Assign Permissions dialog box displays all users or groups that do not have permission on the SQL data service object.
7. Enter the filter conditions to search for users and groups, and click the Filter button.
8. Select a user or group, and click Next.
9. Select Allow for each permission type that you want to assign.
10. Click Finish.
Viewing Permission Details on an SQL Data Service
When you view permission details, you can view the origin of effective permissions.
1. On the Manage tab, select the Services and Nodes view.
2. In the Navigator, select a Data Integration Service.
3. In the contents panel, select the Applications view.
4. Select the SQL data service object.
5. In the details panel, select the Group Permissions or User Permissions view.
6. Enter the filter conditions to search for users and groups, and click the Filter button.
7. Select a user or group and click the View Permission Details button.
The Permission Details dialog box appears. The dialog box displays direct permissions assigned to the user or group, direct permissions assigned to parent groups, and permissions inherited from parent objects. In addition, permission details display whether the user or group is assigned the Administrator role which bypasses permission checking.
8. Click Close.
9. Or, click Edit Permissions to edit direct permissions.
Editing Permissions on an SQL Data Service
You can edit direct permissions on an SQL data service for a user or group. You cannot revoke inherited permissions or your own permissions.
Note: If you revoke direct permission on an object, the user or group might still inherit permission from a parent group or object.
1. On the Manage tab, select the Services and Nodes view.
2. In the Navigator, select a Data Integration Service.
3. In the contents panel, select the Applications view.
4. Select the SQL data service object.
5. In the details panel, select the Group Permissions or User Permissions view.
6. Enter the filter conditions to search for users and groups, and click the Filter button.
7. Select a user or group and click the Edit Direct Permissions button.
The Edit Direct Permissions dialog box appears.
8. Choose to allow or revoke permissions.
- - Select Allow to assign a permission.
- - Clear Allow to revoke a single permission.
- - Select Revoke to revoke all permissions.
You can view whether the permission is directly assigned or inherited by clicking View Permission Details.
9. Click OK.
Denying Permissions on an SQL Data Service
You can explicitly deny permissions on some SQL data service objects. When you deny a permission on an object in an SQL data service, you are applying an exception to the effective permission.
To deny permissions use one of the following infacmd commands:
- •infacmd sql SetStoredProcedurePermissions. Denies Execute or Grant permissions at the stored procedure level.
- •infacmd sql SetTablePermissions. Denies Select and Grant permissions at the virtual table level.
- •infacmd sql SetColumnPermissions. Denies Select permission at the column level.
Each command has options to apply permissions (-ap) and deny permissions (-dp). The SetColumnPermissions command does not include the apply permissions option.
Note: You cannot deny permissions from the Administrator tool.
The Data Integration Service verifies permissions before running SQL queries and stored procedures against the virtual database. The Data Integration Service validates the permissions for users or groups starting at the SQL data service level. When permissions apply to a parent object in an SQL data service, the child objects inherit the permission. The Data Integration Service checks for denied permissions at the column level.
Column Level Security
An administrator can deny access to columns in a virtual table of an SQL data object. The administrator can configure the Data Integration Service behavior for queries against a restricted column.
The following results might occur when the user queries a column that the user does not have permissions for:
- •The query returns a substitute value instead of the data. The query returns a substitute value in each row that it returns. The substitute value replaces the column value through the query. If the query includes filters or joins, the results substitute appears in the results.
- •The query fails with an insufficient permission error.
For more information about configuring security for SQL data services, see the Informatica How-To Library article "How to Configure Security for SQL Data Services":
https://kb.informatica.com/h2l/HowTo%20Library/1/0266_ConfiguringSecurityForSQLDataServices.pdf.
Restricted Columns
When you configure column level security, set a column option that determines what happens when a user selects the restricted column in a query. You can substitute the restricted data with a default value. Or, you can fail the query if a user selects the restricted column.
For example, an Administrator denies a user access to the salary column in the Employee table. The Administrator configures a substitute value of 100,000 for the salary column. When the user selects the salary column in an SQL query, the Data Integration Service returns 100,000 for the salary in each row.
Run the infacmd sql UpdateColumnOptions command to configure the column options. You cannot set column options in the Administrator tool.
When you run infacmd sql UpdateColumnOptions, enter the following options:
- ColumnOptions.DenyWith=option
- Determines whether to substitute the restricted column value or to fail the query. If you substitute the column value, you can choose to substitute the value with NULL or with a constant value. Enter one of the following options:
- - ERROR. Fails the query and returns an error when an SQL query selects a restricted column.
- - NULL. Returns null values for a restricted column in each row.
- - VALUE. Returns a constant value in place of the restricted column in each row. Configure the constant value in the ColumnOptions.InsufficientPermissionValue option.
- ColumnOptions.InsufficientPermissionValue=value
- Substitutes the restricted column value with a constant. The default is an empty string. If the Data Integration Service substitutes the column with an empty string, but the column is a number or a date, the query returns errors. If you do not configure a value for the DenyWith option, the Data Integration Service ignores the InsufficientPermissionValue option.
To configure a substitute value for a column, enter the command with the following syntax:
infacmd sql UpdateColumnOptions -dn empDomain -sn DISService -un Administrator -pd Adminpass -sqlds employee_APP.employees_SQL -t Employee -c Salary -o ColumnOptions.DenyWith=VALUE ColumnOptions.InsufficientPermissionValue=100000
If you do not configure either option for a restricted column, default is not to fail the query. The query runs and the Data Integration Service substitutes the column value with NULL.
Adding Column Level Security
Configure column level security with the infacmd sql SetColumnPermissions command. You cannot set column level security from the Administrator tool.
An Employee table contains FirstName, LastName, Dept, and Salary columns. You enable a user to access the Employee table but restrict the user from accessing the salary column.
To restrict the user from the salary column, disable the Data Integration Service and enter an infacmd similar to the following command:
infacmd sql SetColumnPermissions -dn empDomain -sn DISService -un Administrator -pd Adminpass -sqlds employee_APP.employees -t Employee -c Salary gun -Tom -dp SQL_Select
The following SQL statements return NULL in the salary column:
Select * from Employee
Select LastName, Salary from Employee
The default behavior is to return null values.