how to Create Profile and Roles and What is the vlaue of DEFAULT in dba_profile?

Hi Team,

Here is how you can deal with working on profile and creating role.

A :---I have created role “ testinguserrole” on my test database;

Step:-1
-----------------------------------
SQL> create role testinguserrole;

GRANT CONNECT TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM_LINE TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM_EDIT TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM_LINE_EDIT TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM_LINE_MODIFIER TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM_LINE_DIAGNOSIS TO testinguserrole;
GRANT SELECT ON ICP_P.CLAIM_LINE_USER_DEFINED_FIELD TO testinguserrole;

to verify the dba_tab_privs for testinguserrole

select * from dba_tab_privs where grantee = 'testinguserrole';

Now i am creating user and will assign them the role.

SQL> CREATE USER &username IDENTIFIED BY &username
  2  DEFAULT TABLESPACE DATA1
  3  TEMPORARY TABLESPACE TEMP
  4  PROFILE DEFAULT
  5  ACCOUNT UNLOCK;

Enter value for username: KHN000008
Enter value for username: *****
old   1: CREATE USER &username IDENTIFIED BY &username
new   1: CREATE USER KHN40708 IDENTIFIED BY ******

User created.


Step:-2
--------------------------
SQL> grant testinguserrole to &username;
Enter value for username: KHN40708
old   1: grant testinguserrole to &username
new   1: grant testinguserrole to KHN40708

Grant succeeded.

Step:-3
-----------------------------------
SQL> select * from dba_role_privs where grantee='KHN000008';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
KHN40708                       testinguserrole                 NO  YES

So i have created many other dummy users and to verify if they have profper role priviledge.

Step:-4
----------------------------------
SQL> select * from dba_role_privs where grantee in('KHN33220', 'KHN01607', 'KHN40732', 'KHN53133', 'KHN39408', 'KHN01609', 'KHN40708');

GRANTEE                        GRANTED_ROLE                   ADM      DEF
------------------------------ ------------------------------ ---      ---
KHN01607                       testinguserrole                 NO          YES
KHN01609                       testinguserrole                 NO          YES
KHN40708                       testinguserrole                 NO          YES
KHN39408                       testinguserrole                 NO          YES
KHN40732                       testinguserrole                 NO          YES
KHN53133                       testinguserrole                 NO          YES
KHN33220                       testinguserrole                 NO          YES

7 rows selected.

=========================================================================



B What is the vlaue of DEFAULT in dba_profile
--------------------------------------------------------------------------
SQL> select * from dba_profiles where profile='DEFAULT';

PROFILE              RESOURCE_NAME                    RESOURCE LIMIT
-------------------- -------------------------------- -------- ----------------------------------------
DEFAULT              COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT              SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT              CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT              CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT              LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT              LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT              IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT              CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT              PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT              FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT              PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT              PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT              PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

To change the parameter you may use

ALTER PROFILE DEFAULT LIMIT idle_time UNLIMITED;
ALTER PROFILE limit PASSWORD_LIFE_TIME unlimited;