USER managment

USER :

Purpose

Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.
You can issue this statement in an Automatic Storage Management cluster to add a user and password combination to the password file that is local to the ASM instance of the current node. Each node's ASM instance can use this statement to update its own password file. The password file itself must have been created by the ORAPWD utility.
You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.


Prerequisites 

You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Refer to GRANT for more information.
Only a user authenticated AS SYSASM can issue this command to modify the Automatic Storage Management instance password file.


 




an Oracle user is a database account with login access to the database;
an Oracle schema is an Oracle user plus the collection of database objects owned by the user.




commands :




--user mirror 
 save as an .sql file and Run --> insert the schema name .

ACCEPT username   char  PROMPT 'Enter username  for the :'
set pages 0
set echo off heading off feedback off
SELECT 'CREATE USER ' || u.username ||' IDENTIFIED ' ||' BY VALUES ''' || c.password || ''' DEFAULT TABLESPACE ' || u.default_tablespace ||' TEMPORARY TABLESPACE ' || u.temporary_tablespace ||' PROFILE ' || u.profile || case when account_status= 'OPEN' then ';' else ' Account LOCK;' end "--Creation Statement"
FROM dba_users u,user$ c where u.username=c.name and u.username=upper('&&username')
UNION
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted Roles"
from dba_role_privs where grantee= upper('&&username')
UNION
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted System Privileges"
from dba_sys_privs where grantee= upper('&&username')
UNION
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||case when GRANTABLE='YES' then ' WITH GRANT OPTION;' else ';' end "Granted Object Privileges"
from DBA_TAB_PRIVS where GRANTEE=upper('&&username');

set heading on pages 1000
col USERNAME for a25
col PASSWORD for a25
col account_status for a23
col PROFILE for a15
col DEFAULT_TABLESPACE for a20
col TEMPORARY_TABLESPACE for a20
PROMPT
SELECT A.USERNAME,B.PASSWORD,A.ACCOUNT_STATUS,A.PROFILE,A.DEFAULT_TABLESPACE,A.TEMPORARY_TABLESPACE FROM DBA_USERS A, USER$ B WHERE A.USER_ID=B.USER# AND USERNAME=UPPER('&&username');
PROMPT
PROMPT USER's OBJECT COUNT:
PROMPT --------------------

select  USERNAME,
        count(decode(o.TYPE#, 2,o.OBJ#,'')) Tables,
        count(decode(o.TYPE#, 1,o.OBJ#,'')) Indexes,
        count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
        count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
        count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
        count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
        count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
        count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
        count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
        count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from    obj$ o,
        dba_users u
where   u.USER_ID = o.OWNER# (+) and u.USERNAME=upper('&&username')
group   by USERNAME
order   by USERNAME;
set heading off
PROMPT
select 'SCHEMA SIZE: '||ceil(sum(bytes)/1024/1024)||' MB' from dba_segments where owner=UPPER('&&username') group by owner;
PROMPT ------------

PROMPT
select     'Number of Invalid Objects: '||count(*) from dba_objects where STATUS = 'INVALID' and owner=upper('&&username');
PROMPT   --------------------------

PROMPT
select 'Number of Connected Sessions: ' || count(*) from gv$session where username=upper('&&username');
PROMPT  -----------------------------


Grants :

select 'grant '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' to GJAIN;' FROM DBA_TAB_PRIVS WHERE GRANTEE='EFIGUERO';
select 'grant '||PRIVILEGE||' to GJAIN;' from dba_sys_privs where GRANTEE='EFIGUERO';
select 'grant '||GRANTED_ROLE||' TO GJAIN;' from dba_role_privs where grantee='EFIGUERO';