Script to create new user with existing DB user Privs:
============================================
userdetails_new.sql
===============
set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept new_sch_password prompt "Enter new user password: "
set serveroutput on
spool CREATE_GRANT_SCH_spool.sql
-- Create user...
select 'create user &&newname identified by &&new_sch_password'||''||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant on base user objects...
select 'grant select,insert,update,delete,index on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('TABLE') and owner = upper('&&oldname');
select 'grant select,insert,update,delete on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('VIEW') and owner = upper('&&oldname');
select 'grant select on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE='SEQUENCE' and owner = upper('&&oldname');
select 'grant execute on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') and owner = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'."'||table_name||'" to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'."'||table_name||'"('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user &&newname quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/
spool off
set pages 1000 feed on veri on lines 500
PROMPT
PROMPT
PROMPT Please Execute : CREATE_GRANT_SCH_spool.sql
PROMPT
PROMPT
gen_synonym_sch_user.sh
===================
echo "Enter Base User Name"
read base_user
echo "Enter new SCH User Name"
read sch_user
${ORACLE_HOME}/bin/sqlplus -s /nolog << SQLEND
conn /as sysdba
set feedback off
set pages 0
set lines 150
set heading off
spool CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql
select 'create SYNONYM ${sch_user}.' || OBJECT_NAME || ' for ' || OWNER||'.'||OBJECT_NAME ||';' from dba_objects where OBJECT_TYPE in ('TABLE','SEQUENCE','PROCEDURE','FUNCTION','VIEW') and owner = upper('${base_user}');
select 'create SYNONYM ${sch_user}.' || SYNONYM_NAME || ' for ' || TABLE_OWNER ||'.'||TABLE_NAME||';' from dba_SYNONYMs where OWNER=upper('${base_user}');
spool off
SQLEND
chmod 777 CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql
echo " "
echo " "
echo "Please Execute : CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql "
echo " "
echo " "
For OTM Users
=============
create user otmgreportdev_sch identified by otmgreportdev#12sch;
grant administer database trigger to otmgreportdev_sch;
grant create session to otmgreportdev_sch;
grant create trigger to otmgreportdev_sch;
connect glogowner/glogowner@otmtest
PROMPT
@@../script8/chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
grant execute on vpd to otmgreportdev_sch;
grant execute on GLOG_UTIL to otmgreportdev_sch;
Connect reportowner/reportowner@otmtest
PROMPT
@@chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
connect otmgreportdev_sch/author_otm@otmtest
CREATE or replace TRIGGER set_vpd_trig AFTER LOGON ON DATABASE
BEGIN
vpd.set_user('DBA.ADMIN');
END;
/
or
create user otmgreportdev_sch identified by otmgreportdev#12sch
grant administer database trigger to otmgreportdev_sch;
grant create session to otmgreportdev_sch;
grant create trigger to otmgreportdev_sch;
connect glogowner/glogowner@otmtest
PROMPT
@@../script8/chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
grant execute on vpd to otmgreportdev_sch;
grant execute on GLOG_UTIL to otmgreportdev_sch;
Connect reportowner/reportowner@otmtest
PROMPT
@@chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
connect otmgreportdev_sch/author_otm@otmtest
CREATE or replace TRIGGER set_vpd_trig AFTER LOGON ON DATABASE
BEGIN
vpd.set_user('DBA.ADMIN');
END;
/
No comments:
Post a Comment