Managing users in SQL

A database may have many users. Each user must have a user account. Database administrators are users with authority to specify new users, specify users roles and grant general privileges for users. They may also revoke the privileges and delete users.

This chapter explains how to

• create new user account and new roles
• grant roles for users
• revoke roles from users
• delete users, and
• change user attributes, especially password

SQL provides create, and drop operations for creating and deleting user accounts, and grant and revoke operations for managing privileges.

• A new user account is created with the statement 'create user'.
  

  syntax			create user username identified by password
  example			create user smith identified by j0nes

Create user statement may have database management system specific additional parts. This statement is not available for normal users. Only database administrators may use it.

• A role is created with the statement 'create role':
  

  syntax			create role rolename
  example			create role manager

• Roles are assigned to users with 'grant' -statement:
  

  syntax			grant rolename {, rolename2 ...}   to username {, username2 ...}   [with admin option]
  example			grant manager to smith

  It the clause  'with admin option' is included, the user may pass the role forward to some other user.

• A role is denied with 'revoke'-statement. Only the database administrator that has granted the role may revoke it.

 

syntax			revoke rolename {, rolename2 ...}   from username {, username2 ...}
example			revoke manager from smith

• Role is deleted with 'drop role'-statement
 

syntax			drop role rolename
example			drop role manager

Deletion of a role revokes it automatically from all users.

• A user account is deleted with a 'drop user'-statement. Only database administrators are entitled to use this statement. 
 

syntax			drop user username [cascade]
example			drop user smith cascade

If the account owns tables or some other database objects it cannot be deleted unless the clause 'cascade' is included in the command.Cascade caused all objects owned by the user to be deleted prior to the deletion of the user account.

 

• The password and also other user account attributes may be changed with the  'alter user'-statement. The user himself may use this command to change his pasword.  
 

syntax			alter user username identified by password
example			alter user smith identified by jones1