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
