Managing User Privileges

3 Managing User Privileges

A user privilege enables users to perform certain actions, such as creating other user accounts or modifying database tables.

Topics:

About Privilege Management
You can control user privileges in a variety of ways, such as granting and revoking privileges or creating roles.
When to Grant Privileges to Users
You should only grant users the minimum privileges necessary to perform their jobs.
When to Grant Roles to Users
A role is a named group of related privileges that you grant, as a group, to users or other roles.
Controlling Access to Applications with Secure Application Roles
A secure application role is a role that can be enabled only by an authorized PL/SQL package.
Initialization Parameters Used for Privilege Security
Oracle Database provides initialization parameters to configure privilege security, such as the restriction of SYSTEM privileges.

3.1 About Privilege Management

You can control user privileges in a variety of ways, such as granting and revoking privileges or creating roles.

Granting and revoking individual privileges. You can grant individual privileges, for example, the privilege to perform the UPDATE SQL statement, to individual users or to groups of users.
Creating a role and assigning privileges to it. A role is a named group of related privileges that you grant, as a group, to users or other roles.
Creating a secure application role. A secure application role enables you to define conditions that control when a database role can be enabled. For example, a secure application role can check the IP address associated with a user session before allowing the session to enable a database role.

3.2 When to Grant Privileges to Users

You should only grant users the minimum privileges necessary to perform their jobs.

For an introduction to managing user privileges and roles, see Oracle Database 2 Day DBA. Oracle Database 2 Day DBA also provides an example of how to grant a privilege.

In other words, the principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs. To implement this principle, restrict the following as much as possible:

The number of system and object privileges granted to database users
The number of people who are allowed to make SYS-privileged connections to the database

For example, generally the CREATE ANY TABLE privilege is not granted to a user who does not have database administrator privileges.

You can find excessive system and object privilege grants, even with large numbers of user accounts in complex Oracle Database installations, by creating a privilege analysis policy. A privilege analysis policy finds privilege usage according to a specified condition and then stores the results in data dictionary views.Oracle Database Vault Administrator’s Guide describes how to create a privilege analysis policy.

3.3 When to Grant Roles to Users

A role is a named group of related privileges that you grant, as a group, to users or other roles.

To learn the fundamentals of managing roles, see Oracle Database 2 Day DBA. In addition, see Oracle Database 2 Day DBA for an example of creating a role.

Roles are useful for quickly and easily granting permissions to users. Although you can use Oracle Database-defined roles, you have more control and continuity if you create your own roles that contain only the privileges pertaining to your requirements. Oracle may change or remove the privileges in an Oracle Database-defined role, as it has with the CONNECT role, which now has only the CREATE SESSION privilege. Formerly, this role had eight other privileges.

Ensure that the roles you define contain only the privileges required for the responsibility of a particular job. If your application users do not need all the privileges encompassed by an existing role, then apply a different set of roles that supply just the correct privileges. Alternatively, create and assign a more restrictive role.

Do not grant powerful privileges, such as the CREATE DATABASE LINK privilege, to regular users such as user SCOTT. (Particularly do not grant any powerful privileges to SCOTT, because this is a well known default user account that may be vulnerable to intruders.) Instead, grant the privilege to a database role, and then grant this role to the users who must use the privilege. And remember to only grant the minimum privileges the user needs.

3.4 Controlling Access to Applications with Secure Application Roles

A secure application role is a role that can be enabled only by an authorized PL/SQL package.

Topics:

About Secure Application Roles
A secure application role requires a PL/SQL package and a way to execute this package when the user logs in.
Tutorial: Creating a Secure Application Role
This tutorial shows how two employees, Matthew Weiss and Winston Taylor, try to gain information from the OE.ORDERS table.

3.4.1 About Secure Application Roles

A secure application role requires a PL/SQL package and a way to execute this package when the user logs in.

This package defines one or more security policies that control access to the application. Both the role and the package are typically created in the schema of the person who creates them, which is typically a security administrator. A security administrator is a database administrator who is responsible for maintaining the security of the database.

The advantage of using a secure application role is you can create additional layers of security for application access, in addition to the privileges that were granted to the role itself. Secure application roles strengthen security because passwords are not embedded in application source code or stored in a table. This way, the decisions the database makes are based on the implementation of your security policies. Because these definitions are stored in one place, the database, rather than in your applications, you modify this policy once instead of modifying the policy in each application. No matter how many users connect to the database, the result is always the same, because the policy is bound to the role.

A secure application role has the following components:

The secure application role itself. You create the role using the CREATE ROLE statement with the IDENTIFIED USING clause to associate it with the PL/SQL package. Then, you grant the role the privileges you typically grant a role.
A PL/SQL package, procedure, or function associated with the secure application role. The PL/SQL package sets a condition that either grants the role or denies the role to the person trying to log in to the database. You must create the PL/SQL package, procedure, or function using invoker's rights, not definer's rights. An invoker's right procedure executes with the privileges of the current user, that is, the user who invokes the procedure. This user must be granted the EXECUTE privilege for the underlying objects that the PL/SQL package accesses. Invoker's rights procedures are not bound to a particular schema. They can be run by a variety of users and enable multiple users to manage their own data by using centralized application logic. To create the invoker's rights package, use the AUTHID CURRENT_USER clause in the declaration section of the procedure code.

The PL/SQL package also must contain a SET ROLE statement or DBMS_SESSION.SET_ROLE call to enable (or disable) the role for the user.

After you create the PL/SQL package, you must grant the appropriate users the EXECUTE privilege on the package.
A way to execute the PL/SQL package when the user logs on. To execute the PL/SQL package, you must call it directly from the application before the user tries to use the privileges the role grants. You cannot use a logon trigger to execute the PL/SQL package automatically when the user logs on.

When a user logs in to the application, the policies in the package perform the checks as needed. If the user passes the checks, then the role is granted, which enables access to the application. If the user fails the checks, then the user is prevented from accessing the application.

3.4.2 Tutorial: Creating a Secure Application Role

This tutorial shows how two employees, Matthew Weiss and Winston Taylor, try to gain information from the OE.ORDERS table.

Access rights to the OE.ORDERS table are defined in the emp_role secure application role. Matthew is Winston's manager, so Matthew, as opposed to Winston, will be able to access the information in OE.ORDERS.

Topics:

Step 1: Create User Accounts for This Tutorial
Your first step is to create user accounts for Matthew and Winston.
Step 2: Create a Security Administrator Account
For greater security, you should apply separation of duty concepts when you assign responsibilities to the system administrators on your staff.
Step 3: Create a Lookup View
A lookup view contains a view of information from a larger table. The lookup view only contains the information that you need.
Step 4: Create the PL/SQL Procedure to Set the Secure Application Role
After you create the administrative account and the lookup view, you can create the secure application role procedure.
Step 5: Create the Secure Application Role
After you create the PL/SQL procedure to set the secure application role, you can create the emp_role secure application role.
Step 6: Grant SELECT for the EMP_ROLE Role to the OE.ORDERS Table
User OE, who owns the OE.ORDERS table, must grant the SELECT privilege for the ORDERS table to the emp_role role.
Step 7: Grant the EXECUTE Privilege for the Procedure to Matthew and Winston
At this stage, Matthew and Winston can try to access the OE.ORDERS table, but they are denied access.
Step 8: Test the EMP_ROLE Secure Application Role
To test the emp_role secure application role, you must log on as Matthew and Winston and trying to access the OE.ORDERS table.
Step 9: Optionally, Remove the Components for This Tutorial
You can remove the components that you created for this tutorial if you no longer need them.

3.4.2.1 Step 1: Create User Accounts for This Tutorial

Your first step is to create user accounts for Matthew and Winston.

Matthew and Winston both are sample employees in the HR.EMPLOYEES table. This table provides columns for the manager ID and email address of the employees, among other information. You must create user accounts for these two employees so that they can later test the secure application role.

To create the user accounts:

In Enterprise Manager, access the Database home page.

See Oracle Database 2 Day DBA for more information.
Access your target database and then log in as user SYSTEM.
From the Schema menu, select Users.
In the Users page, click Create.
In the Create User page, enter the following information:
- Name: mweiss (to create the user account for Matthew Weiss)
- Profile: DEFAULT
- Authentication: Password
- Enter Password and Confirm Password: Enter a password that meets the requirements in Requirements for Creating Passwords.
- Default Tablespace: USERS
- Temporary Tablespace: TEMP
- Status: Unlocked
Click System Privileges.
Click Edit List.
In the Modify System Privileges, from the Available System Privileges lists, select the CREATE SESSION privilege, and then click Move to move it to the Selected System Privileges list.
Click OK.

The Create User page appears, with CREATE SESSION listed as the system privilege for user mweiss.
Ensure that the Admin Option for CREATE SESSION is not selected, and then click OK.
In the Users page, select the selection button for user MWEISS from the list of users, and then from the Actions list, select Create Like. Then, click Go.
In the Create User page, enter the following information to create the user account for Winston, which will be almost identical to the user account for Matthew:
- Name: wtaylor
- Enter Password and Confirm Password: Enter a password that meets the requirements in Requirements for Creating Passwords.
You do not need to specify the default and temporary tablespaces, or the CREATE SESSION system privilege, for user wtaylor because they are already specified.
Click OK.

Now both Matthew Weiss and Winston Taylor have user accounts that have identical privileges.

3.4.2.2 Step 2: Create a Security Administrator Account

For greater security, you should apply separation of duty concepts when you assign responsibilities to the system administrators on your staff.

For the tutorials used in this guide, you will create and use a security administrator account called sec_admin.

To create the sec_admin security administrator account:

From the Schema menu, select Users.

If the Database Login page appears, then log in as an administrative user, such as SYS. User SYS must log in with the SYSDBA role selected.
In the Users page, click Create.
In the Create User page, enter the following information:
- Name: sec_admin
- Profile: Default
- Authentication: Password
- Enter Password and Confirm Password: Enter a password that meets the requirements in Requirements for Creating Passwords.
- Default Tablespace: USERS
- Temporary Tablespace: TEMP
- Status: UNLOCKED
Click System Privileges.
Click Edit List.

The Modify System Privileges page appears.
In the Available System Privileges list, select the following privileges and then click Move to move each one to the Selected System Privileges list. (Hold down the Control key to select multiple privileges.)
- CREATE PROCEDURE
- CREATE ROLE
- CREATE SESSION
- INHERIT ANY PRIVILEGES
- SELECT ANY DICTIONARY
Click OK.

The Create User page appears. Under Admin Option, do not select the boxes.
Click OK.

The Users page appears. User sec_admin is listed in the User Name list.

3.4.2.3 Step 3: Create a Lookup View

A lookup view contains a view of information from a larger table. The lookup view only contains the information that you need.

The grant_emp_role procedure, which you will create later in this tutorial, grants the emp_role only to managers who report to Steven King, whose employee ID is 100. This information is located in the HR.EMPLOYEES table. However, you should not use that table in this procedure, because it contains sensitive data such as salary information, and for it to be used, everyone will need access to it. In most real world cases, you create a lookup view that contains only the information that you need. (You could create a lookup table, but a view will reflect the most recent data.) For this tutorial, you create your own lookup view that only contains the employee names, employee IDs, and their manager IDs.

To create the HR.HR_VERIFY lookup view:

In SQL*Plus, connect as user HR.
```
CONNECT HR
Enter password: password
```
If you receive an error message saying that HR is locked, then you can unlock the account and reset its password by entering the following statements. For greater security, do not reuse the same password that was used in previous releases of Oracle Database. Enter any password that is secure, according to the password guidelines described in Requirements for Creating Passwords.
```
CONNECT SYSTEM
Enter password: password

PASSWORD HR
Changing password for HR
New password: password
Retype new password: password
Password changed.

ALTER USER HR ACCOUNT UNLOCK;

CONNECT HR
Enter password: password
```

Enter the following CREATE VIEW SQL statement to create the lookup view:

CREATE VIEW hr_verify AS 
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, MANAGER_ID 
FROM EMPLOYEES;

Grant the EXECUTE privilege for this view to mweiss, wtaylor, and sec_admin by entering the following SQL statements:

GRANT SELECT ON hr_verify TO mweiss;
GRANT SELECT ON hr_verify TO wtaylor;
GRANT SELECT ON hr_verify TO sec_admin;

3.4.2.4 Step 4: Create the PL/SQL Procedure to Set the Secure Application Role

After you create the administrative account and the lookup view, you can create the secure application role procedure.

In most cases, you create a package to hold the procedure, but because this is a simple tutorial that requires only one secure application role test (as defined in the procedure), you will create a procedure by itself. If you want to have a series of procedures to test for the role, then you would create them in a package. At this stage, the role does not exist, but it does not need to. You will create it in the next step.

A PL/SQL package defines a simple, clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages also make code more reusable and easier to maintain. The advantage here for secure application roles is that you can create a group of security policies that used together present a solid security strategy designed to protect your applications. For users (or potential intruders) who fail the security policies, you can add auditing checks to the package to record the failure.

To create the secure application role procedure:

In Enterprise Manager, click Log Out to log out of the database.
In the Confirmation dialog box, select Logout of (Database Instance) and then select the Display login page after logout check box. Then click Logout.
Log in as user sec_admin using the NORMAL role.
From the Schema menu, select Programs, then Procedures.
In the Procedures page, click Create.
In the Create Procedure page, enter the following information:
- Name: GRANT_EMP_ROLE
- Schema: SEC_ADMIN
- Source: Delete the empty procedure code that has been provided and then enter following text to create the secure application role procedure.
```
AUTHID CURRENT_USER
 AS
v_user varchar2(50); 
v_manager_id number :=1;
 BEGIN    
  v_user := lower((sys_context ('userenv','session_user')));
  SELECT manager_id 
     INTO v_manager_id FROM hr.hr_verify WHERE lower(email)=v_user;
   IF v_manager_id = 100
    THEN 
     EXECUTE IMMEDIATE 'SET ROLE emp_role';  
    ELSE NULL; 
   END IF;        
  EXCEPTION  
   WHEN NO_DATA_FOUND THEN v_manager_id:=0;  
   DBMS_OUTPUT.PUT_LINE(v_manager_id);
END;
```
  In this specification:
  - AUTHID CURRENT USER: Appends the AUTHID CURRENT_USER clause to the CREATE PROCEDURE statement. The AUTHID CURRENT_USER clause enables the invoking user to run the procedure using his or her privileges.
    
    You must create the procedure to use invoker's rights for the procedure to work. Invoker's rights allow the user to have EXECUTE privileges on all objects that the procedure accesses.
    
    Roles that are enabled inside an invoker's right procedure remain in effect even after the procedure exits, but after the user exits the session, he or she no longer has the privileges associated with the secure application role. In this case, you can have a dedicated procedure that enables the role for the rest of the session.
    
    Because users cannot change the security domain inside definer's rights procedures, secure application roles can only be enabled inside invoker's rights procedures.
    
    See About Secure Application Roles for information about the importance of creating the procedure using invoker's rights.
  - v_user varchar2(50): Declares the v_user variable, which will store the user session information.
  - v_manager_id number :=1: Declares the v_manager_id variable, which will store the manager's ID of the v_user user.
  - v_user := lower...: Retrieves the user session information for the user logging on, in this case, Matthew or Winston. To retrieve user session information, use the SYS_CONTEXT SQL function with the USERENV namespace attributes ('userenv', session_attribute), and writes this information to the v_user variable.
    
    The information returned by this function indicates the way in which the user was authenticated, the IP address of the client, and whether the user connected through a proxy. See Oracle Database SQL Language Reference for more information about SYS_CONTEXT.
  - SELECT manager_id ... INTO...: Get the manager's ID of the current user. The SELECT statement copies the manager ID into the v_manager_id variable, and then checking the HR.HR_VERIFY view for the manager ID of the current user. This example uses the employees' email addresses because they are the same as their user names.
  - IF ... THEN ... END IF: Use an IF condition to test whether the user should be granted the grant_emp_role role. In this case, the test condition is whether the user reports to Matthew's manager, Steven King, whose employee number is 100. If the user reports to King, as Matthew does, then the secure application role is granted to the user. Otherwise, the role is not granted.
    
    The result is that the secure application role will grant Matthew Weiss the role because he is a direct report of Steven King, but will deny the role to Winston, because he is not a direct report of Steven King.
  - THEN ... ELSE NULL: Within the IF condition, the THEN condition grants the role by executing immediately the SET ROLE statement. Otherwise, the ELSE condition denies the grant.
  - EXCEPTION: Use an EXCEPTION statement to set v_manager_id to 0 if no data is found.
  - DBMS_OUTPUT.PUT_LINE: Copies the manager's ID, which is now 0, into a buffer so that it is readily available.
Click OK.

Tip:

If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The USER_DUMP_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST in SQL*Plus. See Oracle Database Administrator’s Guide for more information about trace files.

3.4.2.5 Step 5: Create the Secure Application Role

After you create the PL/SQL procedure to set the secure application role, you can create the emp_role secure application role.

To create the secure application role:

Ensure that you are still logged in as user sec_admin.
From the Administration menu, select Security, then Roles.
In the Roles page, click Create.
In the Create Role page, enter the following information:
- Name: emp_role
- Authentication: Select Application.
  
  The page expands to show the additional prompts necessary to create the secure application role.
  
  Description of the illustration sec_app_role.gif
Select the Procedure option.
In the Procedure Name field, enter SEC_ADMIN.GRANT_EMP_ROLE.
Click OK.

3.4.2.6 Step 6: Grant SELECT for the EMP_ROLE Role to the OE.ORDERS Table

User OE, who owns the OE.ORDERS table, must grant the SELECT privilege for the ORDERS table to the emp_role role.

This gives anyone who has been authorized to use the emp_role role the ability to select from the OE.ORDERS table.

To grant the SELECT privilege for the EMP_ROLE role to the OE.ORDERS table:

Connect as user OE.
```
CONNECT OE
Enter password: password
```
If you receive an error message saying that OE is locked, then you can unlock the OE account and reset its password by entering the following statements. For greater security, do not reuse the same password that was used in previous releases of Oracle Database. Enter any password that is secure, according to the password guidelines described in Requirements for Creating Passwords.
```
CONNECT SYSTEM
Enter password: sys_password

PASSWORD OE  -- First, change the OE account password.
Changing password for OE
New password: password
Retype new password: password
Password changed.

ALTER USER OE ACCOUNT UNLOCK; -- Next, unlock the OE account.
```
Another way to unlock a user account and create a new password is to use the following syntax:
```
ALTER USER account_name ACCOUNT UNLOCK IDENTIFIED BY new_password:
```
Now you can connect as user OE.
```
CONNECT OE 
Enter password: password
```
Enter the following statement to grant the emp_role role SELECT privileges on the OE.ORDERS table.
```
GRANT SELECT ON ORDERS TO emp_role;
```
Do not grant the role directly to the users. The PL/SQL package will do that for you, assuming the users pass its security policies.

3.4.2.7 Step 7: Grant the EXECUTE Privilege for the Procedure to Matthew and Winston

At this stage, Matthew and Winston can try to access the OE.ORDERS table, but they are denied access.

The next step is to grant them the EXECUTE privilege on the grant_emp_role procedure, so that the grant_emp_role procedure can execute, and then grant or deny access, when they try to select from the OE.ORDERS table.

To grant EXECUTE privileges for the grant_emp_role procedure:

In SQL*Plus, log in as user sec_admin.

connect sec_admin
Enter password: password

Run the following GRANT SQL statements for users mweiss and wtaylor:

GRANT EXECUTE ON grant_emp_role TO mweiss;
GRANT EXECUTE ON grant_emp_role TO wtaylor;

3.4.2.8 Step 8: Test the EMP_ROLE Secure Application Role

To test the emp_role secure application role, you must log on as Matthew and Winston and trying to access the OE.ORDERS table.

When Matthew and Winston log on, and before they issue a SELECT statement on the OE.ORDERS table, the grant_emp_role procedure must be executed for the role verification to take place.

Topics:

Testing the emp_role Secure Application Role as User MWEISS
You can connect to the database as user mweiss using SQL*Plus.
Testing the emp_role Secure Application Role as User WTAYLOR
Next, Winston tries to access the secure application.

3.4.2.8.1 Testing the emp_role Secure Application Role as User MWEISS

You can connect to the database as user mweiss using SQL*Plus.

To test the emp_role secure application role as user MWEISS:

In SQL*Plus, connect as user mweiss.

CONNECT mweiss
Enter password: password

Enter the following SQL statement to run the grant_emp_role procedure:
```
EXEC sec_admin.grant_emp_role;
```
This statement executes the grant_emp_role procedure for the current session. (In a real world scenario, this statement would be automatically run when the user logs in to the application.)
Perform the following SELECT statement on the OE.ORDERS table:
```
SELECT COUNT(*) FROM OE.ORDERS;
```
Matthew has access to the OE.ORDERS table:
```
  COUNT(*)
----------
       105
```

3.4.2.8.2 Testing the emp_role Secure Application Role as User WTAYLOR

Next, Winston tries to access the secure application.

To test the emp_role secure application role as user WTAYLOR:

In SQL*Plus, connect as user wtaylor.

CONNECT wtaylor
Enter password: password

Enter the following SQL statement to run the grant_emp_role procedure:
```
EXEC sec_admin.grant_emp_role;
```
This statement executes the grant_emp_role procedure for the current session.
Perform the following SELECT statement on the OE.ORDERS table:
```
SELECT COUNT(*) FROM OE.ORDERS;

ERROR at line 1:
ORA-00942: table or view does not exist
```
Because Winston does not report directly to Steven King, he does not have access to the OE.ORDERS table. He will never learn the true number of orders in the ORDERS table, at least not by performing a SELECT statement on it.

3.4.2.9 Step 9: Optionally, Remove the Components for This Tutorial

You can remove the components that you created for this tutorial if you no longer need them.

To remove the components:

In SQL*Plus, connect as user SYSTEM.

CONNECT SYSTEM
Enter password: password

Enter the following DROP statements:
```
DROP USER mweiss;
DROP USER wtaylor;
```
Do not drop user sec_admin. You will need this user account for other tutorials in this guide.

In SQL*Plus, connect as user sec_admin.

CONNECT sec_admin
Enter password: password

Enter the following DROP SQL statements:

DROP ROLE emp_role;
DROP PROCEDURE grant_emp_role;

Connect as user HR, and then drop the HR_VERIFY view.

CONNECT HR
Enter password: password
DROP VIEW hr_verify;

Exit SQL*Plus.
```
EXIT
```

3.5 Initialization Parameters Used for Privilege Security

Oracle Database provides initialization parameters to configure privilege security, such as the restriction of SYSTEM privileges.

Table 3-1 lists initialization parameters that you can use to secure user privileges.

Table 3-1 Initialization Parameters Used for Privilege Security

Initialization Parameter	Default	Description
`O7_DICTIONARY_ACCESSIBILITY`	`FALSE`	Controls restrictions on `SYSTEM` privileges. See Enabling Data Dictionary Protection for more information about this parameter.
`OS_ROLES`	`FALSE`	Determines whether the operating system identifies and manages the roles of each user.
`REMOTE_OS_ROLES`	`FALSE`	Specifies whether operating system roles are allowed for remote clients. The default value, `FALSE`, causes Oracle to identify and manage roles for remote clients.
`SQL92_SECURITY`	`TRUE`	Specifies whether users must be granted the `SELECT` object privilege to execute `UPDATE` or `DELETE` statements.

To modify an initialization parameter, see Modifying the Value of an Initialization Parameter. For detailed information about initialization parameters, see Oracle Database Reference.