Authorization MethodsObjectivesAfter completing this lesson, you should be able to do the following:Implement rolesImplement the securing of objects through proceduresDescribe how secure application roles workManage roles and users by using secure application rolesAuthorizationAuthorization determines the privileges that the user has in the database. User privileges are set in the database by:System privilegesObject privilegesRolesThere are two types of user privileges:System: Enables users to perform particular actions in the databaseObject: Enables users to access and manipulate a specific objectPrivilegesUsersPrivilegesRolesHR_CLERKHR_MGRRolesNeenaGirardVanceDeleteemployeesSelectemployeesUpdateemployeesInsertemployeesEasier privilege managementDynamic privilege managementSelective availability of privilegesCan be granted through the operating systemBenefits of RolesPredefined RolesCREATE ANY JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULERSCHEDULER_ ADMINNo system privileges, but over 1600 object privileges on the data dictionarySELECT_CATALOG_ROLEMost system privileges, several other roles. Do not grant to nonadministrators.DBACREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATORRESOURCECREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE DATABASE LINK, CREATE CLUSTER, ALTER SESSIONCONNECTUsing Proxy Authentication with RolesSpecify roles that the proxy is allowed to activate:Prevent any roles from being activated by the proxy:ALTER USER phall GRANT CONNECT THROUGH hruser WITH ROLE hr_clerk;ALTER USER phall GRANT CONNECT THROUGH appsrv WITH NO ROLES;Securing Objects with ProceduresObject access can be strictly controlled through procedures.The object owner creates procedures and functions to access the object.Users are granted the EXECUTE privileges on program units.Users do not have direct access to objects.Secure Application RoleThe secure application role solves the problem of preventing unauthorized access to data through other client programs.It is better than the previous mechanism with a hidden password.It uses the same SYS_CONTEXT mechanism as Virtual Private Database.Enabling a role is checked through a package,and not a password.Implementing a Secure Application Role1.Create the role.2.Create the package that sets the role:a. Create the package specification.b. Create the package body.3.Grant the execute privilege on the package.4.Write the application server code that sets the role.CREATE ROLE oe_sales_rep IDENTIFIED USING secure.oe_roles;Step 1: Create the RoleThe CREATE ROLE command identifies the package that sets the role.The package does not need to exist.Example:CREATE OR REPLACE PACKAGE oe_roles AUTHID CURRENT_USERIS PROCEDURE set_sales_rep_role;END;/Step 2.1: Create the Package SpecificationThe OE_ROLES package is referenced in the CREATE ROLE command.The AUTHID CURRENT_USER clause is required to properly set the role.Example:.SELECT id INTO v_id FROM oe.app_roles WHERE username = sys_context (userenv,current_user) AND role = SALES_REP AND ip_address = sys_context (userenv,ip_address); dbms_session.set_role(oe_sales_rep);.Step 2.2: Create the Package BodyGRANT execute ON oe_roles TO appsrv;Step 3: Grant the EXECUTE Privilege on the PackageThe application server connects as the appsrv user. It sets the role after it starts the users session.Example:Step 4: Write the Application Server Code That Sets the RoleWhen starting, the application server:Connects as the APPSRV user Creates a connection poolWhen starting a session for a user, the application server:Gets a connection from the poolStarts a session for the userSets the users roleSet the users role by using:secure.oe_roles.set_sales_rep_role;Data Dictionary ViewsSQL SELECT * 2 FROM dba_application_roles 3 WHERE ROLE = OE_SALES_REP;ROLE SCHEMA PACKAGE- - -OE_SALES_REP SECURE OE_ROLESSQLSummaryIn this lesson, you should have learned how to:Implement rolesImplement the securing of objects through proceduresDescribe how secure application roles workManage roles and users by using secure application roles