Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    20

    Unanswered: Revoke CREATE LIBRARY or CREATE ANY LIBRARY

    Hello people!
    I was wondering how can I find with an sql statement which user (there
    are a lot) has the above priviledges.
    O/S: AIX 5.1
    DB server: Oracle 8.1.7.4

    Thank you

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Arrow Re: Revoke CREATE LIBRARY or CREATE ANY LIBRARY

    Originally posted by demisco
    Hello people!
    I was wondering how can I find with an sql statement which user (there
    are a lot) has the above priviledges.
    O/S: AIX 5.1
    DB server: Oracle 8.1.7.4

    Thank you
    This is one I picked up off MetaLink, that will give you all the roles of the users. I modified it to dump to a spool as a script. When I rebuild a database, I run this first to get all the roles and then as the last step in the rebuild, I regrant the roles back to the user.

    I would suggest that what you do is to create a role that gives the Create Library to who you want to have it, and then revoke the Create Library to all users and Public, then regrant the privilege with a role to those who you want to have it.


    REM
    REM SCRIPT FOR CAPTURING ROLE GRANTS
    REM
    REM This script must be run by a user with the DBA role.
    REM
    REM This script is intended to run with Oracle versions 7.3.X, 8.0.X, and 8.1.X.
    REM
    REM Running this script will in turn create a script of all the grants
    REM of roles to users and other roles. This created script, grant_roles.sql,
    REM must be run by a user with the DBA role.
    REM
    REM Since role grants are not dependant on the schema that issued the grant,
    REM the grant_roles.sql script will not issue the grant of a role by the
    REM original grantor. All grants will be issued by the user specified when
    REM running this script.
    REM
    REM NOTE: Grants made to 'SYS' are not captured.
    REM
    REM Only preliminary testing of this script was performed. Be sure to test
    REM it completely before relying on it.
    REM
    REM @D:\BILDPROD\DETERMINE_ROLES.SQL

    CONNECT INTERNAL/********;
    SET TERMOUT OFF
    SET TIMING OFF
    SET VERIFY OFF
    SET FEEDBACK OFF

    SET ECHO OFF;
    SET PAGESIZE 0

    set termout on
    select 'Creating role grant script...' from dual;
    set termout off

    SPOOL D:\BILDPROD\REGRANT_ROLES.SQL
    SELECT 'CONNECT INTERNAL/******;' AS REM FROM DUAL;
    SELECT 'SPOOL D:\BILDPROD\REGRANT_ROLES.TXT' AS REM FROM DUAL;

    select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
    decode(admin_option,'YES',' WITH ADMIN OPTION;',';') AS REM
    from sys.dba_role_privs
    where grantee != 'SYS'
    order by grantee
    /
    SELECT 'SPOOL OFF' AS REM FROM DUAL;
    SELECT 'EXIT' AS REM FROM DUAL;
    SPOOL OFF

    EXIT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •