Tag Archives: Oracle

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing of user’s connections

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article, we will discuss the auditing of connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail and audit_sys_operations parameters are set to TRUE.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL> alter system set audit_trail=TRUE scope=spfile;

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single user’s connections; however it is easy to see the power that Oracle auditing can provide.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate Oracle View

Leave a comment

Oracle provides views to allow you to hide the structures of under lying tables, to combine multiple tables into a single logical structure, and to reduce the complexity of SQL being generated against a database. Views allow the developer the flexibility to give users the look and feel of the database they desire, while maintaining a normalized structure under the covers. In this article we will review the SQL to generate a view’s DDL for review or transport.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 20 23:51:33 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Logon as the user SCOTT and generate the view TEST_VIEW to demonstrate the DDL extraction.

SQL> grant create view to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create view TEST_VIEW as
2 select emp.ename as employee, dept.dname as department, bonus.sal as salary
from
3 dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

View created.

SQL>

3. Execute the following PL/SQL block to generate your DDL.

set serveroutput on
declare

v_ct number;
v_owner varchar2(30):=’SCOTT’;

begin

for v_view in(select view_name, text from dba_views where owner=v_owner)
loop

dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
dbms_output.put_line(v_view.text||’;’);

end loop;
end;
/

SQL> connect / as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> declare
2
3 v_ct number;
4 v_owner varchar2(30):=’SCOTT’;
5
6 begin
7
8 for v_view in(select view_name, text from dba_views where owner=v_owner)
9 loop
10
11 dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
12 dbms_output.put_line(v_view.text||’;’);
13
14 end loop;
15 end;
16 /
create view TEST_VIEW as
select emp.ename as employee, dept.dname as department, bonus.sal as salary from
dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

PL/SQL procedure successfully completed.

SQL>
SQL>

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing insert, update, and delete on a table

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article we will discuss the auditing of insert, update, and deletes by user account access.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 03 19:18:21 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail is set to DB_EXTENDED and audit_sys_operations parameter is set to TRUE. Note: It is important to realize that setting the AUDIT_TRAIL to DB_EXTENDED will dramatically increase storage, so monitor this closely.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Execute the audit command below to begin monitoring changes to the table SCOTT.DEPT.

SQL> audit insert, update, delete on scott.dept by session;

Audit succeeded.

SQL>

6. Connect to the database as the user LJCATT and perform a insert, update and delete operation on the table scott.dept.

SQL> connect ljcatt/ljcatt
Connected.
SQL> insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
;

1 row created.

SQL> commit;

Commit complete.

SQL> update scott.dept set loc=’WASHINGTON’ where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from scott.dept where deptno=50;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

7. Login as sysdba again.

SQL> connect sys as sysdba
SQL>

8. Execute the following SQL to extract the statements executed by LJCATT.
“select timestamp, sql_text from dba_audit_object where username=’LJCATT’;”

SQL> select timestamp, sql_text from dba_audit_object where username=’LJCATT’;

TIMESTAMP SQL_TEXT
——— ——————————————————-
27-AUG-09 update scott.dept set loc=’WASHINGTON’ where deptno=50
27-AUG-09 insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
27-AUG-09 delete from scott.dept where deptno=50

SQL>

9. That completes the use of Oracle Auditing to monitor the changes to the Oracle database by users.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Disabling and Enabling Oracle Jobs with DBMS_JOB

Leave a comment

Oracle provides the ability to schedule predetermined jobs via the package DBMS_JOB, just as you would in a UNIX environment with CRON or the Windows environment with SCHEDULER. Normally, a production database will have several dozen pre-scheduled jobs executing at various times of the day. There are times when you will need to disable a JOB or all jobs due to various events. This article explains the use of procedure DBMS_JOB.BROKEN which allows you to disable a job and re-enable it once you are finished. This procedure will work in Oracle 8, 8i, 9i, 10g, and 11g.

NOTE: only the user whom own a job can modify with the package DBMS_JOB, if you logon to the database as an administrator and want to disable jobs you must use the package DBMS_IJOB.

1. Logon to SQL*PLUS as sysdba

Mylinux> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.3.0 – Production on Tue May 25 10:19:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. To disable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,TRUE);’
———————————————————————
exec dbms_ijob.broken(1,true);
exec dbms_ijob.broken(2,true);
exec dbms_ijob.broken(4,true);
exec dbms_ijob.broken(5,true);
exec dbms_ijob.broken(41,true);
exec dbms_ijob.broken(42,true);
exec dbms_ijob.broken(370,true);
exec dbms_ijob.broken(371,true);
exec dbms_ijob.broken(1605,true);
exec dbms_ijob.broken(2204,true);
exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);

13 rows selected.

SQL>

3. Execute the SQL generated by the step above to disable all jobs.

exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

4. To re-enable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,FALSE);’
———————————————————————-
exec dbms_ijob.broken(1,false);
exec dbms_ijob.broken(2,false);
exec dbms_ijob.broken(4,false);
exec dbms_ijob.broken(5,false);
exec dbms_ijob.broken(41,false);
exec dbms_ijob.broken(42,false);
exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
exec dbms_ijob.broken(2224,false);
exec dbms_ijob.broken(2225,false);
exec dbms_ijob.broken(2245,false);

5. Execute the SQL generated by the step above to enable all jobs.

exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

6. This completes the disablement and enablement of jobs with DBMS_JOB package.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle RESOURCE role

Leave a comment

Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role RESOURCE.

The RESOURCE role is used to give an individual the right to create any object within his/her own schema. This role is normally used to define a developer’s privilege within a test and development system. It allows a developer to build objects necessary to create a system without impacting other developers on the same system. The following instructions define how to create a user account, assign the RESOURCE role to that user and lists the privileges granted by RESOURCE role.

1. Logon to you Oracle database as sysdba.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed May 20 17:16:49 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create a permanent tablespace for your new user.

SQL> create tablespace devl datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/d
evl01.dbf’ size 5000m;

Tablespace created.

SQL>

3. Create a temporary tablespace for you new user.

SQL> create temporary tablespace devl_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/OR
ADATA/ORCL10G/devl_temp01.dbf’ size 2500m;

Tablespace created.

SQL>

4. Create you development user.

SQL> Create user developer01 identified by developer
2 Default tablespace devl
3 Quota unlimited on devl
4 Temporary tablespace devl_temp;

User created.

SQL>

5. You have to grant create session directly to the user developer01. NOTE: “create session” is not part of the RESOURCE role.

SQL> grant create session to developer01;

Grant succeeded.

SQL>

6. Grant the role RESOURCE to the user developer01.

SQL> grant resource to developer01;

Grant succeeded.

SQL>

7. Now you can logon and test the new user account by creating an object.

SQL> connect developer01/developer
Connected.
SQL> create table test1(testing varchar2(30));

Table created.

SQL> select tablespace_name from user_tables where table_name=’TEST1′;

TABLESPACE_NAME
——————————
DEVL

SQL>

8. The following PL/SQL block gives you all of the privileges granted to a specific role where the variable V_USER defines the role.

set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’RESOURCE’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

SQL> set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’RESOURCE’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
31 32 33 34 35 36 37 38 39 40 41 42 grant CREATE TYPE to
RESOURCE;
grant CREATE TABLE to RESOURCE;
grant CREATE CLUSTER to RESOURCE;
grant CREATE TRIGGER to RESOURCE;
grant CREATE OPERATOR to RESOURCE;
grant CREATE SEQUENCE to RESOURCE;
grant CREATE INDEXTYPE to RESOURCE;
grant CREATE PROCEDURE to RESOURCE;
grant CREATE TYPE to RESOURCE;
grant CREATE TABLE to RESOURCE;
grant CREATE CLUSTER to RESOURCE;
grant CREATE TRIGGER to RESOURCE;
grant CREATE OPERATOR to RESOURCE;
grant CREATE SEQUENCE to RESOURCE;
grant CREATE INDEXTYPE to RESOURCE;
grant CREATE PROCEDURE to RESOURCE;

PL/SQL procedure successfully completed.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate the Oracle DDL for a PROFILE

Leave a comment

Oracle uses PROFILES to limit the access and resources a user has within an Oracle database. In a properly secured Oracle environment care must be taken in defining resource limits of your users. This article covers the creation of the DDL to define an Oracle profile already established in one database and recreate it in another.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 19 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create the profile TEST_PROFILE to demonstrate the recreation of the DDL to generate the profile in another database.

SQL> create profile test_profile limit IDLE_TIME 15
2 CONNECT_TIME 30
3 FAILED_LOGIN_ATTEMPTS 3
4 PASSWORD_LIFE_TIME 5
5 PASSWORD_REUSE_TIME 3
6 PASSWORD_REUSE_MAX 3
7 PASSWORD_LOCK_TIME 30
8 PASSWORD_GRACE_TIME 30
9 SESSIONS_PER_USER 2;

Profile created.

SQL>

3. Execute the following PL/SQL block to recreate the DDL for your profile.

set serveroutput on
spool ./create_profile.lst

declare

v_ct number;
v_profile varchar2(30):=’TEST_PROFILE’;
begin

dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);

for v_limit in(select resource_name, limit from dba_profiles
where profile = v_profile)
loop

dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);

end loop;
end;
/

SQL> set serveroutput on
SQL>
SQL> spool ./create_profile.lst
SQL>
SQL> declare
2
3 v_ct number;
4 v_profile varchar2(30):=’TEST_PROFILE’;
5 begin
6
7 dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);
8
9 for v_limit in(select resource_name, limit from dba_profiles
10 where profile = v_profile)
11 loop
12
13 dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);
14
15 end loop;
16 end;
17 /
create profile TEST_PROFILE limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 15
CONNECT_TIME 30
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 5
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 30

PL/SQL procedure successfully completed.

SQL>

4. With the DDL generated you can recreate the TEST_PROFILE in any other Oracle database environment.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, linux, Oracle 10g, oracle 9i, SQL

Oracle DBA role

Leave a comment

Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role DBA.

The DBA role is used to give an individual user the right to administrator an oracle database. This role is normally only granted to users who have a need to view dictionary level views and administrate other user accounts. NOTE: Great care has to be used when assigning the DBA role, because users who have it can perform almost every action in the database short of actually shutting down the system. The following instructions define how to create a user account, assign the DBA role to that user and lists the privileges granted by DBA role.

1. Logon the you Oracle database as sysdba.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 4 21:56:18 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create a permanent tablespace for your new user.

SQL> create tablespace admin datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/admin01.dbf’ size 5m;

Tablespace created.

SQL>

3. Create a temporary tablespace for you new user.

SQL> create temporary tablespace admin_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/O
RADATA/ORCL10G/admin_temp01.dbf’ size 5m;

Tablespace created.

SQL>

4. Create you administrative user.

SQL> Create user admin01 identified by admin
2 Default tablespace admin
3 Quota unlimited on admin
4 Temporary tablespace admin_temp;

User created.

SQL>

5. Grant the role DBA to the user developer01.

SQL> grant dba to admin01;

Grant succeeded.

SQL>

6. Connect to the system as the admin01 user and perform a select from a system view.

SQL> connect admin01/admin
Connected.
SQL> select count(*) from v$session;

COUNT(*)
———-
18

SQL>

7. The PL/SQL block below will list all privileges contained in the role DBA, where the variable V_USER defines the role’s privileges to be displayed.
set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’DBA’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

34
35 36 37 38 39 40 41 42
grant XDBADMIN to DBA;
grant JAVA_ADMIN to DBA;
grant JAVA_DEPLOY to DBA;
grant WM_ADMIN_ROLE to DBA;
grant SCHEDULER_ADMIN to DBA with admin option;
grant EXP_FULL_DATABASE to DBA;
grant IMP_FULL_DATABASE to DBA;
grant DELETE_CATALOG_ROLE to DBA with admin option;
grant SELECT_CATALOG_ROLE to DBA with admin option;
grant EXECUTE_CATALOG_ROLE to DBA with admin option;
grant GATHER_SYSTEM_STATISTICS to DBA;
grant ADVISOR to DBA with admin option;
grant AUDIT ANY to DBA with admin option;
grant DROP USER to DBA with admin option;
grant RESUMABLE to DBA with admin option;
grant ALTER USER to DBA with admin option;
grant CREATE JOB to DBA with admin option;
grant ANALYZE ANY to DBA with admin option;
grant BECOME USER to DBA with admin option;
grant CREATE ROLE to DBA with admin option;
grant CREATE RULE to DBA with admin option;
grant CREATE TYPE to DBA with admin option;
grant CREATE USER to DBA with admin option;
grant CREATE VIEW to DBA with admin option;
grant ALTER SYSTEM to DBA with admin option;
grant AUDIT SYSTEM to DBA with admin option;
grant CREATE TABLE to DBA with admin option;
grant DROP PROFILE to DBA with admin option;
grant ALTER PROFILE to DBA with admin option;
grant ALTER SESSION to DBA with admin option;
grant DROP ANY ROLE to DBA with admin option;
grant DROP ANY RULE to DBA with admin option;
grant DROP ANY TYPE to DBA with admin option;
grant DROP ANY VIEW to DBA with admin option;
grant QUERY REWRITE to DBA with admin option;
grant ALTER ANY ROLE to DBA with admin option;
grant ALTER ANY RULE to DBA with admin option;
grant ALTER ANY TYPE to DBA with admin option;
grant ALTER DATABASE to DBA with admin option;
grant CREATE ANY JOB to DBA with admin option;
grant CREATE CLUSTER to DBA with admin option;
grant CREATE LIBRARY to DBA with admin option;
grant CREATE PROFILE to DBA with admin option;
grant CREATE SESSION to DBA with admin option;
grant CREATE SYNONYM to DBA with admin option;
grant CREATE TRIGGER to DBA with admin option;
grant DROP ANY INDEX to DBA with admin option;
grant DROP ANY TABLE to DBA with admin option;
grant GRANT ANY ROLE to DBA with admin option;
grant LOCK ANY TABLE to DBA with admin option;
grant MERGE ANY VIEW to DBA with admin option;
grant UNDER ANY TYPE to DBA with admin option;
grant UNDER ANY VIEW to DBA with admin option;
grant ALTER ANY INDEX to DBA with admin option;
grant ALTER ANY TABLE to DBA with admin option;
grant CREATE ANY RULE to DBA with admin option;
grant CREATE ANY TYPE to DBA with admin option;
grant CREATE ANY VIEW to DBA with admin option;
grant CREATE OPERATOR to DBA with admin option;
grant CREATE RULE SET to DBA with admin option;
grant CREATE SEQUENCE to DBA with admin option;
grant DROP TABLESPACE to DBA with admin option;
grant UNDER ANY TABLE to DBA with admin option;
grant ALTER TABLESPACE to DBA with admin option;
grant BACKUP ANY TABLE to DBA with admin option;
grant CREATE ANY INDEX to DBA with admin option;
grant CREATE ANY TABLE to DBA with admin option;
grant CREATE DIMENSION to DBA with admin option;
grant CREATE INDEXTYPE to DBA with admin option;
grant CREATE PROCEDURE to DBA with admin option;
grant DELETE ANY TABLE to DBA with admin option;
grant DROP ANY CLUSTER to DBA with admin option;
grant DROP ANY CONTEXT to DBA with admin option;
grant DROP ANY LIBRARY to DBA with admin option;
grant DROP ANY OUTLINE to DBA with admin option;
grant DROP ANY SYNONYM to DBA with admin option;
grant DROP ANY TRIGGER to DBA with admin option;
grant EXECUTE ANY RULE to DBA with admin option;
grant EXECUTE ANY TYPE to DBA with admin option;
grant INSERT ANY TABLE to DBA with admin option;
grant MANAGE ANY QUEUE to DBA with admin option;
grant MANAGE SCHEDULER to DBA with admin option;
grant SELECT ANY TABLE to DBA with admin option;
grant UPDATE ANY TABLE to DBA with admin option;
grant ALTER ANY CLUSTER to DBA with admin option;
grant ALTER ANY LIBRARY to DBA with admin option;
grant ALTER ANY OUTLINE to DBA with admin option;
grant ALTER ANY TRIGGER to DBA with admin option;
grant COMMENT ANY TABLE to DBA with admin option;
grant CREATE TABLESPACE to DBA with admin option;
grant DEQUEUE ANY QUEUE to DBA with admin option;
grant DROP ANY OPERATOR to DBA with admin option;
grant DROP ANY RULE SET to DBA with admin option;
grant DROP ANY SEQUENCE to DBA with admin option;
grant ENQUEUE ANY QUEUE to DBA with admin option;
grant EXECUTE ANY CLASS to DBA with admin option;
grant FORCE TRANSACTION to DBA with admin option;
grant MANAGE FILE GROUP to DBA with admin option;
grant MANAGE TABLESPACE to DBA with admin option;
grant ON COMMIT REFRESH to DBA with admin option;
grant ALTER ANY RULE SET to DBA with admin option;
grant ALTER ANY SEQUENCE to DBA with admin option;
grant CREATE ANY CLUSTER to DBA with admin option;
grant CREATE ANY CONTEXT to DBA with admin option;
grant CREATE ANY LIBRARY to DBA with admin option;
grant CREATE ANY OUTLINE to DBA with admin option;
grant CREATE ANY SYNONYM to DBA with admin option;
grant CREATE ANY TRIGGER to DBA with admin option;
grant DROP ANY DIMENSION to DBA with admin option;
grant DROP ANY DIRECTORY to DBA with admin option;
grant DROP ANY INDEXTYPE to DBA with admin option;
grant DROP ANY PROCEDURE to DBA with admin option;
grant RESTRICTED SESSION to DBA with admin option;
grant ALTER ANY DIMENSION to DBA with admin option;
grant ALTER ANY INDEXTYPE to DBA with admin option;
grant ALTER ANY PROCEDURE to DBA with admin option;
grant ALTER RESOURCE COST to DBA with admin option;
grant CHANGE NOTIFICATION to DBA with admin option;
grant CREATE ANY OPERATOR to DBA with admin option;
grant CREATE ANY RULE SET to DBA with admin option;
grant CREATE ANY SEQUENCE to DBA with admin option;
grant CREATE EXTERNAL JOB to DBA with admin option;
grant DEBUG ANY PROCEDURE to DBA with admin option;
grant DROP PUBLIC SYNONYM to DBA with admin option;
grant EXECUTE ANY LIBRARY to DBA with admin option;
grant EXECUTE ANY PROGRAM to DBA with admin option;
grant FLASHBACK ANY TABLE to DBA with admin option;
grant GRANT ANY PRIVILEGE to DBA with admin option;
grant READ ANY FILE GROUP to DBA with admin option;
grant SELECT ANY SEQUENCE to DBA with admin option;
grant CREATE ANY DIMENSION to DBA with admin option;
grant CREATE ANY DIRECTORY to DBA with admin option;
grant CREATE ANY INDEXTYPE to DBA with admin option;
grant CREATE ANY PROCEDURE to DBA with admin option;
grant CREATE DATABASE LINK to DBA with admin option;
grant DROP ANY SQL PROFILE to DBA with admin option;
grant EXECUTE ANY OPERATOR to DBA with admin option;
grant EXECUTE ANY RULE SET to DBA with admin option;
grant EXPORT FULL DATABASE to DBA with admin option;
grant GLOBAL QUERY REWRITE to DBA with admin option;
grant IMPORT FULL DATABASE to DBA with admin option;
grant ALTER ANY SQL PROFILE to DBA with admin option;
grant CREATE PUBLIC SYNONYM to DBA with admin option;
grant DEBUG CONNECT SESSION to DBA with admin option;
grant DROP ROLLBACK SEGMENT to DBA with admin option;
grant EXECUTE ANY INDEXTYPE to DBA with admin option;
grant EXECUTE ANY PROCEDURE to DBA with admin option;
grant FORCE ANY TRANSACTION to DBA with admin option;
grant MANAGE ANY FILE GROUP to DBA with admin option;
grant SELECT ANY DICTIONARY to DBA with admin option;
grant ALTER ROLLBACK SEGMENT to DBA with admin option;
grant ANALYZE ANY DICTIONARY to DBA with admin option;
grant CREATE ANY SQL PROFILE to DBA with admin option;
grant SELECT ANY TRANSACTION to DBA with admin option;
grant CREATE ROLLBACK SEGMENT to DBA with admin option;
grant CREATE MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER SQL TUNING SET to DBA with admin option;
grant CREATE EVALUATION CONTEXT to DBA with admin option;
grant DROP PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY MATERIALIZED VIEW to DBA with admin option;
grant GRANT ANY OBJECT PRIVILEGE to DBA with admin option;
grant ADMINISTER DATABASE TRIGGER to DBA with admin option;
grant ADMINISTER RESOURCE MANAGER to DBA with admin option;
grant ALTER ANY MATERIALIZED VIEW to DBA with admin option;
grant CREATE PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER ANY EVALUATION CONTEXT to DBA with admin option;
grant CREATE ANY MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER ANY SQL TUNING SET to DBA with admin option;
grant CREATE ANY EVALUATION CONTEXT to DBA with admin option;
grant EXECUTE ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER on SYS.MAP_OBJECT to DBA;
grant DELETE on SYS.MAP_OBJECT to DBA;
grant INSERT on SYS.MAP_OBJECT to DBA;
grant SELECT on SYS.MAP_OBJECT to DBA;
grant UPDATE on SYS.MAP_OBJECT to DBA;
grant ON COMMIT REFRESH on SYS.MAP_OBJECT to DBA;
grant QUERY REWRITE on SYS.MAP_OBJECT to DBA;
grant DEBUG on SYS.MAP_OBJECT to DBA;
grant FLASHBACK on SYS.MAP_OBJECT to DBA;
grant EXECUTE on SYS.DBMS_FLASHBACK to DBA;
grant EXECUTE on SYS.OUTLN_PKG to DBA;
grant EXECUTE on SYS.OUTLN_EDIT_PKG to DBA;
grant EXECUTE on SYS.DBMS_RESUMABLE to DBA;
grant EXECUTE on SYS.DBMS_DEFER_QUERY to DBA;
grant EXECUTE on SYS.DBMS_DEFER_SYS to DBA;
grant EXECUTE on SYS.DBMS_STORAGE_MAP to DBA;
grant UPDATE on WKSYS.WK$SYS_CONFIG to DBA;
grant EXECUTE on PORTAL.WWV_EXPORT to DBA;
grant EXECUTE on SYS.DBMS_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_INTERNAL_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_SERVER_ALERT to DBA;
grant EXECUTE on SYS.DBMS_TDB to DBA;
grant ALTER on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AW$ to DBA;
grant DEBUG on SYS.AW$ to DBA;
grant SELECT on SYS.PS$ to DBA;
grant DEBUG on SYS.PS$ to DBA;
grant SELECT on SYS.AW_PROP$ to DBA;
grant DEBUG on SYS.AW_PROP$ to DBA;
grant SELECT on SYS.AW_OBJ$ to DBA;
grant DEBUG on SYS.AW_OBJ$ to DBA;
grant EXECUTE on SYS.DBMS_SERVER_TRACE to DBA;
grant EXECUTE on SYS.DBMS_SERVICE to DBA;
grant EXECUTE on SYS.DBMS_MONITOR to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;
grant EXECUTE on SYS.DBMS_UADV_ARR to DBA;
grant EXECUTE on SYS.DBMS_UNDO_ADV to DBA;
grant EXECUTE on SYS.LOAD_UNDO_STAT to DBA;
grant EXECUTE on SYS.RESET_UNDO_STAT to DBA;
grant EXECUTE on SYS.DBMS_FEATURE_USAGE_REPORT to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_CAPTURE to DBA;

PL/SQL procedure successfully completed.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g

Disabling and Enabling Oracle job with DBMS_SCHEDULER.

Leave a comment

Oracle 10g and higher provides the DBMS_SCHEDULER package to create pre-defined jobs for execution, like executing schedule jobs in UNIX with CRON and in Windows with the SCHEDULER. There are times during maintenance when you need to disable scheduled jobs from executing in order to perform other tasks. The disabling of scheduled jobs is performed by the procedure DBMS_SCHEDULER.DISABLE. In this article we will review the use of DBMS_SCHEDULER in disabling and re-enabling pre-defined Oracle jobs. This procedure will work in Oracle 10g and 11g.

1. Connect to SQL*PLUS as sysdba.

Mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Apr 21 13:05:00 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL to build the statements to disable the jobs:
“select ‘exec bms_scheduler.disable(name=>’||””||JOB_NAME||””||’);’ from dba_scheduler_jobs;”

SQL> select ‘exec dbms_scheduler.disable(name=>’||””||JOB_NAME||””||’);’
from dba_scheduler_jobs;

‘EXECDBMS_SCHEDULER.DISABLE(NAME=>’||””||JOB_NAME||””||’);’
——————————————————————–
exec dbms_scheduler.disable(name=>’PURGE_LOG’);
exec dbms_scheduler.disable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.disable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.disable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.disable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.disable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.disable(name=>’PERSTAT_SNAP’);

7 rows selected.

SQL>

3. Take the output from the previous step and execute it.

SQL> exec dbms_scheduler.disable(name=>’PURGE_LOG’);
exec dbms_scheduler.disable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.disable(name=>’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>

4. Execute the following SQL to build the statements to re-enable the jobs.
“select ‘exec dbms_scheduler.enable(name=>’||””||JOB_NAME||””||’);’ from dba_scheduler_jobs;”

SQL> select ‘exec dbms_scheduler.enable(name=>’||””||JOB_NAME||””
dba_scheduler_jobs;

‘EXECDBMS_SCHEDULER.ENABLE(NAME=>’||””||JOB_NAME||””||’);’
——————————————————————-
exec dbms_scheduler.enable(name=>’PURGE_LOG’);
exec dbms_scheduler.enable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.enable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.enable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’PERSTAT_SNAP’);

7 rows selected.

SQL>

5. Take the output from the previous step to re-enable jobs.

SQL> exec dbms_scheduler.enable(name=>’PURGE_LOG’);
exec dbms_scheduler.enable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.enable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.enable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’PERSTAT_SNAP’);

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

That completes disabling and enabling jobs with DBMS_SCHEDULER in 10g and 11g.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Recreate Oracle User account.

Leave a comment

Since the release of Oracle 10g and 9i, most would think that the need for scripts to recreate individual database components are unnecessary and they would be correct if you are already at Oracle 9i or 10g. Oracle 9i provides the user of the DBMS_METADATA package, which give the DBA the ability to generate the DDL for most any Oracle structure. Oracle 10g has enhanced the abilities of DBMS_METADATA and given the add value of ORACLE expdmp and impdmp, which do not require the existence of such objects as a user to transport data between databases. However, there still exist tons of Oracle 8i and earlier versions, which do not contain either of the abilities above and from which we still have to recreate individual oracle objects. This article is part of a series that covers the recreation of Oracle objects by producing the DDL from which to recreate them.

In this article we will cover the recreate of an Oracle User account in its most simple form. NOTE: We will not cover the recreation of a user defined profile or other objects an oracle user account may depend on. For that information, please see my related article Recreation of User defined Oracle Profiles.

The below script regenerates a user account:

set serveroutput on
spool c:\oradata\create_user_account.sql

declare

curr varchar2(30):= ‘TEST_USER’;
v_ext varchar2(3);

begin

for user in(select * from dba_users where username = curr)
loop

dbms_output.put_line(‘create tablespace ‘||user.default_tablespace);

for dat_file in(select * from dba_data_files where
tablespace_name=user.default_tablespace)
loop

if dat_file.autoextensible=’YES’
then
v_ext:=’ON’;
else
v_ext:=’OFF’;
end if;

dbms_output.put_line(‘datafile ‘||””||dat_file.file_name||””||’ size
‘||floor(dat_file.bytes/1024/1024)||’m’);
dbms_output.put_line(‘autoextend ‘||v_ext);
dbms_output.put_line(‘maxsize ‘||floor(dat_file.maxbytes/1024/1024)||’m’);
end loop;
dbms_output.put_line(‘/’);

dbms_output.put_line(‘create user ‘||user.username||’ identified by
‘||user.username||’;’);

dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace
‘||user.default_tablespace||’;’);

dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace
‘||user.temporary_tablespace||’;’);

dbms_output.put_line(‘alter user ‘||user.username||’ profile
‘||user.profile||’;’);

if user.account_status<>’OPEN’
then
dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
end if;

end loop;

for role in(select * from dba_role_privs where grantee=curr)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=curr)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=curr)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;

end loop;
end;
/

spool off

The output generated by above script to recreate the TEST_USER account is listed below.

MYDB >
MYDB > set serveroutput on
MYDB > spool c:\oradata\create_user_account.sql
MYDB >
MYDB >
MYDB > declare
2
3 curr varchar2(30):= ‘TEST_USER’;
4 v_ext varchar2(3);
5
6
7 begin
8
9
10 for user in(select * from dba_users where username = curr)
11 loop
12
13
14 dbms_output.put_line(‘create tablespace ‘||user.default_tablespace);
15
16 for dat_file in(select * from dba_data_files where tablespace_name=user.def
ault_tablespace)
17 loop
18
19 if dat_file.autoextensible=’YES’
20 then
21 v_ext:=’ON’;
22 else
23 v_ext:=’OFF’;
24 end if;
25
26
27 dbms_output.put_line(‘datafile ‘||””||dat_file.file_name||””||’ size ‘|
|floor(dat_file.bytes/1024/1024)||’m’);
28 dbms_output.put_line(‘autoextend ‘||v_ext);
29 dbms_output.put_line(‘maxsize ‘||floor(dat_file.maxbytes/1024/1024)||’m’);
30 end loop;
31 dbms_output.put_line(‘/’);
32
33 dbms_output.put_line(‘create user ‘||user.username||’ identified by ‘||user
.username||’;’);
34
35
36 dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace ‘||
user.default_tablespace||’;’);
37
38 dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace ‘
||user.temporary_tablespace||’;’);
39
40 dbms_output.put_line(‘alter user ‘||user.username||’ profile ‘||user.profil
e||’;’);
41
42 if user.account_status<>’OPEN’
43 then
44 dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
45 end if;
46
47 end loop;
48
49
50 for role in(select * from dba_role_privs where grantee=curr)
51 loop
52
53
54 if role.admin_option = ‘YES’
55 then
56 dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ w
ith admin option’||’;’);
57 else
58 dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’
);
59 end if;
60
61
62 end loop;
63
64 for sys_priv in(select * from dba_sys_privs where grantee=curr)
65 loop
66
67 if sys_priv.admin_option = ‘YES’
68 then
69 dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee
||’ with admin option’||’;’);
70 else
71 dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee
||’;’);
72 end if;
73
74 end loop;
75
76 for tab_priv in(select * from dba_tab_privs where grantee=curr)
77 loop
78
79 if tab_priv.grantable = ‘YES’
80 then
81 dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on ‘||tab_priv.owner||
‘.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with grant option;’);
82 else
83 dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on ‘||tab_priv.owner||
‘.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
84 end if;
85
86 end loop;
87 end;
88 /
create tablespace FOMIS_PROBLEM_TRACKING_BE2
datafile ‘C:\ORACLE\PRODUCT\10.1.0\ORADATA\MYDB\FOMIS_PROBLEM_TRACKING_BE2’
size 304m
autoextend ON
maxsize 4096m
/

create user TEST_USER identified by TEST_USER;
alter user TEST_USER default tablespace TEST_USER_T;
alter user TEST_USER temporary tablespace TEMP;
alter user TEST_USER profile DEFAULT;
grant CONNECT to TEST_USER;
grant CREATE JOB to TEST_USER;
grant CREATE TYPE to TEST_USER;
grant CREATE VIEW to TEST_USER;
grant CREATE TABLE to TEST_USER;
grant ALTER SESSION to TEST_USER;
grant CREATE CLUSTER to TEST_USER;
grant CREATE LIBRARY to TEST_USER;
grant CREATE SESSION to TEST_USER;
grant CREATE SYNONYM to TEST_USER;
grant CREATE TRIGGER to TEST_USER;
grant CREATE OPERATOR to TEST_USER;
grant CREATE SEQUENCE to TEST_USER;
grant CREATE DIMENSION to TEST_USER;
grant CREATE INDEXTYPE to TEST_USER;
grant CREATE PROCEDURE to TEST_USER;
grant CREATE ANY CONTEXT to TEST_USER;
grant CREATE DATABASE LINK to TEST_USER;
grant UNLIMITED TABLESPACE to TEST_USER;
grant CREATE MATERIALIZED VIEW to TEST_USER;
grant EXECUTE on SYS.DBMS_RLS to TEST_USER;
grant EXECUTE on CTXSYS.CTX_DDL to TEST_USER;
grant EXECUTE on CTXSYS.CTX_DOC to TEST_USER;

PL/SQL procedure successfully completed.

MYDB >
MYDB >

This completes the creation of SQL to regenerate a specific user account.

 

LARRY J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com