Managing Security with Roles

Managing Security with Roles

ROLE is an abstract concept introduced in the relational databases to facilitate user management tasks by grouping users' privileges according to some criteria, usually a job function. If, for example, your accounting staff of 100 people needs privileges for the dozens of objects they access daily — in addition to some system-level privileges — you have two choices: go through each and every user and individually grant him/her all the privileges required; or create a group (role), such as ACCOUNTANTS, grant all the privileges to the role, and thus grant this role to all the users in the group. Revoking the privileges would pose the same choices. It seems fairly obvious which choice is better.

Some RDBMS provide roles-creating capabilities, in addition to having a number of predefined system roles that could be granted to a user. Oracle 9i and the Microsoft SQL Server 2000 have this feature, while DB2 UDB employs only fixed, predefined roles (authorities).

The Oracle 9i roles are collections of privileges that could be granted to (or revoked from) a user or another role, thus providing a hierarchy of privileges. A role must be enabled (with a SET ROLE statement or by the database administrator) before it can pass on all the privileges granted to it.

Oracle 9i has a number of predefined roles through which privileges are granted to users. Table 12-8 shows these with short descriptions.

Table 12-8: Oracle 9i Predefined Roles

Predefined Role



Provided for compatibility with the previous versions of Oracle; it is explicitly stated that these roles might not be supported in the future releases.

There are a number of privileges associated with each of these roles in Oracle 9i. The DBA role, for example, has 124 privileges; RESOURCE and CONNECT have 8 each. Some of these have overlapping privileges — like CREATE TABLE or CREATE VIEW — and some of them are unique.

Refer to the DBA_SYS_PRIVS dictionary view for the full list of privileges and their descriptions.


Provided for users who need to access data dictionary views.


Provided for users who need to perform full database export.


Needed for Oracle's advanced queuing functionality.


Used by Enterprise Manager/Intelligent Agent.


Needed for a user who owns a recovery catalog.


Provided to grant access to the DBMS_HS package, which is required for heterogeneous services administration.

You neither can add new privileges to a predefined role, nor can you revoke any from the role.

The following Oracle syntax, which creates a custom role, is straightforward:

CREATE ROLE <role name>
		[<schema>].[<package>] } ];

Only the role name is a required argument for this statement, the rest is optional. If the role is to have its own password, then the IDENTIFIED clause must be used. The NOT IDENTIFIED clause indicates that no password is required to enable it.

For identified users, the IDENTIFIED BY <password> clause actually creates a local user with this password if none previously existed; a password needs to be specified when enabling the role. The USING [<schema>].[<package>] clause creates an application role (compared to that in the MS SQL Server), which means that the role can be enabled by that specific application using authorized package.


Package in Oracle RDBMS is a collection of pre-compiled routines (usually written in PL/SQL), and residing in RDBMS itself. A user could access database functionality through procedures and functions defined in the package.

Using the EXTERNALLY clause creates an external user (see OS Security integration sidebar later in the chapter), and the GLOBALLY clause creates a global user, authorized by the enterprise directory service.

Here is an example of a role created for the ACME database with a minimal set of default options:

CREATE ROLE sales_force; Role

Now you can grant privileges to this role (see GRANT statement paragraph earlier in this chapter for more information), and later grant the privileges to everyone who needs them by assigning those people to the sales_force role.


You can enable or disable ROLE for the duration of the current database session using the SET ROLE statement. There might be a limit to the number of concurrent roles that can be set by the database administrator.

A custom role can be altered or dropped by using, respectively, the ALTER ROLE or DROP ROLE statements.

Any user that accesses the IBM DB2 UDB database must have a valid OS account. Once authenticated, the user's access to the database's objects is governed by a system of authorities (roles) and privileges inside the RDBMS. DB2 does not support user-defined roles. Instead it relies on the system's predefined authorities (roles), which a user can be a member of, and on GROUP, which behave almost the same as roles that are employed in Oracle or the MS SQL Server 2000.

System authorities (roles) include system administration (SYSADM), system control (SYSCTRL), system maintenance (SYSMAINT), and database administration (DBADM), listed in Table 12-9. Each of these roles implies certain privileges; certain types of privileges are automatically granted to every user authenticated by the OS.

Table 12-9: IBM DB2 UDB System Authorities

System Authority



System administration, which includes all the privileges of all other system authorities as well as the ability to grant and revoke DBADM authority.


System control, which includes privileges to create, update, or drop a database. It does not allow direct access to the data.


System Maintenance, which enables database maintenance tasks in all databases associated with an instance, including the authority to modify configure files, and backup and restore databases.


Database administration, which has all the privileges within a single database.

A privilege in DB2 UDB is defined as permission for the authenticated user to access and use database resources such as tables, views, and stored procedures, which will be discussed in greater detail later in the chapter.

Most of IBM DB2 UDB privileges — both on system and object levels — granted to the authorities (Table 12-9) are listed in Table 12-6.


Some privileges for the database objects are not relevant for all discussed RDBMS. For example, the PACKAGE object can be found in the DB2 UDB or Oracle database, but is nonexistent in the MS SQL Server.

The highest authority level belongs to SYSADM, which has full control over all database objects, as well as the DB2 UDB installation that contains this database. It defaults to the Administrators Group on Windows NT/2000/XP; on Unix the initial value is NULL and defaults to the primary group of the database instance owner.

The SYSCTRL and SYSMAINT roles represent a lower level of hierarchy, followed by DBADM. Users that do not belong to any of these roles are granted privileges on an object-by-object basis. A new group can be created by DBADM, and privileges could then be granted to this group and to users in this group. This is a handy way to administer privileges for a number of users with similar responsibilities within the database, and is similar in functionality to the roles in Oracle and the Microsoft SQL Server.

For the syntax of granting system authorities, refer to the GRANT statement section of this chapter.

On the authentication level, the MS SQL Server 2000 offers two choices: Windows OS authentication and mixed authentication modes. The first is usually rated as the better security provider, because it relies on the more robust security mechanisms of the operating system, and — for accessing the database over the network — it does not require sending login information unprotected. The second offers a more "personal approach" that allows many users to connect to the same SQL Server without needing to be added to the Windows users group. Microsoft defines a special system administrator user — usually sa, which is the default — who is responsible for all administrative tasks within the MS SQL Server 2000.


The SQL Server 2000 is tightly integrated with Windows OS: no matter what authentication mode you choose during installation, Windows authentication mode would always allow you to log on.

Authorization is performed by the SQL Server itself. All information about the user's granted permissions is recorded in the server's system tables (partially accessible through INFORMATION_SCHEMA views). Like IBM DB2 UDB and Oracle, the Microsoft SQL Server enforces security through a hierarchical system of users implemented via fixed roles and application roles (the concept of a role is explained at the beginning of this chapter).

Fixed server roles provide a server-wide scope hierarchy where each role is allowed to perform certain activities, SYSADMIN being on the top, and having privileges to perform any activity. These roles are listed in Table 12-10.

Table 12-10: Microsoft SQL Server Fixed Server Roles

Fixed Server Role

Actions Allowed


Can perform any activity within the SQL Server (this is the highest privileges level).


Able to startup/shutdown server, as well as modify server's configuration.


Able to manage linked servers and modify startup procedures.


Manages logins, passwords, and permissions; is allowed to read error logs.


Allowed to manage the SQL Server's processes.


Has permissions to create, alter, and drop databases.


Allowed to manage SQL Server disk files.


Allowed to perform BULK INSERT operations.

A fixed role cannot be altered, and new fixed server roles cannot be created. You may add new members to a role, or remove members from the role using SQL Server system-stored procedures, or through graphical user interface (GUI).

The next level in the SQL Server 2000's security hierarchy is fixed database roles, shown in Table 12-11. Each database defined within an SQL Server instance has a set of predefined (fixed) database roles to which any of the database users (logins) can be added. The scope of these roles is much more limited — they are confined to the database within which they are declared. As with the fixed server roles, no permissions can be altered for these roles, but new database roles can be created (unlike fixed server roles).

Table 12-11: Microsoft SQL Server Fixed Database Roles

Fixed Database Role

Actions Allowed


Members of this group have permissions to do anything — within the database scope.


Members of this role can add or remove users from the database.


Members of this role manage security: all the privileges, objects, roles, etc.


Members of this role may issue any DDL statement, but cannot issue GRANT, REVOKE, or DENY statements.


Members of this role may issue DBCC, CHECKPOINT, and BACKUP statements.


Members of this role are allowed to select all data from any user table in the database.


Members of this role are allowed to modify any data in any user table in the database.


Members of this role are not allowed to select any data from any user table in the database.


Members of this role are not allowed to modify any data in any user table in the database.

Application roles are unique to the SQL Server 2000 (and Oracle 9i with some specifics). They are activated only by the application that accesses RDBMS; there are no predefined application roles. In a way, they just provide another method to manage group permissions — if users always connect to the database server through some accounting program, the SQL Server DBA can create a role for that accounting program and assign all the privileges it needs for normal functioning. When the SQL Server receives a request from the accounting program, it activates the role for this application — no sooner, no later. If the application is phased out and replaced by a new one, all the DBA must do to prevent access from the previous application is to drop an associated application role.

Application roles contain no members, and there are no predefined application roles. There is much more to application roles than described here. If you need to use them, refer to SQL Server (and Oracle) documentation.


The column level privileges are recorded in the system tables and can be viewed through the INFORMATION_SCHEMA view COLUMN_PRIVILEGES. The INFORMATION_SCHEMA views are covered in detail in Chapter 13.

As with other RDBMS, permissions (privileges) can be assigned at the object level, for example, in a table or stored procedure — all the way down to a column. A column is the smallest object for which a user may have privilege. In addition to object granularity, privileges can be differentiated by type — EXECUTE privilege, SELECT privilege, DELETE privilege, and so on, in any combination. The privileges — both on system and object levels — are discussed earlier in this chapter.

The SQL Server does not have the CREATE ROLE statement; it employs system-stored procedures instead. The list of some relevant procedures is given in Table 12-12.

Table 12-12: Selected Microsoft SQL Server 2000 Role Management System-Stored Procedures

sp_addrole <role name>,

Creates a new role in the current database.[<role owner>]

sp_droprole <role name>

Removes a role from the current database.

sp_addapprole <role name>, <password>

Creates a new application role in the current database.

sp_setapprole <role name>, [<password>], [<encryption>]

Activates the permissions associated with an application role in the current database.

sp_dropapprole <role name>

Removes an application role from the current database.

sp_addrolemember <role name>, <user name>

Adds a member to an existing database role.

sp_droprolemember <role name>, <user name>

Removes a member from the existing role.

sp_addsrvrolemember <role name>, <user name>

Adds a member to an existing fixed server role.

sp_dropsrvrolemember <role name>, <user name>

Removes a member from the fixed server role.


Microsoft provides a number of sp_ help system-stored procedures to obtain information on roles — either fixed or user-defined.