Gebruiker configuratie script

set lines 100 pages 999
set verify off
set feedback off

undefine user

accept userid prompt 'Enter username:'
select username
,      default_tablespace
,      temporary_tablespace
from   dba_users
where  username = '&userid'
/

select tablespace_name
,      decode(max_bytes, -1, 'unlimited'
,      ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  username = upper('&&userid')
/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "ROLE"
from   dba_role_privs
where  grantee = upper('&&userid')
/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "PRIV"
from   dba_sys_privs
where  grantee = upper('&&userid')
/

undefine user

set verify on
set feedback on

Gebruiker clone script

set lines 999 pages 999
set verify off
set feedback off
set heading off

select    username
from    dba_users
order    by username
/

undefine user

accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '

select username
,      created
from   dba_users
where  lower(username) = lower('&newuser')
/

accept poo prompt 'Continue? (ctrl-c to exit)'
spool /tmp/user_clone_tmp.sql

select 'create user ' || '&newuser' ||
       ' identified by ' || '&passwd' ||
       ' default tablespace ' || default_tablespace ||
       ' temporary tablespace ' || temporary_tablespace || ';' "user"
from   dba_users
where  username = '&userid'
/

select 'alter user &newuser quota '||
       decode(max_bytes, -1, 'unlimited'
       ,                     ceil(max_bytes / 1024 / 1024) || 'M') ||
       ' on ' || tablespace_name || ';'
from   dba_ts_quotas
where  username = '&&userid'
/

select 'grant ' ||granted_role || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from   dba_role_privs
where  grantee = '&&userid'
/

select 'grant ' || privilege || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from   dba_sys_privs
where  grantee = '&&userid'
/

spool off

undefine user

set verify on
set feedback on
set heading on

@/tmp/user_clone_tmp.sql

!rm /tmp/user_clone_tmp.sqlqq

Laat alle gebruikers zien

set pages 999 lines 100
col username    format a20
col status    format a8
col tablespace    format a20
col temp_ts    format a20

select    username
,    account_status status
,    created
,    default_tablespace tablespace
,    temporary_tablespace temp_ts
from    dba_users
order    by username
/

Lock of unlock een gebruiker

user <username> account lock;
alter user <username> account unlock;