To Create a Oracle User Profile Example

To Create A User Profile


SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';



select * from dba_sys_privs where grantee='CONNECT';


select * from dba_sys_privs where grantee='RESOURCE';



SQL> select * from dba_sys_privs where grantee='CONNECT';


GRANTEE    PRIVILEGE                                ADM

---------- ---------------------------------------- ---

CONNECT    CREATE VIEW                              NO

CONNECT    CREATE TABLE                             NO

CONNECT    ALTER SESSION                            NO

CONNECT    CREATE CLUSTER                           NO

CONNECT    CREATE SESSION                           NO

CONNECT    CREATE SYNONYM                           NO

CONNECT    CREATE SEQUENCE                          NO

CONNECT    CREATE DATABASE LINK                     NO


8 rows selected.


SQL> select * from dba_sys_privs where grantee='RESOURCE';


GRANTEE    PRIVILEGE                                ADM

---------- ---------------------------------------- ---

RESOURCE   CREATE TYPE                              NO

RESOURCE   CREATE TABLE                             NO

RESOURCE   CREATE CLUSTER                           NO

RESOURCE   CREATE TRIGGER                           NO

RESOURCE   CREATE OPERATOR                          NO

RESOURCE   CREATE SEQUENCE                          NO

RESOURCE   CREATE INDEXTYPE                         NO

RESOURCE   CREATE PROCEDURE                         NO


8 rows selected.


//***** Create a Profile script *****//

create profile

  ABCDDEVELOPERS

limit

  sessions_per_user                 1 

  connect_time              unlimited 

  idle_time                        20 

  logical_reads_per_session   default 

  logical_reads_per_call      default

  failed_login_attempts             3 

  password_life_time               30 

  password_reuse_time              12 

  password_reuse_max        unlimited 

  password_lock_time          default 

  password_grace_time               2  ;


create user ABCDI12 identified by ABCD12

PASSWORD EXPIRE

PROFILE BOIDEVELOPERS

DEFAULT              TABLESPACE PSDEFAULT

TEMPORARY TABLESPACE PSTEMP ;

By Prasanth Bhaskaran (Oracle People Soft Upgrade and DBA Specialist )