Oracle Database - Roles & ProfilesROLESWhat is a Role ?A role is group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges. Types of Privileges :
SQL> create role tesdbrole; Role created.Create Password protection for Role SQL> create role tesdbrole1 identified by tesdbrole1; Role created.Assign User to create Role SQL> grant create role to tes; SQL> conn tes/tesAssigning multiple privileges in single role SQL> create role role_r1; Role created. SQL> grant select on tes_tab to role_r1; Grant succeeded. SQL> grant select on tes_tab1 to role_r1; Grant succeeded. SQL> grant select on tes_tab2 to role_r1; Grant succeeded.Checking Roles and privileges SQL> select role, table_name, privilege from role_tab_privs where role='ROLE_R1'; ROLE TABLE_NAME PRIVILEGE ------------------------------------------ ROLE_R1 TES_TAB SELECT ROLE_R1 TES_TAB2 SELECT ROLE TABLE_NAME PRIVILEGE ------------------------------------------ ROLE TABLE_NAME PRIVILEGE ROLE_R1 TES_TAB1 SELECTAssigning Roles SQL> show user USER is "TES" SQL> SQL> grant role_r1 to scott; Grant succeeded. SQL> conn scott/tiger Connected. select role, table_name, privilege from role_tab_privs where owner='TES'; ROLE TABLE_NAME PRIVILEGE --------------------------------------- ROLE_R1 TES_TAB SELECT ROLE_R1 TES_TAB2 SELECT ROLE TABLE_NAME PRIVILEGE -------------------------------------- ROLE_R1 TES_TAB1 SELECTCreate role to System Privileges SQL> create role tes_set; Role created. SQL> grant connect to tes_set; Grant succeeded. SQL> grant datapump_exp_full_database, datapump_imp_full_database to tes_set; Grant succeeded. 1* select granted_role from role_role_privs where role='TES_SET' SQL> / GRANTED_ROLE ----------------------------- DATAPUMP_IMP_FULL_DATABASE DATAPUMP_EXP_FULL_DATABASE CONNECT 1* revoke datapump_imp_full_database from tes_set SQL> / Revoke succeeded. SQL> drop role tes_set; Role dropped. PROFILES
No. of allowed concurrent sessions for a user CPU_PER_SESSION CPU time limit for a session, expressed in hundredth of seconds. CPU_PER_CALL Specify teh CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds. CONNECT_TIME Specify teh total elapsed time limit for a session, expressed in minutes. IDLE_TIME Specify teh permitted periods of continuous inactive time during a session, expressed in minutes. LOGICAL_READS_PER_SESSION Specify teh permitted number of data blocks read in a session, including blocks read from memory and disk LOGICAL_READS_PER_CALL permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). PRIVATE_SGA SGA a session can allocate in teh shared pool of teh system global area (SGA), expressed in bytes. FAILED_LOGIN_ATTEMPTS No. of failed attempts to log in to the user account before the account is locked PASSWORD_LIFE_TIME No. of days the account will be open. after dat it will expiry. PASSWORD_REUSE_TIME number of days before which a password cannot be reused PASSWORD_REUSE_MAX number of days before which a password can be reused PASSWORD_LOCK_TIME Number of days teh user account remains locked after failed login PASSWORD_GRACE_TIME Number of grace days for user to change password PASSWORD_VERIFY_FUNCTION PL/SQL that can be used for password verification To check the profiles for PASSWORD resource_type 1* select resource_name, limit from dba_profiles where resource_type='PASSWORD' and profile='DEFAULT' SQL> /
To check the profiles for KERNEL resource_type select resource_name, limit from dba_profiles where resource_type='KERNEL' and profile='DEFAULT' SQL> /
Creating profile with session limit create profile tes_profile limit sessions_per_user 3 SQL> / Profile created. SQL> select distinct(profile) from dba_profiles; PROFILE ------------------------------------------------------ TES_PROFILE DEFAULT ORA_STIG_PROFILE Creating profile with different parameters SQL> create profile tes_profile1 limit sessions_per_user unlimited cpu_per_session unlimited cpu_per_call 3000 connect_time 45 logical_reads_per_session default logical_reads_per_call 1000; Profile created. Setting profile to user SQL> alter user tes profile tes_profile; User altered. SQL> select username, profile from dba_users where username ='TES'; USERNAME PROFILE -------- ------------ TES TES_PROFILE Alter the profile for cpu_per_cal SQL> alter profile tes_profile limit cpu_per_call default; Profile altered. SQL> select resource_name, limit from dba_profiles where resource_type='KERNEL' and profile='TES_PROFILE';
USER LOCKED SQL> conn tes Enter password: ERROR: ORA-28000: The account is locked. Warning: You are no longer connected to ORACLE. SQL> CHECK THE USER STATUS SQL> select username, account_status from dba_users where username='TES'; USERNAME ACCOUNT_STATUS -------- ------------- TES LOCKED UNLOCK THE USER SQL> alter user tes account unlock; User altered. SQL> select username, account_status from dba_users where username='TES'; USERNAME ACCOUNT_STATUS -------- ------------- TES OPEN « Previous Next Topic » (Oracle Database - User Management) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||