Download: SQL Roles: Users and Security in InterBase What is an InterBase user?

D.2.j (Database Talk) [SQL Roles: Users and Security in InterBase] SQL Roles: Users and Security in InterBase Brett Bandy Markus Kemper BorCon 1998 Databases need security. Data stored in database files must be secure. InterBase provides two levels of security for data; user validation and database privileges. This paper will discuss how to setup security for both levels to maintain security for your database. Topics covered: • What an InterBase user is and how to create them • Basic SQL privileges and how to apply them to a database • How SQL Roles can make a Database Administrator's life eas...
Author: Apers1947 Shared: 7/30/19
Downloads: 895 Views: 2510

Content

D.2.j (Database Talk) [SQL Roles: Users and Security in InterBase]

SQL Roles: Users and Security in InterBase

Brett Bandy Markus Kemper BorCon 1998 Databases need security. Data stored in database files must be secure. InterBase provides two levels of security for data; user validation and database privileges. This paper will discuss how to setup security for both levels to maintain security for your database. Topics covered: • What an InterBase user is and how to create them • Basic SQL privileges and how to apply them to a database • How SQL Roles can make a Database Administrator's life easier • How to use SQL Roles in a database What is an InterBase user? InterBase security is based on the concept of a user. A user is the identity that all database security is validated against. Authorized InterBase users are stored in a security database, which is called ISC4.GDB. Each InterBase server has its own security database, which means that a user definition is bound to the server where it is stored. The same user may exist on several servers, but it must be created on all servers where it is required. The security database also stores an encrypted password for each user. Users in the security database are authorized for all databases that reside on that server. The username can have a maximum of 31 characters. The username is NOT case-sensitive. The password can have up to 8 characters. The password IS case-sensitive. Common Mistake: This is a common mistake for a lot of new InterBase users. They create a user and password, but when they try to use it they get an error message saying that they don't have a valid username and password. Make sure that you have specified the password EXACTLY how it was defined. Column Required? Data Type Description User Yes String The name the user supplies when logging in. Name, Password Yes String The user's password UID No Integer Optional UserID. Currently not used by InterBase GID No Integer Optional GroupID. Currently not used by InterBase Full Name No String User's real name Extending InterBase users with Operating System users All versions of InterBase use the security database to authenticate users. Some versions of InterBase allow access based on operating system permissions. All InterBase kits running on Unix will allow the use of either operating system users or InterBase users when logging into an InterBase server or database. InterBase kits running on Win95 or NT do not make use of operating system security database permissions. A Win95 or NT user is NOT a valid InterBase user. InterBase relies solely on the security database for user authentication on Win95 and NT. InterBase will treat a Unix user the same as a user stored in the InterBase security database, as long as the server sees the client as a trusted host. The user account must exist on both the client and the server. To establish a trusted host relationship between the client and the server, an entry must be in the server's /etc/hosts.equiv or /etc/gds_hosts.equiv file. The hosts.equiv file will setup an OS trusted host relationship, which extends to other services (for example rlogin, rsh, rcp). The gds_hosts.equiv will setup an InterBase only trusted host relationship. The format of an entry is identical for the two files. The format of an entry in either file is: hostname [username] It is not possible to setup a trusted host relationship between a Unix machine and a Windows machine. To use Unix usernames, the client and the server must both be Unix machines. Unix usernames are used only if no InterBase username is specified at database login time. SYSDBA user When InterBase is installed there is only one user authorized, SYSDBA. SYSDBA is a special user that is above security restrictions and has full access to all databases on the server. The default password for SYSDBA is masterkey. It is strongly recommended to change the SYSDBA password, because this is the default for all InterBase kits and can easily be guessed by anyone who has ever used InterBase. Only SYSDBA can add, modify, or delete new users. On Unix systems the root superuser can be used as the SYSDBA user. InterBase treats the root username as SYSDBA. When using root you will have all privileges on all databases residing on the server. Managing users InterBase provides three interfaces for SYSDBA to manage users. • GSEC, • Server Manager • InterBase API function calls All three methods provide the same functionality. They just differ in how they are used. Using GSEC InterBase's command-line security utility GSEC is a command-line utility that provides an interface to InterBase's security database. You must be SYSDBA or the superuser (on Unix) to use GSEC. GSEC can be used interactively or from the command-line. The following table summarizes GSEC's commands. Command Description Displays all users stored in di[splay] ISC4.GDB Displays the information for user di[splay] name name a[dd] name -pw passwd [option Adds user name with password argument option argument ...] passwd and optional information mo[dify] name [options] Modify a user's attributes de[lete] name Deletes user name from ISC4.GDB Display's GSEC's commands and h[elp] syntax q[uit] Quits interactive GSEC Displaying users The DISPLAY command will show all authorized users GSEC display user name uid gid full name - SYSDBA00TEST00BBANDY00MKEMPER00Adding a user Use the ADD command to add new users to the security database. The following table lists all options that can be specified when adding or modifying a user., Option Description -pw User's password -u[id] User ID -g[id] Group ID -f[name] User's first name -mn[ame] User's middle name -l[name] User's last name This example will add user BJONES with specified password. It also stores information for first and last names. GSEC add BJONES -pw blah -fname Bobby -lname Jones GSEC display BJONES user name uid gid full name - BJONES00Bobby Jones Modifying a user The MODIFY command is used to change existing user information. This example updates the UserID and lastname for user BJONES GSEC modify BJONES -uid 22 -fname Brad GSEC display BJONES user name uid gid full name - BJONES 22 0 Brad Jones Deleting a user This example will use the DELETE command to delete the user BJONES. To verify that the use has been deleted the DISPLAY command will be used to show all users. GSEC delete BJONES GSEC display user name uid gid full name - - SYSDBA00TEST00BBANDY00MKEMPER 0 0, Using Server Manager to manage users InterBase's Server Manager provides a Graphical Interface for many common database administration tasks. The Server Manager is only available on Windows platforms. You can manage an InterBase server on any platform InterBase supports, but the Server Manager must be run on a windows platform. The InterBase Client for Windows must be installed on the machine where Server Manager is being used. Among the many tasks it performs is user management. Server Manager will perform the same user management tasks that GSEC does, but in a Graphical Environment. Since each InterBase server has its own security database, you must login to the server as SYSDBA before you can manage the server's users. InterBase Security Dialog There are two main windows dealing with user security. The first form, InterBase Security, presents a list of current users in the security database, analogous to GSEC's DISPLAY command. This form has buttons to add, modify, or delete users. User Configuration Dialog The second Server Manager window is the User Configuration Dialog. This dialog will display when you perform an "Add User" or "Modify User". The dialog groups all the required fields separate from the optional information. When adding a new user the User Configuration Dialog will display with all its fields empty. For a new user you must enter a username and password. Additional user information can be entered as well., Using the InterBase API to Manage Users InterBase provides a native Application Programming Interface (API) to access databases and perform administration functions. Applications can use these API functions to provide connectivity to InterBase databases. Among these API functions are three that allow the application programmer to manage users. The following table summarizes the three API functions available for user management. API Function Description isc_add_user Adds a new user to security database isc_modify_user Modifies a users record in security database isc_delete_user Deletes a user from security database For each of the three functions you will setup a USER_SEC_DATA structure. This structure is defined in ibase.h as: typedef struct { short sec_flags; /* which fields are specified */ int uid; /* the user's id */ int gid; /* the user's group id */, int protocol; /* protocol to use for connection */ char ISC_FAR *server; /* server to administer */ char ISC_FAR *user_name; /* the user's name */ char ISC_FAR *password; /* the user's password */ char ISC_FAR *group_name; /* the group name */ char ISC_FAR *first_name; /* the user's first name */ char ISC_FAR *middle_name; /* the user's middle name */ char ISC_FAR *last_name; /* the user's last name */ char ISC_FAR *dba_user_name; /* the dba user name */ char ISC_FAR *dba_password; /* the dba password */ } USER_SEC_DATA; This structure has members for each of the user attributes that can be stored in the security database. There are additional members in the structure that are required for the server to know which server's security database is being modified and which fields in the database are being modified. Examples using the USER_SEC_DATA structure with the three API functions are provide below. For the user management functions to succeed the SYSDBA password must be supplied. This can be done in two different ways. When setting up the USER_SEC_DATA structure, there are two fields that can be used to specify the SYSDBA username and password. The two fields used to specify the SYSDBA user and password are: • dba_user_name • dba_password The dba_user_name should be set to SYSDBA, while the dba_password should be set to the SYSDBA password (default for InterBase is masterkey). Common Mistake: You should not hardcode the SYSDBA password into the application. Any user can run a grep utility or hex editor on the binary and retrieve the static string. Instead you should have a provision to pass in the SYSDBA password so that it is not statically stored with the binary. Following are several examples using these user management API functions. The examples are provided in both C and Delphi. Adding a new user in C This example will add user BJONES with password bjones into the security database. This example will also store the user's first and lastnames. File: adduser.c #include "ibase.h" #include Adding a new user in Delphi This example does the same thing as the previous add user example written in C. File: Adduser.dpr program Adduser; uses ibase in 'ibase.pas', ib_externals in 'ib_externals.pas'; var userData: TUserSecData; userDataPtr: PUserSecData; status: array[0..19] of ISC_STATUS; isc_status: PISC_STATUS; begin { setup isc_status pointer } isc_status := @status; { setup user data pointer to point to user data structure } userDataPtr := @userData; { setup user data structure } userData.user_name := 'BJONES'; userData.password := 'bjones'; userData.protocol := sec_protocol_local; userData.dba_user_name := 'SYSDBA'; userData.dba_password := 'masterkey'; /* Don't hardcode this */ userData.first_name := 'Bobby'; userData.last_name := 'Jones'; userData.sec_flags := sec_password_spec or sec_dba_user_name_spec or sec_dba_password_spec or sec_first_name_spec or sec_last_name_spec;, { add user to security database } isc_add_user(isc_status, userDataPtr); end.

Modifying a user in C This example will modify the user BJONES's password to bjones. File: modifyuser.c

#include "ibase.h" #include Modifying a user in Delphi This example will modify the user BJONES's password to newpass and also change the UID to 22.

File: ModifyUser.dpr

program ModifyUser; uses ib_externals in 'ib_externals.pas', ibase in 'ibase.pas'; var userData: TUserSecData; userDataPtr: PUserSecData; status: array[0..19] of ISC_STATUS; isc_status: PISC_STATUS;, begin { setup isc_status pointer } isc_status := @status; { setup user data pointer to point to user data structure } userDataPtr := @userData; { setup user data structure } userData.user_name := 'BJONES'; userData.password := 'newpass'; userData.uid := 22; userData.protocol := sec_protocol_local; userData.dba_user_name := 'SYSDBA'; userData.dba_password := 'masterkey'; /* Don't hardcode this */ userData.sec_flags := sec_uid_spec or sec_password_spec or sec_dba_user_name_spec or sec_dba_password_spec; { add user to security database } isc_modify_user(isc_status, userDataPtr); end.

Deleting a user in C This example will delete the user BJONES from the security database. As shown in the

examples, the only fields required are the user to be deleted and the SYSDBA user and password.

File: deleteuser.c

#include "ibase.h" #include SQL Privileges: The second level of security InterBase implements two levels of security. The first is user validation. This is done at database connection time. Users are validated against InterBase's security database (See previous section for discussion of authorized users). The second level of security is implemented at the database level. All privileges for this level are stored in the database itself. An authorized user does not have privileges to data stored in the database unless that user is explicitly assigned privileges. Authorized users are allowed to connect to databases, but unless they have privileges they cannot access any of the objects or data stored in the database. SQL privileges are controlled on a table level. Each user has a list of operations that he or she is, allowed to perform on a given table or view. This list of operations makes up that user's access privileges. Additionally, InterBase puts restrictions on the use of stored procedures as well. A user cannot execute a stored procedure unless that user has been assigned the privilege to do so. When a database object is created only the SYSDBA user and the owner of the object have privileges to access that object. The user that creates the database object is the owner of that object. SYSDBA or the owner of the object can explicitly grant privileges to other users. Additionally, the privilege of assigning privileges to other users can also be assigned to a user other than SYSDBA or the object's owner. How to Assign Privileges to Users As previously stated, only SYSDBA and the owner of the object initially have privileges to access the object. If SQL privileges stopped here, they would be useless. There needs to be a way to allow additional users to access the database objects. With SQL privileges this is done with the GRANT and REVOKE statements. The GRANT statement is used to assign privileges on tables or views to authorized users. Conversely, the REVOKE statement is used to take away privileges that were previously assigned to a user with the GRANT statement. Common Mistake: A common mistake developers make is to create database objects and not assign privileges to any users. Since the developer is the owner of the object there are no privilege problems. Being the owner allows the developer to perform any and all operations on the database object. When it comes time to deploy the application, no other users are allowed access, because they have not been granted privileges on the database object. For all security operations, SYSDBA always has the right to grant or revoke privileges from users. The SYSDBA user always assumes total control over a database. SYSDBA is the superuser, there is no way to deny access from the SYSDBA user. The GRANT and REVOKE statements have no effect on the SYSDBA user. GRANT Statement The GRANT statement assigns a new privilege or privileges on a database object to a user. The types of privileges that can be assigned to a user are listed in the following table. Privilege Definition of Privileges Insert Allows insertion of new rows into table Update Allows existing rows to be updated Delete Allows existing rows to be deleted Select Allows user to view/query the rows in a table Execute Allows a user to execute a procedure Referenc Allows server to lookup rows in a primary/foreign key, es relationship Shortcut to assign insert, update, delete, select, references to a All user Granting privileges to PUBLIC SQL defines the special user PUBLIC to represent all uses. If an operation is granted to PUBLIC, then any valid user can perform the operation on the specified database object. Care should be taken when granting privileges to PUBLIC. All current and future users will be able to perform the operations that have been granted to PUBLIC. Granting privileges to execute a Stored Procedure To use a stored procedure a user must have the privilege to execute that stored procedure. There is a form of the GRANT statement that assigns the privilege to execute a stored procedure. The form of the statement is: GRANT EXECUTE ON PROCEDURE procname to user; Additionally, the stored procedure must have privileges on all relevant tables for all operations that it performs. The stored procedure must have been given the privilege to access all the tables that it uses. An example assigning the select privilege to a stored procedure would take this form: GRANT SELECT on tablename to PROCEDURE procname; Granting access to Views For the most part, assigning privileges on views is the same as for tables. There are a few differences that are very important and need pointing out. First, a view is a virtual table. A view is a query against base tables. The data that is queried from the table is NOT stored, only the query definition is stored. The data is retrieved from the base tables every time the view is queried. So, for any INSERT, UPDATE, or DELETE statement the operation is performed on the base tables. It is meaningful to grant INSERT, UPDATE, or DELETE privileges to a view only if that view is updatable. An updatable view is one that generally does not involve joins or aggregate functions. For a more thorough explanation of views see the InterBase Data Definition Guide's chapter Working with Views. While it is possible to grant INSERT, UPDATE, or DELETE privileges to a read-only view without receiving an error, any actual write operation attempted through the view will fail because it is read-only. SELECT privileges, however, can be granted to a read-only view to control which users have read access on the view. Granting privileges to execute a Stored Procedure Views can also be used to control access to base table data. If a user is only required to have access to a subset of rows for a table a view can be defined to query that subset of rows from the base table. The user can then be given privileges only on the view, not the base table. This allows the user privileges to the subset of rows that are required, but no privileges to the other, rows in the table. For example, imagine a table called TEST_SCORES that contains the columns, LASTNAME, FIRSTNAME, TESTNAME, SCORE. This table contains information that can be viewed by anyone, LASTNAME, FIRSTNAME and TESTNAME. This information is made available so that everyone can query to see who has taken which test. There is also information that should not be made available to all users, SCORE. There is a need to allow everyone access to a subset of the columns in the TEST_SCORES table, while only allowing access to the SCORE column to an exclusive set of users. This can be accomplished by creating a view that queries the rows available to all users and only granting everyone privileges to the view and not the base table: CREATE VIEW TESTTAKERS AS SELECT LASTNAME, FIRSTNAME, TESTNAME FROM TEST_SCORES; GRANT SELECT ON TESTTAKERS TO PUBLIC; GRANT ALL ON TEST_SCORES TO INSTRUCTOR; Examples using Grant Statement This example grants ALL privileges for the table TEST_SCORES to the user JJOHNSON. The ALL privilege grants JJOHNSON the right to insert, update, select, delete from this table: GRANT ALL ON TEST_SCORES TO JJOHNSON; This example will grant the ALL privilege to the special user PUBLIC. The PUBLIC user will grant all existing and future users ALL privileges on table TEST_SCORES: GRANT ALL ON TEST_SCORES TO PUBLIC; The following example will grant ALL privileges to a stored procedure. This will give the procedure rights to access the TEST_SCORES base table. For users to be able to use the stored procedure they must be granted EXECUTE privileges to the stored procedure (see later example). GRANT ALL ON TEST_SCORES TO PROCEDURE GET_PASSING_SCORES; This example will grant EXECUTE privileges to the special user PUBLIC. This will allow all current and future users to execute this procedure. The procedure must be given the privileges to access any base tables that it requires. GRANT EXECUTE ON PROCEDURE GET_PASSING_SCORES TO PUBLIC; This example will grant INSERT privileges on table TEST_SCORES to the user JJOHNSON. JJOHNSON will be able to add new rows to the table, but will not be able to query the table. GRANT INSERT ON TEST_SCORES TO JJOHNSON; This example will grant UPDATE privileges on a subset of columns to the user JJOHNSON. JJOHNSON will be able to update only those rows explicitly listed in the grant statement. GRANT UPDATE (FIRST_NAME, LAST_NAME) ON TEST_SCORES TO JJOHNSON; Granting Right to Grant to Others, Initially, only the SYSDBA and owner can grant privileges to other users. The grant statement has a clause that allows the SYSDBA or owner to grant the right for a user to grant privileges to other users. The WITH GRANT OPTION is used to allow other users the right to grant privileges to other users. Because the grant statement works on individual tables, the WITH GRANT OPTION can only be used on one table at a time. A grant statement with the WITH GRANT OPTION clause will have to be used for every table that the SYSDBA or owner wish to allow others to grant privileges for. Users who have be given the right to grant privileges to other users can only grant the privileges that they have been granted themselves. For instance, a user who has been granted select privilege can not grant the update privilege to other uses. That user can only grant the select privilege to other users. The following example grant's select privilege on table employee to the user BJONES and allows BJONES to grant the select privilege for table employee to other users: Grant SELECT on employee to BJONES WITH GRANT OPTION; Once a user has been given the right to grant privileges, that user can give the right to grant privileges to other users. This can cause a security breach unless controlled. Every user that has been given the right to grant privileges can also pass that right on to other users. There is no way to give a user the right to grant privileges to others, but not be able to pass on that right. Because of this fact, caution should be used when using the WITH GRANT OPTION clause. REVOKE Statement While the GRANT statement allows privileges to be given to a user, the REVOKE statement allows privileges to be removed. Only privileges that have been previously granted can be revoked. The basic syntax of the revoke statement is of the following form: Revoke SQL Security is inefficient The SQL security mechanism will get the job done. It is, however, inefficient for database administrators to setup and manage. The SQL security mechanism works on individual users. Outside of the PUBLIC user, there is no way to assign privileges at a group level. Additionally, SQL security works on a table by table basis. Privileges must be assigned for each table in the database. For example, assume a database administrator is trying to setup SQL security for 100 users on a database with 100 tables in it. With SQL security the database administrator will have to execute 10000 grant statements to setup the security for the users on this database. For each user there is one grant statement per table. This comes out to 100 grant statements per user. For 100 users that totals the 10000 grant statements required to setup SQL security. For each table you can grant privileges to a list of users, but this approach leads to many errors. The database administrator has to partition the users among the grant statements and ensure that each user is granted privileges for each table. In most cases, database administrators usually stick to one user per grant statement. SQL Security makes it hard to manage users The SQL security mechanism does not make it easy for the database administrator (DBA) to maintain an existing database either. There is no provision to help the DBA add new users to an existing database. Likewise, there are no provisions to help the DBA modify existing user, privileges for a set of users. For each new user that must be added to an existing system the DBA must reuse the same grant statements that were executed for all other users. This must be done for each table in the database. This administration operation is very repetitive. SQL security has no provision for simplifying the operation. Every time a new user is added the DBA has to go through the same routine to assign privileges to the new user. The lack of group security means that for any little change to security, privileges for each individual user must be changed to reflect the new privileges. This requires revoking the privileges that are no longer available to users and granting the new privileges to the same users. SQL Security is not flexible SQL security does not allow for flexibility of user privileges. It is an all or nothing mechanism. Either you have privileges or you don't. SQL security doesn't have the flexibility to allow changing user privileges without the overhead of managing each individual user change, via a grant or revoke statement. Also, there is no way to assign a set of privileges to a user without assigning them one table at a time.

What are SQL Roles?

InterBase 5.0 introduced an extension to SQL security called SQL Roles. SQL Roles implement the concept of group level security. Roles also act as templates for predefined sets of privileges. SQL Roles are an extension to the standard SQL security mechanism implemented in InterBase. A Role defines a set of SQL privileges on one or more tables in a database. Roles work with normal SQL security, but add the benefit of group level security. For example, a programmer must assume many roles during the life cycle of a product. Initially, the programmer must assume the role of a marketing person to define what the product is and what its capabilities are. Next, the programmer must assume the role of the developer while engineering the product. Finally, the programmer must assume the role of the end user and QA the product to assure its quality. SQL Roles do just this for database security. A role acts as a group template for users. In the above example, the programmer can be one user or many users. Roles also allow users to assume different roles (different sets of privileges). The programmer in the example can take on different privileges as he is assuming a different role. The marketing role has a set of privileges defined for it, while the QA role has an entirely different set of privileges defined for it. Roles are an identity To reiterate, a role is like a template, or set of privileges. The role is first created, then it is granted privileges to database objects. Users are then granted the right to assume a role upon connection. When a connection is attempted the user can specify a role that the user is to assume when connected. When a user assumes a role, that user is given all the privileges that are assigned to that role., Roles are additive As stated earlier, roles are an extension of basic SQL security. Privileges assigned when assuming a role are added to the privileges that are granted explicitly to a user. For example, assume the role TEST_ADMIN has been granted the INSERT privilege on table TEST_SCORES. USERA has explicitly been granted the SELECT privilege, and has been granted the right to assume the role TEST_ADMIN. When USERA connects specifying role TEST_ADMIN, USERA will have the accumulated privileges: INSERT and SELECT on table TEST_SCORES. Which InterBase kits support Roles? SQL Roles are available with InterBase v5.0. If upgrading from a previous version of InterBase, you must backup your database and restore onto the server which is running InterBase v5.0. It is not enough to move your database to the v5.0 server, you must also backup and restore to enable the database to support SQL Roles. Here are some general role requirements and conditions: • A role is bound to the database that it is created in. • Role names must be unique with respect to other roles and usernames • Roles must be assumed at connection time. A user cannot switch roles without disconnecting and reconnecting to the database. • A database must be backed up and restored with a V5.0 server to use roles. The InterBase V5.0 server can access V4.x databases, but unless the database is restored with a V5.0 server roles cannot be used in that database.

Advantages of Roles

Roles have several advantages over the basic SQL security model. These advantages make it easier for a DBA to administer a database's security. As stated earlier, Roles provide much needed group level security for InterBase. This makes it easy for the DBA to add security for multiple users. Roles also provide a degree of flexibility that is not easy attainable with the basic SQL security model. Lets revisit the example of the DBA setting up security for 100 users on a database with 100 tables. With basic SQL security we concluded that the DBA would have to execute 10000 grant statements to fully setup security for these 100 users on all 100 tables in the database. Using roles, the best case scenario would only require 200 grant statements to be executed. The best case would be where every user requires the same privileges on all tables. In this scenario the DBA could setup one role, which would require 100 grant statements. To add the 100 users to the role would require an additional 100 grant statements, which equals the 200 grant statements stated above. The more general case would require 100x + 100 grant statements, where x equals the number of different roles that are required. For example, if 5 roles were required then a total of 600 grant statements would have to be executed by the DBA to setup security. Clearly, using Roles has made setting up security for this database much easier for the DBA., Roles make it easier to manage user Roles also make database security easier to maintain for an existing database. Since roles extend basic SQL security there is no need to redesign the database's security to use roles. Roles will allow the DBA to keep the already defined privileges intact and build new privileges along side these. There are a couple key areas where Roles really make the DBA's job easier when it comes to maintaining security for an existing database. One, roles allow a new user's privileges to be easily added to the database. Second, Roles implement group level security, which makes the job of modifying existing privileges for a large number of users manageable. With basic SQL security adding a new user's privileges to a database requires the DBA to grant privileges to the user for each table in the database. This is a very repetitive task, because user privileges fall into categories and the same set of privileges are defined for several users. Roles can act as templates when setting up privileges for new users. A role can be defined in the database for a common set of privileges. When a new user is added the DBA must only grant the new user privileges to assume the role that was defined for the user's required set of privileges. One common method DBA's use to work around the basic SQL security model's lack of groups is to grant privileges to one generic user and allow all users to connect with this same username. This makes setting up and maintaining privileges easy to manage. The major drawback to this method is that when connected, every user has the same username. There is no way to differentiate between the users, because they are all using the same username. To InterBase, they are all effectively the same user, because the username is the only way to differentiate users. Using roles as templates alleviates this issue. The DBA still only has to setup privileges once, this time for the role instead of the generic user. When users connect they still provide their unique username, but also add the role that they are assuming. This allows InterBase to maintain security privileges, but still differentiate between the individual users. There are only two differences between using roles and using the generic user. One, users must specify the role when connecting. Two, the DBA must grant to all users the right to assume the role. The second major advantage with using roles is the management of user groups. Roles are defined around the concept of group level security. Roles allow DBAs to manage the privileges for entire groups of users, not one user at a time like the basic SQL security model. Roles allow the DBA to modify privileges at a group level instead of an individual user level. When the DBA modifies the privileges of a role, the effective privileges of all users is modified as well. The privileges are modified for the entire group, everyone who is granted privileges to the role. For example, suppose a role FULL_ACCESS has been defined with the ALL privilege on table TEST_SCORES. SQL create table test_scores (i1 integer); SQL create role FULL_ACCESS; SQL grant all on test_scores to full_access; SQL show grant test_scores; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TEST_SCORES TO FULL_ACCESS Users TEST and TEST2 have been granted the privilege to assume that role SQL grant FULL_ACCESS to TEST; SQL grant FULL_ACCESS to TEST2;, Users TEST and TEST2 now have the ALL privilege for table TEST_SCORES when they connect assuming the FULL_ACCESS role. If TEST or TEST2 tries to connect to the database without specifying the FULL_ACCESS role they will not have privileges on table TEST_SCORES. SQL connect \temp\employee.gdb user test password test; Database: \temp\employee.gdb, User: test SQL select * from test_scores; Statement failed, SQLCODE = -551 no permission for read/select access to table TEST_SCORES SQL connect \temp\employee.gdb user test password test role full_access; Database: \temp\employee.gdb, User: test, Role: full_access SQL select * from test_scores; Now, for the DBA to modify the privileges for both TEST and TEST2 all that is required is to modify the role FULL_ACCESS. In this example the INSERT privilege is taken away from the role. TEST and TEST2 can assume the role and have the UPDATE, DELETE, SELECT, and, REFERENCES privileges on table TEST_SCORES. SQL show grant test_scores; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TEST_SCORES TO FULL_ACCESS SQL revoke insert on test_scores from full_access; SQL show grant test_scores; GRANT DELETE, SELECT, UPDATE, REFERENCES ON TEST_SCORES TO FULL_ACCESS SQL connect \temp\employee.gdb user test password test role full_access; Database: \temp\employee.gdb, User: test, Role: full_access SQL select * from test_scores; SQL insert into test_scores values (96); Statement failed, SQLCODE = -551 no permission for insert/write access to table TEST_SCORES The select, while not returning any rows, was successful. The insert, however, received the no permission error message. While this example demonstrates a group with only 2 users, the same example can easily be extended to incorporate hundreds of users. Roles are flexible Roles also add much needed flexibility to the basic SQL security model. To reiterate, the SQL model doesn't have the flexibility to allow changing user privileges without the overhead of managing each individual user change, via a grant or revoke statement. Roles allow a user to change the privileges assigned by assuming a different role when connecting to the database. This allows for a secure database, while still having the flexibility to accommodate a users changing needs. Revisiting the product life cycle example presented earlier will demonstrate how roles add the flexibility required for this scenario. For each role the programmer assumes, there are a predefined set of privileges that are assigned. When the programmer is assuming the marketing role, he should not have any privileges that are granted to a developer. Likewise, when the programmer is working as the developer role, he should not have any of the privileges granted to the QA role. The point is each role has its own predefined set of privileges, and the programmer when assuming that role should only have those privileges granted to that role., Moving this example into the database realm, there should be three roles defined on the database; Marketing, development, and QA. Each role will have its own set of privileges to the tables in the database. When a user connects to the database assuming one of the roles, that user will only have the privileges granted to that role. Roles also provide the flexibility, because they work in conjunction with the basic SQL security model. As explained earlier, the total privileges for a user are cumulative: Total Privileges = Privileges explicitly granted to user + Privileges granted to role being assumed Continuing the product life cycle example, assume user BJONES has been granted privileges to assume the QA role. Each month BJONES is responsible for accumulating a count of all the developer source code changes that have occurred. Since BJONES has only been granted privileges to assume the QA role, he cannot access the tables that are used during development. The DBA does not want to give BJONES the privileges to assume the developer role, because the privileges BJONES requires are a subset of the total privileges that have been granted to the developer role. The DBA can explicitly grant privileges on the subset of tables directly to BJONES. When BJONES connects assuming the QA role he will have privileges to access the subset of development tables as well as the tables for QA, because of the conjunctive nature of roles with the basic SQL security model. BJONES has privileges granted to him through the role, plus all privileges explicitly granted to him by the DBA.

Using Roles

Creating a Role To create a role you must use the CREATE ROLE statement. By default a role has no privileges when it is created. The role must be granted privileges to database objects before it can access them. Any user can create a role, since creating a role assigns no privileges. Also, since roles are defined and stored in a database, you must be connected to a database before you can define a role. Here is an example of creating a role: CREATE ROLE FULL_ACCESS; Granting Privileges to a Role When a role is created it has no privileges to any objects in the database. This follows the SQL security model of providing no access unless explicitly granted. To assign privileges to a role you must use the grant statement. The basic syntax for granting privileges to a role is: GRANT Where and How Roles are Supported For roles to be of value, client tools must be able to specify a role upon database connection. The first step is for InterBase to surface the ability to use roles while connecting to databases. The second step is for development tools to use one of the methods that InterBase surfaced to implement roles in the client application. It is a two step process for end users to use roles with their database applications. InterBase provides three interfaces to roles InterBase allows client applications and development tools to use SQL Roles. InterBase surfaces roles via the native API, embedded SQL, and the interactive query tools. All tools that are built using one of these access methods will be able to support roles., The InterBase native API is a set of functions that allow developers to programmatically construct and send SQL statements to the InterBase engine and receive results back. All database work can be performed through calls to the API. The InterBase API provides the most flexibility and power of any access method. This flexibility and power comes at the expense of using a lower level interface. The API relies on the developer to manage more of the burden when communicating with the InterBase engine. The developer is required to allocate and populate underlying data structures, which are hidden in the other higher-level interfaces. The following example will use InterBase's native API to attach to a database. The role full_access will be used for the connection so that user bjones will have the privileges that have been granted to the role. File: sampleapi.c #include
15

Similar documents

SAP Excellence
SAP Excellence Series Editors: Professor Dr. Dr. h.c. mult. Peter Mertens Universität Erlangen-Nürnberg Dr. Peter Zencke SAP AG, Walldorf Jörg Thomas Dickersbach Characteristic Based Planning with mySAP SCM™ Scenarios, Processes, and Functions With contributions by A. Forstreuter, C. Fuhlbrügge and
SYSTEM SHOCK 2® WARNING: TO OWNERS OF PROJECTION TELEVISIONS
sysshock man -eng 02/05/2000 9:11 Page 1 SYSTEM SHOCK 2® WARNING: TO OWNERS OF PROJECTION TELEVISIONS STILL PICTURES OR IMAGES MAY CAUSE PERMANENT PICTURE-TUBE DAMAGE OR MARK THE PHOSPHOR OF THE CRT. AVOID REPEATED OR EXTENDED USE OF VIDEO GAMES ON LARGE- SCREEN PROJECTION TELEVISIONS. EPILEPSY WARN
STEERING SYSTEM SECTIONST
STEERING SYSTEM SECTIONST CONTENTS PRECAUTIONS ...2 TILT MECHANISM ...15 Supplemental Restraint System (SRS) ″AIR POWER STEERING GEAR AND LINKAGE ...16 BAG″ and ″SEAT BELT PRE-TENSIONER″...2 Components...16 Precautions for Steering System...2 Removal and Installation ...17 PREPARATION ...3 Disassemb
STAFF KATAS
STAFF KATAS CHUNG GI Begin with bow Step to left and down block Bring up down block arm Step forward with ready striking arm front smash fo staff
g GETTING STARTEDDPCSystem Requirements
g GETTING STARTEDDPCSystem Requirements Computer: Pentium 90 MHz processor or equivalent. Operating Systems: Windows 2000, Windows XP, or Windows Vista. Memory: 16 MB of RAM Controls: A keyboard and mouse are required. Joysticks, game pads, graphic tablets, and input devices other than the mouse and
300ItIlluminatil Mensaez g wr ep Orz gg
S2 Map.qxp 3/3/97 11:33 AM Page1agad300ItIlluminatil Mensaez g wr ep Orz gg etabth etbbdzLalande epep a Bootis zaad250 et g Vulpeculaed g bSaurusdgbbaagaProcyon Olber Rigel Mira 200 Zeeman a bd g ep Wolf g Vela bbbzaaRaynet a Luyten et b Sirius a 150 VolantisbgCanopus IndiSolbaMizardaPk gu Lyrae Reg
EPILEPSY WARNING
EPILEPSY WARNING Please read before using this video game system or allowing your children to use it. Some people are susceptible to epileptic seizures or loss of consciousness when exposed to certain flashing lights or light patterns in every day life. Such people may have a seizure while watching
Designer COMING SOON! o( DEBRA B AILEY )o Assistant Editor
Designer COMING SOON! o( DEBRA B AILEY )o Here's more Star Wars reading that you won't want to miss! Assistant Editor STAR WARS: EMPIRE #2J by JEREMY BARLOW and BRANDON BADEAUX o( KATIE MOODY )o Cover by KILIAN PLUNKETT It was supposed to be an easy gig ... transport a young woman across the desert
SEPTEMBER 1 - OCTOBER 19, 1973 EUROPEAN TOUR HOME
SEPTEMBER 1 - OCTOBER 19, 1973 EUROPEAN TOUR HOME September 1 - October 19, 1973 EUROPEAN TOUR VENUE: NOTES: Austria, West Germany, UK, Switzerland, Denmark, Opening acts on this tour were: Billy Preston, who also Sweden, Holland and Belgium backed the Stones on piano during their set; and Kracker,
STK433-130-E 2-channel class AB audio power IC,
Thick-Film Hybrid IC STK433-130-E 2-channel class AB audio power IC, 150W+150W Overview The STK433-130-E is a hybrid IC designed to be used in 150W × 2ch class AB audio power amplifiers. Applications • Audio power amplifiers. Features • Pin-to-pin compatible outputs ranging from 80W to 150W. • Can b
STLport License Agreement
STLport License Agreement Boris Fomitchev grants Licensee a non-exclusive, non-transferable, royalty-free license to use STLport and its documentation without fee. By downloading, using, or copying STLport or any portion thereof, Licensee agrees to abide by the intellectual property laws and all oth
of S. S. Stewart’s Banjo and Guitar Journal
of S. S. Stewart’s Banjo and Guitar Journal Vol. III, No.10, August, 1886 Pages 11-12 lacking in Sibley’s original copy
SECTION STC STEERING CONTROL SYSTEM
STEERINGABSECTION STCCDESTEERING CONTROL SYSTEM CONTENTS F TCHIJKLMNOPBASIC INSPECTION ... 2 ECU DIAGNOSIS ...13 DIAGNOSIS AND REPAIR WORK FLOW ... 2 POWER STEERING CONTROL UNIT ...13 Work Flow ...2 Reference Value ...13 S Wiring Diagram - ELECTRONICALLY CON- FUNCTION DIAGNOSIS ... 3 TROLLED POWER S
Dear Customer, Thank you for selecting Fiat and congratulations on your choice of a Fiat Stilo.
Dear Customer, Thank you for selecting Fiat and congratulations on your choice of a Fiat Stilo. We have written this handbook to help you get to know all your new Fiat Stilo features and use it in the best possible way. You should read it right through before taking the road for the first time. You
U.S. $16.99 Can. $23.95 U.K. £12.99 Games/Strategy/RPG ® Platform: Xbox® PRIMAOFFICIAL GAME GUIDE Visit us online at primagames.com DavidSJHodgson
U.S. $16.99 Can. $23.95 U.K. £12.99 Games/Strategy/RPG ® Platform: Xbox® PRIMAOFFICIAL GAME GUIDE Visit us online at primagames.com LucasArts and the LucasArts logo are registered trademarks of This game has received the Lucasfilm Ltd. © 2004 Lucasfilm Entertainment Company Ltd. or following rating
STREAMING MAXIMUM-MINIMUM FILTER USING NO MORE THAN THREE COMPARISONS PER ELEMENT
STREAMING MAXIMUM-MINIMUM FILTER USING NO MORE THAN THREE COMPARISONS PER ELEMENT Daniel Lemire University of Quebec at Montreal (UQAM), UER ST 100 Sherbrooke West, Montreal (Quebec), H2X 3P2 Canada email is hidden Abstract. The running maximum-minimum (-) filter computes the maxima and minima over
StoreJet ToolBox ユーザーガイド
StoreJet ToolBox ユーザーガイド (v1.0) 目次 ハードウェア要件 ... 3 対応 OS ... 3 Low Level Formatとは ... 3 StoreJet Format Toolについて ... 3 Low Level Formatの使い方 ... 4 StoreJet Format Toolの使い方 ... 8 ハードウェア要件 1. トランセンドの StoreJet外付けハードドライブ 2. USBポート搭載のデスクトップ/ノートブック PC 対応 OS StoreJet ToolBoxは以下の OSで利用できます。 Windows XP Windows
StoreJet ToolBox User Guide
StoreJet ToolBox User Guide (v1.0) Contents Hardware Requirements... 3 Supported Operating Systems ... 3 What is Low Level Format... 3 What is StoreJet Format Tool ... 3 How to use Low Level Format ... 4 1. Click “Scan Device” button and search StoreJet Drive... 4 2. Select a device that you want to
Qucs Test Report SPICE to Qucs conversion: Test File 1 Mike Brinson
Qucs Test Report SPICE to Qucs conversion: Test File 1 Mike Brinson Copyright ©c 2007 Mike Brinson Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.1 or any later version published by the Free Software Foundation.
SETUP GUIDE
SETUP GUIDE SETUP GUIDE Model PP39L Notes, Cautions, and Warnings NOTE: A NOTE indicates important information that helps you make better use of your computer. CAUTION: A CAUTION indicates either potential damage to hardware or loss of data and tells you how to avoid the problem. WARNING: A WARNING
Mp3tag File Overview
Mp3tag File Overview 01/08/2008 Title: Bye Bye Blackbird Artist: Stephane Grappelli & Toots Thielemans Album: Bringing It Together Year: 2008 Track: 01 Genre: Jazz Comment: By CheRrom'08 Title: Just The Two Of Us Artist: Stephane Grappelli & Toots Thielemans Album: Bringing It Together Year: 2008 Tr
Red Hat Certificate of Expertise in Containerized Application Development
Study Guide Red Hat Certificate of Expertise in Containerized Application Development Contents Prerequisites 3 Linux 3 Installation 3 Docker Refresh 3 What is Docker? 3 Basic Docker Commands 3 Containers4ASample Command 4 Running Containers Locally 4 Linking Containers 4 Container Logs 4 Docker Even
FRONT & REAR SUSPENSION SECTIONSU
FRONT & REAR SUSPENSION SECTIONSU CONTENTS FRONT SUSPENSION ...2 WHEELARCH HEIGHT (UNLADEN*1) ...15 Precautions ...2 WHEEL RUNOUT ...15 PRECAUTIONS ...2 WHEEL BALANCE...15 Preparation ...2 REAR SUSPENSION...16 SPECIAL SERVICE TOOLS ...2 Precautions ...16 COMMERCIAL SERVICE TOOLS...2 PRECAUTIONS ...1
2010 SP 5.0 SPR # Description Product
2010 SP 5.0 SPR # Description Product 221873 Cannot drag the endpoint of this underdefined sketch SolidWorks line 322870 Centerline in drawing does not update to new position SolidWorks when feature is modified. 352787 ModelDoc2::SketchModifyFlip does not flip sketch SolidWorks API with external poi
LIMITED WARRANTY
LIMITED WARRANTY THE LICENSOR expressly disclaims any warranty for the Program, Editor, and Manual(s). The Program, Editor and Manual(s) are provided "as is" without warranty of any kind, either express or implied, including, without limitation, the implied warranties of merchantability, fitness for
CAD 製図基準テンプレート ユーザマニュアル
CAD 製図基準テンプレート ユーザマニュアル 目次 1 はじめに ... 1 2 SXF テンプレート ファイルの概要 ... 1 (1) テンプレートファイル(*.dwt) ... 1 (2) 画層テンプレート(*.dwg) ... 3 3 AUTOCAD の設定 ... 5 1 テンプレートファイルの読み込み ... 5 2 画層情報の取り込み ... 6 3 図面表題欄情報の入力 ... 9 4 尺度付きテンプレート ファイルの使用 ... 11 1 オブジェクトの作図 ... 11 2 文字の入力 ... 12 3 寸法の作図 ... 14 5 カスタムテンプレート ファイルの使用
Epilepsy warning
DVDPAGE_SYBERIA_UK.qxd 8/12/04 10:13 Page 1 Epilepsy warning Please read before using this game or allowing your children to use it. When subject to certain types of light effects, some people are prone to epilepsy attacks, leading to loss of consciousness. This may be due to images flashing up in q
Autodesk SOFTWARE LICENSE AGREEMENT
Autodesk SOFTWARE LICENSE AGREEMENT SUOMI READ CAREFULLY: AUTODESK, INC. (“AUTODESK”) LICENSES THIS SOFTWARE TO YOU ONLY UPON THE CONDITION THAT YOU ACCEPT ALL OF THE TERMS CONTAINED IN THIS SOFTWARE LICENSE AGREEMENT (“AGREEMENT”). BY SELECTING THE “I ACCEPT” BUTTON AT THE END OF THIS AGREEMENT OR
1 SYNDICATE CONTENTS
SYNDICATE CONTENTS Introduction ...3 Game Overview ...4 Object Of The Game ...5 Quickstart...6 Main Menu Options ...14 F1. Configure Company ...14 F2. Begin Mission ...14 F3. Load And Save Game...14 F4. Restart Game...14 F5. Quit To DOS...14 CHAPTER 1: CONFIGURING YOUR COMPANY ...15 Select Detail To
1 SYNDICATE CONTENTS
SYNDICATE CONTENTS Introduction ...3 Game Overview ...4 Object Of The Game ...5 Quickstart...6 Main Menu Options ...14 F1. Configure Company ...14 F2. Begin Mission ...14 F3. Load And Save Game...14 F4. Restart Game...14 F5. Quit To DOS...14 CHAPTER 1: CONFIGURING YOUR COMPANY ...15 Select Detail To