Managing Security with Privileges

Granting system-level privileges

System privileges in general allow users to perform some administrative tasks within a given RDBMS (creating a database; creating and dropping users; creating, altering, and destroying database objects; and so on). You need a sufficiently high level of authority within the RDBM system to be able to exercise or grant system privileges. The features that distinguish these system privileges from object privileges are their scope and, sometimes, the types of activities they allow the user to perform.

System privileges are strictly database specific: each vendor implements its own set of system privileges and some system privileges may have different meanings for different vendors. Some systems — the Microsoft SQL Server, for instance — do not even define system privileges, using privileges for SQL statements instead.

Oracle 9i has literally dozens of system privileges (and roles that bundle them together), the most common of which are given in Table 12-3. The SQL statement syntax that grants a system privilege is very much in line with the SQL standard. All granted privileges enable the grantee immediately (if the grantee is a ROLE, it acquires the privileges once enabled). The following code presents a basic syntax for granting system privileges in Oracle 9i.

 GRANT ALL [PRIVILEGES] |
			 <system privilege,...> | <role> TO <user> | <role> |
			 <PUBLIC> [IDENTIFIED BY <password>] [WITH ADMIN OPTION];

System privileges might be any of those listed in Table 12-3 (and some more complex or obscure ones, which were not included here). You can either grant a privilege or a role (that was granted some privileges); the ALL keyword refers to all privileges at once and might be followed by an optional PRIVILEGES keyword, introduced in Oracle for compatibility with the SQL99 standard.

The privilege or role can be granted to a user, role (either predefined or created), or PUBLIC (which effectively means all users defined in the RDBMS). The IDENTIFIED BY clause specifies a password for an existing user, or — if a user does not yet exist — tells Oracle to create such a user implicitly. This clause is invalid if the grantee is a role, because it has to be created explicitly.

WITH ADMIN OPTION is an Oracle-specific clause. Essentially it means that the user or members of a role will be allowed to GRANT the assigned system privilege to some other users or roles (with the exception of GLOBAL roles), revoke the privilege from another user or role, and so on. In that regard it works very much like the WITH GRANT OPTION clause for the object-level privilege, though there are some subtle differences in usage. Refer to vendor documentation for a full explanation.

Here are some examples based in the ACME database. To grant a user privilege to create a table in the database and, in turn, pass it onto others, the following statement could be used:

GRANT CREATE TABLE TO
			 new_user IDENTIFIED BY it_is_me WITH ADMIN OPTION; Grant
			 succeeded.

If you have sufficient privileges, the user NEW_USER identified by the password IT_IS_ME will be created, but you cannot use this user ID and password to connect to the Oracle database if the user NEW_USER has not been granted the CREATE SESSION system privilege, which it would need to access the database. The error ORA-01045: user NEW_USER lacks CREATE SESSION privilege; logon denied would be generated.

To fix the situation you need to grant the newly created user this privilege:

GRANT CREATE SESSION TO
			 new_user IDENTIFIED BY it_is_me WITH ADMIN OPTION; Grant succeeded.

Now you can connect to the database using NEW_USER/IT_IS_ME credentials, and — because of the WITH ADMIN OPTION — grant this privilege to other users.

There are two more system privileges in Oracle deserving separate discussion: SYSDBA and SYSOPER, shown in Table 12-4. These privileges act like roles in that they include a number of other system privileges. When connecting to the Oracle database, you can specify to connect AS SYSDBA or AS SYSOPER, assuming that these privileges had been granted to the user. SYSDBA is one of the highest privileges that can be granted.

IBM DB2 UDB is somewhat similar in this aspect to Oracle; it has system privileges, and some of the privileges are associated with authority levels (see more on this later in the chapter). All system-level privileges for DB2 UDB are shown in Table 12-5.

The generic GRANT statement in DB2 UDB follows the syntax:

GRANT PRIVILEGES |
			 <system privilege,...> ON DATABASE TO USER <user> | GROUP
			 <group> | PUBLIC

As you can see, DB2 UDB does not have WITH ADMIN OPTION clause (as in Oracle), and you cannot use ALL PRIVILEGES, though granting DBADM essentially serves the same purpose.

Here is the example of granting CREATETAB system privilege to PUBLIC (all users), in the database ACME:

GRANT CREATETAB ON DATABASE
			 TO PUBLIC DB0000I The SQL command completed successfully

Note that unlike Oracle or the MS SQL Server, the keywords USER and GROUP must be specified in DB2 UDB. Granting the system privilege (database authority in IBM DB2 jargon) to a group called SALES would have the following syntax:

GRANT CREATETAB ON DATABASE
			 TO GROUP sales DB0000I The SQL command completed successfully

If neither USER nor GROUP keywords are specified, then DB2 UDB employs a set of security authorization rules to resolve potential conflicts: if the name is defined in the OS as GROUP, then GROUP would be assumed; if it is defined in the OS as USER, or is undefined, then USER would be assumed; if the name refers to both GROUP and USER (it is possible to have a GROUP and a USER with the same name) then an error is generated. The same error would also be generated if external DCE authentication were used. There is more on authentication methods later in this chapter, and a detailed discussion can be found in the vendor's documentation.

To GRANT the DBADM authority, a user must have SYSADM authority. Both SYSADM and DBADM can grant the other privileges to users or groups. There is more on IBM DB2 UDB's authorities later in this chapter.

The Microsoft SQL Server 2000 does not have system privileges, or at least not in the sense that Oracle or IBM have it. The privileges are granted to a user (or role) for specific SQL statements. Once the privilege is granted, a user can execute the statement to perform operations that they define. Note that the SQL Server has no WITH ADMIN OPTION clause for these privileges:

GRANT ALL |
			 <statement,...> TO <security_account>

The statements that require special permissions (privileges) are those that could do the most harm, if misused: adding new objects to a database, altering existing ones, and performing some administrative tasks. Most of these statements are discussed in detail in Chapter 4. The statement list includes (among others) the following:

• CREATE VIEW

• CREATE TABLE

• CREATE DEFAULT

• CREATE PROCEDURE

• CREATE RULE

• BACKUP DATABASE

• BACKUP LOG

The system permissions are tied to a database (MS SQL Server also uses this concept; the closest Oracle equivalent would be schema) and are hierarchical. For example, to GRANT the privilege to execute a CREATE DATABASE statement, you must be in the context of the SQL Server master database as this statement produces results affecting the whole instance of the SQL Server 2000.

The security account refers to the SQL Server user, SQL Server role, Windows NT user, or Windows NT group. There is some granularity to the security accounts defined by the SQL Server: privileges granted to a user (either on the SQL Server or Windows NT) affect this user only; privileges granted to a role or Windows NT group affect all members of this role or group. In the case of a privileges conflict between a group/role and their members, the most restrictive privilege — DENY — takes precedence (discussed later in the chapter).

Granting the CREATE DATABASE statement to a user/role while being in context of the ACME database would produce an error, as follows:

USE acme
			 GRANT CREATE DATABASE TO PUBLIC CREATE DATABASE permission can only be granted
			 in the master database.

Changing the context to the master database resolves the issue:

USE master GRANT CREATE
			 DATABASE TO PUBLIC The command(s) completed successfully.

Granting something more local, pertaining to a database itself, requires a narrower scope. To grant a privilege to create a view in the ACME database, one must be in ACME database context:

USE acme GRANT CREATE VIEW
			 TO PUBLIC The command(s) completed successfully.

Some Transact-SQL statements cannot be granted through privileges; the grantee must be a member of a predefined fixed server role (discussed later in this chapter). This means that in order to be able to execute, for example, the KILL statement (that stops a process inside an SQL Server installation) you have to be a member of the processadmin fixed role, in order to be able to grant ALL statement permissions you have to be a member of the sysadmin fixed role, the members of the db_owner role can grant and/or revoke any privilege within their database, and so on.

Granting object-level privileges

By their very nature, the object-level privileges are much more fine-grained than system-level ones. This is reflected in the syntax of the GRANT statement. These privileges could go all the way down to column level (if the object is a database table or view), or to any other object within the database such as stored procedures, functions, and triggers. The SQL Object-Level privileges are listed in Table 12-6.

This is the Oracle 9i generic syntax for granting privileges to the database objects:

GRANT [ALL [PRIVILEGES]] |
			 <object_privilege,...> [ON [<schema>].<object>] TO
			 <user> | <role> | <PUBLIC> [WITH {GRANT OPTION | HIERARCHY
			 OPTION}];

As with the GRANT system privileges statement, you need to supply a list of all the privileges you wish to grant (see Table 12-6 for a list of relevant object privileges). Specifying ALL would enable all privileges, but you as a user must have sufficient system privileges to grant this option yourself. Next comes the list of columns to which you may grant access (if applicable, as some database objects do not have columns), then you specify the object itself — table, view, procedure, package, sequence, synonym, and any other valid Oracle database object (the new JAVA and DIRECTORY clauses are not part of SQL and are beyond the scope of this book).

Note that not every object has a given privilege: some privileges are irrelevant to the objects. For example, the REFERENCES privilege does not make much sense if you are trying to assign it to an Oracle sequence, nor does the EXECUTE privilege make sense for a table. Consequently, if you specify ALL privileges, only those allowed for the object type would be granted. The following GRANT statement would generate an error:

SQL> GRANT EXECUTE ON
			 deduction TO PUBLIC; ORA-02224: EXECUTE privilege not allowed for
			 tables

The object privilege could be granted to a user, to a role, or to PUBLIC (which is a specific way to grant privileges to each and every user within that database).

The WITH GRANT OPTION indicates that the grantee will be able in his/her turn to GRANT this privilege to other users or roles.

The WITH HIERARCHY OPTION (Oracle only) indicates that the object privilege is granted not only for the object itself but also for all derived objects. For example, if a view is based upon a table, granting privileges to a table with such an option would automatically grant the same privileges for the view; however, it does not work the other way around — privileges for the view would not give the same access to the base table.

Here is a real example that is less confusing; it grants ALL privileges in the ACME database table PRODUCT to the SALES_FORCE role. Whoever belongs to the SALES_FORCE role will be able to exercise these privileges as soon as the following statement is executed:

SQL> GRANT SELECT,
			 UPDATE, DELETE ON product TO sales_force; Grant succeeded.

IBM DB2 UDB has probably the most diverse syntax when it comes to object-level privileges. In addition to the object-level privileges shown in Table 12-6, it has a bunch of its own (Table 12-7).

Table 12-7: IBM DB2 UDB Object-Level Privileges

Object Privilege	Syntax	Description	Pertains To
CONTROL	GRANT CONTROL ON {OBJECT}<object_name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to drop the object.	Index package table view nickname
BIND	GRANT BIND ON PACKAGE <package TO USER <user> name>| GROUP <group>| PUBLIC	Permits grantee to bind the package.	Package
ALTERIN	GRANT ALTERIN ON SCHEMA<schema name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to alter the existing objects in the schema, or to add comments to them.	Schema
CREATEIN	GRANT ALTERIN ON SCHEMA<schema name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to create objects in the schema.	Schema
DROPIN	GRANT ALTERIN ON SCHEMA<schema name> TO USER< Preface How This Book Is Organized Conventions Used in This Book Part I: SQL Basic Concepts and Principles Chapter 1: SQL and Relational Database Management Systems (RDBMS) Selecting Your Database Software Everything in Details: DBMS Implementations Real-Life Database Examples Database Legacy Relational Databases Object Database and Object-Relational Database Models Brief History of SQL and SQL Standards Summary Chapter 2: Fundamental SQL Concepts and Principles SQL: The First Look Any Platform, Any Time Summary Chapter 3: SQL Data Types No Strings Attached In Numbers Strength Once Upon a Time: Date and Time Data Types Object and User-Defined Data Types Other Data Types NULL Summary Part II: Creating and Modifying Database Objects Chapter 4: Creating RDBMS Objects Tables Views Aliases and Synonyms Schemas Other SQL99 and Implementation-Specific Objects CREATE Statement Cross-Reference Summary Chapter 5: Altering and Destroying RDBMS Objects Tables Views Aliases and Synonyms Schemas Other Implementation-Specific Objects ALTER and DROP Statements Cross-Reference Summary Part III: Data Manipulation and Transaction Control Chapter 6: Data Manipulation Language (DML) INSERT: Populating Tables with Data UPDATE: Modifying Table Data DELETE: Removing Data from Table Other SQL Statements to Manipulate Data Summary Chapter 7: Sessions, Transactions, and Locks Sessions Transactions Understanding Locks Summary Part IV: Retrieving and Transforming Data Chapter 8: Understanding SELECT Statement Single Table SELECT Statement Syntax SELECT Clause: What Do We Select? FROM Clause: Select from What? WHERE Clause: Setting Horizontal Limits GROUP BY and HAVING Clauses: Summarizing Results ORDER BY Clause: Sorting Query Output Combining the Results of Multiple Queries Summary Chapter 9: Multitable Queries Inner Joins Outer Joins: Joining Tables on Columns Containing NULL Values Joins Involving Inline Views Multitable Joins with Correlated Queries Improving Efficiency of Multitable Queries Summary Chapter 10: SQL Functions Summary Chapter 11: SQL Operators Arithmetic Operators Logical Operators Operator Precedence Assignment Operator Comparison Operators Bitwise Operators User-defined Operators Summary Part V: Implementing Security Using System Catalogs Chapter 12: SQL and RDBMS Security Defining a Database User Managing Security with Privileges Managing Security with Roles Using Views for Security Using Constraints for Security Using Stored Procedures and Triggers for Security Database Auditing Security Standards Summary Chapter 13: The System Catalog and INFORMATION_SCHEMA Oracle 9i Data Dictionary IBM DB2 UDB 8.1 System Catalogs Microsoft SQL Server 2000 System Catalog Summary Part VI: Beyond SQL--Procedural Programming and Database Access Mechanisms Chapter 14: Stored Procedures, Triggers, and User-Defined Functions Procedural Extension Uses and Benefits Key Elements of a SQL Procedural Language Stored procedures User-Defined Functions Triggers Summary Chapter 15: Dynamic and Embedded SQL Overview SQL Statement Processing Steps Embedded (Static) SQL Dynamic SQL Techniques The Future of Embedded SQL Summary Chapter 16: SQL API Microsoft Open Database Connectivity (ODBC) Java Database Connectivity (JDBC) IBM DB2 UDB Call-Level Interface (CLI) Oracle Call Interface (OCI) Oracle Objects for OLE (OO4O) Microsoft Data Access Interfaces Summary Chapter 17: New Developments--XML, OLAP, and Objects XML OLAP and Business Intelligence Objects Summary Part VII: Appendix Appendix A: What's on the CD-ROM Using the CD with Windows What's on the CD Troubleshooting Appendix B: The ACME Sample Database General Information and Business Rules Naming Conventions Relationships Between Tables Column Constraints and Defaults SQL Scripts to Create ACME Database Objects Appendix C: Basics of Relational Database Design Identifying Entities and Attributes Normalization Specifying Constraints Pitfalls of Relational Database Design Appendix D: Installing RDBMS Software Installing IBM DB2 UDB 8.1 Personal Edition Installing Microsoft SQL Server 2000 Appendix E: Accessing RDBMS Using IBM DB2 UDB 8.1 Command-Line Processor (CLP) Using Microsoft SQL Server Utilities to Access Database Appendix F: Installing the ACME Database Installing the ACME Database on Oracle 9i (Unix/Linux) Using SQL*Plus Installing the ACME Database on DB2 UDB 8.1 (Windows) Using CLP Installing ACME Database on Microsoft SQL Server 2000 Using OSQL Utility Appendix G: SQL Functions Appendix H: SQL Syntax Reference DDL Statements DCL Statements DML Statements DQL Statements Transactional Control Statements Predicates Appendix I: SQL-Reserved Keywords Future Keywords ODBC Reserved Keywords Appendix J: SQL99 Major Features Compliance Across Different RDBMS Appendix K: The Other RDBMS Appendix L: A Brief Introduction to the Number Systems, Boolean Algebra, and Set Theory The Number Systems Logic Elements of Boolean Algebra Set Theory List of Figures List of Tables List of Code Examples List of Sidebars Remember the name: eTutorials.org Copyright eTutorials.org 2008-2023. All rights reserved.