This chapter contains:
You can control user privileges in the following ways:
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.
Because privileges are the rights to perform a specific action, such as updating or deleting a table, do not provide database users more privileges than are necessary. For an introduction to managing privileges, see "About User Privileges and Roles" in 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 should revoke unnecessary privileges and roles from the PUBLIC role. The PUBLIC role is automatically assumed by every database user account. By default, it has no privileges assigned to it, but it does have grants to many Java objects. You cannot drop the PUBLIC role, and a manual grant or revoke of this role to a user account has no meaning, because the user account will always assume this role. Because all database user accounts assume the PUBLIC role, it does not appear in the DBA_ROLES and SESSION_ROLES data dictionary views.
Because all users have the PUBLIC role, any database user can exercise privileges that are granted to this role. These privileges include, potentially enabling someone with minimal privileges to access and execute functions that this user would not otherwise be permitted to access directly.
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 "Administering Roles" in Oracle Database 2 Day DBA. In addition, see "Example: Creating a Role" in Oracle Database 2 Day DBA.
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.
For example, it is imperative to strictly limit the privileges of user SCOTT, because this is a well known default user account that may be vulnerable to intruders. Because the CREATE DBLINK privilege allows access from one database to another, drop its privilege for SCOTT. Then, drop the entire role for the user, because privileges acquired through a role cannot be dropped individually. Recreate your own role with only the privileges needed, and grant that new role to that user. Similarly, for even better security, drop the CREATE DBLINK privilege from all users who do not require it.
A secure application role is a role that can be enabled only by an authorized PL/SQL package. The PL/SQL package itself reflects the security policies necessary to control access to the application.
This section contains:
A secure application role is a role that can be enabled only by an authorized PL/SQL package. 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.
Do not grant the role directly to the user; the PL/SQL package will do that for you. However, if the policy for your site is to grant roles to users, you can grant the secure application role to the user if you alter the user account to not have any default roles. For example:
ALTER USER psmith DEFAULT ROLE NONE;
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. Invoker's rights enable the user to have EXECUTE privileges on all objects that the package accesses. An invoker's right procedure executes with the privileges of the current user, that is, the user who invokes the procedure. These 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 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 EXECUTE privileges 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 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.
This tutorial shows how two employees, Matthew Weiss and Winston Taylor, try to gain information from the OE.ORDERS table. Access rights to this table are defined in the EMPLOYEE_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.
In this tutorial:
Step 5: Create the PL/SQL Procedure to Set the Secure Application Role
Step 6: Grant EXECUTE Privileges for the Procedure to Matthew and Winston
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:
Start Database Control.
See Oracle Database 2 Day DBA for instructions about how to start Database Control.
Enter an administrator user name (for example, SYSTEM) and password, and then click Login.
The Database Home page appears.
Click Server to display the Server subpage.
Under Security, select Users.
The Users page appears.
Click Create.
The Create User page appears.
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: SYSTEM
Temporary Tablespace: TEMP
Status: UNLOCKED
Click System Privileges to display the System Privileges subpage.
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
SELECT ANY DICTIONARY
Click OK.
Under Admin Option, do not select the boxes.
Click OK.
Matthew and Winston both are sample employees in the HR.EMPLOYEES schema, which provides columns for the manager ID and e-mail 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.
In Database Control, select the Database Instance link to display the Database Home page.
If you are not logged in to Database Control, see Oracle Database 2 Day DBA for instructions about how to start Database Control. In the Login page, enter an administrator user name (for example, SYSTEM) and password, and then click Login.
Click Server to display the Server subpage.
Under Security, select Users.
The Users page appears.
Click Create.
The Create User page appears.
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 to display the System Privileges subpage.
Click Edit List.
The Modify System Privileges page appears.
In 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.
The Users page appears.
Select 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".
Click OK.
You do not need to grant wtaylor the CREATE SESSION privilege, because the Create Like action has done of this for you.
Exit Database Control.
Now both Matthew Weiss and Winston Taylor have user accounts that have identical privileges.
Now, you are ready to create the employee_role secure application role. To do so, you must log on as the security administrator sec_admin. "Step 1: Create a Security Administrator Account" explains how to create the sec_admin account.
To create the secure application role:
Start SQL*Plus and log on as the security administrator sec_admin.
SQLPLUS sec_admin
Enter password: password
SQL*Plus starts, connects to the default database, and then displays a prompt.
SQL>
For detailed information about starting SQL*Plus, see Oracle Database 2 Day DBA.
Create the following secure application role:
CREATE ROLE employee_role IDENTIFIED USING sec_roles;
The IDENTIFIED USING clause sets the role to be enabled (or disabled) only within the associated PL/SQL package, in this case, sec_roles. At this stage, the sec_roles PL/SQL package does not need to exist.
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 sys/as sysdba Enter password: sys_password PASSWORD OE Changing password for OE New password: password Retype new password: password Password changed. CONNECT oe Enter password: password
Enter the following statement to grant the EMPLOYEE_ROLE role SELECT privileges on the OE.ORDERS table.
GRANT SELECT ON OE.ORDERS TO employee_role;
Do not grant the role directly to the user. The PL/SQL package will do that for you, assuming the user passes its security policies. If your site requires that you directly grant users the role, then you must disable the role for that user. This is because the role must be initially disabled before the security policies in the package can begin performing their checks. For example, to disable the role for user wsmith (assuming wsmith was granted the role in the first place), enter the following statement:
ALTER USER wsmith DEFAULT ROLE NONE;
You are almost ready to create the procedure that determines who is granted the employee_role role. The procedure will grant the employee_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 use an existing application table as the lookup table. For this tutorial, you create your own lookup table that only contains the employee names, employee IDs, and their manager IDs.
To create the HR.HR_VERIFY lookup table:
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 sys/as sysdba Enter password: password ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password; CONNECT hr Enter password: password
Enter the following CREATE TABLE SQL statement to create the lookup table:
CREATE table hr_verify AS SELECT employee_id, first_name, last_name, email, manager_id FROM employees; /
Grant EXECUTE privileges for this table to mweiss and wtaylor by entering the following SQL statements:
GRANT SELECT ON hr.hr_verify TO mweiss; GRANT SELECT ON hr.hr_verify TO wtaylor; GRANT SELECT ON hr.hr_verify TO sec_admin;
Now, you are ready to 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, create them in a package.
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 SQL*Plus, connect as user sec_admin.
CONNECT sec_admin
Enter password: password
Enter the following CREATE PROCEDURE statement to create the secure application role procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE OR REPLACE procedure sec_roles 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 employee_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 example:
Line 1: Appends the AUTHID CURRENT_USER clause to the CREATE PROCEDURE statement, which creates the procedure using invoker's rights. The AUTHID CURRENT_USER clause creates the package using invoker's rights, using the privileges of the current user.
You must create the package using invoker's rights for the package to work. Invoker's rights allow the user to have EXECUTE privileges on all objects that the package 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.
Line3: Declares the v_user variable, which will store the user session information.
Line 4: Declares the v_manager_id variable, which will store the manager's ID of the v_user user.
Line 6: 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 the write 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.
Lines 7–8: 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 table for the manager ID of the current user.
Lines 9–13: Use an IF condition to test whether or not the user should be granted the sec_roles 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.
Lines 10–12: Within the IF condition, the THEN condition grants the role by executing immediately the SET ROLE statement. Otherwise, its ELSE condition denies the grant.
Lines 14–15: Use an EXCEPTION statement to set v_manager_id to 0 if no data is found.
Line 16: Copies the manager's ID into a buffer so that it is readily available.
At this stage, Matthew and Winston can try to access the OE.ORDERS table, but they are not able to, even if they should. The next step is to grant them EXECUTE privileges on the sec_roles procedure, so that the sec_roles 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 sec_roles procedure:
In SQL*Plus, as user sec_admin, enter the following GRANT SQL statements:
GRANT EXECUTE ON sec_admin.sec_roles TO mweiss; GRANT EXECUTE ON sec_admin.sec_roles TO wtaylor;
You are ready to test the employee_role secure application role by logging 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 sec_roles procedure must be executed for the role verification to take place.
To test the employee_role secure application role, as user MWEISS:
Connect as user mweiss.
CONNECT mweiss
Enter password: password
Enter the following SQL statement to run the sec_roles procedure:
EXEC sec_admin.sec_roles;
This statement executes the sec_roles procedure for the current session.
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
Now, Winston will try to access the secure application.
To test the employee_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 sec_roles procedure:
EXEC sec_admin.sec_roles;
This statement executes the sec_roles procedure for the current session.
Perform the following SELECT statement on the OE.ORDERS table:
SELECT COUNT(*) FROM OE.ORDERS;
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.
ERROR at line 1: ORA-00942: table or view does not exist
Remove the components that you created for this tutorial.
In SQL*Plus, connect as SYS with the SYSDBA privilege.
CONNECT SYS/AS SYSDBA
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 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 employee_role; DROP PROCEDURE sec_roles;
Connect as user HR, and then drop the HR_VERIFY table.
CONNECT HR
Enter password: password
DROP TABLE hr_verify;
Exit SQL*Plus.
EXIT
Table 4-1 lists initialization parameters that you can use to secure user privileges.
Table 4-1 Initialization Parameters Used for Privilege Security
| Initialization Parameter | Default Setting | Description |
|---|---|---|
|
|
|
Controls restrictions on |
|
|
Determines whether Oracle or the operating system identifies and manages the roles of each user name. |
|
|
|
Specifies the maximum number of database roles that users can enable, including roles contained within other roles. |
|
|
|
Specifies whether or not operating system roles are allowed for remote clients. The default value, |
|
|
|
Specifies whether or not users must be granted the |
To modify an initialization parameter, see "Modifying the Value of an Initialization Parameter". For detailed information about initialization parameters, see Oracle Database Reference and Oracle Database Administrator's Guide.