12. Users and Access Rights
For many network and database administrators, security has become an almost overwhelming concern. Relational DBMSs have always had some measure of security separate from that provided by networks. In this chapter we will look at some examples of managing database user accounts as well as SQL's support for granting and revoking access rights.
Managing User Accounts
Many multiuser DBMSs maintain user names and passwords that are distinct from any authentication that may be imposed by a network. A user must supply the DBMS's authentication information before being allowed to connect to the database.
Most DBMSs are shipped with only one or two authorized users (often DBA, SYSTEM, and/or ADMIN) that have access to the entire database. All other users must be created by one of these accounts or another account that has been given the appropriate rights.
Although the specific syntax for creating and maintaining user names and passwords is not a part of the SQL standard and therefore implementation dependent, the syntax used by many products is very similar.
Oracle and the two major open source DBMSs (mySQL and Postgres) use some type of CREATE USER syntax. mySQL has the simplest version:
Oracle's version uses the following pattern:
CREATE USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
QUOTA storage_space_allocation
The DEFAULT TABLESPACE and QUOTA clauses set the area of the database the user will use for temporary storage and the amount of temporary storage the user can fill.
Postgres varies the syntax slightly:
CREATE USER user_name
PASSWORD ‘password’
Postgres also supports clauses to allow/disallow the creation of databases and the creation of other users.
SQL Server uses yet another similar syntax:
CREATE LOGIN user_name
WITH PASSWORD = ‘password’
In contrast, DB2 does not provide its own user names and passwords. Instead, it uses a person's account with the operating system. In other words, once a user is authenticated by the operating system, DB2 requires no further account authorization. Access rights to database elements are therefore linked to OS accounts rather than to special DB2 accounts. 1
1For more information on DB2 security, see http://www.databasesecurity.com/db2/db2cert2v8-a4.pdf.
Having a user ID does not necessarily give a user the right to access the database. Although the details are implementation dependent, you typically will find that the DBMS has extended the GRANT command—which we will discuss shortly—to support user-level access. For example,
GRANT CONNECT TO user_id
grants the user the right to connect to the database. Connect rights, however, do not give the user the right to create database elements or access existing database elements. The right to create database elements usually must be granted by someone with DBA rights, using a syntax similar to
GRANT RESOURCE TO user_id
Rights to database elements such as tables and views are given using the SQL GRANT command (discussed in the next section of this chapter).
DBA rights permit a user to grant connect and resource rights to others, to create accounts, and access all database elements. Any user ID with DBA rights also can assign them to another user ID:
GRANT DBA TO user_name
Because DBA rights have such broad access, in most cases they will be restricted to only one or two user IDs.
Granting and Revoking Access Rights
When you create an element of database structure, the user name under which you are working becomes that element's owner. The owner has the right to do anything to that element; all other users have no rights at all. This means that if tables and views are going to accessible to other users, you must grant them access rights. 2
2Some major DBMSs (for example, Oracle and DB2) also provide support for multilevel security (MLS). An MLS scheme classifies data into levels, such as top secret, secret, classified, and unclassified. Users are then given clearance levels. A user can view data at or below his or her clearance level and cannot change a classification level to anything less than the data's current level. MLS is used in many government databases and to satisfy government regulations surrounding data access.
Types of Access Rights
There are six types of access rights that you can grant:
◊ SELECT: Allows a user to retrieve data from a table or view.
◊ INSERT: Allows a user to insert new rows into a table or updatable view. Permission may be granted to specific columns rather than the entire database element.
◊ UPDATE: Allows a user to modify rows in a table or updatable view. Permission may be granted to specific columns rather than the entire database element.
◊ DELETE: Allows a user to delete rows from a table or updatable view.
◊ REFERENCES: Allows a user to reference a table as a foreign key in a table he or she creates. Permission may be granted to specific columns rather than the entire table.
◊ EXECUTE: Allows the user to execute stored procedures. (You will read about stored procedures in Chapter 14.)
◊ ALL PRIVILEGES: Gives a user all of the preceding rights to a table or view.
By default, granting access rights to another user does not give that user the ability to pass on those rights to others. If, however, you add a WITH GRANT OPTION clause, you give the user the ability to grant the right that he or she has to another user.
Storing Access Rights
Access rights to tables and views are stored in the data dictionary. Although the details of the data dictionary tables vary from one DBMS to another, you will usually find access rights split between two system tables named something like SYSTABLEPERM and SYSCOLPERM. 3
The first table is used when access rights are granted to entire tables or views; the second is used when rights are granted to specific columns within a table or view.
A SYSTABLEPERM table has a structure similar to the following:
Systableperm (table_id, grantee, grantor, selectauth, insertauth, deleteauth, updateauth, updatecols, referenceauth)
The columns represent
◊ TABLE_ID: An identifier for the table or view.
◊ GRANTEE: The user ID to which rights have been granted.
◊ GRANTOR: The user ID granting the rights.
◊ SELECTAUTH: The grantee's SELECT rights.
◊ INSERTAUTH: The grantee's INSERT rights.
◊ DELETEAUTH: The grantee's DELETE rights.
◊ UPDATEAUTH: The grantee's UPDATE rights.
◊ REFERENCEAUTH: The grantee's REFERENCE rights.
The columns that hold the access rights take one of three values: Y (yes), N (no), or G (yes with grant option).
Whenever a user makes a request to the DBMS to manipulate data, the DBMS first consults the data dictionary to determine whether the user has the rights to perform the requested action. (SQL-based DBMSs are therefore said to be data dictionary driven.) If the DBMS cannot find a row with a matching user ID and table identifier, then the user has no rights at all to the table or view. If a row with a matching user ID and table identifier exists, then the DBMS checks for the specific rights that the user has to the table or view and—based on the presence of Y, N, or G in the appropriate column—either permits or disallows the requested database access.
Granting Rights
To grant rights to another user, a user who either created the database element (and therefore has all rights to it) or who has GRANT rights issues a GRANT statement:
GRANT type_of_rights
ON table_or_view_name TO user_ID
For example, if the DBA of the rare book store wanted to allow the accounting manager (who has a user ID of acctg_mgr) to access the sales_summary view, the DBA would type:
GRANT SELECT
ON sales_summary TO acctg_mgr;
To allow the accounting manager to pass those rights on to other users, the DBMS would need to add one line to the SQL:
If the DBA wanted to give some student interns limited rights to some of the base tables, the GRANT might be written
GRANT SELECT, UPDATE (selling_price, sale_date)
ON volume TO intern1, intern2, intern3;
The preceding example grants SELECT rights to the entire table but gives UPDATE rights only on two specific columns. Notice also that you can grant multiple rights in the same command as well as give the same group of rights to more than one user. However, a single GRANT statement applies to only one table or view.
In most cases, rights are granted to specific user IDs. You can, however, make database elements accessible to anyone by granting rights to the special user ID PUBLIC. For example, the following statement gives every authorized user the rights to see the sales_summary view:
GRANT SELECT
ON sales_summary TO PUBLIC;
Revoking Rights
To remove previously granted rights, you use the REVOKE statement, whose syntax is almost opposite to that of GRANT:
REVOKE access_rights
ON table_or_view_name FROM user_ID
For example, if the rare book store's summer interns have finished their work for the year, the DBA might want to remove their access from the database:
REVOKE SELECT, UPDATE (selling_price, sale_date)
ON volume FROM intern1, intern2, intern3;
If the user from which you are revoking rights has the GRANT option for those rights, then you also need to make a decision about what to do if the user has passed on those rights. In the following case, the REVOKE will be disallowed if the accounting manager has passed on his or her rights:
REVOKE SELECT
ON sales_summary FROM acctg_mgr
RESTRICT;
In contrast, the syntax
REVOKE SELECT
ON sales_summary FROM acctg_mgr
CASCADE;
will remove the rights from the acctg_mgr ID along with any user IDs to which the acctg_mgr granted rights.
Note: Some DBMSs also support a DENY command, which explicitly prohibits a user from performing a given action. It is not a part of the SQL standard, however,
Roles
As the number of people working with a database grows, it becomes difficult to keep track of which rights have been assigned to each individual user. SQL therefore lets you group rights together and assign them as a unit called a role.
You create a role with the CREATE ROLE statement:
CREATE ROLE role_name
The DBA at the rare book store, for example, might create a role for the summer interns:
CREATE ROLE interns;
Then the DBA assigns rights to the role:
GRANT SELECT, UPDATE (selling_price, sale_date) ON volume TO interns;
Finally, the role is then assigned to the users that should have the rights that are grouped into the role:
GRANT interns TO intern1, intern2, intern3;
To revoke privileges that are part of a role, use
REMOVE role_name FROM user_ID
as in
REVOKE interns FROM intern1, intern2, intern;
A role is removed from the database with
DROP ROLE role_name
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.