About User Privileges
Toad Edge provides a number of options related to user privileges and how to grant/revoke them. As a general overview, user privileges are granted/revoked on three levels.
- Server (global level)
- Database/Schema
- Database objects (tables, views, routines - procedures and functions)
These levels also represent a hierarchy where privileges on higher level are inherited at lower levels by default.
User privileges can be of several types:
- Inherited (
) - the privilege has been granted on higher level and it is not possible to revoke it on the current level
- Example: User has been granted the CREATE privilege on the server level. It appears as inherited on the database/object levels and cannot be revoked there. It can be granted again on lower levels although this is unnecessary
- Granted & Inherited (
) - this privilege has been granted on higher level and also on the current level
- Example: User has been granted the SELECT privilege on the server level and then again on the database level. It appears as granted & inherited on the database level
- Granted (
) - the privilege has been granted on the current level and can be revoked
- Example: User has been granted the UPDATE privilege on the object level. It appears as granted on the object level, and it can be revoked
- Revoked (blank value) - the privilege has not been granted on any level
- Example: User has been revoked the EXECUTE privilege on the database level. It appears as revoked on the object level. It also appears as revoked on the server level as if it were granted on the server level, it could not be revoked on the database level in the first place
The following table describes how privileges set on higher levels affect privileges on the other levels:
| Higher level | Current level | Lower level |
|---|---|---|
| Granted | Inherited (granted) | Inherited (granted) |
| Revoked | Inherited (revoked) | Inherited (revoked) |
| Revoked | Granted | Inherited (granted) |
| Granted | Granted & Inherited | Inherited (granted) |
User and Object Privilege Views
Toad Edge allows you to examine currently configured privileges from two views:
- User view - displays objects and the privileges the user has to the objects
- Object view - displays users and the privileges the users have to the object
To examine privileges of a user
- In Object Explorer, expand the Users node and double-click the user whose privileges you would like to review
- Switch to the Privileges tab
To examine privileges of an object
- In Object Explorer, locate the object you would like to review (server/database/database object)
- Double-click the object to open it in Object Detail
- Switch to the Privileges tab
User Detail | Privileges tab
| Button | Option | Description |
|---|---|---|
 |
Modify Privileges | Opens a dialog where you can grant/revoke privileges to each individual object |
 |
Add Wildcard Pattern Privileges | Opens a dialog where you can configure Wildcard patterns privileges |
 |
Add Proxy User Privileges | Opens a dialog where you can set up Proxy user privileges |
 |
Revoke Privileges | Revokes the selected privilege for the selected object |
Object Detail | Privileges tab
| Button | Option | Description |
|---|---|---|
| |
Modify Privileges | Opens a dialog where you can grant/revoke privileges of any user to the object |
 |
Open Script in Worksheet | Opens the privilege configuration script of the selected user to the object in Worksheet |
 |
Open Script Generation Settings | Allows you to enable/disable inclusion of inherited privileges in the generated privilege script. When disabled, only granted or granted & inherited privileges will be included |
 |
Open User Detail | Opens the User Detail of the selected user |
 |
Filter Unprivileged | Hides users that have not been granted any privileges |
Wildcard Patterns and Proxy Privileges
|
|
NOTE: Privileges that are set using these methods are NOT visible in the Privileges tab in User Detail and Object Detail. |
Wildcard patterns privileges
Wildcard pattern privileges offer a way to grant privileges for multiple databases at once. The condition is that the database names must match a specific wildcard.
To set wildcard pattern privileges for databases
- Locate the user whose privileges you would like to modify in Object Explorer and double-click it
- Switch to the Privileges tab
- Click the Add Wildcard Pattern Privileges
- In the first dialog of the wizard, specify the wildcard pattern. The percent (%) sign represents one or more characters and the underscore (_) sign represents a single character. If you need to use a literal % or _, escape them using a back slash (\)
- In the next dialog, select the privileges that you would like to grant and Finish the wizard
|
|
NOTE: Setting database privileges using wildcards has a side effect. You are not able to revoke privileges to a particular database from the set of databases that match the wildcard. Consider the following example. A user has been granted the SELECT privilege to all databases which match the "test\_%" wildcard. There are three such databases - test_tables, test_views and test_routines. In this situation, you are not able to revoke the privilege for any one of the three databases, such as test_tables. |
Proxy user privileges
Using proxies, users are able to use privileges of other users. In this operation, there are two sides:
- Proxy - the user that uses the privileges of some other user
- Proxied - the user whose privileges will be available to the original user
To add proxy user privileges
- Locate the user whose privileges you would like to modify in Object Explorer and double-click it
- Switch to the Privileges tab
- Click the Add Proxy User Privileges
- In the wizard, specify the Proxied and the Proxy users and click Finish
