Script to include users in the PRODUCT_USER_PROFILE table

This script generates a script that includes users in the table PRODUCT_USER_PROFILE table.

This script generates a script that includes users in the table PRODUCT_USER_PROFILE table. In this way you can avoid entering them with SQL*PLUS. This tip is useful because DBAs can disable certain SQL and SQL*Plus commands on a user. It thus provides product-level security that supplements user-level security. It has been tested on versions 7.x and 8.x.

rem 
rem
rem Este scritp genera otro script que incluye a los usuarios en la tabla
rem PRODUCT_USER_PROFILE excepto el usuario SYS y SYSTEM.
rem
rem
rem El script debe ser corrido con privilegios de DBA.
rem
rem Creado el 09/10/02 Freddy Duran, ORACLE DBA. 
rem
set verify off
set pagesize 0
set linesize 150
select 'Creating user build script...' from dual;

create table u_temp ( lineno NUMBER,
   text VARCHAR2(150));

declare
 cursor user_cursor is select
  username
  from sys.dba_users
  where username not exists in (select distinct(userid) from
product_user_profile
  order by username;

 lv_username  sys.dba_users.username%TYPE;
 lv_string  varchar2(150);
 lv_lineno  number;
 
 procedure write_out is
 begin
  lv_lineno := lv_lineno + 1;
  insert into u_temp (lineno, text) values (lv_lineno, lv_string);
 end;

begin
 lv_lineno := 0;
 open user_cursor;
 loop
  fetch user_cursor into
   lv_username;
  exit when user_cursor%NOTFOUND;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''SELECT'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''DELETE'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''UPDATE'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''INSERT'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''DROP'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''EXECUTE'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''GRANT'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''HOST'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''LOCK'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''RENAME'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''REVOKE'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''RUN'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''SET'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''START'',''DISABLED'',NULL);';
  write_out;
lv_string := 'INSERT into
product_user_profile(product,userid,attribute,char_value,date_value) values
(''SQL*Plus'','||chr(39)||lv_username||chr(39)||',''TRUNCATE'',''DISABLED'',
NULL);';
  write_out;
  lv_string := 'COMMIT;';
 end loop;
 close user_cursor;
end;
/

set heading off
spool c:product_user_profile.sql
select text from u_temp order by lineno;
spool off
drop table u_temp;
select 'Created product_user_profile.sql...' from dual;

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in December 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close