Sams Teach Yourself SQL in 24 Hours

Recommended Book 

The Management Process

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.

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:

   
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]

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:

   
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:

   
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:

   
GRANT PRIV1 [ , PRIV2, ... ] TO USER_ID

CREATE SCHEMA

Schemas are created via the CREATE SCHEMA statement.

The following is the syntax:

   
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:

   
   

   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;

   
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.

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:

   
DROP SCHEMA SCHEMA_NAME { RESTRICT | CASCADE }

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 K|M |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.

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

User sessions can be—and often are—monitored 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:

   
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:

   
REVOKE PRIV1 [ ,PRIV2, ... ] FROM USERNAME