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.
| Predefined Role | Description |
|---|---|
| CONNECT RESOURCE DBA | 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. |
| DELETE_CATALOG_ROLE EXECUTE_CATALOG_ROLE SELECT_CATALOG_ROLE | Provided for users who need to access data dictionary views. |
| EXP_FULL_DATABASE IMP_FULL_DATABASE | Provided for users who need to perform full database export. |
| AQ_USER_ROLE AQ_ADMINISTRATOR_ROLE | Needed for Oracle's advanced queuing functionality. |
| SNMPAGENT | Used by Enterprise Manager/Intelligent Agent. |
| RECOVERY_CATALOG_OWNER | Needed for a user who owns a recovery catalog. |
| HS_ADMIN_ROLE | 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> [IDENTIFIED {BY <password> | EXTERNALLY | GLOBALLY | USING [<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.
 |
The Microsoft-specific DENY statement adds an additional level of granularity to the system of privileges not found in Oracle or DB2 UDB. The SQL92/99 standard does not define such a statement.
If you are using roles to assign a set of privileges to a user, you might find yourself in a situation when this set should be modified for one and only one user, while preserving it for all other users to whom this role was granted. Attempting to revoke a privilege from the user that was granted through a role will result in an error. You could create another role and grant a subset of privileges from the first role and then grant it to a user; or you could grant these privileges to the user directly, bypassing the role. Any of these, while workable, would lead to redundancy and possible confusion. The DENY statement provides an elegant solution to the problem. It allows you to grant a role with a full set of privileges and then deny some of these privileges to a user.
The following DENY statement works both for system-level and object-level privileges, with the syntax very similar to that of GRANT and REVOKE statements:
DENY ALL [PRIVILEGES] | <permission>,... [(<column>)] ON [<table> | <view>] | ON [<table> | <view>] [(<column>,...)] | ON [<stored_procedure> | <extended_procedure>] | ON [<user_defined_function>] TO <security_account>,... [CASCADE]
The CASCADE clause means that a privilege (permission) denied to the security_account will also be denied to every other user/role/group to that has this security_account permission.
For example, you have a role SALES that has all the privileges in the database, and a user NEW_USER needs all of these, except for CREATE TABLE. The following statements take care of granting the appropriate privileges to the user:
GRANT sales TO new_user GO DENY CREATE TABLE TO new_user GO
The DENY statement always takes precedence, so while having the inherited privileges of the SALES role, the user NEW_USER will get an error if s/he ever attempts to create a table, though other members granted SALES role will have no problems doing so.
|
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.
| Note | 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 created.
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.
| Note | 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.
| System Authority | Description |
|---|---|
| SYSADM | System administration, which includes all the privileges of all other system authorities as well as the ability to grant and revoke DBADM authority. |
| SYSCTRL | System control, which includes privileges to create, update, or drop a database. It does not allow direct access to the data. |
| SYSMAINT | 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. |
| DBADM | 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.
| Note | 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.
| Note | 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).
|
It is important to remember that the operating system (OS) was there before the first computer database was invented, and no RDBMS could operate without some kind of OS. Each operating system comes with its own security mechanisms. All the RDBMS discussed in this book — to a certain extent — provide security integration with the OS they are running on. Essentially, it boils down to using operating system accounts and privileges to access the database, instead of relying on the RDBMS itself.
The Microsoft SQL Server 2000, for example, has tight OS-integrated security, which allows users with a valid Windows account to be authenticated based on their Windows NT/2000/XP credentials. Instead of supplying user ID and password, the user is able to access the SQL Server 2000 automatically as soon as s/he logs onto the machine that runs RDBMS.
Both Oracle 9i and IBM DB2 UDB extend this functionality to multiple operating systems, and the details of implementation and usage are just as different as the systems they integrate with.
DB2 UDB uses an external facility for user authentication — either the operating system, or a so-called distributed computing environment (DCE) facility. A user must have a valid OS account — login and user ID — in order to access the database.
For regular users to be authenticated externally, an Oracle database administrator must create a user account prefixed with a character value from the Oracle initialization parameter OS_AUTHENT_PREFIX and marked as AUTHENTICATED EXTERNALLY. Oracle uses the value of the parameter OS_AUTHENT_PREFIX to check whether any of the users' names created within the database are prefixed with this value if a user name does not have this prefix, it will not be allowed to be authenticated externally. This is only a brief description of the basic mechanism implemented by Oracle, refer to the vendor's documentation for more information on this issue.
For EXTERNALLY authenticated users that intend to administer a database, Oracle 9i requires that two predefined OSDBA and OSOPER groups be created on the machine that is running the operating system, and make these users members of one of these groups. In addition, the Oracle start-up parameter REMOTE_LOGIN_PASSWORDFILE has to be set to one of its valid values.
RDBMS running on some operating systems (notably, Windows 9x) do not have OS security integration, as the OS itself does not provide facilities for this.
|
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.
| Fixed Server Role | Actions Allowed |
|---|---|
| SYSADMIN | Can perform any activity within the SQL Server (this is the highest privileges level). |
| SERVERADMIN | Able to startup/shutdown server, as well as modify server's configuration. |
| SETUPADMIN | Able to manage linked servers and modify startup procedures. |
| SECURITYADMIN | Manages logins, passwords, and permissions; is allowed to read error logs. |
| PROCESSADMIN | Allowed to manage the SQL Server's processes. |
| DBCREATOR | Has permissions to create, alter, and drop databases. |
| DISKADMIN | Allowed to manage SQL Server disk files. |
| BULKADMIN | 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).
| Fixed Database Role | Actions Allowed |
|---|---|
| DB_OWNER | Members of this group have permissions to do anything — within the database scope. |
| DB_ACCESSADMIN | Members of this role can add or remove users from the database. |
| DB_SECURITYADMIN | Members of this role manage security: all the privileges, objects, roles, etc. |
| DB_DDLADMIN | Members of this role may issue any DDL statement, but cannot issue GRANT, REVOKE, or DENY statements. |
| DB_BACKUPOPERATOR | Members of this role may issue DBCC, CHECKPOINT, and BACKUP statements. |
| DB_DATAREADER | Members of this role are allowed to select all data from any user table in the database. |
| DB_DATAWRITER | Members of this role are allowed to modify any data in any user table in the database. |
| DB_DENYDATAREADER | Members of this role are not allowed to select any data from any user table in the database. |
| DB_DENYDATAWRITER | 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.
| Note | 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.
| 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. |
| Note | Microsoft provides a number of sp_ help system-stored procedures to obtain information on roles — either fixed or user-defined. |
|
|
EAN: 2147483647
Pages: 208