Creating Database Users and Privileges

---

This section explains how to create users and assign privileges for databases. User accounts and associated file or table privileges can be created by two different methods:

• SQL statements
• Users and Groups window in PCC

You must be logged into the database as Master before you can create users or assign privileges by either method.

---

Note
If your database uses Mixed security, you must create database user names that are the same as the operating system user names used to login to the computer where the database engine is installed.

---

General Tasks

To log into a database using PCC when you are already logged into that database as another user

---

Note
As the Master user, logging in as another user can aid you in testing the more restrictive permissions you have assigned this user.

---

1. Click on the machine name in the PCC namespace
2. Click the F5 button (Refresh). This clears all open connections.
3. Select the database again and you will be prompted to log in.

Creating Database Users

To create a new user with SQL statements

1. In PCC, right-click on the database you wish to work with. From the pop-up menu, select Tasks 4 Execute SQL Query to open SQL Data Manager (SQL DM).
2. In SQL DM (or your selected SQL interface, such as ODBC Test), enter the following query and run it:

GRANT LOGIN TO myuser:mypass

where myuser is the user name you wish to create, and mypass is the password for that user. For example, to create the user 'john' with password 'jb456', you would enter the following syntax:

GRANT LOGIN TO john:jb456 

   You have now created a new database user account. To assign privileges for this user, see Assigning Permissions .

To create a new user with PCC and assign permissions

1. Log into your secure database as the Master user.
2. Right-click on the Users node for a database.
3. Select New User

    
   Figure 4-6 Creating a New Database User With PCC

4. If you want this new user to inherit the permissions from a group instead of having individual permissions, you should first define a group. You cannot add a user to a group other than at the time of the user's creation. If you do not assign a group, this new user still inherits the permissions of the PUBLIC group. No user can have permissions that are lower than PUBLIC. Select the checkbox that corresponds to the group and click OK.

    
   Figure 4-7 Entering a New User

5. If you want the user to have individual permissions, click the Permissions tab when you see Figure 4-7 and specify individual permissions by selecting the checkbox next to the database or table right you want to add.

    
   Figure 4-8 Assigning a User's Permissions

The Database Rights section grants that right to all tables in the database. The lower part of the dialog allows you to specify rights specifically by table. In the example shown in Figure 4-8, the user Joe has Update rights to all the tables, and Insert rights to only the Customer and Employees tables.

The following SQL statements would produce the same results as the selections in Figure 4-8:

GRANT Update on * to Joe#

GRANT Insert on Customers to Joe#

GRANT Insert on Employees to Joe#

6. It is also important to note that Joe could also inherit rights from the default group PUBLIC that you could not see in Figure 4-8. To see all of a user's potential rights, you need to look at both the user's properties and that of PUBLIC.

    
   Figure 4-9 Rights Assigned to PUBLIC Group

In this case, PUBLIC also has Select rights to all tables, so user 'Joe' has Select and Update rights on all tables, based on his combined individual and PUBLIC rights.

Assigning Permissions

To assign a user table privilege using SQL statements

1. In SQL DM or your selected SQL interface, enter the following statement to assign privileges for an existing user:
2. GRANT privilege ON table to user

To assign default permissions for a specific user

1. Create a user as described in To create a new user with PCC and assign permissions but do not assign any specific permissions.
2. The permissions assigned to the group PUBLIC will be enforced for the user.

To assign default permissions for all users

1. Using PCC, Click the Users node for the secure database.
2. Right-click PUBLIC and select Properties.
3. Click the Permissions tab.
4. Assign the database or table permissions for PUBLIC using the checkboxes as shown in Figure 4-9 . Any right that is not checked is not granted to default new users.