Re: NEWBIE: granting permission to entire schema (Oracle8)

From: David Pattinson <david_at_addease.com.au>
Date: Fri, 14 May 1999 10:24:43 +1000
Message-ID: <373B6D4B.B71E7F7E@addease.com.au>

David,

Try the following stored procedure, replace 'THE_SCHEMA_OWNER' with your schema owner user name. I wrote this to support the creation and maintenance of database users from within our application. I put this and other similar procedures inside an admin package. You can use similar procs to create synonyms, re-compile invalid pl/sql and re-enable constraints after loading data.

Let me know how it goes.

Regards, David.

----syntax begins here----

--GRANT RIGHTS TO ALL NON-ADMIN OBJECTS IN THE DATABASE OWNED BY
THE_SCHEMA_OWNER TO
--THE NOMINATED ROLE OR USER
--

PROCEDURE sp_admin_grantObjectRights(p_grantName IN varchar2) IS

v_cursorID INTEGER ;
v_statement VARCHAR2(400) ;
v_rights  VARCHAR(200) ;

--GET THE LIST OF TABLES, VIEWS, PACKAGES, PROCEDURES AND FUNCTIONS OWNED BY
"THE_SCHEMA_OWNER"
cursor constr_cursor is
 select *
 from all_objects
 where owner = 'THE_SCHEMA_OWNER'
 and object_type in ('VIEW', 'TABLE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
 and object_name not IN ('SP_ADMIN_GRANTOBJECTRIGHTS');

constr_val constr_cursor%rowtype;

begin
 open constr_cursor ;
 --LOOP THROUGH THE OBJECTS ONE BY ONE
 loop
  begin
  fetch constr_cursor into constr_val ;   exit when constr_cursor%NOTFOUND;
  v_cursorID := DBMS_SQL.OPEN_CURSOR;

  --BUILD THE COMMAND
  begin
   select DECODE(constr_val.object_type, 'VIEW', 'GRANT SELECT ON ',

      'TABLE', 'GRANT SELECT, UPDATE, INSERT, DELETE ON ',
      'GRANT EXECUTE ON ')

   into v_rights from DUAL;
   v_statement := v_rights||constr_val.object_name||' TO '||p_grantName;    --PARSE THE COMMAND, WHICH ALSO EXECUTES IT AS IT IS DDL    DBMS_SQL.PARSE(v_cursorID, v_statement, DBMS_SQL.NATIVE);    DBMS_SQL.CLOSE_CURSOR(v_cursorID);
  EXCEPTION
   WHEN OTHERS THEN
    --IGNORE INVALID OBJECTS AND CONTINUE     if SQLCODE != -4063 then
     RAISE;
    end if;
  end;
 EXCEPTION
   WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_cursorID);
      RAISE;
 end;
 end loop ;
 close constr_cursor ;
EXCEPTION
 WHEN OTHERS THEN
  close constr_cursor ;
  RAISE;
end sp_admin_grantObjectRights;

----syntax ends here--------

David Thom wrote:

> I've created an application schema under which to do my first development
> work.
>
> Now how do I permit my own userid access to that schema?  I would rather not
> do the development work while logged-in as the schema owner. But I can't see
> how to get Security Manager to let me permit access to anything but one
> Schema object at a time.
>
> And the GRANT command seems to want a specific object, too.
>
> What am I missing here?
>
> Thanks,
>
> David Thom

Received on Thu May 13 1999 - 19:24:43 CDT