https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=334297584806695&parent=DOCUMENT&sourceId=419550.1&id=1503653.1&_afrWindowMode=0&_adf.ctrl-state=15is5xh3l8_33
Wednesday, 5 December 2018
Tuesday, 27 November 2018
ORA-12514: TNS:listener does not currently know of service requested in connect
ORA-12514: TNS:listener does not currently know of service requested in connect
check the service name and sid name.
if there is sid _name running fine , then add a entry about the service_name as global _name
example
listener.ora
(SID_DESC =
(GLOBAL_DBNAME = DOM.h1.al.mi.com)
(ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = DOM)
)
(SID_DESC =
(GLOBAL_DBNAME = DOM)
(ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = DOM)
Let me explain :
listener status :
Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh
a) When you connected using SID : magesh , connection succeed as the instance name in the listener matched :
Note :
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh
----------------------------------------------------------
b) When you connected using SERVICE_NAME = magesh , connection succeed as the service name in the listener status is not matching : :
Note :
Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com
Solution:
Change the SERVICE_NAME=magesh.hq.dal.omi.com
check the service name and sid name.
if there is sid _name running fine , then add a entry about the service_name as global _name
example
listener.ora
(SID_DESC =
(GLOBAL_DBNAME = DOM.h1.al.mi.com)
(ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = DOM)
)
(SID_DESC =
(GLOBAL_DBNAME = DOM)
(ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = DOM)
Let me explain :
listener status :
Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh
a) When you connected using SID : magesh , connection succeed as the instance name in the listener matched :
Note :
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh
----------------------------------------------------------
b) When you connected using SERVICE_NAME = magesh , connection succeed as the service name in the listener status is not matching : :
Note :
Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com
Solution:
Change the SERVICE_NAME=magesh.hq.dal.omi.com
Wednesday, 14 November 2018
Table Reorganization:
Step 1:Check the last analyzed, tables size before Reorganization:
-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;
NAME
---------
TESTDB
SQL> SELECT OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';
OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990
--Query 2 :Check the size of the table
SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';
OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304
Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16
ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12
Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.
Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.
set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';
If db is up:
=================
select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';
INDEXES NEEDS TO BE REBUILDED:
=================================
--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
Step 3:Make sure to make it noparallel and logging after indexes are rebuild:
Make it no parallel to release the CPU channels
select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';
Step 3:Move the tables to reduce fragmentation.
SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off
Table altered.
Step 4: Rebuild indexes online using VNC session(vncserver)
We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.
spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off
Step 5:Check index status again after rebuild
Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.
SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';
OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
APPS ORDER_TAB_NU17 UNUSABLE
ORD_SCHEMA ORDER_TAB_F2 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_NU3 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N10 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N11 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N12 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_U1 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N6 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_U2 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_NU1 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N9 UNUSABLE
OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA XHL_ORDER_TAB_NU4 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N1 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_NU2 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N7 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N8 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N2 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N3 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N4 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N5 UNUSABLE
APPS XHL_ORDER_TAB_NU5 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_R1 UNUSABLE
22 rows selected.
To check unusable indexes for table:
==========================================
SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';
no rows selected
SQL>
Step 6:Make it no parallel and release the channels:
select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';
Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
Step 8:Run the gather stats for the tables in VNC session:
We run gather statistics to choose the best posible execution plan.
spool gather_stats_tables_TESTDB.out
sqlplus "/as sysdba"
SQL>
Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
Step 9:Verify the number of rows,statistics ,tables size after reorganization
SQL> select name from v$database;
NAME
---------
TESTDB
SQL> SELECT OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';
OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700
SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';
OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352
Hope it helps.Enjoy DBA tasks...
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);
SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN (<TABLENAMES>);
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<INDEXNAMES>);
SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';
2. Export table statistic
Exporting the tables stats is very important; else, tables will start gathering the stats from beginning for the whole table and sometimes that will affects the performance of the queries respect to these tables. Once the reorg has been completed we need to import the stats back to the table, so that the tables will use the old stats while querying the table data
(a) Create Stats Table to store the statistics
Exporting the stats to one of the table say ‘MY_STATS_TABLE’ as temporary table. Once the stats has been imported you can drop the table
EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>'<OWNERNAME>’,STATTAB =>'<TABLENAME>’, TBLSPACE =>'<TABLESPACENAME>’);
Example:
EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>’SCOTT’,STATTAB =>’MY_STATS_TABLE’, TBLSPACE =>’USER’);
Example:
EXEC DBMS_STATS.EXPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);
3. Perform Table & associated index reorg
(a) Table Reorg:
For Non-LOB Tables:
ALTER TABLE <USERNAME>.<TABLE NAME> MOVE;
Example:
ALTER TABLE SCOTT.EMP MOVE;
For Non-LOB Tables:
ALTER TABLE <USERNAME>.<TABLE_NAME> MOVE LOB (COLUMN_NAME) STORE AS (TABLESPACE NEWTABLESPACE);
Example:
ALTER TABLE SCOTT.EMP MOVE LOB (PHOTO) STORE AS TABLESPACE DATA;
b) Rebuild the associated indexes:
If the table has associated indexes which we got it from the step-1 queries, we need to perform the rebuild as they were become INVALID once after the reorg of the parent table.
For Normal Indexes:
ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD ONLINE;
Example:
ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE;
For Bitmap indexes:
ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD;
Example:
ALTER INDEX SCOTT.EMP_BIT_IDX REBUILD;
4. Import table stats
EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘<USERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);
Example:
EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);
5. Repeat step 1
6. Please collect the invalid object count
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS=’INVALID';
7. Run utlrp.sql (If necessary)
SQL> @?/rdbms/admin/utlrp.sql
8. Again Collect the Invalid Object information
9. Execute the below query and compare the values before and after the the reorg, you will be surprised
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);
------------------ TABLE FRAGMENTATION--------------------
(I) ------------------- TABLE FRAGMENTATION--------------------
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.
Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.
HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.
DDL statement always resets the HWM.
===TABLE SIZE (WITH FRAGMENTATION)===============
SQL> select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';
Example:
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb
========ACTUAL DATA IN TABLE======================
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables
where table_name = 'BIG1';
Example:
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb
Note = 72952 - 30604 = 42348 Kb is wasted space in table
The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize the fragmented table.
We have four options to reorganize fragmented tables:
1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition
#################################################################################################################################################################
2. JOBS RUNNING STATUS SCRIPTS
SQL>SELECT owner, job_name, enabled FROM dba_scheduler_jobs
SQL>select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name='ABC';
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.
Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.
HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.
DDL statement always resets the HWM.
===TABLE SIZE (WITH FRAGMENTATION)===============
SQL> select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';
Example:
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb
========ACTUAL DATA IN TABLE======================
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables
where table_name = 'BIG1';
Example:
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb
Note = 72952 - 30604 = 42348 Kb is wasted space in table
The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize the fragmented table.
We have four options to reorganize fragmented tables:
1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition
#################################################################################################################################################################
2. JOBS RUNNING STATUS SCRIPTS
SQL>SELECT owner, job_name, enabled FROM dba_scheduler_jobs
SQL>select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name='ABC';
Tuesday, 13 November 2018
Privileges and roles
Privilege command
/*for tables*/
select 'grant select on '||owner||'.'||table_name||' to <user name>' from dba_tables where owner=<schema>;
/*for procedures*/
select 'grant execute on '||owner||'.'||object_name||' to <user name>' from dba_objects where owner=<schema>
and object_type='PROCEDURE';
where OBJECT_TYPE='PROCEDURE'
Select 'GRANT SELECT ON GBO_DB.'||Table_Name||'TO GBO_READONLY' From All_Tables Where Owner='GBO_DB';
select 'GRANT SELECT ON GBO_DB.'||view_name||' TO GBO_READONLY' From dba_views where Owner='GBO_DB';
BEGIN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || GBO_DB.table_name || ' TO readon';
END LOOP;
END;
/
==================================================================================
SQL> CREATE USER books IDENTIFIED BY books
2 ;
User created.
SQL> GRANT CONNECT TO books;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE, DBA TO books;
select * from dba_sys_privs
grant debug connect session to SCDM;
GRANT DEBUG ANY PROCEDURE TO SCDM;
password expire
SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='SIMON';
What are the privileges that role contain
================================================================================
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DBMON';
select privilege from dba_sys_privs where grantee='DWUSER';
select * from dba_role_privs where grantee='DWUSER';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
DBMON APP_BASE_ROLE YES YES
DBMON CONNECT NO YES
DBMON SQLT_USER_ROLE NO YES
DBMON DBA NO YES
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DWUSER';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('SCOTT')
UNION
SELECT grantee,granted_role
FROM dba_role_privs
UNION
SELECT grantee,privilege
FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;
=================================================================================
Listing Privilege and Role Information
To list the grants made for objects, a user can query the following data dictionary views:
ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD
ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD
DBA_ROLES
USER_ROLE_PRIVS, DBA_ROLE_PRIVS
USER_SYS_PRIVS, DBA_SYS_PRIVS
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
SESSION_PRIVS, SESSION_ROLES
See Also: See the Oracle8 Reference for a detailed description of these data dictionary views.
Listing Privilege and Role Information: Examples
For the following examples, assume the following statements are issued:
CREATE ROLE security_admin IDENTIFIED BY honcho;
GRANT create profile, alter profile, drop profile,
create role, drop any role, grant any role, audit any,
audit system, create user, become user, alter user, drop user
TO security_admin WITH ADMIN OPTION;
GRANT SELECT, DELETE ON sys.aud$ TO security_admin;
GRANT security_admin, create session TO swilliams;
GRANT security_admin TO system_administrator;
GRANT create session TO jward;
GRANT SELECT, DELETE ON emp TO jward;
GRANT INSERT (ename, job) ON emp TO swilliams, jward;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Listing All System Privilege Grants
The following query indicates all system privilege grants made to roles and users:
SELECT * FROM sys.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
SWILLIAMS CREATE SESSION NO
JWARD CREATE SESSION NO
Listing All Role Grants
The following query returns all the roles granted to users and other roles:
SELECT * FROM sys.dba_role_privs;
GRANTEE GRANTED_ROLE ADM
------------------ ------------------------------------ ---
SWILLIAMS SECURITY_ADMIN NO
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 sys.dba_tab_privs WHERE grantee = 'JWARD';
TABLE_NAME PRIVILEGE GRANTABLE
----------- ------------ ----------
EMP SELECT NO
EMP DELETE NO
To list all the column specific privileges that have been granted, use the following query:
SELECT grantee, table_name, column_name, privilege FROM sys.dba_col_privs where grantee='GOURC';
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
JWARD 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 SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:
ROLE
------------------------------
SECURITY_ADMIN
The following query lists all system privileges currently available in the issuer's security domain, 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, 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 SWILLIAMS, the first query would have returned 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 sys.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 = 'SYSTEM_ADMIN';
GRANTED_ROLE ADM
---------------- ----
SECURITY_ADMIN NO
The following query lists all the system privileges granted to the SECURITY_ADMIN role:
SELECT * FROM role_sys_privs WHERE role = 'SECURITY_ADMIN';
ROLE 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
The following query lists all the object privileges granted to the SECURITY_ADMIN role:
SELECT table_name, privilege FROM role_tab_privs
WHERE role = 'SECURITY_ADMIN';
SELECT table_name, privilege FROM role_tab_privs WHERE role ='QUERY_ALL_ROLE';
TABLE_NAME PRIVILEGE
--------------------------- ----------------
AUD$ DELETE
AUD$ SELECT
SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1;
SQL> CONNECT u1/u1
Connected.
SQL> GRANT SELECT ON scott.t2 TO u2;
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'employees';
GRANTEE OWNER
------------------------------ ------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
U2 SCOTT
SCOTT SELECT NO
-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
2> FROM DBA_TAB_PRIVS
3> WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';
GRANTEE OWNER GRANTOR PRIVILEGE
-------------- -------------- ------------- ---------------
ESRVT_ROLE ESRVP ESRVP DELETE
-------------------------------------------------------------------------------------------------------------------------------------------------
----->SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
========================================================================================
grant select, insert, update, delete on suppliers to smithj;
grant select, insert, update, delete on suppliers to smithj;
grant select on Eagan to gourc;
===========================================================================================
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege Description
Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to user;
For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:
grant execute on Find_Value to smithj;
If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
SELECT grantee, table_name, column_name, privilege
FROM sys.dba_col_privs;
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
SQL> select privilege from dba_sys_privs where GRANTEE='ERMUSR';
PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
UNLIMITED TABLESPACE
9 rows selected.
grant sysdba to ermusr;
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='QUERY_ALL_ROLE';
select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
SELECT * FROM sys.dba_role_privs where grantee='MASTERSAF'
select * from dba_role_privs where granted_role='APPDBA';
SQL> desc dba_role_privs
Name Null? Type
------------ -------- ------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
To find a list of all users with DBA privilege execute the following code:
SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS DBA YES YES
SYSTEM DBA YES YES
WHAT ARE THE PRIVILEGES ASSAIGN TO ROLE
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='APPDBA';
=================================================================================
SELECT * FROM sys.dba_sys_privs where grantee='DW_DML_ROLE';
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='BI_SEC';
revoke role from user
=========================
REVOKE <role_name> FROM <user_name>;
Roles
SELECT granted_role, admin_option FROM role_role_privs WHERE role = 'HS_ADMIN_SELECT_ROLE';
SQL>FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || your.table_names || ' TO <<user>>' ;
END LOOP ;
Roles
++++
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE like '%DBA%' and ADMIN_OPTION='YES';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED
_ROLE like '%DBA%';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE='DBA';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTEE
like '%ORACLE%';
select GRANTEE,PRIVILEGE,ADMIN_OPTION from dba_sys_privs where grantee like
'%ORACLE%';
to check what are the privilege assign to role
-------------------------------------------------------------
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='DW_DML_ROLE';
Karthik, Team,
The below incident was not worked upon correctly. The request was to:
Create a new role APPSDBARESTRICT in UNNPSHRD. It should have the following privileges:
CONNET
CREATE SESSION
SELECT ANY DICTIONARY
ADVISOR
EXECUTE privilege on DBMS_WORKLOAD_REPOSITORY
Create a new user SARATHJ1 in database UNNPSHRD. Assign APPSDBARESTRICT role to SARATHJ1.
Of this, the role APPSDBARESTRICT was created , but the role has not been assigned to SARATHJ1 but I see that it has been assigned to SYSTEM!!!. See below queries. Going forward, please do not close the Incident unless the requester agrees.
SQL> select name, sysdate from v$database;
NAME SYSDATE
--------- ---------
UNNPSHRD 28-NOV-12
SQL> set lines 300
SQL> set pages 1000
SQL> select * from dba_sys_privs where grantee='SARATHJ1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SARATHJ1 UNLIMITED TABLESPACE NO
SQL> select * from dba_ROLE_privs where grantee='SARATHJ1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SARATHJ1 CONNECT NO YES
SARATHJ1 RESOURCE NO YES
SQL> select * from dba_roles where role='APPSDBARESTRICT';
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
APPSDBARESTRICT NO NONE
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='APPSDBARESTRICT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM APPSDBARESTRICT YES YES
SQL> select * from dba_sys_privs where grantee='APPSDBARESTRICT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
APPSDBARESTRICT ADVISOR NO
APPSDBARESTRICT CREATE SESSION NO
APPSDBARESTRICT SELECT ANY DICTIONARY NO
-- System privileges granted to an user ( scott)
SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';
-- Roles granted to an user ( scott)
SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';
-- Object privileges granted to an user ( SCOTT)
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';
-- Column specific privileges granted
SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';/*for tables*/
select 'grant select on '||owner||'.'||table_name||' to <user name>' from dba_tables where owner=<schema>;
/*for procedures*/
select 'grant execute on '||owner||'.'||object_name||' to <user name>' from dba_objects where owner=<schema>
and object_type='PROCEDURE';
where OBJECT_TYPE='PROCEDURE'
Select 'GRANT SELECT ON GBO_DB.'||Table_Name||'TO GBO_READONLY' From All_Tables Where Owner='GBO_DB';
select 'GRANT SELECT ON GBO_DB.'||view_name||' TO GBO_READONLY' From dba_views where Owner='GBO_DB';
BEGIN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || GBO_DB.table_name || ' TO readon';
END LOOP;
END;
/
==================================================================================
SQL> CREATE USER books IDENTIFIED BY books
2 ;
User created.
SQL> GRANT CONNECT TO books;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE, DBA TO books;
select * from dba_sys_privs
grant debug connect session to SCDM;
GRANT DEBUG ANY PROCEDURE TO SCDM;
password expire
SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='SIMON';
What are the privileges that role contain
================================================================================
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DBMON';
select privilege from dba_sys_privs where grantee='DWUSER';
select * from dba_role_privs where grantee='DWUSER';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
DBMON APP_BASE_ROLE YES YES
DBMON CONNECT NO YES
DBMON SQLT_USER_ROLE NO YES
DBMON DBA NO YES
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DWUSER';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('SCOTT')
UNION
SELECT grantee,granted_role
FROM dba_role_privs
UNION
SELECT grantee,privilege
FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;
=================================================================================
Listing Privilege and Role Information
To list the grants made for objects, a user can query the following data dictionary views:
ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD
ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD
DBA_ROLES
USER_ROLE_PRIVS, DBA_ROLE_PRIVS
USER_SYS_PRIVS, DBA_SYS_PRIVS
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
SESSION_PRIVS, SESSION_ROLES
See Also: See the Oracle8 Reference for a detailed description of these data dictionary views.
Listing Privilege and Role Information: Examples
For the following examples, assume the following statements are issued:
CREATE ROLE security_admin IDENTIFIED BY honcho;
GRANT create profile, alter profile, drop profile,
create role, drop any role, grant any role, audit any,
audit system, create user, become user, alter user, drop user
TO security_admin WITH ADMIN OPTION;
GRANT SELECT, DELETE ON sys.aud$ TO security_admin;
GRANT security_admin, create session TO swilliams;
GRANT security_admin TO system_administrator;
GRANT create session TO jward;
GRANT SELECT, DELETE ON emp TO jward;
GRANT INSERT (ename, job) ON emp TO swilliams, jward;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Listing All System Privilege Grants
The following query indicates all system privilege grants made to roles and users:
SELECT * FROM sys.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
SWILLIAMS CREATE SESSION NO
JWARD CREATE SESSION NO
Listing All Role Grants
The following query returns all the roles granted to users and other roles:
SELECT * FROM sys.dba_role_privs;
GRANTEE GRANTED_ROLE ADM
------------------ ------------------------------------ ---
SWILLIAMS SECURITY_ADMIN NO
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 sys.dba_tab_privs WHERE grantee = 'JWARD';
TABLE_NAME PRIVILEGE GRANTABLE
----------- ------------ ----------
EMP SELECT NO
EMP DELETE NO
To list all the column specific privileges that have been granted, use the following query:
SELECT grantee, table_name, column_name, privilege FROM sys.dba_col_privs where grantee='GOURC';
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
JWARD 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 SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:
ROLE
------------------------------
SECURITY_ADMIN
The following query lists all system privileges currently available in the issuer's security domain, 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, 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 SWILLIAMS, the first query would have returned 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 sys.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 = 'SYSTEM_ADMIN';
GRANTED_ROLE ADM
---------------- ----
SECURITY_ADMIN NO
The following query lists all the system privileges granted to the SECURITY_ADMIN role:
SELECT * FROM role_sys_privs WHERE role = 'SECURITY_ADMIN';
ROLE 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
The following query lists all the object privileges granted to the SECURITY_ADMIN role:
SELECT table_name, privilege FROM role_tab_privs
WHERE role = 'SECURITY_ADMIN';
SELECT table_name, privilege FROM role_tab_privs WHERE role ='QUERY_ALL_ROLE';
TABLE_NAME PRIVILEGE
--------------------------- ----------------
AUD$ DELETE
AUD$ SELECT
SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1;
SQL> CONNECT u1/u1
Connected.
SQL> GRANT SELECT ON scott.t2 TO u2;
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'employees';
GRANTEE OWNER
------------------------------ ------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
U2 SCOTT
SCOTT SELECT NO
-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
2> FROM DBA_TAB_PRIVS
3> WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';
GRANTEE OWNER GRANTOR PRIVILEGE
-------------- -------------- ------------- ---------------
ESRVT_ROLE ESRVP ESRVP DELETE
-------------------------------------------------------------------------------------------------------------------------------------------------
----->SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
========================================================================================
grant select, insert, update, delete on suppliers to smithj;
grant select, insert, update, delete on suppliers to smithj;
grant select on Eagan to gourc;
===========================================================================================
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege Description
Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to user;
For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:
grant execute on Find_Value to smithj;
If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
SELECT grantee, table_name, column_name, privilege
FROM sys.dba_col_privs;
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
SQL> select privilege from dba_sys_privs where GRANTEE='ERMUSR';
PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
UNLIMITED TABLESPACE
9 rows selected.
grant sysdba to ermusr;
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='QUERY_ALL_ROLE';
select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
SELECT * FROM sys.dba_role_privs where grantee='MASTERSAF'
select * from dba_role_privs where granted_role='APPDBA';
SQL> desc dba_role_privs
Name Null? Type
------------ -------- ------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
To find a list of all users with DBA privilege execute the following code:
SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS DBA YES YES
SYSTEM DBA YES YES
WHAT ARE THE PRIVILEGES ASSAIGN TO ROLE
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='APPDBA';
=================================================================================
SELECT * FROM sys.dba_sys_privs where grantee='DW_DML_ROLE';
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='BI_SEC';
revoke role from user
=========================
REVOKE <role_name> FROM <user_name>;
Roles
SELECT granted_role, admin_option FROM role_role_privs WHERE role = 'HS_ADMIN_SELECT_ROLE';
SQL>FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || your.table_names || ' TO <<user>>' ;
END LOOP ;
Roles
++++
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE like '%DBA%' and ADMIN_OPTION='YES';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED
_ROLE like '%DBA%';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE='DBA';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTEE
like '%ORACLE%';
select GRANTEE,PRIVILEGE,ADMIN_OPTION from dba_sys_privs where grantee like
'%ORACLE%';
to check what are the privilege assign to role
-------------------------------------------------------------
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='DW_DML_ROLE';
Karthik, Team,
The below incident was not worked upon correctly. The request was to:
Create a new role APPSDBARESTRICT in UNNPSHRD. It should have the following privileges:
CONNET
CREATE SESSION
SELECT ANY DICTIONARY
ADVISOR
EXECUTE privilege on DBMS_WORKLOAD_REPOSITORY
Create a new user SARATHJ1 in database UNNPSHRD. Assign APPSDBARESTRICT role to SARATHJ1.
Of this, the role APPSDBARESTRICT was created , but the role has not been assigned to SARATHJ1 but I see that it has been assigned to SYSTEM!!!. See below queries. Going forward, please do not close the Incident unless the requester agrees.
SQL> select name, sysdate from v$database;
NAME SYSDATE
--------- ---------
UNNPSHRD 28-NOV-12
SQL> set lines 300
SQL> set pages 1000
SQL> select * from dba_sys_privs where grantee='SARATHJ1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SARATHJ1 UNLIMITED TABLESPACE NO
SQL> select * from dba_ROLE_privs where grantee='SARATHJ1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SARATHJ1 CONNECT NO YES
SARATHJ1 RESOURCE NO YES
SQL> select * from dba_roles where role='APPSDBARESTRICT';
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
APPSDBARESTRICT NO NONE
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='APPSDBARESTRICT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM APPSDBARESTRICT YES YES
SQL> select * from dba_sys_privs where grantee='APPSDBARESTRICT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
APPSDBARESTRICT ADVISOR NO
APPSDBARESTRICT CREATE SESSION NO
APPSDBARESTRICT SELECT ANY DICTIONARY NO
Sunday, 11 November 2018
TNS-12541: TNS:no listener
TNS-12541: TNS:no listener
The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.
TNS-12545: Connect failed because target host or object does not exist
The host in specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping '. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
What are the symptoms?
The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LIST_ANDY'
Why is this happening?
Oracle only checks for listeners running on the default port (1521). It would have to spend all day trying every possible port number otherwise. You'll need to give it some help to find your listener.
How do I fix it?
Simply add an entry to the servers tnsnames.ora pointing at the listener. For example my listener 'LIST_ANDY' is running on port 3801, so I would add the following entry to my tnsnames:
LIST_ANDY = (ADDRESS = (PROTOCOL = TCP)(HOST = fermat)(PORT = 3801))
Test it with tnsping:
tnsping
Then try starting your instance again - it should work now.
The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.
TNS-12545: Connect failed because target host or object does not exist
The host in specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping '. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
What are the symptoms?
The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LIST_ANDY'
Why is this happening?
Oracle only checks for listeners running on the default port (1521). It would have to spend all day trying every possible port number otherwise. You'll need to give it some help to find your listener.
How do I fix it?
Simply add an entry to the servers tnsnames.ora pointing at the listener. For example my listener 'LIST_ANDY' is running on port 3801, so I would add the following entry to my tnsnames:
LIST_ANDY = (ADDRESS = (PROTOCOL = TCP)(HOST = fermat)(PORT = 3801))
Test it with tnsping:
tnsping
Then try starting your instance again - it should work now.
ORA-12505: TNS:listener does not currently know of SID given in connect
Either the SID is misspelled in the tnsnames, or the listener isn't listening for it. Check the tnsnames.ora first. If it looks ok, do a 'lsnrctl status' on your server, to see what databases the listener is listening for.
Subscribe to:
Posts (Atom)