Java Reference
In-Depth Information
[ WITH
[ SYSID
uid
]
[ PASSWORD '
password
'' ] ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ IN GROUP
groupname
[, ...] ]
[ VALID UNTIL '
abstime
' ]
The following code example creates a user with no password. This is not a very safe thing to do from a
security perspective, so we do not recommend using this command. Nevertheless, the command is
valid and creates a user with the given username, as shown here:
CREATE USER user_name
Next, create a user with a password, whose account is valid until the end of 2001. This user also has
permission to create other users but not to create other databases. Here's an example:
CREATE USER user_name WITH PASSWORD 'jw8s0F4' NOCREATEDB
CREATEUSER VALID UNTIL 'Jan 1 2002'
Recall that we can assign users to groups only if the user exists. Similarly, the
CREATE USER
command
allows us to assign users to groups only if the groups exist. For example, let us say we want to create a
new group called "Managers" and assign two users to the group, "John Doe" and "Jack Smith." Neither
of these users currently exists in the system. Here's one way to assign them to a group:
CREATE USER 'jdoe' WITH PASSWORD 'temppassword'
CREATE USER 'jsmith' WITH PASSWORD 'temppassword2'
CREATE GROUP 'managers' WITH USER jdoe, jsmith
Adding a user to a group does not create the user. Similarly, removing a user from a group does not
drop the user itself. To create a new group and assign two new users to that group, you have to issue
three separate commands to the database as shown below.
CREATE GROUP 'managers'
CREATE USER 'jdoe' WITH PASSWORD 'temppassword' IN GROUP managers
CREATE USER 'jsmith' WITH PASSWORD 'temppassword2' IN GROUP managers
Finally, you can use the ALTER commands to do the same as above.
CREATE GROUP 'managers'
CREATE USER 'jdoe' WITH PASSWORD 'temppassword'
CREATE USER 'jsmith' WITH PASSWORD 'temppassword2'
ALTER GROUP 'managers' ADD USER jdoe, jsmith
As you can see, there are several ways to create users. It is very hard to tell which method is better
than another other. This depends on how well defined are your requirements for the groups and users.
Is this something that will change frequently or is it something that you can define once? Depending on
your organizational structure and how well defined is your Org Chart, you can decide which
combinations of commands minimize your work.
Dropping a user
Only database super users and administrators can use the
DROP USER
command, which removes the
specified user from the database. It does not remove tables, views, or other objects the user owns. If
the user owns any database objects, you get an error message. Thus, to delete a user, you need to
delete all objects the user owns or to change the ownership of the objects the user owns. Here's the
general syntax for this command:
DROP USER user_name
