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. | 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: -
Create the database user account with default settings. -
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] | 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. | | 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: -
Create the database user account for SQL Server and assign a password and a default database for the user. -
Add the user to the appropriate database(s). -
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 | 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: 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. | 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: DROP SCHEMA SCHEMA_NAME { RESTRICT CASCADE } | 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. | 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. | | 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 | 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: 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 |