Script for user with password and permission in Oracle

Script for user with password and permission in Oracle

In 10g, Oracle USER Info, Password, Tablespace, Status

select 'Alter user '||username||' identified by '''||password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '
||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users;

Creation commands and grants for users

select 'CREATE USER '||username||' IDENTIFIED BY VALUES '''||PASSWORD||''' DEFAULT TABLESPACE ' ||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||' PROFILE '||PROFILE|| ' ACCOUNT '||ACCOUNT_STATUS||';' from dba_users where username='ELF';

In 11g, Info about Oracle USER, Password, Default tablespace
In 11g, Get info about Oracle USER, Password, Default tablespace ,temp tablespace, account status in one command

select 'Alter user '||a.username||' identified by values '''||b.password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '
||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users a,sys.user$ b where b.name = a.username;

Need to fetch the permission granted to user scott from dba users

select ‘GRANT ‘||GRANTED_ROLE||’ TO ;’ FROM DBA_ROLE_PRIVS WHERE GRANTEE=’SCOTT’
union all
select ‘GRANT ‘||PRIVILEGE||’ TO ;’ FROM DBA_SYS_PRIVS WHERE GRANTEE=’SCOTT’
union all
select ‘GRANT ‘||PRIVILEGE||’ ON ‘||OWNER||’.’||TABLE_NAME||’ TO ;’ from DBA_TAB_PRIVS where GRANTEE=’SCOTT’;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.