SQL User Authorization & Security

SQL User Authorization & Security

As previously presented, a RDBMS serves as the foundation for today’s Internet accessible enterprise information system (IS).  It is also understood that computer security and information assurance is critical functionality for today’s organizations and their extended value and supply chains. With this basis system administrators and database administrators (DBA) must establish policies and mechanisms to provide for IS confidentiality, integrity and availability (Bishop, 2003).  Central to both system and RDBMS security mechanisms are the principles of authentication and authorization.   By definition, authentication serves to identify an agent whereas authorization provides a policy driven mechanism to control resource access (Bishop, 2003; Nutt, 2004).

By design, a RDBMS provides a more finely granular information access control mechanism than an operating system (OS) managed file system (Silberschatz, Korth & Sudarshan, 1999).  A RDBMS goes beyond the file system’s coarse granular disk block access by allowing selected tuples and projected attributes to be accessed directly (Silberschatz, Korth & Sudarshan, 1999). This concept allows  SQL views to be created and tailored to an agent’s role.

A natural and complimentary SQL extension to views is the provision to discretely control information access based in agent’s authorization and need to know.   To provide an example of the need for this type of authorization mechanism, consider that access to sensitive employee information such as social security numbers and salaries should be restricted to an organization’s internal Human Resources and Accounting departments (Robbins & Judge, 2007).   With this basis, SQL’s support for views and authorization provides discretionary access control in accord with the military’s Bell-LaPadula model and allows an organization to tailor information access to their many different constituencies throughout their supply and value chains (Bishop, 2004; Codd, 1990; Elmasri & Navathe, 2000).

As a basis, relational logic (RL) based on first-order predicate logic and relational algebra provides a RDBMS with the necessary foundation for authorization (Codd, 1990).  With this basis, authorization can be categorized as RDBMS constraint (Codd, 1990).  It should be noted contemporary RDBMS authorization goes beyond its RL basis in several ways.  Authorization can implement a temporal component to control an agent’s length of access to the database (Codd, 1990).  Additionally, the RDBMS can implement authorized access control mechanisms that adapt to system loads (Codd, 1990).

RDBMS authorization is managed through agent Authorization IDs and their ascribed authorization levels (Codd, 1990; Garcia-Molina, Ullman & Widom, 2009).  Agents can be further discriminated as individuals, processes or specific terminals whereas authorization levels apply to module schemas and sessions (Codd, 1990; Garcia-Molina, Ullman & Widom, 2009).  Authorization IDs and authorization information is maintained in the catalog (Codd, 1990).  Authorization IDs are required for every agent in the system however the RDBMS may define a special PUBLIC ID that includes all users generically (Codd, 1990; Garcia-Molina, Ullman & Widom, 2009).  This framework provides a RDBMS with the requisite functionality to verify that each agent generated operation has the appropriate privileges to execute on a specific component of the RDBMS (Codd, 1990; Garcia-Molina, Ullman & Widom, 2009).

In accord with the relational and Bell-LaPadula models, RDBMS authorization is granted on an affirmative basis (Bishop, 2003; Codd, 1990, p. 307, p. 352).  Affirmative authorization is based on explicitly stated permission rather than explicitly stated denial (Codd, 1990, p. 307).  This means that agents are implicitly restricted from accessing the RDBMS and they must receive explicit permission to perform specific operations and access specific database components.  To mitigate this management complexity, SQL provides a flexible mechanism for granting privileges.

Permission can be transferred or assigned to agents through the SQL GRANT clause from an agent that possesses the requisite authorization (Codd, 1990; Garcia-Molina, Ullman & Widom, 2009).  By default, schemas and modules have an owner and this owner has full privileges to manage the schema and provide access to other agents.  This owner can also determine whether this secondary agent can extend access to other agents through subsequent GRANT clauses.  SQL provides the ability to revoke privileges through the SQL REVOKE clauses.  Consistent with other RDBMS constraint mechanisms, revoked privileges may be set to cascade using the SQL CASCADE clause or cascading revocation may be prevented through the SQL RESTRICT clause.


Authorization is an essential component of a DBA’s responsibilities since the DBA is responsible for the integrity of the database and its catalog (Codd, 1990).  Authorization must be managed very carefully based on professional expertise and knowledge of organizational policies and procedures (Codd, 1990).  From a management standpoint, privileges can be assessed and tracked through grant diagrams (Garcia-Molina, Ullman & Widom, 2009).  To illustrate the danger of improperly managed data authorizations consider the possibility of an agent that is granted too much authority as this agent may invoke cascading updates and deletions that quickly and irrevocably compromise the database’s integrity (Codd, 1990).  To illustrate the danger of an improperly managed catalog, recall that relational algebra and relational tables (R-tables) are based on relation and attribute names rather than pointers therefore renaming components can render access and authorization meaningless (Codd, 1990).  With this basis, the DBA must continually assess all references, views, indexes, constraints and authorizations whenever there is a change to the catalog (Codd, 1990).


Bishop, M. (2003). Computer security. Boston, MA: Addison Wesley.

Codd, E. F. (1990). The relational model for database management: version 2.  Boston, MA: Addison-Wesley Longman Publishing Co.  Retrieved June 1, 2010 from, http://portal.acm.org/citation.cfm?id=77708.

Codd, E. F., Codd, S. B., & Salley, C. T. (1993). Providing OLAP (on-line analytical processing) to user-analysis: An IT mandate.  www.aaai.org. Retrieved July 21, 2010 from, http://www.cs.bgu.ac.il/~dbm031/dw042/Papers/olap_to_useranalysts_wp.pdf.

Elmasri, R., & Navathe, S. B. (2000).  Fundamentals of database systems. Reading MA: Addison-Wesley.

Erl, T. (2005) Service-oriented architecture: Concepts, technology, and design. Upper Saddle River, NJ: Pearson Education.

Fayyad, U., Piatetsky-Shapiro, G., & Smyth, P. (1996).  From data mining to knowledge discovery in databases. www.aaai.org. Retrieved July 21, from, http://www.daedalus.es/fileadmin/daedalus/doc/MineriaDeDatos/fayyad96.pdf

Garcia-Molina, H., Ullman, J.D., & Widom, J.   (2009).   Database systems: The complete book, (2nd ed.).   Upper Saddle River, NJ: Pearson Prentice Hall.

Graham, S. Davis, D. Simeonov, S. Daniels, G. Brittenham, P. Nakamura, Y, Fremantle, P. Konig, D., & Zentner, C. (2005). Building Web services with Java (2nd ed.). Indianapolis, IN: Sams Publishing

Jackson, P. (1999). Introduction to expert systems. Essex, England: Addison Wesley.

Kotler, P. & Keller, K. L. (2007). Marketing management (12th ed.). Upper Saddle River, NJ: Pearson Publishing.

Malik, D. S. (2006). Java programming: Program design including data structures. Boston MA: Course Technology.

Nagarajan, M., Verma, K., Sheth, A. P., & Miller, J. A. (2007). Ontology driven data mediation in web services. International Journal of Web Services Research, 4(4), 104-126.  Retrieved February 16, 2009, from ABI/INFORM Global database. (Document ID: 1522690091).

Nutt, G. (2004). Operating systems (3rd ed.). Boston, MA: Addison Wesley.

Raab, D. (2009). An about face on the database: Markers may shift from analytical two transactional databases as focus on real-time interactions increases. Information Management, 19 (5), 38.

Rivest, S., Bedard, Y., & Marchand, P. (2001). Toward better support for spatial decision making: Defining the characteristics of spatial on-line analytical processing (SOLAP).  Centre for Research in Geomatics, Laval University, Québec.  Retrieved  July 21, 2010 from, http://citeseerx.ist.psu.edu/viewdoc/download?doi=

Robbins, S. P., & Judge, T. A. (2007). Organizational Behavior. Upper Saddle River, NJ: Prentice Hall.

Schalkoff, R. J. (1990). Artificial intelligence: An engineering approach. New York: McGraw-Hill.

Sheth, A. P., Gomadam, K., & Lathem, J. (2007). SA-REST: Semantically interoperable and easier-to-use services and mashups. IEEE Internet Computing, 11(6), 91-94.  Retrieved February 16, 2009, from ProQuest Computing database. (Document ID: 1424144951).

Silberschatz, A., Korth, H. F., & Sudarshan, S. (1999). Database system concepts (3rd ed.). Boston, MA: McGraw-Hill.

Steiert, H. (2007). Towards a component-based n-Tier C/S architecture. Kaiserslautern, Germany: Department of Computer Science, Database and Information Systems Group,University of Kaiserslautern.  Retrieved January 1, 2009 from http://www.dr-gail.org/upload/p137-steiert.pdf

Van Le, T. (1993). Techniques of Prolog programming. New York: Wiley Publishing.

W3C, (2004). Web services architecture: W3C working group note 11 February 2004. Retrieved February 21, 2009 from, http://www.w3.org/TR/ws-arch/#id2260892

Weisfield, M. (2009). The object-oriented thought process (3rd ed.). Upper Saddle River, NJ: Addison Wesley.

Wilander, J., & Kamkar, M. (2003). A comparison of publicly available tools for dynamic buffer overflow prevention. Retrieved March 28, 2010 from, http://citeseerx.ist.psu.edu/viewdoc/download?doi=

Wilson, L. B., & Clark, R. G. (2001).  Comparative programming languages (3rd ed.). Essex, England: Addison-Wesley.

Wolfe, A. (2008). Is a smartphone your next computer. Information Week.  Retrieved January 7, 2009 from, http://www.informationweek.com/news/personal_tech/smartphones/showArticle.jhtml?articleID=210605369.

Leave a Reply