Abstract:
The present invention provides a system and method allows a user to add a parameter at the end of the GRANT statement that would not require multiple changes by the DBA. The parameter may indicate that the user only needs to have access for 2 days, or to have access when a flag is set in the database. This reduces the frequency that a DBA needs to be engaged and decreases the cycle time that is necessary to turn the request around. An additional benefit is that the authority is removed when it should be. This then also improves the business controls around the data.

Description:
FIELD OF THE INVENTION 
       [0001]    The present invention relates generally to granting access to database systems and, more specifically, to improvements to systems and methods for dynamically granting authorization to database objects. 
       BACKGROUND OF THE INVENTION 
       [0002]    In the data warehousing space, there are frequently times when someone needs to gain temporary access to a database object. A data warehouse is the main repository of an organization&#39;s historical data, its corporate memory. An example is IBM&#39;s DB2® and can be seen here http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp. Currently, a user needs to be authenticated (authentication is the process of attempting to verify the digital identity of the sender of a communication such as a request to log in. (See http://www.authenticationworld.com/.) And then, engage a database administrator (DBA) (a person who is responsible for the environmental aspects of a database) to grant the access, and at the appropriate time, have the DBA revoke the access. A better solution would be to have the grant to the object be based dynamically on time or other objects in the database. 
         [0003]    Today, the GRANT statement for a database is in the general format of Grant &lt;requested authority&gt; on &lt;object&gt; to &lt;id/group&gt;. This form of the GRANT statement grants privileges on a package. This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). The privileges held by the authorization ID of the statement must include at least one of the following:
       CONTROL privilege on the referenced package   The WITH GRANT OPTION for each identified privilege on package-name   SYSADM or DBA authority       
 
         [0007]    SYSADM or DBA authority is required to grant the CONTROL privilege. 
         [0008]    This is a problem as an authority, such as a DBA, is required to grant or to not grant privileges. This causes latency in database access requests. Unfortunately, sometimes, temporary authority is granted, but then someone forgets to request that the authority be removed. 
         [0009]    Some systems allow the ability to grant access to a database, such as through the use of time based access authorization, to an access requestor such as obtaining access, or authorization, through an Internet server utilizing the WHOIS infrastructure. (WHOIS is a TCP-based query/response protocol which is widely used for querying a database in order to determine the owner of a domain name, an IP address, or an autonomous system number on the Internet.) WHOIS lookups are generally utilized using simplified web-based tools to look up domain ownership details from different databases. For more information, see http://tools.ietf.org/html/rfc3912.) However, these systems are not geared towards authenticating access to database objects with different levels of predefined rules. 
         [0010]    There is a need for dynamically granting authorization to database objects. 
       BRIEF SUMMARY OF THE INVENTION 
       [0011]    The present invention provides a system and method for dynamically granting authorization to database objects. 
         [0012]    The present invention provides a system and method allows a user to add a parameter at the end of the GRANT statement that would not require multiple changes by the DBA. The parameter may indicate that the user only needs to have access for 2 days, or to have access when a flag is set in the database. This reduces the frequency that a DBA needs to be engaged and decreases the cycle time that is necessary to turn the request around. An additional benefit is that the authority is removed when it should be. This then also improves the business controls around the data. 
         [0013]    The illustrative aspects of the present invention are designed to solve one or more of the problems herein described and/or one or more other problems not discussed. 
     
    
     
       BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWINGS 
         [0014]    These and other features of the invention will be more readily understood from the following detailed description of the various aspects of the invention taken in conjunction with the accompanying drawings that depict various embodiments of the invention, in which: 
           [0015]      FIG. 1  is a diagram which illustrates a basic database access system. 
           [0016]      FIG. 2  illustrates the system of the present invention for the system and method of the present invention for dynamically granting authorization to database objects. 
           [0017]      FIG. 3  illustrates the system of the present invention. 
           [0018]      FIG. 4  illustrates the new GRANT statement of the present invention. 
           [0019]      FIG. 5  illustrates a second embodiment of the new GRANT statement of the present invention. 
       
    
    
       [0020]    The drawings are intended to depict only typical aspects of the invention, and therefore should not be considered as limiting the scope of the invention. In the drawings, like numbering represent like elements between the drawings. 
       DETAILED DESCRIPTION OF THE INVENTION 
       [0021]    The present invention provides a system and method for dynamically granting authorization to database objects. 
         [0022]      FIG. 1  illustrates a basic database access system. It comprises a User A  102  and a User B  103 , both of whom have logged in using their respective user IDs, are requesting access to Databases  118  via Users&#39; A, B  102 ,  103  SELECTs  102   b ,  103   b . The SELECTs could be requests for data located within tables within Databases  118 . User A  102  and User B  103  have communicating Devices  102   c ,  103   c , such as personal computers, having screens  102   a ,  103   a  to illustrate messages from the Server  106 . (However, the Devices  102   c ,  103   c  don&#39;t need to be personal computers as it can as easily be a cell phone, PDA and the like.) Like most, if not all, servers, Server  106  has a network input/output device  112  to receive and send messages, one or more CPUs  114 , Databases  118  to store data, such as a data table, and an internal bus  114  like other computers. According to typical security procedures, User A has a Key  115 , User B  116  has a key, and Server  106  has a Key  118  for authentication purposes. Both users use the keys and transmit other known information to log into the Databases  118 . 
         [0023]    The SELECT request, or other types of requests, is done using current state of the art processes for granting access to a relational database object. The processes are built into the relational database (e.g., IBM® DB2®—see http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp). The present invention allows for logic to determine on and off. 
         [0024]    As can be seen, an access grant request, User A SELECT  102   b , is sent to Server  106  via Network  104 . A SELECT request asks for privileges on a package, or data object such as a data table, and a GRANT grants the requested privileges and the requested data is transmitted to the requesting user. A GRANT reject does not accept the request and the reject message is transmitted to the requesting user. The control statement is embedded within the database by a DBA. This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). More information on this topic can be found at http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp. 
         [0025]    The User A SELECT  102   b  is processed through the Network  104  and through the Server  106  and is processed by Database Control Software  105  and User A Requested Data  102   d  is sent to User A  102 . User B  103  sends User B SELECT  103   b  through Network  104  and is processed but is rejected due to the fact that User B  103  is not authorized by DBA  105 . The User B SELECT Reject  107   b  is sent through Network  104 . 
         [0026]    The DBA utilizes a GRANT statement to control access to the Databases  118 . The GRANT statement exists today. An example of that statement would be: GRANT SELECT on table1 to User A 
         [0027]    The GRANT SELECT Statement is issued by a DBA or authorized user (after he/she logs into the database management system (DBMS)) and grants access to table1 to User A  102 . The authority to access to User A  102  to table1 is also logged in the DBMS. 
         [0028]    This is shown in  FIG. 2  where the Process  200  of the present invention is shown. It starts at Step  202  and moves to Steps  204 ,  206 ,  208  where User A, User B and DBA log in. It should be noted that all of these parties can be located remotely. User A has User A ID, User B has User B ID, etc., and each has specific access rights. For instance, in  FIG. 2 , both User A and User B send SELECT statements at  210  and  212 , such as a SELECT statement to a specific table in a database. Because User A has access to the selected database according to the GRANT statement, he gets the data he requested while User B does not have access according to the GRANT statement so he does not receive the access to the data. In addition, if the DBA issues a REVOKE command for a specific user to a specific database, the user will receive an error message. 
         [0029]      FIG. 3  illustrates the System  300  of the present invention which has a Transmitter/Receiver  302  for transmitting and receiving messages such as User A SELECT  102   b  message and DBA Update  312 . System  300  further has a Parser  302  for parsing messages, such as the User A SELECT  102   b  message, and pulling user IDs from a previous log on. System  300  further has an Examiner  304  for examining the parsed message and corresponding user ID and a Determiner  306  for determining whether User A had the requested access by comparing the User A ID and the User A SELECT  102   b  message against the GRANT document of the Database Object  310  stored in Databases  308 . The Updater  306  allows the DBA to update the GRANT document associated with the Database Object  310 . 
         [0030]      FIG. 4  illustrates the new GRANT statement  400  of the present invention. At  402 , the GRANT statement  400  identifies the statement as a GRANT, the authority, the data object and the ID/group. Statement  404  identifies the time interval for which the GRANT statement  400  is viable. Statement  406  identifies the “until” timestamp or which the GRANT statement  400  is viable for Database Object  310  for ID/group. Statement  408  identifies the “between” time for which the GRANT statement  400  is viable. Statement  410  identifies the end of the SQL query. 
         [0031]      FIG. 5  illustrates a second illustrative embodiment of the new GRANT statement  500  of the present invention. At  502 , the GRANT statement  500  identifies the statement as a GRANT, the authority, the data object and the ID/group. Statement  504  identifies the “except” time interval for which the GRANT statement  500  is not viable, the “except” time is by Statement  506  which identifies the “for” timestamp. Statement  508  identifies the “until” timestamp when the “except” statement expires. Statement  510  identifies the “between” time for which the “except” statement is active. Statement  512  identifies the end of the SQL query. 
         [0032]    The foregoing description of various aspects of the invention has been presented for purposes of illustration and description. It is not intended to be exhaustive or to limit the invention to the precise form disclosed, and obviously, many modifications and variations are possible. Such modifications and variations that may be apparent to an individual in the art are included within the scope of the invention as defined by the accompanying claims.