How to show user profile and change its limit ?

How to show user profile and change its limit ?


1. First see the profile which are using my user ;

      SELECT * FROM dba_users WHERE USERNAME='KMI';

2. For showing the specific profiles property

     select * from dba_profiles where profile='DEFAULT';

profile attribute
===================
    select * from DBA_PROFILES;


3.In order to track password related profile limits, Oracle stores the history of passwords for a user in user_history$.


select * from user_history$

select    username,   h.password,   password_date from    sys.user_history$ h,   dba_users where   user_id = user#;

4. To change profile limit use the following query

ALTER PROFILE default limit failed_login_attempts UNLIMITED;



CREATE USER kmi
    IDENTIFIED BY kmi
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA unlimited ON users;






Listing All System Privilege Grants


The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
DBA                CREATE SESSION                    YES
DBA                ALTER SESSION                     YES
DBA                DROP TABLESPACE                   YES
DBA                BECOME USER                       YES
DBA                DROP ROLLBACK SEGMENT             YES
DBA                SELECT ANY TABLE                  YES
DBA                INSERT ANY TABLE                  YES
DBA                UPDATE ANY TABLE                  YES
DBA                DROP ANY INDEX                    YES
DBA                SELECT ANY SEQUENCE               YES
DBA                CREATE ROLE                       YES
DBA                EXECUTE ANY PROCEDURE             YES
DBA                ALTER PROFILE                     YES
DBA                CREATE ANY DIRECTORY              YES
DBA                CREATE ANY LIBRARY                YES
DBA                EXECUTE ANY LIBRARY               YES
DBA                ALTER ANY INDEXTYPE               YES
DBA                DROP ANY INDEXTYPE                YES
DBA                DEQUEUE ANY QUEUE                 YES
DBA                EXECUTE ANY EVALUATION CONTEXT    YES
DBA                EXPORT FULL DATABASE              YES
DBA                CREATE RULE                       YES
DBA                ALTER ANY SQL PROFILE             YES
DBA                ADMINISTER ANY SQL TUNING SET     YES
DBA                CHANGE NOTIFICATION               YES
DBA                DROP ANY EDITION                  YES
DBA                DROP ANY MINING MODEL             YES
DBA                ALTER ANY MINING MODEL            YES
DBA                ALTER ANY CUBE DIMENSION          YES
DBA                CREATE CUBE                       YES
DBA                DROP ANY CUBE BUILD PROCESS       YES
SYS                AUDIT SYSTEM                      NO
SYS                ALTER SESSION                     NO
SYS                ALTER ROLLBACK SEGMENT            NO
SYS                ALTER ANY CLUSTER                 NO
SYS                CREATE ANY INDEX                  NO
SYS                CREATE DATABASE LINK              NO
SYS                DROP PUBLIC DATABASE LINK         NO
SYS                GRANT ANY ROLE                    NO
SYS                ALTER ANY ROLE                    NO
SYS                EXECUTE ANY PROCED





Listing All Role Grants


The following query returns all the roles granted to users and other roles:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SYS                            JMXSERVER                      YES YES
SYS                            XDB_SET_INVOKER                YES YES
SYS                            XDBADMIN                       YES YES
SYS                            IMP_FULL_DATABASE              YES YES
DBA                            SCHEDULER_ADMIN                YES YES
DBA                            DATAPUMP_IMP_FULL_DATABASE     NO  YES
SYSTEM                         AQ_ADMINISTRATOR_ROLE          YES YES
EXECUTE_CATALOG_ROLE           HS_ADMIN_EXECUTE_ROLE          NO  YES
HS_ADMIN_ROLE                  HS_ADMIN_EXECUTE_ROLE          NO  YES
OEM_MONITOR                    SELECT_CATALOG_ROLE            NO  YES
test                           CONNECT                        NO  YES
SYSMAN                         MGMT_USER                      YES YES






Listing Object Privileges Granted to a User


The following query returns all object privileges (not including column-specific privileges) granted to the specified user:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS     WHERE GRANTEE = 'TEST';

TABLE_NAME                     PRIVILEGE                                GRA
------------------------------ ---------------------------------------- ---
DAILY_EXPORTS                  WRITE                                    YES
DAILY_EXPORTS                  READ                                     YES
DAILY_EXPORTS                  EXECUTE                                  YES
ENCRYPT_TXT                    EXECUTE                                  NO
UTL_MAIL                       EXECUTE                                  NO
DECRYPT_TXT                    EXECUTE                                  NO



To list all the column-specific privileges that have been granted, use the following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE     FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------    --------------
TEST         EMP            ENAME            INSERT
KMI          EMP            JOB              INSERT
TEST1        EMP            NAME             INSERT
TEST2        EMP            JOB              INSERT






Listing the Current Privilege Domain of Your Session


The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES;


If TEST has enabled the security_admin role and issues this query, then Oracle Database returns the following information:

ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
XDBADMIN
XDB_SET_INVOKER
JAVA_ADMIN
JAVA_DEPLOY


The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;


If swilliams has the security_admin role enabled and issues this query, then Oracle returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE


If the security_admin role is disabled for TEST, then the first query would return no rows, while the second query would only return a row for the CREATE SESSION privilege grant.





Listing Roles of the Database


The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN        YES






Listing Information About the Privilege Domains of Roles


The ROLE_ROLE_PRIVS,ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles. For example, the following query lists all the roles granted to the system_admin role:

SELECT GRANTED_ROLE, ADMIN_OPTION   FROM ROLE_ROLE_PRIVS   WHERE ROLE = 'DBA';

GRANTED_ROLE                   ADM
------------------------------ ---
SCHEDULER_ADMIN                YES
DATAPUMP_IMP_FULL_DATABASE     NO
DELETE_CATALOG_ROLE            YES
EXECUTE_CATALOG_ROLE           YES
EXP_FULL_DATABASE              NO
SELECT_CATALOG_ROLE            YES
JAVA_DEPLOY                    NO
GATHER_SYSTEM_STATISTICS       NO
JAVA_ADMIN                     NO
XDB_SET_INVOKER                NO
DATAPUMP_EXP_FULL_DATABASE     NO
XDBADMIN                       NO
IMP_FULL_DATABASE              NO


The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'DBA';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            CREATE SESSION                           YES
DBA                            ALTER SESSION                            YES
DBA                            DROP TABLESPACE                          YES
DBA                            BECOME USER                              YES
DBA                            DROP ROLLBACK SEGMENT                    YES
DBA                            SELECT ANY TABLE                         YES
DBA                            INSERT ANY TABLE                         YES
DBA                            UPDATE ANY TABLE                         YES
DBA                            DROP ANY INDEX                           YES
DBA                            SELECT ANY SEQUENCE                      YES
DBA                            CREATE ROLE                              YES
DBA                            EXECUTE ANY PROCEDURE                    YES
DBA                            ALTER PROFILE                            YES
DBA                            CREATE ANY DIRECTORY                     YES
DBA                            CREATE ANY LIBRARY                       YES
DBA                            EXECUTE ANY LIBRARY                      YES


The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS    WHERE ROLE = 'DBA';

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
MAP_OBJECT                     INSERT
DBMS_LOGSTDBY                  EXECUTE
V_$DIAG_PROBLEM                SELECT
V_$DIAG_HM_FINDING             SELECT
V_$DIAG_IPS_CONFIGURATION      SELECT
V_$DIAG_INC_METER_IMPT_DEF     SELECT
V_$DIAG_AMS_XACTION            SELECT
V_$DIAG_VPROBLEM_INT           SELECT
V_$DIAG_VPROBLEM1              SELECT
V_$DIAG_VPROBLEM_BUCKET_COUNT  SELECT
V_$DIAG_VNOT_EXIST_INCIDENT    SELECT
DBMS_RESUMABLE                 EXECUTE
DBMS_SERVER_ALERT              EXECUTE
DBMS_HM                        EXECUTE
DBMS_WORKLOAD_REPLAY           EXECUTE
DBMS_DEFER_SYS                 EXECUTE
AW$                            SELECT
AW_TRACK$                      SELECT
XDB$H_LINK                     DELETE
XDB$H_LINK                     SELECT
XDB$H_LINK                     UPDATE
XDB$H_LINK                     QUERY REWRITE