Message-Id: <10509.106913@fatcity.com> From: Bruce Page Date: Fri, 26 May 2000 14:29:38 -0500 Subject: Revoking access This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFC748.DF3D25C4 Content-Type: text/plain; charset="iso-8859-1" Yesterday I asked anyone if they had anything for a non DBA to revoke the DBA role from a user id in Oracle 7.3.4. I heard back from 1 person that led me in this direction. (Thanks Ed) Incase anyone is interested this is what I came up with, so I thought I would share it. The procedure needs to be build as SYS and then you need to grant execute to the non power user or role. CREATE OR REPLACE PROCEDURE remove_access (userid IN VARCHAR2) IS /* */ /* REVOKE_ACCESS */ /* This procedure is to remove all roles and system */ /* privileges from the user id that is passed in. */ /* */ /* execute revoke_access('xxxx') */ /* where xxxx is the user id to have roles and */ /* system privileges revoked from. */ /* */ c1 INTEGER; stmt varchar2(50); ret INTEGER; grnted_role dba_role_privs.granted_role%TYPE; grnted_priv dba_sys_privs.privilege%TYPE; cursor user_roles(grntee varchar2) is select granted_role from dba_role_privs where grantee = upper(grntee); cursor user_privs(grntee varchar2) is select privilege from dba_sys_privs where grantee = upper(grntee) and privilege like '%SESSION%'; BEGIN FOR role_rec IN user_roles(userid) LOOP c1 := DBMS_SQL.OPEN_CURSOR; stmt := 'revoke '||role_rec.granted_role||' from ' || userid; DBMS_SQL.PARSE(c1, stmt, DBMS_SQL.native); ret := DBMS_SQL.EXECUTE(c1); DBMS_SQL.CLOSE_CURSOR(c1); END LOOP; FOR syspriv_rec IN user_privs(userid) LOOP c1 := DBMS_SQL.OPEN_CURSOR; stmt := 'revoke '||syspriv_rec.privilege||' from ' || userid; DBMS_SQL.PARSE(c1, stmt, DBMS_SQL.native); ret := DBMS_SQL.EXECUTE(c1); DBMS_SQL.CLOSE_CURSOR(c1); END LOOP; END; / Bruce Page Oracle DBA Kimball international Jasper, In 47549 ------_=_NextPart_001_01BFC748.DF3D25C4 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Revoking access

Yesterday I asked anyone if they had = anything for a non DBA to revoke the DBA role from a user id in Oracle = 7.3.4.  I heard back from 1 person that led me in this direction. = (Thanks Ed)  Incase anyone is interested this is what I came up = with, so I thought I would share it.  The procedure needs to be = build as SYS and then you need to grant execute to the non power user = or role.


CREATE OR REPLACE PROCEDURE = remove_access (userid IN VARCHAR2) IS
/*         &= nbsp;           &= nbsp;           &= nbsp;           &= nbsp;      */
/*  = REVOKE_ACCESS          = ;            = ;            = ;   */
/*  This procedure is to remove = all roles and system  */
/*  privileges from the user id = that is passed in.    */
/*         &= nbsp;           &= nbsp;           &= nbsp;           &= nbsp;      */
/*  execute = revoke_access('xxxx')        &nb= sp;            = */
/*    where xxxx is = the user id to have roles and     */
/*         &= nbsp;     system privileges revoked = from.      */
/*         &= nbsp;           &= nbsp;           &= nbsp;           &= nbsp;      */

    = c1    INTEGER;
    stmt  = varchar2(50);
    ret   = INTEGER;
    grnted_role = dba_role_privs.granted_role%TYPE;
    grnted_priv = dba_sys_privs.privilege%TYPE;
    cursor = user_roles(grntee varchar2) is select granted_role
         &nb= sp;      from dba_role_privs
         &nb= sp;      where grantee =3D = upper(grntee);
    cursor = user_privs(grntee varchar2) is select privilege
         &nb= sp;      from dba_sys_privs
         &nb= sp;      where grantee =3D = upper(grntee)
         &nb= sp;        and privilege like = '%SESSION%';
BEGIN
   FOR role_rec IN = user_roles(userid) LOOP
       = c1   :=3D DBMS_SQL.OPEN_CURSOR;
       = stmt :=3D 'revoke '||role_rec.granted_role||' from ' || userid;
       = DBMS_SQL.PARSE(c1, stmt, DBMS_SQL.native);
       = ret :=3D DBMS_SQL.EXECUTE(c1);
       = DBMS_SQL.CLOSE_CURSOR(c1);
   END LOOP;
  
   FOR syspriv_rec IN = user_privs(userid) LOOP
       = c1   :=3D DBMS_SQL.OPEN_CURSOR;
       = stmt :=3D 'revoke '||syspriv_rec.privilege||' from ' || userid;
       = DBMS_SQL.PARSE(c1, stmt, DBMS_SQL.native);
       = ret :=3D DBMS_SQL.EXECUTE(c1);
       = DBMS_SQL.CLOSE_CURSOR(c1);
   END LOOP;
END;
/


Bruce Page
Oracle DBA