Here is a list of stored procedures that are helpful in managing fixed database roles:
- sp_addrole: Creates a new database role in the current database
- sp_addrolemember: Adds a user to an existing database role in the current database
- sp_dbfixedrolepermission: Displays permissions for each fixed database role
- sp_droprole: Removes a database role from the current database
- sp_helpdbfixedrole: Returns a list of fixed database roles
- sp_helprole: Returns information about the roles in the current database
- sp_helprolemember: Returns information about the members of a role in the current database
- sp_droprolemember: Removes users from the specified role in the current database
Application Roles
Application roles are another way of implementing permissions. These are quite different from the server and database roles. After creating and assigning the required permissions to an application role, the client application needs to activate this role at run-time to get the permissions associated with that application role. Application roles simplify the job of DBAs, as they don’t have to worry about managing permissions at individual user level. All they need to do is to create an application role and assign permissions to it. The application that is connecting to the database activates the application role and inherits the permissions associated with that role. Here are the characteristics of application roles:
- There are no built-in application roles.
- Application roles contain no members.
- Application roles need to be activated at run-time, by the application, using a password.
- Application roles override standard permissions. For example, after activating the application role, the application will lose all the permissions associated with the login/user account used while connecting to SQL Server and gain the permissions associated with the application role.
- Application roles are database specific. After activating an application role in a database, if that application wants to run a cross-database transaction, the other database must have a guest user account enabled.
Here are the stored procedures that are required to manage application roles:
- sp_addapprole: Adds an application role in the current database
- sp_approlepassword: Changes the password of an application role in the current database
- sp_dropapprole: Drops an application role from the current database
- sp_setapprole: Activates the permissions associated with an application role in the current database
Permissions
Now that we discussed different kinds of roles, let’s talk about granting and revoking permissions to and from database users and database roles and application roles. The following T-SQL commands are used to manage permissions at the user and role level.
- GRANT: Grants the specific permission (SELECT, DELETE etc.) to the specified user or role in the current database
- REVOKE: Removes a previously granted or denied permission from a user or role in the current database
- DENY: Denies a specific permission to the specified user or role in the current database
Using the above commands, permissions can be granted, denied, or revoked to users and roles on all database objects. You can manage permissions at as low as the column level.
There is no way to manage permissions at the row level. That is, in a given table, you can’t grant SELECT permission on a specific row to User1 and deny SELECT permission on another row to User2. This kind of security can be implemented by creating user specific views and granting SELECT permission on these views to users. But it will be an ugly solution in case of too many users with varying data access requirements. Just an FYI, Oracle has a feature called “Virtual Private Databases” (VPD) that allows DBAs to configure permissions at row level.