The Management Process

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 18.  Managing Database Users


A stable user management system is mandatory for data security in any database system. The user management system starts with the new user's immediate supervisor, who should initiate the access request, and then go through the company's approval authorities, at which time the request, if accepted by management, is routed to the security officer or database administrator, who takes action. A good notification process is necessary; the supervisor and the user must be notified that the user account has been created and that access to the database has been granted. The user account password should only be given to the user , who should immediately change the password upon initial login to the database.

graphics/note_icon.gif

You must check your particular implementation for the creation of users. Also refer to company policies and procedures when creating and managing users. The following section compares the user creation processes in Oracle, Sybase, and Microsoft SQL Server.


Creating Users

The creation of database users involves the use of SQL commands within the database. There is no one standard command for creating database users in SQL; each implementation has a method for doing so. Some implementations have similar commands, while others vary in syntax. The basic concept is the same, regardless of the implementation. There are several GUI, Graphical User Interface, tools on the market that can be used for user management.

When the database administrator or assigned security officer receives a user account request, the request should be analyzed for the necessary information. The information should include your particular company's requirements for establishing a user ID.

Some items that should be included are Social Security number, full name, address, phone number, office or department name , assigned database, and sometimes, a suggested user ID.

Syntactical examples of creating users compared between two different implementations are shown in the following sections.

Creating Users in Oracle

Steps for creating a user account in an Oracle database:

  1. Create the database user account with default settings.

  2. Grant appropriate privileges to the user account.

The following is the syntax for creating a user:

 graphics/syntax_icon.gif CREATE USER  USER_ID  IDENTIFIED BY [  PASSWORD  EXTERNALLY  ] [ DEFAULT TABLESPACE  TABLESPACE_NAME  ] [ TEMPORARY TABLESPACE  TABLESPACE_NAME  ] [ QUOTA (INTEGER (K  M)  UNLIMITED) ON  TABLESPACE_NAME  ] [ PROFILE PROFILE_TYPE ] [PASSWORD EXPIRE ACCOUNT [LOCK  UNLOCK] 
graphics/note_icon.gif

The previous syntax for creating users can be used to add a user to an Oracle database, as well as a few other major relational database implementations.

The CREATE USER command is not support by MySQL. Users can be managed using the mysqladmin tool. Once a local user account is set up on a Windows computer, a login is not required. However, a user should be set up for each user requiring access to the database in a multi-user environment using mysqladmin.


graphics/newterm_icon.gif

If you are not using Oracle, do not overly concern yourself with some of the options in this syntax. A tablespace is a logical area that houses database objects, such as tables and indexes, that is managed by the DBA. The DEFAULT TABLESPACE is the tablespace in which objects created by the particular user reside. The TEMPORARY TABLESPACE is the tablespace used for sort operations (table joins, ORDER BY, GROUP BY) from queries executed by the user. The QUOTA is the space limit placed on a particular tablespace to which the user has access. PROFILE is a particular database profile that has been assigned to the user.

The following is the syntax for granting privileges to the user account:

 graphics/syntax_icon.gif GRANT PRIV1 [ , PRIV2, ... ] TO USERNAME  ROLE [, USERNAME ] 

The GRANT statement can grant one or more privileges to one or more users in the same statement. The privilege(s) can also be granted to a role, which in turn can be granted to a user(s).

In MySQL, the GRANT command can be used to grant users on the local computer to the current database. For example:

 GRANT USAGE ON *.* TO USER@LOCALHOST IDENTIFIED BY 'PASSWORD'; 

Additional privileges can be granted to a user as follows :

 GRANT SELECT ON TABLENAME TO USER@LOCALHOST; 

For the most part, multi-user setup and access for MySQL is only required in multi-user environments.

Creating Users in Sybase and Microsoft SQL Server

The steps for creating a user account in a Sybase and Microsoft SQL Server database follow:

  1. Create the database user account for SQL Server and assign a password and a default database for the user.

  2. Add the user to the appropriate database(s).

  3. Grant appropriate privileges to the user account.

The following is the syntax for creating the user account:

 graphics/syntax_icon.gif SP_ADDLOGIN USER_ID ,PASSWORD [, DEFAULT_DATABASE ] 

The following is the syntax for adding the user to a database:

 SP_ADDUSER USER_ID [, NAME_IN_DB [, GRPNAME ] ] 

The following is the syntax for granting privileges to the user account:

 graphics/syntax_icon.gif GRANT PRIV1 [ , PRIV2, ... ] TO  USER_ID  
graphics/note_icon.gif

The discussion of privileges within a relational database is further elaborated on during Hour 19, "Managing Database Security."


CREATE SCHEMA

Schemas are created via the CREATE SCHEMA statement.

The following is the syntax:

 graphics/syntax_icon.gif CREATE SCHEMA [  SCHEMA_NAME  ] [  USER_ID  ]               [ DEFAULT CHARACTER SET  CHARACTER_SET  ]               [PATH  SCHEMA NAME  [,  SCHEMA NAME  ] ]               [ SCHEMA_ELEMENT_LIST ] 

The following is an example:

 CREATE SCHEMA USER1  CREATE TABLE TBL1   (COLUMN1    DATATYPE    [NOT NULL],    COLUMN2    DATATYPE    [NOT NULL]...) CREATE TABLE TBL2   (COLUMN1    DATATYPE    [NOT NULL],    COLUMN2    DATATYPE    [NOT NULL]...) GRANT SELECT ON TBL1 TO USER2 GRANT SELECT ON TBL2 TO USER2 [ OTHER DDL COMMANDS ... ] 

The following is the application of the CREATE SCHEMA command in one implementation:

 graphics/mysql_icon.gif graphics/input_icon.gif  CREATE SCHEMA AUTHORIZATION USER1   CREATE TABLE EMP   (ID      NUMBER          NOT NULL,   NAME    VARCHAR2(10)    NOT NULL)   CREATE TABLE CUST   (ID      NUMBER          NOT NULL,   NAME    VARCHAR2(10)    NOT NULL)   GRANT SELECT ON TBL1 TO USER2   GRANT SELECT ON TBL2 TO USER2;  graphics/output_icon.gif Schema created. 

The AUTHORIZATION keyword is added to the CREATE SCHEMA command. This example was performed in an Oracle database. This goes to show you, as you have also seen in this book's previous examples, that vendors ' syntax for commands often varies in their implementations.

graphics/note_icon.gif

Some implementations may not support the CREATE SCHEMA command.

However, schemas can be implicitly created when a user creates objects. The CREATE SCHEMA command is simply a single-step method of accomplishing this task. After objects have been created by a user, the user can grant privileges that allow access to the user's objects to other users.

The CREATE SCHEMA command is not support by MySQL.


Dropping a Schema

A schema can be removed from the database using the DROP SCHEMA statement. There are two options that must be considered when dropping a schema. First, the RESTRICT option. If RESTRICT is specified, an error occurs if objects currently exist in the schema. The second option is CASCADE. The CASCADE option must be used if any objects currently exist in the schema. Remember that when you drop a schema, you also drop all database objects associated with that schema.

The syntax is as follows:

 graphics/syntax_icon.gif DROP SCHEMA SCHEMA_NAME { RESTRICT  CASCADE } 
graphics/note_icon.gif

The absence of objects in a schema is possible because objects, such as tables, can be dropped using the DROP TABLE command. Some implementations may have a procedure or command that drops a user, which can also be used to drop a schema. If the DROP SCHEMA command is not available in your implementation, you can remove a schema by removing the user that owns the schema objects.


Altering Users

A very important part of managing users is the ability to alter a user's attributes after user creation. Life for the database administrator would be a lot simpler if personnel with user accounts were never promoted, never left the company, or if the addition of new employees was minimized. In the real world, high personnel turnover , as well as users' responsibilities, are a reality and a significant factor in user management. Nearly everyone changes jobs or job duties , therefore, user privileges in a database must be adjusted to fit a user's needs.

The following is one implementation's example of altering the current state of a user.

For Oracle:

 ALTER USER USER_ID [ IDENTIFIED BY  PASSWORD   EXTERNALLY  GLOBALLY AS 'CN=USER']  [ DEFAULT TABLESPACE  TABLESPACE_NAME  ] [ TEMPORARY TABLESPACE  TABLESPACE_NAME  ] [ QUOTA  INTEGER KM UNLIMITED ON  TABLESPACE_NAME  ] [ PROFILE  PROFILE_NAME  ] [ PASSWORD EXPIRE] [ ACCOUNT [LOCK UNLOCK]] [ DEFAULT ROLE  ROLE1  [,  ROLE2  ]  ALL [ EXCEPT  ROLE1  [,  ROLE2  NONE ] ] 

Many of the user's attributes can be altered in this syntax. Unfortunately, not all implementations provide a simple command that allows the manipulation of database users. Some implementations also provide GUI tools that allow users to be created, modified, and removed.

graphics/note_icon.gif

You must check your particular implementation for the correct syntax for altering users. Oracle's ALTER USER syntax is shown here. In most major implementations, there is a tool used to alter or change a user's roles, privileges, attributes, and password.


graphics/note_icon.gif

A user can change an established password. You must check your particular implementation for the exact syntax or tool used to reset a password. The ALTER USER command is typically used in Oracle.


User Sessions

A user database session is the time that begins at database login and ends when a user logs out. During the time a user is logged in to the database (a user session), the user can perform various actions that have been granted, such as queries and transactions.

An SQL session is initiated when a user connects from the client to the server using the CONNECT statement. Upon the establishment of the connection and the initiation of the session, any number of transactions can be started and performed until the connection is disconnected; at that time, the database user session terminates.

Users can explicitly connect and disconnect from the database, starting and terminating SQL sessions, using commands such as the following:

 CONNECT TO DEFAULT  STRING1  [ AS  STRING2  ] [ USER  STRING3  ]  DISCONNECT DEFAULT  CURRENT  ALL  STRING  SET CONNECTION DEFAULT  STRING  
graphics/note_icon.gif

Remember that the syntax varies between implementations. In addition, most database users do not manually issue the commands to connect or disconnect from the database. Most users access the database through a vendor-provided or third-party tool that prompts the user for a username and password, which in turn connects to the database and initiates a database user session.


User sessions can beand often aremonitored by the database administrator or other personnel having interest in user activities. A user session is associated with a particular user account when a user is monitored . A database user session is ultimately represented as a process on the host operating system.

Removing User Access

Removing a user from the database or disallowing a user's access can easily be accomplished through a couple of simple commands. Once again, however, variations among different implementations are numerous , so you must check your particular implementation for the syntax or tools used to accomplish user removal or access revocation.

Methods for removing user database access:

  • Change the user's password.

  • Drop the user account from the database.

  • Revoke appropriate previously granted privileges from the user.

The DROP command can be used in some implementations to drop a user from the database:

 graphics/syntax_icon.gif DROP USER  USER_ID  [ CASCADE ] 

The REVOKE command is the counterpart of the GRANT command in many implementations, allowing privileges that have been granted to a user to be revoked . An example syntax for this command in some implementations follows:

 graphics/syntax_icon.gif REVOKE  PRIV1  [ ,  PRIV2,  ... ] FROM  USERNAME  

Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net