Originally posted by demisco
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 188.8.131.52
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 SCRIPT FOR CAPTURING ROLE GRANTS
REM This script must be run by a user with the DBA role.
REM This script is intended to run with Oracle versions 7.3.X, 8.0.X, and 8.1.X.
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 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 NOTE: Grants made to 'SYS' are not captured.
REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
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
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
where grantee != 'SYS'
order by grantee
SELECT 'SPOOL OFF' AS REM FROM DUAL;
SELECT 'EXIT' AS REM FROM DUAL;