User Creation and

Management

TEC 5323

Outlines

I. Creating a User

II. User Privileges

III. User Roles

IV. Public Synonym

Data Security

 User accounts provide a method of authentication

 They can grant access to specific objects

 They identify owners of objects

Creating a User

 The CREATE USER command gives each user a user name and password

 Syntax:

Assigning User Privileges

There are two types of privileges:

 System privileges

 Allow access to the database and execution of DDL operation

 Object privileges

 Allow a user to perform DML and query operations

Assigning User Privileges (cont)

 Even with a valid user name and password, a user still needs the CREATE SESSION privilege to connect to a database

 Syntax: example

System Privileges

 Affect a user’s ability to create, alter, and drop objects

 Use of ANY keyword with an object privilege (INSERT ANY TABLE) is considered a system privilege

 List of all available system privileges available through SYSTEM_PRIVILEGE_MAP

SYSTEM_PRIVILEGE_MAP

Granting System Privileges

 System privileges are given through the GRANT command

 Example:

GRANT CREATE SESSION TO rthomas;

Granting System Privileges (cont)

 GRANT clause – identifies system privileges being granted

 TO clause – identifies receiving user or role

 WITH ADMIN OPTION clause – allows a user to grant privilege to other database users

Object Privileges

 SELECT – display data from table, view, or sequence

 INSERT – insert data into table or view

 UPDATE – change data in a table or view

 DELETE – remove data from a table or view

 ALTER – change definition of table or view

Granting Object Privileges

 Grant object privileges through the GRANT command

Granting Object Privileges (cont)

 GRANT clause – identifies object privileges

 ON clause – identifies object

 TO clause – identifies user or role receiving privilege

 WITH GRANT OPTION clause – gives a user the ability to assign the same privilege to other users

Granting Object Privileges (cont)

 GRANT command examples

Password Management

 To change a user password, use the PASSWORD command or the ALTER USER command

Utilizing Roles

 A role is a group, or collection, of privileges

 Example: allow orderentry role to SELECT, INSERT, and UPDATE customers, orders, and orderitems tables in “scott” schema.

Utilizing Roles (cont)

 Roles can be assigned to users or other roles

 Example:

Utilizing Roles (cont)

 A user can be assigned several roles

 All roles can be enabled at one time

 Only one role can be designated as the default role for each user

 Default role can be assigned through the ALTER USER command

Utilizing Roles (cont)

 Roles can be modified with the ALTER ROLE command

 Roles can be assigned passwords

Viewing Privilege Information

 ROLE_SYS_PRIVS lists all system privileges assigned to a role

 SESSION_PRIVS lists a user’s currently enabled roles

ROLE_TAB_PRIVS Example

Removing Privileges and Roles

 Revoke system privileges with the REVOKE command

 Syntax:

Removing Privileges and Roles

(cont)

 Revoking an object privilege – if the privilege was originally granted using WITH GRANT OPTION, the effect cascades and is revoked from subsequent recipients

 Syntax:

Removing Privileges and Roles

(cont)

 Revoke role from user or role

 Syntax:

Dropping a Role

 Users receiving privileges via a role that is dropped will no longer have those privileges available

 Syntax:

 Example:

Dropping a User

 The DROP USER command is used to remove a user account

 Syntax:

PUBLIC SYNONYM

 Synonyms are aliases for referencing the database objects

 Grant the CREATE PUBLIC SYNONYM privilege

 Drop a synonym

Example 1: Script that creates an

“AR” Schema

Explanation: Example 1

 The script first connects to system account (make sure to provide the correct system password)

 “ar” is created as the main schema. The same syntax for creating user is used. You may also view “ar” as the admin user.

 Then, the script connects to “ar” and create tables under “ar” schema

Example 2: Script that sets up

roles and users

Explanation I: Example 2

 In this example, we want to manage users for “ap” schema. This schema can be created the same way as “ar” schema in previous example.

 At the beginning, we will drop all users, roles, and public synonyms. Similar to what we have done when creating tables. This should be done as a regular practice to avoid creating the same objects in the database.

Example 2: Script that sets up

roles and users (cont)

Explanation II: Example 2 (cont)

 Following the DROP script in PL/SQL block, we now create three roles (ap_user, ap_manager, and ap_developer).

 Then, we grant privileges to ap_user role (both system privileges and object privileges).

Example 2: Script that sets up

roles and users (cont)

Explanation III: Example 2 (cont)

 Then, we grant privileges to ap_manager role. Notice in the first line, we grant ap_user role to ap_manager role. This means that ap_manager will have all privileges that ap_user has. Additional privileges for ap_manager are added in the script.

 Similar to ap_manager, ap_developer will have all privileges that ap_manager has. Additional privileges are added in the script as well.

Example: Script that sets up roles

and users (cont)

Explanation IV: Example 2 (cont)

 Four users are created with the same password.

 Then, users are assigned their roles. You can see that Joel are allowed to create tables as a developer with the Quota of 10MB.

Example: Script that sets up roles

and users (cont)

Explanation V: Example 2 (cont)

 Finally, we grant the synonym to all users. Although, we only connect to Joel user to grant the synonym. Since “PUBLIC” keyword is used, these grants will apply to all users that we create.

Summary

 Database account management is only one facet of data security

 System privileges are used to grant access to the database and to create, alter, and drop database objects

 Object privileges allow users to manipulate data in database objects

 Roles are collections of privileges

 Roles can be revoked from users using the REVOKE command

TEC 5323

Assignment 10 – User Creation and Management (Extra Credits)

Task:

You will create a SQL script to accomplish the following tasks.

1. Assume that we have connected to the system account. Write a script to create “testdb” schema. Use “password” without quote as the password. You should review example in Week 14 Lecture

Slides (page 28-29).

Connect to “testdb” schema and write a script that creates tables below. Adding data is not

necessary.

user_id in users table and download_id in downloads table are primary keys.

user_id in downloads table is foreign key.

Column attributes and constraints should be assigned as appropriate.

2. Continue adding to the script; create the roles for “db_user” and “db_manager” by granting the following privileges (5 points):

a. db_user: i. CREATE SESSION

ii. CREATE PUBLIC SYNONYM iii. SELECT ON users iv. SELECT ON downloads

b. db_manager: i. db_user role

ii. SELECT, INSERT, UPDATE, DELETE ON users iii. SELECT, INSERT, UPDATE, DELETE ON downloads

3. Continue adding to the script; create two users with a username and password of your choosing. Assign one user to db_user role and another user to db_manager role. Then, create public

synonym of both tables for all users. (5 points)

Tip:

• You should review example script in our Week 14 Lecture Slides (page 28-39). It should be straightforward to adapt the example.

Submission:

1. Create a single SQL script for the assignment (10% deduction if not in one file). Your SQL script is only in “.sql” format. The script should be run in Oracle SQL Plus as

“SQL>@filepath\filename.sql”. For example, if your SQL script is saved as myscript.sql, then we

will be able to execute it in SQL Plus as “SQL>@C:\temp\myscript.sql”.

2. Make sure to test your script before submission. Ten (10) % will be automatically taken if the script is not running (even with minor syntax error). Please note this practice is very important for

a database professional in the field.

3. Please use file convention as follows. Name your file as “firstname_lastname_user.sql”, where firstname and lastname are your first and last name, respectively. Five (5) % will be deducted if

the file name is not followed this convention. There should not be any space in your file name;

otherwise the script will not be run.

4. Submit your project in Drobpox in D2L.