NEWBIE: granting permission to entire schema (Oracle8) 
Author Message
 NEWBIE: granting permission to entire schema (Oracle8)
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



Mon, 29 Oct 2001 03:00:00 GMT
 NEWBIE: granting permission to entire schema (Oracle8)

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--------

Quote:

> 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



Tue, 30 Oct 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Replicating an entire schema

2. How do I read an entire database schema into a dataset

3. Reorgnization of a schema or the entire database

4. Reorgnization of a schema or the entire database

5. Reorgnization of a schema or the entire database

6. Quick way to replace an entire database schema?

7. Restoring entire DB with permissions/sp's/etc on another machine with a different name

8. Restoring entire DB with permissions/sp's/etc on another machine with a different name

9. EXEC Permission on a entire DB

10. Grants to Public Role ON INFORMATION SCHEMA

11. Set current-schema and granting

12. automatically granting access to schema objects???


 
Powered by phpBB® Forum Software