====== Database Account Procedures and Policies ======

This page details the creation and maintenance procedures for database accounts. 

There are two types of accounts created:
  * Student accounts
  * Application accounts

Student accounts are used for students to log in and perform administrator tasks related to their database. Student accounts are NEVER to be coded into an application. 

Application accounts are used for programs and applications. These username and passwords are going to be coded in to programs or otherwise saved in places that are not completely secure.

Accounts that use a local authentication source can be used as both student and application accounts. 

The tables for each database server use the format:
^ Server ^ Account purpose ^ Authentication source ^
| server name | student or application account? | where the username/password are stored |

===== MS SQL Student Server (msdb.cs.ndsu.nodak.edu) =====
{{:line.png?nolink|}}

This server exists solely for students to use in coursework.  (Back in the Helsene Dynasty, students were using Gendo for their coursework.  Gendo is also the database used by VMware, which made us scared enough to spawn up another box.).  Every database account on this machine is a local SQL account (thus not using Windows Authentication, which seemed easiest).

There is a script to make the database accounts here: crono.cs.ndsu.nodak.edu/root/bin/mssql.bash.  Just pass it a Unix ID for a user, and they will get a password generated along with the proper SQL to make all the things that require making.

Then simply use the Microsoft SQL Management Studio to run the query with the user data and you're done!  :)

===== MS SQL (Gendo) Production Server =====
{{:line.png?nolink|}}

^ Server ^ Account purpose ^ Authentication source ^
| gendo.cs.ndsu.nodak.edu | Student account :?:| Computer Science domain |
| gendo.cs.ndsu.nodak.edu | Application account | Local to SQL server |

==== SQL Server account model ====

SQL Server uses the concepts of 'logins', 'users', and 'roles'. 

=== Logins ===

A login is a combination of username and password. We use CS domain usernames and passwords for standard SQL Server database setups. This allows a user in to the server, but does not, by itself, grant any kind of access or permission. 

Logins are server-wide and are a server-level security object.

=== Users ===

Users are database-level security objects that define sets of roles. They are linked to logins to allow a specific account access to a database. The access is defined by the roles that a user has.

=== Roles ===

Roles are database-level security objects that define permissions. Roles are not granted to logins directly, instead they are granted to users, which are linked to logins. 

==== Creating the database ====

In SQL Server Management Studio, right-click on 'Databases' and select 'New Database'. You can then give a name to the database. Leave the other settings as their defaults unless the project has specific configuration requirements or the database is large ( > 100 MB) or has specific performance requirements. 

==== Making accounts ====

Use SQL Server Management Studio to connect to gendo.cs.ndsu.nodak.edu using Windows Authentication.

Once connected, you need to complete the following tasks:
  * Enable the user's login for the server (server-level principle)
  * Create database accounts (database-level principle)
  * Link the logins with the database users

==== Enable the user's login ====

The user needs to use a set of credentials to log in to the server for both management and their applications. All logins should be done using CS domain usernames and passwords. 

=== Add user group (student user accounts) ===

Add all users that need access (real users only) to a Windows group in the domain. Often this group will be named something like 'csci445_s12_project' to identify the course, semester, and team. 

To allow this group to log in, go to 'Security -> Logins -> (right-click Logins) -> New Login'. Use the 'Search' button or type in the group name with the domain before it, like 'CS\csci445_s12_project'. Do NOT grant any additional server roles to the login, all other settings can be left as their defaults. 

=== Add application user (application accounts) ===

Create an application user in the domain. This user is meant to be coded in to the application and used for queries. Give the application user a name like 'csci445_s12_project_app' and a randomly generated password. 

Give this user a login using the same method as adding the user group. 

These need to be granted separately as the student accounts will have different permissions than the application users.

==== Create database users ====

The database now needs users created at the database level that define permissions on that database. These database-level accounts define permission sets and are linked to the logins created above to give the logins their database permissions. 

=== Standard scenario ===

Most projects will need an admin-level user for the student group login to be linked to and a restricted user for the application account. 

== Admin-level user ==

In SSMS, go to 'Databases -> the project's database -> Security -> Users', right-click on 'Users' and select 'New User'. Give the user a name and select the student's project group for 'Login name'. This will allow any student in the group access to this user. 

In 'Database role membership', select db_owner, this grants all permissions to the database and its tables. 

== Application-level user ==

In SSMS, go to 'Databases -> the project's database -> Security -> Users', right-click on 'Users' and select 'New User'. Give the user a name and select the project's application account for 'Login name'. This links the application's username/password with the user definition of this specific database.

===== Oracle (Asuka) =====
{{:line.png?nolink|}}

<color #ed1c24>Oracle accounts are now done through CCAST.</color>

^ Server ^ Account purpose ^ Authentication source ^
| asuka.cs.ndsu.nodak.edu | student/application account | Local to Oracle server |

==== Making accounts manually ====

=== Creating the user ===

<code>
CREATE USER username IDENTIFIED BY password 
    DEFAULT TABLESPACE tablespace 
    TEMPORARY TABLESPACE tablespace
    QUOTA UNLIMITED ON tablespace
</code>

Replace username with the user's username, password with a randomly generated password, and tablespace with the intended tablespace for the user (we are currently using USERS as a default and TEMP for a temporary tablespace).

<code>
example:
CREATE USER testuser IDENTIFIED BY asdf1234 
    DEFAULT TABLESPACE users 
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users
</code>

The username must be the student's Unix ID (as defined by ITS, see [[http://cs.ndsu.edu/tools/uid.php|NDSU Unix ID lookup]]) and the password must be randomly generated. The username should be emailed to the students @ndsu.edu email account.

=== Creating a schema for the user ===

Creating a user also automatically creates a schema which the user owns. The user can do any operations to their schema.

=== Granting permissions to connect ===

== CSCI 366 students ==
Students in CSCI 366 can simply be given the role CSCI366. This can be done using:
<code>
GRANT CSCI366 TO username;
</code>

== Other users ==
Users need the 'CONNECT' role and the 'CREATE SESSION' system privilege to connect from a client. 
<code>
GRANT CONNECT TO username;
GRANT CREATE SESSION TO username;
</code>


This role grants users 'CONNECT' and 'CREATE SESSION', among other things used for the course.

=== Granting permissions to a tablespace (if needed) ===

All users need to be given permissions to a tablespace as well. A tablespace defines underlying storage for objects that the user creates. 

 -- THIS IS NEEDED ONLY IF A TABLESPACE IS NOT DEFINED WHEN THE USER IS CREATED --

== CSCI 366 students ==

The CSCI366 role grants users unlimited tablespace, and this role should be for CSCI366 users. 

== Other users ==

Other users can be given permissions to a tablespace using:

<code>
ALTER USER username QUOTA UNLIMITED ON tablespace;
</code>

We use the USERS tablespace by default. 

==== Making accounts using TOra ====

Accounts can also be made using the TOra Security Manager. Users created in this way need to be given a random password, and the CSCI366 role. They also need to be given the default and temporary tablespace 'USERS'



 
 
===== MySQL (Rei) =====
{{:line.png?nolink|}}

^ Server ^ Account purpose ^ Authentication source ^
| rei.cs.ndsu.nodak.edu | student/application account | Local to mySQL server |

==== Using phpmyadmin ====

rei.cs.ndsu.nodak.edu hosts phpymadmin, you can find it at https://rei.cs.ndsu.nodak.edu/phpmyadmin. 

Create the database, and then create users in each database. 

After logging in to phpmyadmin, you can create a database at the front page. After doing so, go into the database page for the new database.

To add a user to a database, click the 'privileges' tab and then click the link at the bottom for 'Add a new User'. Fill in appropriate values for username and password (or generate one) and set appropriate privileges.

==== Using scripts on rei.cs ====

There are two scripts to create accounts on rei.cs.ndsu.nodak.edu in /usr/local/sbin/.

The script make_db_accounts.sh takes a user-entered (or piped) list of usernames to create accounts for.

The script make_db_accounts_group.sh takes a user-entered group name and creates accounts for those users. 

** User account information is stored in the file specified in the script. Currently, this is /tmp/users_passwords.txt. THIS FILE IS OVERWRITTEN ON EACH EXECUTION **

** USERS THAT ALREADY HAVE ACCOUNTS WILL CAUSE ISSUES WITH THESE SCRIPTS.** Currently, users that currently have DB accounts should NOT be fed into the script, it will cause the process to fail. The script should be modified such that the DB is checked for an existing account for each user. 



===== PostgreSQL (Shinji) =====
{{:line.png?nolink|}}

<color #ed1c24>Postgres hasn't been offered by the department since at least 2014 if not earlier.</color>

^ Server ^ Account purpose ^ Authentication source ^
| shinji.cs.ndsu.nodak.edu | student account | Computer Science domain |
| shinji.cs.ndsu.nodak.edu | application account | Local to PostgreSQL server |

==== Accounts ====

Accounts are created in the database first, and then authorization and password sources can be modified later. 

To create an account, the easy way is to use [[http://pgadmin.org|pgadmin3]] to connect to the database with a privileged user. Student techs and admins should have the 'user_manager' group role in the database to create and manage accounts. 

The standard method for creating accounts is to create a new 'login role' containing the users username (and password for application accounts). Each login role should correspond to a single account. 

Group roles are then used to grant permissions to sets of login roles. Login roles will belong to one or more group roles. 

For login roles that are to authenticate with student accounts (via PAM), generate a very long (32-character or so) password and enter a comment indicating that the account is using PAM for authentication. 

==== Auth ====

PostgreSQL uses the file at /etc/postgresql/8.4/main/pg_hba.conf to define authentication and authorization. //(As of Jan 2017. postgresql 9.5)// 

This file works in a method such that the first applicable match for 'type', 'database', 'host' and 'username' dictate how the user can auth. Lines should go from the most specific at the top to the most general at the bottom. 

The file has 5 entries per line: 'type', 'database', 'user', 'host', and 'method'.

^ Entry ^ Purpose ^ 
| type | matches based on the type of access |
| database | matches based on the database being requested |
| user | matches based on username of the connecting client |
| host | matches based on the host/IP of the connecting client, CIDR addresses are usable, leave blank for type 'local' |
| method | defines the method of authentication |

^ type ^ description ^ 
| local | local machine connection |
| host | network-based connection | 
| hostssl | SSL-based network connection |

^ method ^ description ^ 
| md5 | auths against password in the database |
| ident | local system account authentication |
| gss | kerberos ticket based authentication |
| pamservice=<SERVICE> | PAM-based authentication, using service defined by <SERVICE> |

There is a list of accounts enabled for PAM-based auth in /etc/postgresql/8.4/main/pam_accounts. 

On any modifications to pam_accounts or pg_hba.conf, you need to reload postgres.