Re: Roles

From: g3000 <carlton_gregory_at_yahoo.com>
Date: 23 Feb 2005 11:59:31 -0800
Message-ID: <1109188771.201775.66710@o13g2000cwo.googlegroups.com>

CONNECT SYS/PASS AS SYSDBA

DROP ROLE SAM_BASE;
DROP ROLE SAM_SECURITY;
DROP ROLE SAM_USER;
DROP ROLE SAM_ADMIN;

DROP USER SAM CASCADE;
CREATE USER SAM IDENTIFIED BY SAM
DEFAULT TABLESPACE sams_tabs
TEMPORARY TABLESPACE sams_temp

QUOTA UNLIMITED ON sams_audit
QUOTA UNLIMITED ON sams_indx
QUOTA UNLIMITED ON sams_interface
QUOTA UNLIMITED ON sams_lob
QUOTA UNLIMITED ON sams_tabs

/

GRANT ALTER SESSION TO SAM;

GRANT ALTER USER                        TO SAM WITH ADMIN OPTION;
GRANT CREATE SESSION                    TO SAM WITH ADMIN OPTION;
GRANT CREATE TABLE                      TO SAM;
GRANT CREATE PROCEDURE                  TO SAM;
GRANT CREATE VIEW                       TO SAM;
GRANT SELECT ON SYS.DBA_ROLE_PRIVS      TO SAM WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_USERS           TO SAM;
GRANT SELECT ON SYS.V_$INSTANCE         TO SAM;
GRANT SELECT ON SYS.V_$PARAMETER        TO SAM;
GRANT SELECT ON SYS.V_$SESSION          TO SAM;

DROP TABLE SAM.SAM_ROLES;
CREATE TABLE SAM.SAM_ROLES
(

  MODULE_ID    VARCHAR2(2),
  PASSWORD     VARCHAR2(30)                     NOT NULL,
  ROLE         VARCHAR2(30)                     NOT NULL,
  VISIBLE_IND  VARCHAR2(1)                      DEFAULT 'N'
      NOT NULL

)
/

Insert into SAM.SAM_ROLES

   (module_id, password, role, visible_ind)  Values
   ('SM', 'ADMIN4', 'SAM_ADMIN', 'N');
Insert into SAM.SAM_ROLES

   (module_id, password, role, visible_ind)  Values
   (NULL, 'USER2', 'SAM_USER', 'N');
Insert into SAM.SAM_ROLES

   (module_id, password, role, visible_ind)  Values
   (NULL, 'SECURITY2', 'SAM_SECURITY', 'N');

CREATE ROLE SAM_BASE;
CREATE ROLE SAM_SECURITY IDENTIFIED BY SECURITY2;
CREATE ROLE SAM_USER     IDENTIFIED BY USER3;
CREATE ROLE SAM_ADMIN    IDENTIFIED BY ADMIN4;
GRANT SAM_BASE TO SAM WITH ADMIN OPTION;

GRANT SAM_SECURITY TO SAM WITH ADMIN OPTION; GRANT SAM_ADMIN TO SAM WITH ADMIN OPTION; ALTER USER SAM DEFAULT ROLE SAM_BASE, SAM_SECURITY, SAM_ADMIN; CREATE OR REPLACE FORCE VIEW SAM.SM_SAM_USER_SECURITY_VW (ROLE, PASSWORD, MODULE_ID, SECURITY_LEVEL, VISIBLE_IND) AS
SELECT sr.role AS role,
sr.password AS password,
sr.module_id AS module_id,
SUBSTR(role, INSTR(role, '_') + 1, 1) AS security_level, visible_ind AS visible_ind
 FROM sam_roles sr, dba_role_privs urp
  WHERE sr.role = urp.granted_role
AND role <> 'SAM_SECURITY'
AND urp.grantee = USER
/

GRANT CREATE SESSION TO SAM_BASE; CREATE OR REPLACE PACKAGE SAM.SAM_ADMIN_LIB IS PROCEDURE grantRole (

    uname IN VARCHAR2,
    rname IN VARCHAR2 );

PROCEDURE enableUser (

    uname IN VARCHAR2 );
END SAM_ADMIN_LIB;
/

CREATE OR REPLACE PACKAGE BODY SAM.SAM_ADMIN_LIB IS

admin_role             CONSTANT VARCHAR2(10)  := 'SAM_ADMIN';
default_roles          CONSTANT VARCHAR2(100) := 'SAM_BASE,
SAM_SECURITY';
user_role              CONSTANT VARCHAR2(10)  := 'SAM_USER';
InvalidUser            EXCEPTION;
InvalidAccessLevel     EXCEPTION;
NoRole                 EXCEPTION;
PRAGMA                 EXCEPTION_INIT (NoRole, -1951);

PROCEDURE grantRole (

    uname IN VARCHAR2,
    rname IN VARCHAR2)
IS

    invalid_role EXCEPTION;
    cnt NUMBER;
BEGIN
    EXECUTE IMMEDIATE
        'GRANT '||rname||' TO "'||uname||'"'; EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, SQLERRM); END;
PROCEDURE enableUser (

    uname IN VARCHAR2)
IS
BEGIN

• Grant default role(s) EXECUTE IMMEDIATE 'GRANT '||default_roles||' TO "'||uname||'"'; EXECUTE IMMEDIATE 'ALTER USER "'||uname||'" DEFAULT ROLE SAM_BASE'; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000, SQLERRM); END; END SAM_ADMIN_LIB;
  /
  CREATE OR REPLACE PACKAGE SAM.SAM_USER_LIB AUTHID CURRENT_USER IS
  /********************************************************************************
  * DOLOGIN * * This function performs all necessary login activities for the calling user * * It calls enableUserRoles and sends login information to the audit trail. * *-------------------------------------------------------------------------------* * @param module_code - the module that the user is logging into. * * @param role_password - the password for the SAMS_ROLES_ROLE role. * * @return NUMBER - a success status code (needed for PB). * *-------------------------------------------------------------------------------* * 29 AUG 2002 - Added processing for WORKSTATION. * ********************************************************************************/ FUNCTION doLogin ( module_code IN VARCHAR2, role_password IN VARCHAR2, work_station IN VARCHAR2) RETURN NUMBER; END SAM_USER_LIB;
  /
  CREATE OR REPLACE PACKAGE BODY SAM.SAM_USER_LIB IS
  /********************************************************************************
  * SAMS_USER_LIB package body * ********************************************************************************/
  /********************************************************************************
  * ENABLEUSERROLES * * This procedure enables all granted SAMS roles for the user. * *-------------------------------------------------------------------------------* * @exception ex_no_role - raised if no SAMS roles are assigned to the user. * * @exception others - raised if any error occurs. * *-------------------------------------------------------------------------------* * This procedure uses dynamic SQL in order to prevent cursor caching. * * Otherwise the procedure would fail if called more than once within a session. * ********************************************************************************/ PROCEDURE enableUserRoles ( role_password IN VARCHAR2) IS ex_no_role EXCEPTION; TYPE cv_type IS REF CURSOR; role_str VARCHAR2(4000) := ' SAM_BASE '; rp_cur cv_type; rp_rec sam.sm_sam_user_security_vw%ROWTYPE; dr_str CONSTANT VARCHAR2(20) := 'SAM_BASE'; nr_str CONSTANT VARCHAR2(50) := 'No roles have been granted to '||' this user.'; pr_str CONSTANT VARCHAR2(250) := 'SAM_SECURITY '||' IDENTIFIED BY '||role_password; rp_str CONSTANT VARCHAR2(100) := 'SELECT * FROM '||' sam.sm_sam_user_security_vw';

/********************************************************************************

• APPEND *
• Private procedure to append role string. *

********************************************************************************/
    PROCEDURE append (
        string  IN OUT VARCHAR2,
        role_nm IN     VARCHAR2,
        role_pw IN     VARCHAR2)
    IS
        id_str CONSTANT VARCHAR2(30) := ' IDENTIFIED BY ';
    BEGIN
        string := string || role_nm || id_str || role_pw;

    END;
-- PL/SQL Block
BEGIN
    dbms_session.set_role (pr_str);
    DBMS_OUTPUT.PUT_LINE('QUERY VIEW');
    OPEN rp_cur FOR rp_str;
    LOOP

        FETCH rp_cur INTO rp_rec;
        EXIT WHEN rp_cur%NOTFOUND;
        IF (rp_cur%rowCount > 1) THEN
            role_str := role_str || ', ';
        END IF;
        append (role_str, rp_rec.role, rp_rec.password);

    END LOOP;
    CLOSE rp_cur;
    dbms_session.set_role (dr_str);
    IF ((LENGTH(role_str) = 0) OR (role_str IS NULL)) THEN

        RAISE ex_no_role;
    END IF;
    DBMS_OUTPUT.PUT_LINE(role_str);
    dbms_session.set_role (role_str);
EXCEPTION
    WHEN ex_no_role THEN

        RAISE_APPLICATION_ERROR (-20000, nr_str);     WHEN OTHERS THEN

        IF (rp_cur%isOpen) THEN
            CLOSE rp_cur;
        END IF;
        dbms_session.set_role (dr_str);
        RAISE_APPLICATION_ERROR (-20000, SQLERRM);

END;
FUNCTION doLogin (

    module_code IN VARCHAR2,
    role_password IN VARCHAR2,
    work_station IN VARCHAR2)
RETURN NUMBER
IS

    atxt_str CONSTANT VARCHAR2(5) := 'LOGIN';     atyp_str CONSTANT VARCHAR2(1) := 'X'; BEGIN
    enableUserRoles (role_password);
    --sams.sams_audit.setModule (UPPER(module_code), work_station);     --sams.sams_audit.setAction (atxt_str, atyp_str, work_station);     COMMIT;
    RETURN 0;
END;
END SAM_USER_LIB;
/

SHOW ERRORS
CONNECT SAM/SAM@

GRANT EXECUTE                        ON SAM.SAM_ADMIN_LIB
  TO SAM_ADMIN;
GRANT EXECUTE                        ON SAM.SAM_USER_LIB
  TO SAM_BASE;
GRANT                 SELECT         ON SAM.SM_SAM_USER_SECURITY_VW

  TO SAM_SECURITY;
CONNECT SYS/PASS@ AS SYSDBA
DROP USER TEST1;
CREATE USER TEST1 IDENTIFIED BY TEST1;
CONNECT SAM/SAM@
EXEC SAM_ADMIN_LIB.ENABLEUSER('TEST1');
connect test1/test1@
set serveroutput on size 1000000
DECLARE
  X NUMBER;
BEGIN
  X := SAM.SAM_USER_LIB.DOLOGIN('SM','SECURITY2','BC12025'); END;
/

I dont know how well my newsreader will post this code so I apologize for the formatting.
If you dont give the user SAM admin privileges or default on all the roles a user will not be able to login.

This shows you can set a role in a package. My issue was that when I import SAM I drop and cascade first. Then issue a create user sam statement then use import. That limits all the creation errors and ensures the old schema is gone.

Well after doing this and recreating the users that were captured during the export the users could not login. It was because sam did not have admin privs. Received on Wed Feb 23 2005 - 13:59:31 CST