Revoking access

From: Bruce Page <bpage_at_kimball.com>
Date: Fri, 26 May 2000 14:29:38 -0500
Message-Id: <10509.106913@fatcity.com>

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>Revoking access</TITLE>
</HEAD>
<BODY>

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 
 <FONT SIZE=3D2 =

FACE=3D"Arial">/*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */</FONT>

 /*  =

REVOKE_ACCESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=

;   */
 /*  This procedure is to remove =
all roles and system  */
 /*  privileges from the user id =
that is passed in.    */
 <FONT SIZE=3D2 =

FACE=3D"Arial">/*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */</FONT>

 /*  execute =
revoke_access('xxxx')        &nb= sp;            = */
 /*    where xxxx is =
the user id to have roles and     */
 <FONT SIZE=3D2 =

FACE=3D"Arial">/*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp; system privileges revoked =
from.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */</FONT>

 <FONT SIZE=3D2 =

FACE=3D"Arial">/*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */</FONT>

    =
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
Received on Fri May 26 2000 - 14:29:38 CDT