Always create CONSTRAINT with name
Example for create table
create table supplier
("s#" varchar2(4) constraint pk_supplier primary key,
sname varchar2(10) constraint nn_sname not null,
cname varchar2(10) constraint nn_cname not null,
salary number(6) default 500 constraint nn_salary not null,
worksfor varchar2(10),
theKey int constraint fk_thekey references parent(theKey),
constraint ck_salary check ((salary > 500) or (salary = 500)))
create table y(stno number constraint ref_y references x(stno) on delete cascade );
SQL> CREATE TABLE dept
(
deptno NUMBER(2)
CONSTRAINT pk_dept PRIMARY KEY
CONSTRAINT ck_deptno CHECK (deptno BETWEEN 10 and 99),
dname VARCHAR2(9) CONSTRAINT ck_dname CHECK (dname=UPPER(dname)),
loc VARCHAR2(10) CONSTRAINT ck_loc CHECK (loc IN ('DALLAS','BOSTON','CHICAGO'))
)
Table created.
create table taAccountTransaction(
AccountNumber char(8) not null
constraint coFKTransactiontaAccount references taAccount on delete cascade,
DateAndTime date not null,
Amount number(14,2) not null,
constraint coPKtaAccountTransaction primary key (AccountNumber, DateAndTime)
using index tablespace tsIndex) tablespace tsData;
CREATE TABLE order_detail
(order_id Number Constraint fk_oid REFERENCES scott.order(order_id),
(part_no Number Constraint fk_pno REFERENCES part(part_no),
(quantity Number Constraint nn_qty Not NULL Constraint check_qty_low CHECK (quantity > 0),
(cost Number Constraint check_cost CHECK (cost>0),
(Constraint pk_od PRIMARY KEY (order_id, part_no))
Wednesday, 24 August 2011
Oracle user Role
user Role
Oracle role info
Oracle create user
undefine user
SELECT GRANTEE, 'ROL' TYPE, GRANTED_ROLE PV
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = upper('&&user')
UNION
SELECT GRANTEE, 'PRV' TYPE, PRIVILEGE PV
FROM DBA_SYS_PRIVS
WHERE GRANTEE = upper('&&user')
UNION
SELECT GRANTEE,
'OBJ' TYPE,
MAX(DECODE(PRIVILEGE, 'WRITE', 'WRITE,')) ||
MAX(DECODE(PRIVILEGE, 'READ', 'READ')) ||
MAX(DECODE(PRIVILEGE, 'EXECUTE', 'EXECUTE')) ||
MAX(DECODE(PRIVILEGE, 'SELECT', 'SELECT')) ||
MAX(DECODE(PRIVILEGE, 'DELETE', ',DELETE')) ||
MAX(DECODE(PRIVILEGE, 'UPDATE', ',UPDATE')) ||
MAX(DECODE(PRIVILEGE, 'INSERT', ',INSERT')) || ' ON ' || OBJECT_TYPE || ' "' ||
A.OWNER || '.' || TABLE_NAME || '"' PV
FROM DBA_TAB_PRIVS A, DBA_OBJECTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.OBJECT_NAME
AND A.GRANTEE = upper('&&user')
GROUP BY A.OWNER, TABLE_NAME, OBJECT_TYPE, GRANTEE
UNION
SELECT USERNAME GRANTEE, '---' TYPE, 'empty user ---' PV
FROM DBA_USERS
WHERE NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_ROLE_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_TAB_PRIVS)
AND USERNAME LIKE upper('&&user')
GROUP BY USERNAME
ORDER BY GRANTEE, TYPE, PV;
undefine user
~
~
SQL> @role
Enter value for user: scott
old 3: WHERE GRANTEE = upper('&&user')
new 3: WHERE GRANTEE = upper('scott')
old 7: WHERE GRANTEE = upper('&&user')
new 7: WHERE GRANTEE = upper('scott')
old 22: AND A.GRANTEE = upper('&&user')
new 22: AND A.GRANTEE = upper('scott')
old 30: AND USERNAME LIKE upper('&&user')
new 30: AND USERNAME LIKE upper('scott')
GRANTEE TYPE PV
------------------------------ ------------ -----------------------------------------------------------------------------------------------------------------------------------
SCOTT PRV UNLIMITED TABLESPACE
SCOTT ROL CONNECT
SCOTT ROL RESOURCE
=================================================
---- **** role1.sql
set pagesize 58 linesize 131
rem
column username format a15 heading User
COLUMN account_status format a10 heading Status
column default_tablespace format a15 heading Default
column temporary_tablespace format a15 heading "Temporary"
column granted_role format a31 heading Roles
column default_role format a10 heading Default?
column admin_option format a7 heading Admin?
column profile format a15 heading 'Users Profile'
rem
start title132 'ORACLE USER REPORT'
-- define output = rep_out\db\usr_rep
break on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
spool role-info.txt
rem
select username, account_status, default_tablespace, temporary_tablespace, profile, granted_role, admin_option, default_role
from sys.dba_users a, sys.dba_role_privs b
where a.username = b.grantee
order by username,account_status, default_tablespace, temporary_tablespace,
profile, granted_role;
rem
spool off
set termout on flush on feedback on verify on
clear columns
clear breaks
pause Press enter to continue
undefine 1
SET VERIFY OFF FEEDBACK OFF PAGES 10000 LIN 80 RECSEP OFF ECHO OFF ARRAY 5
CL COL
SPOOL output.lst
COL username NOPRINT
COL external_name NOPRINT
COL default_tablespace FOR A16 HEADING "Default TS"
COL temporary_tablespace FOR A16 HEADING "Temporary TS"
COL account_status FOR A16
COL password FOR A16
COL profile FOR A16
COL grantee FOR A16 NOPRINT
COL owner FOR A16
COL table_name FOR A16
COL grantor FOR A16
PROMPT
PROMPT ============================= DBA_USERS ===============================
SELECT * FROM dba_users WHERE username = UPPER('&1')
/
PROMPT
PROMPT ============================= Granted roles ===========================
SELECT * FROM dba_role_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted system privileges ===============
SELECT * FROM dba_sys_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted object privileges ===============
COL privilege FOR A20 WORD_WRAPPED
SELECT * FROM dba_tab_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted column privileges ===============
SELECT * FROM dba_col_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Tablespace quotas =======================
SELECT tablespace_name, DECODE(max_bytes ,-1,'UNLIMITED',TO_CHAR(max_bytes/1048576,9999.9 )||' Mb') "Quota"
FROM dba_ts_quotas WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Database objects ========================
SELECT object_type, COUNT(*) FROM dba_objects
WHERE owner = UPPER('&&1')
GROUP BY object_type
/
PROMPT
PROMPT ============================= End of report ===========================
SPOOL OFF
UNDEFINE USER
PROMPT Output is spooled in output.lst
SET FEEDBACK ON RECSEP WR
SET PAGES 24
CL COL
undefine 1
--- -**** role1.sql ---<<<<<<<<<<<<<<<<<<<<<<<
sample output -----------------
User Status Default Temporary Users Profile Roles Admin? Default?
--------------- ---------- --------------- --------------- --------------- --------------------- ------- ----------
WMSYS EXPIRED & SYSAUX TEMP1 DEFAULT RESOURCE NO YES
LOCKED
WM_ADMIN_ROLE YES YES
XDB EXPIRED & SYSAUX TEMP1 DEFAULT CTXAPP NO YES
LOCKED
JAVAUSERPRIV NO YES
RESOURCE NO YES
105 rows selected.
Press enter to continue
============================= DBA_USERS ===============================
Enter value for 1: BOEXI1
USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
---------- ---------------- ---------------- --------- ---------
Default TS Temporary TS CREATED PROFILE
---------------- ---------------- --------- ----------------
INITIAL_RSRC_CONSUMER_GROUP PASSWORD E AUTHENTI
------------------------------ -------- - --------
89 OPEN 27-FEB-12
APTUS_OBJ TEMP1 31-AUG-11 DEFAULT
DEFAULT_CONSUMER_GROUP 10G 11G N PASSWORD
============================= Granted roles ===========================
Enter value for 1: BOEXI1
GRANTED_ROLE ADM DEF
------------------------------ --- ---
CONNECT NO YES
RESOURCE NO YES
============================= Granted system privileges ===============
PRIVILEGE ADM
---------------------------------------- ---
UNLIMITED TABLESPACE NO
============================= Granted object privileges ===============
============================= Granted column privileges ===============
============================= Tablespace quotas =======================
============================= Database objects ========================
============================= End of report ===========================
==================
role2.sql ----------<<<<<<<<<<<<<<
set pages 1001
set lines 132
set verify off
set echo off
set head off
accept role_nm prompt 'Enter Role Name >>->'
prompt 'Role Has Following Object Privileges : '
select grantee || ' Have ' || lpad(substr(privilege, 1, 10), 10, ' ') ||
' On ' || substr(owner || '.' || table_name,1,50)
from dba_tab_privs
where owner = 'prod_schema_owner'
and grantee = upper('&role_nm')
order by grantee;
prompt 'Role Has Following System Privileges : '
select grantee || ' Have ' || lpad(substr(privilege, 1, 30), 30, ' ')
|| decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_sys_privs
where grantee = upper('&&role_nm')
order by grantee;
prompt 'Role Has Following Roles Granted : '
select grantee || ' Have Role' || lpad(substr(granted_role, 1, 30), 30, ' ')
|| decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where grantee = upper('&&role_nm')
order by grantee;
prompt 'Role is Granted to Following Roles : '
select 'Role ' || granted_role || ' Is Granted to Role ' || grantee ||
decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where granted_role = upper('&&role_nm')
and grantee in (select role from dba_roles)
order by grantee;
prompt 'Role is Granted to Following Users : '
select 'Role ' || granted_role || ' Is Granted to User ' || grantee ||
decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where granted_role = upper('&&role_nm')
and grantee in (select username from dba_users)
order by grantee;
set verify on
set echo on
set head on
SQL> @role2
Enter Role Name >>->connect
'Role Has Following Object Privileges : '
no rows selected
'Role Has Following System Privileges : '
CONNECT Have CREATE SESSION
1 row selected.
'Role Has Following Roles Granted : '
no rows selected
'Role is Granted to Following Roles : '
no rows selected
'Role is Granted to Following Users : '
Role CONNECT Is Granted to User ABC
Role CONNECT Is Granted to User ANUJ
Role CONNECT Is Granted to User ANUJREP
Role CONNECT Is Granted to User ANUJTEST
Role CONNECT Is Granted to User APEX_030200 With Admin Option
Role CONNECT Is Granted to User APEX_040000
Role CONNECT Is Granted to User GGATE
Role CONNECT Is Granted to User IX
Role CONNECT Is Granted to User MDDATA
Role CONNECT Is Granted to User MDSYS
Role CONNECT Is Granted to User OWBSYS With Admin Option
Role CONNECT Is Granted to User PM
Role CONNECT Is Granted to User SCOTT
Role CONNECT Is Granted to User SPATIAL_CSW_ADMIN_USR
Role CONNECT Is Granted to User SPATIAL_WFS_ADMIN_USR
Role CONNECT Is Granted to User SYS With Admin Option
Role CONNECT Is Granted to User TEST_USER With Admin Option
Role CONNECT Is Granted to User WMSYS
Role CONNECT Is Granted to User XYZ
======================
all_users
all_tables
user_sys_privs
sys.dba_users
sys.dba_tab_privs
sys.dba_sys_privs
sys.dba_profiles
sys.dba_roles
sys.dba_role_privs
sys.dba_audit_trail
sys.aud$
set linesize 121
col username format a25
col profile format a10
col "tmp tbs" format a10
SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role,r.admin_option, r.default_role
FROM sys.dba_users u, sys.dba_role_privs r
WHERE u.username = r.grantee (+)
GROUP BY u.username, u.default_tablespace,
u.temporary_tablespace, u.profile, r.granted_role,
r.admin_option, r.default_role
order by 1;
set pause off;
set linesize 78;
set pagesize 56;
set newpage 0;
column c1 heading "User" format a20;
column c2 heading "Privilege";
column c3 heading "Default TS" format a10;
column c4 heading "Temp TS" format a10;
select substr(dba_users.username,1,20) c1,
dba_role_privs.granted_role c2,
substr(dba_users.default_tablespace,1,15) c3,
substr(dba_users.temporary_tablespace,1,15) c4
from sys.dba_role_privs, sys.dba_users
where sys.dba_role_privs.grantee = sys.dba_users.username
order by 1,2;
Don't give connect,resource to any user while creating user
SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
FROM sys.dba_users a, sys.dba_role_privs b
wHERE a.username = b.grantee
and a.username = upper('&&user')
UNION
SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
WHERE a.username = b.grantee
and a.username = upper('&&user')
UNION
SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
FROM sys.dba_users a, sys.dba_tab_privs b
WHERE a.username = b.grantee
and a.username = upper('&&user')
ORDER BY 1;
USERNAME WHAT_GRANTED
------------------------------ ---------------------------------------------------------------------------------------------
SCOTT CONNECT
SCOTT RESOURCE
SCOTT UNLIMITED TABLESPACE -----not very good idea ....
SQL> revoke UNLIMITED TABLESPACE from scott;
Revoke succeeded.
SQL> SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
2 FROM sys.dba_users a, sys.dba_role_privs b
3 wHERE a.username = b.grantee
4 and a.username = upper('&&user')
5 UNION
6 SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
7 8 WHERE a.username = b.grantee
9 and a.username = upper('&&user')
10 UNION
11 SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
12 FROM sys.dba_users a, sys.dba_tab_privs b
13 WHERE a.username = b.grantee
14 and a.username = upper('&&user')
15 ORDER BY 1;
Enter value for user: scott
old 4: and a.username = upper('&&user')
new 4: and a.username = upper('scott')
old 9: and a.username = upper('&&user')
new 9: and a.username = upper('scott')
old 14: and a.username = upper('&&user')
new 14: and a.username = upper('scott')
USERNAME WHAT_GRANTED
------------------------------ ---------------------------------------------------------------------------------------------
SCOTT CONNECT
SCOTT RESOURCE
select * from sys.dba_sys_privs where GRANTEE=upper('&role') ;
select distinct grantee from dba_sys_privs ;
select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';
select grantee, granted_role from dba_role_privs where grantee not in ('SYS', 'SYSTEM','DBA') order by grantee;
create user rrr identified by rr
default tablespace users
temporary tablespace temp
quota unlimited on users
quota unlimited on temp ---- 10gR2 . you cannot grant quota on temporary tablespace
/
SQL> create user rrr identified by rrr
default tablespace users
temporary tablespace temp
quota unlimited on users
quota unlimited on temp
/
create user rrr identified by rr
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace
Note: 114673.1 RESOURCE Role in DBA_SYS_PRIVS does not Include UNLIMITED TABLESPACE Privilege
Note: 1005485.6 ORA-1950 When Creating an Object and Resource Role is Granted to the User
Note: 1084014.6 Revoking DBA or RESOURCE Privilege Revokes UNLIMITED TABLESPACE from the User
SELECT * FROM user_sys_privs;
select * from session_privs;
SQL> create user rrr identified by rrr
default tablespace users
temporary tablespace temp
quota unlimited on users
/
User created.
SQL> ALTER USER scott QUOTA UNLIMITED ON users;
User altered.
--- connect
grant CREATE SESSION to rrr ;
resoure ---
grant CREATE TRIGGER to rrr ;
grant CREATE SEQUENCE to rrr ;
grant CREATE TYPE to rrr ;
grant CREATE PROCEDURE to rrr ;
grant CREATE CLUSTER to rrr ;
grant CREATE OPERATOR to rrr ;
grant CREATE INDEXTYPE to rrr ;
grant CREATE TABLE to rrr ;
grant create view to rrr ;
grant CREATE ANY INDEX to rrr ; ?????
Grant succeeded.
revoke CREATE SESSION from rrr ;
Revoke succeeded.
SQL> REVOKE CREATE ANY INDEX FROM rrr ;
Revoke succeeded.
SQL> SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
2 FROM sys.dba_users a, sys.dba_role_privs b
3 wHERE a.username = b.grantee
4 and a.username = upper('&&user')
5 UNION
6 SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
7 FROM sys.dba_users a, sys.dba_sys_privs b
8 WHERE a.username = b.grantee
9 and a.username = upper('&&user')
10 UNION
11 SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
12 FROM sys.dba_users a, sys.dba_tab_privs b
13 WHERE a.username = b.grantee
14 and a.username = upper('&&user')
15 ORDER BY 1;
old 4: and a.username = upper('&&user')
new 4: and a.username = upper('RRR')
old 9: and a.username = upper('&&user')
new 9: and a.username = upper('RRR')
old 14: and a.username = upper('&&user')
new 14: and a.username = upper('RRR')
USERNAME WHAT_GRANTED
-------------------- ---------------------------------------------------------------------------------------------
RRR CREATE CLUSTER
RRR CREATE INDEXTYPE
RRR CREATE OPERATOR
RRR CREATE PROCEDURE
RRR CREATE SEQUENCE
RRR CREATE SESSION
RRR CREATE TABLE
RRR CREATE TRIGGER
RRR CREATE TYPE
RRR CREATE VIEW
10 rows selected.
select * from session_privs;
The UNLIMITED TABLESPACE system privilege does not let a user create their own datafiles.
The CREATE TABLESPACE system privilege lets a user create a tablespace. The ALTER TABLESPACE system
privilege lets a user add a datafile to an
SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='SCOTT';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMP
ALTER USER scott QUOTA unlimited ON users;
ALTER USER scott QUOTA 10M ON users;
SELECT * FROM DBA_ts_quotas;
A quota value of MAX_BYTES -1 indicated UNLIMITED
SQL> SELECT U.USERNAME,R.GRANTED_ROLE FROM SYS.DBA_USERS U,SYS.DBA_ROLE_PRIVS R
WHERE U.USERNAME=R.GRANTEE(+)
AND R.ADMIN_OPTION='NO'
AND U.USERNAME = UPPER('SCOTT')
GROUP BY U.USERNAME,R.GRANTED_ROLE
ORDER BY U.USERNAME ;
USERNAME GRANTED_ROLE
------------------------------ ------------------------------
SCOTT RESOURCE
SCOTT CONNECT
SQL> SELECT R.GRANTED_ROLE,U.USERNAME FROM SYS.DBA_USERS U,SYS.DBA_ROLE_PRIVS R WHERE U.USERNAME=R.GRANTEE(+)
AND R.ADMIN_OPTION='NO'
AND R.GRANTED_ROLE = 'CONNECT'
GROUP BY U.USERNAME,R.GRANTED_ROLE
ORDER BY U.USERNAME ;
GRANTED_ROLE USERNAME
------------------------------ ------------------------------
CONNECT ABC
CONNECT ANUJ
CONNECT ANUJREP
CONNECT ANUJTEST
CONNECT APEX_040000
CONNECT GGATE
CONNECT IX
CONNECT MDDATA
CONNECT MDSYS
CONNECT PM
CONNECT SCOTT
CONNECT SPATIAL_CSW_ADMIN_USR
CONNECT SPATIAL_WFS_ADMIN_USR
CONNECT WMSYS
CONNECT XYZ
15 rows selected.
SELECT table_name FROM dba_tab_privs p,dba_objects o
WHERE p.owner=o.owner
--- Managing Default User Accounts 333
AND p.table_name = o.object_name
-- AND p.owner = 'SCOTT'
AND p.privilege = 'EXECUTE'
AND p.grantee = 'PUBLIC'
AND o.object_type='PACKAGE';
col USERNAME format a25
col DEFAULT_TABLESPACE format a15
col PROFILE format a10
col GRANTED_ROLE format a25
SELECT usr.username, usr.default_tablespace, usr.temporary_tablespace "TMP TBS", usr.profile,rl.granted_role, rl.admin_option, rl.default_role
FROM sys.dba_users usr, sys.dba_role_privs rl
WHERE usr.username = rl.grantee (+)
and usr.username not in ('SYS','SYSTEM')
and usr.username=upper('&&user')
GROUP BY usr.username, usr.default_tablespace,usr.temporary_tablespace, usr.profile, rl.granted_role, rl.admin_option, rl.default_role;
USERNAME DEFAULT_TABLESP TMP TBS PROFILE GRANTED_ROLE ADM DEF
------------------------- --------------- ------------------------------ ---------- ------------------------- --- ---
WMSYS SYSAUX TEMP1 DEFAULT RESOURCE NO YES
OWBSYS SYSAUX TEMP1 DEFAULT JAVAUSERPRIV NO YES
MGMT_VIEW SYSTEM TEMP1 DEFAULT MGMT_USER NO YES
BOEXI APTUS_OBJ TEMP1 DEFAULT RESOURCE NO YES
select * from role_sys_privs where role in (select role from dba_role_privs where role=upper('&role') )
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
select * from role_role_privs where role in (select role from user_role_privs where username = 'SCOTT')
CREATE ROLE test_role IDENTIFIED BY test123;
grant select, insert, update, delete on suppliers to test_role;
SQL> SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS;
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_1 UPDATE
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_2 UPDATE
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_3 UPDATE
FLOWS_FILES WWV_FLOW_COMPANIES PROVISIONING_COMPANY_ID REFERENCES
FLOWS_FILES WWV_FLOW_COMPANIES PROVISIONING_COMPANY_ID REFERENCES
=====
===============
SQL> show user
USER is "SYS"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
SYSDBA
SYSOPER
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
ALTER ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
EXEMPT ACCESS POLICY
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
EXEMPT IDENTITY POLICY
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
CREATE ANY EDITION
DROP ANY EDITION
ALTER ANY EDITION
CREATE ASSEMBLY
CREATE ANY ASSEMBLY
ALTER ANY ASSEMBLY
DROP ANY ASSEMBLY
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
CREATE MINING MODEL
CREATE ANY MINING MODEL
DROP ANY MINING MODEL
SELECT ANY MINING MODEL
ALTER ANY MINING MODEL
COMMENT ANY MINING MODEL
CREATE CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
SELECT ANY CUBE DIMENSION
CREATE CUBE
ALTER ANY CUBE
CREATE ANY CUBE
DROP ANY CUBE
SELECT ANY CUBE
UPDATE ANY CUBE
CREATE MEASURE FOLDER
CREATE ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
INSERT ANY MEASURE FOLDER
CREATE CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DROP ANY CUBE BUILD PROCESS
UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
ADMINISTER SQL MANAGEMENT OBJECT
ALTER PUBLIC DATABASE LINK
ALTER DATABASE LINK
FLASHBACK ARCHIVE ADMINISTER
208 rows selected.
===========
COL roles FOR a60
COL table_name FOR a30
col privilege for a9
set lin 200 trims on pages 0 emb on hea on newp none
SELECT *
FROM ( SELECT CONNECT_BY_ROOT grantee grantee,
privilege,
REPLACE (
REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
'^/[^/]*'),
'/',
' --> ')
ROLES,
owner,
table_name,
column_name
FROM (SELECT PRIVILEGE,
GRANTEE,
OWNER,
TABLE_NAME,
NULL column_name
FROM DBA_TAB_PRIVS
WHERE owner NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')
UNION
SELECT PRIVILEGE,
GRANTEE,
OWNER,
TABLE_NAME,
column_name
FROM DBA_COL_PRIVS
WHERE owner NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')
UNION
SELECT GRANTED_ROLE,
GRANTEE,
NULL,
NULL,
NULL
FROM DBA_ROLE_PRIVS
WHERE GRANTEE NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')) T
START WITH grantee IN (SELECT username FROM dba_users)
CONNECT BY PRIOR PRIVILEGE = GRANTEE)
WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,
owner,
table_name,
column_name,
privilege;
=================
VARIABLE user_name VARCHAR2(255);
exec :user_name :='&user_name' ;
WITH user_roles AS
(SELECT granted_role FROM dba_role_privs START WITH grantee =:user_name CONNECT BY grantee = prior granted_role
UNION ALL
SELECT username FROM dba_users WHERE username =:user_name
)
SELECT lpad(' ', 2 *(level - 1)) || privilege "Privilege",
type AS "Privilege_Type",
object_type AS "Object_Type",
owner AS "Owner",
table_name AS "Name",
grantable AS "Grantable",
grantor AS "Grantor",
hierarchy AS "Hierarchy"
FROM
(SELECT grantee,
privilege,
'Object' AS type,
do.object_type,
dtp.owner,
dtp.table_name,
dtp.grantable,
dtp.grantor,
dtp.hierarchy
FROM dba_tab_privs dtp
JOIN dba_objects DO
ON dtp.owner = do.owner
AND dtp.table_name = do.object_name
JOIN user_roles
ON dtp.grantee = user_roles.granted_role
WHERE NOT object_type LIKE '%_BODY'
UNION ALL
SELECT grantee,
privilege,
'Column' AS type,
'TABLE COLUMN' AS object_type,
owner,
table_name || ' (' || column_name || ')' AS table_name,
grantable,
grantor,
'NO' AS hierarchy
FROM dba_col_privs
JOIN user_roles
ON dba_col_privs.grantee = user_roles.granted_role
UNION ALL
SELECT grantee,
granted_role AS privilege,
'Role' AS type,
NULL AS object_type,
NULL AS owner,
NULL AS table_name,
NULL AS grantable,
NULL AS grantor,
NULL AS hierarchy
FROM dba_role_privs
)
START WITH grantee =:user_name
CONNECT BY grantee = prior privilege
Oracle role info
Oracle create user
undefine user
SELECT GRANTEE, 'ROL' TYPE, GRANTED_ROLE PV
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = upper('&&user')
UNION
SELECT GRANTEE, 'PRV' TYPE, PRIVILEGE PV
FROM DBA_SYS_PRIVS
WHERE GRANTEE = upper('&&user')
UNION
SELECT GRANTEE,
'OBJ' TYPE,
MAX(DECODE(PRIVILEGE, 'WRITE', 'WRITE,')) ||
MAX(DECODE(PRIVILEGE, 'READ', 'READ')) ||
MAX(DECODE(PRIVILEGE, 'EXECUTE', 'EXECUTE')) ||
MAX(DECODE(PRIVILEGE, 'SELECT', 'SELECT')) ||
MAX(DECODE(PRIVILEGE, 'DELETE', ',DELETE')) ||
MAX(DECODE(PRIVILEGE, 'UPDATE', ',UPDATE')) ||
MAX(DECODE(PRIVILEGE, 'INSERT', ',INSERT')) || ' ON ' || OBJECT_TYPE || ' "' ||
A.OWNER || '.' || TABLE_NAME || '"' PV
FROM DBA_TAB_PRIVS A, DBA_OBJECTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.OBJECT_NAME
AND A.GRANTEE = upper('&&user')
GROUP BY A.OWNER, TABLE_NAME, OBJECT_TYPE, GRANTEE
UNION
SELECT USERNAME GRANTEE, '---' TYPE, 'empty user ---' PV
FROM DBA_USERS
WHERE NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_ROLE_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_TAB_PRIVS)
AND USERNAME LIKE upper('&&user')
GROUP BY USERNAME
ORDER BY GRANTEE, TYPE, PV;
undefine user
~
~
SQL> @role
Enter value for user: scott
old 3: WHERE GRANTEE = upper('&&user')
new 3: WHERE GRANTEE = upper('scott')
old 7: WHERE GRANTEE = upper('&&user')
new 7: WHERE GRANTEE = upper('scott')
old 22: AND A.GRANTEE = upper('&&user')
new 22: AND A.GRANTEE = upper('scott')
old 30: AND USERNAME LIKE upper('&&user')
new 30: AND USERNAME LIKE upper('scott')
GRANTEE TYPE PV
------------------------------ ------------ -----------------------------------------------------------------------------------------------------------------------------------
SCOTT PRV UNLIMITED TABLESPACE
SCOTT ROL CONNECT
SCOTT ROL RESOURCE
=================================================
---- **** role1.sql
set pagesize 58 linesize 131
rem
column username format a15 heading User
COLUMN account_status format a10 heading Status
column default_tablespace format a15 heading Default
column temporary_tablespace format a15 heading "Temporary"
column granted_role format a31 heading Roles
column default_role format a10 heading Default?
column admin_option format a7 heading Admin?
column profile format a15 heading 'Users Profile'
rem
start title132 'ORACLE USER REPORT'
-- define output = rep_out\db\usr_rep
break on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
spool role-info.txt
rem
select username, account_status, default_tablespace, temporary_tablespace, profile, granted_role, admin_option, default_role
from sys.dba_users a, sys.dba_role_privs b
where a.username = b.grantee
order by username,account_status, default_tablespace, temporary_tablespace,
profile, granted_role;
rem
spool off
set termout on flush on feedback on verify on
clear columns
clear breaks
pause Press enter to continue
undefine 1
SET VERIFY OFF FEEDBACK OFF PAGES 10000 LIN 80 RECSEP OFF ECHO OFF ARRAY 5
CL COL
SPOOL output.lst
COL username NOPRINT
COL external_name NOPRINT
COL default_tablespace FOR A16 HEADING "Default TS"
COL temporary_tablespace FOR A16 HEADING "Temporary TS"
COL account_status FOR A16
COL password FOR A16
COL profile FOR A16
COL grantee FOR A16 NOPRINT
COL owner FOR A16
COL table_name FOR A16
COL grantor FOR A16
PROMPT
PROMPT ============================= DBA_USERS ===============================
SELECT * FROM dba_users WHERE username = UPPER('&1')
/
PROMPT
PROMPT ============================= Granted roles ===========================
SELECT * FROM dba_role_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted system privileges ===============
SELECT * FROM dba_sys_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted object privileges ===============
COL privilege FOR A20 WORD_WRAPPED
SELECT * FROM dba_tab_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted column privileges ===============
SELECT * FROM dba_col_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Tablespace quotas =======================
SELECT tablespace_name, DECODE(max_bytes ,-1,'UNLIMITED',TO_CHAR(max_bytes/1048576,9999.9 )||' Mb') "Quota"
FROM dba_ts_quotas WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Database objects ========================
SELECT object_type, COUNT(*) FROM dba_objects
WHERE owner = UPPER('&&1')
GROUP BY object_type
/
PROMPT
PROMPT ============================= End of report ===========================
SPOOL OFF
UNDEFINE USER
PROMPT Output is spooled in output.lst
SET FEEDBACK ON RECSEP WR
SET PAGES 24
CL COL
undefine 1
--- -**** role1.sql ---<<<<<<<<<<<<<<<<<<<<<<<
sample output -----------------
User Status Default Temporary Users Profile Roles Admin? Default?
--------------- ---------- --------------- --------------- --------------- --------------------- ------- ----------
WMSYS EXPIRED & SYSAUX TEMP1 DEFAULT RESOURCE NO YES
LOCKED
WM_ADMIN_ROLE YES YES
XDB EXPIRED & SYSAUX TEMP1 DEFAULT CTXAPP NO YES
LOCKED
JAVAUSERPRIV NO YES
RESOURCE NO YES
105 rows selected.
Press enter to continue
============================= DBA_USERS ===============================
Enter value for 1: BOEXI1
USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
---------- ---------------- ---------------- --------- ---------
Default TS Temporary TS CREATED PROFILE
---------------- ---------------- --------- ----------------
INITIAL_RSRC_CONSUMER_GROUP PASSWORD E AUTHENTI
------------------------------ -------- - --------
89 OPEN 27-FEB-12
APTUS_OBJ TEMP1 31-AUG-11 DEFAULT
DEFAULT_CONSUMER_GROUP 10G 11G N PASSWORD
============================= Granted roles ===========================
Enter value for 1: BOEXI1
GRANTED_ROLE ADM DEF
------------------------------ --- ---
CONNECT NO YES
RESOURCE NO YES
============================= Granted system privileges ===============
PRIVILEGE ADM
---------------------------------------- ---
UNLIMITED TABLESPACE NO
============================= Granted object privileges ===============
============================= Granted column privileges ===============
============================= Tablespace quotas =======================
============================= Database objects ========================
============================= End of report ===========================
==================
role2.sql ----------<<<<<<<<<<<<<<
set pages 1001
set lines 132
set verify off
set echo off
set head off
accept role_nm prompt 'Enter Role Name >>->'
prompt 'Role Has Following Object Privileges : '
select grantee || ' Have ' || lpad(substr(privilege, 1, 10), 10, ' ') ||
' On ' || substr(owner || '.' || table_name,1,50)
from dba_tab_privs
where owner = 'prod_schema_owner'
and grantee = upper('&role_nm')
order by grantee;
prompt 'Role Has Following System Privileges : '
select grantee || ' Have ' || lpad(substr(privilege, 1, 30), 30, ' ')
|| decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_sys_privs
where grantee = upper('&&role_nm')
order by grantee;
prompt 'Role Has Following Roles Granted : '
select grantee || ' Have Role' || lpad(substr(granted_role, 1, 30), 30, ' ')
|| decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where grantee = upper('&&role_nm')
order by grantee;
prompt 'Role is Granted to Following Roles : '
select 'Role ' || granted_role || ' Is Granted to Role ' || grantee ||
decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where granted_role = upper('&&role_nm')
and grantee in (select role from dba_roles)
order by grantee;
prompt 'Role is Granted to Following Users : '
select 'Role ' || granted_role || ' Is Granted to User ' || grantee ||
decode(admin_option, 'YES', ' With Admin Option', 'NO', ' ')
from dba_role_privs
where granted_role = upper('&&role_nm')
and grantee in (select username from dba_users)
order by grantee;
set verify on
set echo on
set head on
SQL> @role2
Enter Role Name >>->connect
'Role Has Following Object Privileges : '
no rows selected
'Role Has Following System Privileges : '
CONNECT Have CREATE SESSION
1 row selected.
'Role Has Following Roles Granted : '
no rows selected
'Role is Granted to Following Roles : '
no rows selected
'Role is Granted to Following Users : '
Role CONNECT Is Granted to User ABC
Role CONNECT Is Granted to User ANUJ
Role CONNECT Is Granted to User ANUJREP
Role CONNECT Is Granted to User ANUJTEST
Role CONNECT Is Granted to User APEX_030200 With Admin Option
Role CONNECT Is Granted to User APEX_040000
Role CONNECT Is Granted to User GGATE
Role CONNECT Is Granted to User IX
Role CONNECT Is Granted to User MDDATA
Role CONNECT Is Granted to User MDSYS
Role CONNECT Is Granted to User OWBSYS With Admin Option
Role CONNECT Is Granted to User PM
Role CONNECT Is Granted to User SCOTT
Role CONNECT Is Granted to User SPATIAL_CSW_ADMIN_USR
Role CONNECT Is Granted to User SPATIAL_WFS_ADMIN_USR
Role CONNECT Is Granted to User SYS With Admin Option
Role CONNECT Is Granted to User TEST_USER With Admin Option
Role CONNECT Is Granted to User WMSYS
Role CONNECT Is Granted to User XYZ
======================
all_users
all_tables
user_sys_privs
sys.dba_users
sys.dba_tab_privs
sys.dba_sys_privs
sys.dba_profiles
sys.dba_roles
sys.dba_role_privs
sys.dba_audit_trail
sys.aud$
set linesize 121
col username format a25
col profile format a10
col "tmp tbs" format a10
SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role,r.admin_option, r.default_role
FROM sys.dba_users u, sys.dba_role_privs r
WHERE u.username = r.grantee (+)
GROUP BY u.username, u.default_tablespace,
u.temporary_tablespace, u.profile, r.granted_role,
r.admin_option, r.default_role
order by 1;
set pause off;
set linesize 78;
set pagesize 56;
set newpage 0;
column c1 heading "User" format a20;
column c2 heading "Privilege";
column c3 heading "Default TS" format a10;
column c4 heading "Temp TS" format a10;
select substr(dba_users.username,1,20) c1,
dba_role_privs.granted_role c2,
substr(dba_users.default_tablespace,1,15) c3,
substr(dba_users.temporary_tablespace,1,15) c4
from sys.dba_role_privs, sys.dba_users
where sys.dba_role_privs.grantee = sys.dba_users.username
order by 1,2;
Don't give connect,resource to any user while creating user
SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
FROM sys.dba_users a, sys.dba_role_privs b
wHERE a.username = b.grantee
and a.username = upper('&&user')
UNION
SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
WHERE a.username = b.grantee
and a.username = upper('&&user')
UNION
SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
FROM sys.dba_users a, sys.dba_tab_privs b
WHERE a.username = b.grantee
and a.username = upper('&&user')
ORDER BY 1;
USERNAME WHAT_GRANTED
------------------------------ ---------------------------------------------------------------------------------------------
SCOTT CONNECT
SCOTT RESOURCE
SCOTT UNLIMITED TABLESPACE -----not very good idea ....
SQL> revoke UNLIMITED TABLESPACE from scott;
Revoke succeeded.
SQL> SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
2 FROM sys.dba_users a, sys.dba_role_privs b
3 wHERE a.username = b.grantee
4 and a.username = upper('&&user')
5 UNION
6 SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
7 8 WHERE a.username = b.grantee
9 and a.username = upper('&&user')
10 UNION
11 SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
12 FROM sys.dba_users a, sys.dba_tab_privs b
13 WHERE a.username = b.grantee
14 and a.username = upper('&&user')
15 ORDER BY 1;
Enter value for user: scott
old 4: and a.username = upper('&&user')
new 4: and a.username = upper('scott')
old 9: and a.username = upper('&&user')
new 9: and a.username = upper('scott')
old 14: and a.username = upper('&&user')
new 14: and a.username = upper('scott')
USERNAME WHAT_GRANTED
------------------------------ ---------------------------------------------------------------------------------------------
SCOTT CONNECT
SCOTT RESOURCE
select * from sys.dba_sys_privs where GRANTEE=upper('&role') ;
select distinct grantee from dba_sys_privs ;
select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';
select grantee, granted_role from dba_role_privs where grantee not in ('SYS', 'SYSTEM','DBA') order by grantee;
create user rrr identified by rr
default tablespace users
temporary tablespace temp
quota unlimited on users
quota unlimited on temp ---- 10gR2 . you cannot grant quota on temporary tablespace
/
SQL> create user rrr identified by rrr
default tablespace users
temporary tablespace temp
quota unlimited on users
quota unlimited on temp
/
create user rrr identified by rr
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace
Note: 114673.1 RESOURCE Role in DBA_SYS_PRIVS does not Include UNLIMITED TABLESPACE Privilege
Note: 1005485.6 ORA-1950 When Creating an Object and Resource Role is Granted to the User
Note: 1084014.6 Revoking DBA or RESOURCE Privilege Revokes UNLIMITED TABLESPACE from the User
SELECT * FROM user_sys_privs;
select * from session_privs;
SQL> create user rrr identified by rrr
default tablespace users
temporary tablespace temp
quota unlimited on users
/
User created.
SQL> ALTER USER scott QUOTA UNLIMITED ON users;
User altered.
--- connect
grant CREATE SESSION to rrr ;
resoure ---
grant CREATE TRIGGER to rrr ;
grant CREATE SEQUENCE to rrr ;
grant CREATE TYPE to rrr ;
grant CREATE PROCEDURE to rrr ;
grant CREATE CLUSTER to rrr ;
grant CREATE OPERATOR to rrr ;
grant CREATE INDEXTYPE to rrr ;
grant CREATE TABLE to rrr ;
grant create view to rrr ;
grant CREATE ANY INDEX to rrr ; ?????
Grant succeeded.
revoke CREATE SESSION from rrr ;
Revoke succeeded.
SQL> REVOKE CREATE ANY INDEX FROM rrr ;
Revoke succeeded.
SQL> SELECT a.username,b.granted_role || DECODE(admin_option,'YES',' (With Admin Option)',NULL) what_granted
2 FROM sys.dba_users a, sys.dba_role_privs b
3 wHERE a.username = b.grantee
4 and a.username = upper('&&user')
5 UNION
6 SELECT a.username,b.privilege || DECODE(admin_option,'YES',' (With Admin Option)', NULL) what_granted
7 FROM sys.dba_users a, sys.dba_sys_privs b
8 WHERE a.username = b.grantee
9 and a.username = upper('&&user')
10 UNION
11 SELECT a.username,b.table_name || ' - ' || b.privilege || DECODE(grantable,'YES',' (With Grant Option)',NULL) what_granted
12 FROM sys.dba_users a, sys.dba_tab_privs b
13 WHERE a.username = b.grantee
14 and a.username = upper('&&user')
15 ORDER BY 1;
old 4: and a.username = upper('&&user')
new 4: and a.username = upper('RRR')
old 9: and a.username = upper('&&user')
new 9: and a.username = upper('RRR')
old 14: and a.username = upper('&&user')
new 14: and a.username = upper('RRR')
USERNAME WHAT_GRANTED
-------------------- ---------------------------------------------------------------------------------------------
RRR CREATE CLUSTER
RRR CREATE INDEXTYPE
RRR CREATE OPERATOR
RRR CREATE PROCEDURE
RRR CREATE SEQUENCE
RRR CREATE SESSION
RRR CREATE TABLE
RRR CREATE TRIGGER
RRR CREATE TYPE
RRR CREATE VIEW
10 rows selected.
select * from session_privs;
The UNLIMITED TABLESPACE system privilege does not let a user create their own datafiles.
The CREATE TABLESPACE system privilege lets a user create a tablespace. The ALTER TABLESPACE system
privilege lets a user add a datafile to an
SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='SCOTT';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMP
ALTER USER scott QUOTA unlimited ON users;
ALTER USER scott QUOTA 10M ON users;
SELECT * FROM DBA_ts_quotas;
A quota value of MAX_BYTES -1 indicated UNLIMITED
SQL> SELECT U.USERNAME,R.GRANTED_ROLE FROM SYS.DBA_USERS U,SYS.DBA_ROLE_PRIVS R
WHERE U.USERNAME=R.GRANTEE(+)
AND R.ADMIN_OPTION='NO'
AND U.USERNAME = UPPER('SCOTT')
GROUP BY U.USERNAME,R.GRANTED_ROLE
ORDER BY U.USERNAME ;
USERNAME GRANTED_ROLE
------------------------------ ------------------------------
SCOTT RESOURCE
SCOTT CONNECT
SQL> SELECT R.GRANTED_ROLE,U.USERNAME FROM SYS.DBA_USERS U,SYS.DBA_ROLE_PRIVS R WHERE U.USERNAME=R.GRANTEE(+)
AND R.ADMIN_OPTION='NO'
AND R.GRANTED_ROLE = 'CONNECT'
GROUP BY U.USERNAME,R.GRANTED_ROLE
ORDER BY U.USERNAME ;
GRANTED_ROLE USERNAME
------------------------------ ------------------------------
CONNECT ABC
CONNECT ANUJ
CONNECT ANUJREP
CONNECT ANUJTEST
CONNECT APEX_040000
CONNECT GGATE
CONNECT IX
CONNECT MDDATA
CONNECT MDSYS
CONNECT PM
CONNECT SCOTT
CONNECT SPATIAL_CSW_ADMIN_USR
CONNECT SPATIAL_WFS_ADMIN_USR
CONNECT WMSYS
CONNECT XYZ
15 rows selected.
SELECT table_name FROM dba_tab_privs p,dba_objects o
WHERE p.owner=o.owner
--- Managing Default User Accounts 333
AND p.table_name = o.object_name
-- AND p.owner = 'SCOTT'
AND p.privilege = 'EXECUTE'
AND p.grantee = 'PUBLIC'
AND o.object_type='PACKAGE';
col USERNAME format a25
col DEFAULT_TABLESPACE format a15
col PROFILE format a10
col GRANTED_ROLE format a25
SELECT usr.username, usr.default_tablespace, usr.temporary_tablespace "TMP TBS", usr.profile,rl.granted_role, rl.admin_option, rl.default_role
FROM sys.dba_users usr, sys.dba_role_privs rl
WHERE usr.username = rl.grantee (+)
and usr.username not in ('SYS','SYSTEM')
and usr.username=upper('&&user')
GROUP BY usr.username, usr.default_tablespace,usr.temporary_tablespace, usr.profile, rl.granted_role, rl.admin_option, rl.default_role;
USERNAME DEFAULT_TABLESP TMP TBS PROFILE GRANTED_ROLE ADM DEF
------------------------- --------------- ------------------------------ ---------- ------------------------- --- ---
WMSYS SYSAUX TEMP1 DEFAULT RESOURCE NO YES
OWBSYS SYSAUX TEMP1 DEFAULT JAVAUSERPRIV NO YES
MGMT_VIEW SYSTEM TEMP1 DEFAULT MGMT_USER NO YES
BOEXI APTUS_OBJ TEMP1 DEFAULT RESOURCE NO YES
select * from role_sys_privs where role in (select role from dba_role_privs where role=upper('&role') )
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
select * from role_role_privs where role in (select role from user_role_privs where username = 'SCOTT')
CREATE ROLE test_role IDENTIFIED BY test123;
grant select, insert, update, delete on suppliers to test_role;
SQL> SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS;
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_1 UPDATE
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_2 UPDATE
IMP_FULL_DATABASE KET$_CLIENT_CONFIG FIELD_3 UPDATE
FLOWS_FILES WWV_FLOW_COMPANIES PROVISIONING_COMPANY_ID REFERENCES
FLOWS_FILES WWV_FLOW_COMPANIES PROVISIONING_COMPANY_ID REFERENCES
=====
===============
SQL> show user
USER is "SYS"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
SYSDBA
SYSOPER
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
ALTER ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
EXEMPT ACCESS POLICY
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
EXEMPT IDENTITY POLICY
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
CREATE ANY EDITION
DROP ANY EDITION
ALTER ANY EDITION
CREATE ASSEMBLY
CREATE ANY ASSEMBLY
ALTER ANY ASSEMBLY
DROP ANY ASSEMBLY
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
CREATE MINING MODEL
CREATE ANY MINING MODEL
DROP ANY MINING MODEL
SELECT ANY MINING MODEL
ALTER ANY MINING MODEL
COMMENT ANY MINING MODEL
CREATE CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
SELECT ANY CUBE DIMENSION
CREATE CUBE
ALTER ANY CUBE
CREATE ANY CUBE
DROP ANY CUBE
SELECT ANY CUBE
UPDATE ANY CUBE
CREATE MEASURE FOLDER
CREATE ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
INSERT ANY MEASURE FOLDER
CREATE CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DROP ANY CUBE BUILD PROCESS
UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
ADMINISTER SQL MANAGEMENT OBJECT
ALTER PUBLIC DATABASE LINK
ALTER DATABASE LINK
FLASHBACK ARCHIVE ADMINISTER
208 rows selected.
===========
COL roles FOR a60
COL table_name FOR a30
col privilege for a9
set lin 200 trims on pages 0 emb on hea on newp none
SELECT *
FROM ( SELECT CONNECT_BY_ROOT grantee grantee,
privilege,
REPLACE (
REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
'^/[^/]*'),
'/',
' --> ')
ROLES,
owner,
table_name,
column_name
FROM (SELECT PRIVILEGE,
GRANTEE,
OWNER,
TABLE_NAME,
NULL column_name
FROM DBA_TAB_PRIVS
WHERE owner NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')
UNION
SELECT PRIVILEGE,
GRANTEE,
OWNER,
TABLE_NAME,
column_name
FROM DBA_COL_PRIVS
WHERE owner NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')
UNION
SELECT GRANTED_ROLE,
GRANTEE,
NULL,
NULL,
NULL
FROM DBA_ROLE_PRIVS
WHERE GRANTEE NOT IN
('SYS',
'SYSTEM',
'WMSYS',
'SYSMAN',
'MDSYS',
'ORDSYS',
'XDB',
'WKSYS',
'EXFSYS',
'OLAPSYS',
'DBSNMP',
'DMSYS',
'CTXSYS',
'WK_TEST',
'ORDPLUGINS',
'OUTLN',
'ORACLE_OCM',
'APPQOSSYS')) T
START WITH grantee IN (SELECT username FROM dba_users)
CONNECT BY PRIOR PRIVILEGE = GRANTEE)
WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,
owner,
table_name,
column_name,
privilege;
=================
VARIABLE user_name VARCHAR2(255);
exec :user_name :='&user_name' ;
WITH user_roles AS
(SELECT granted_role FROM dba_role_privs START WITH grantee =:user_name CONNECT BY grantee = prior granted_role
UNION ALL
SELECT username FROM dba_users WHERE username =:user_name
)
SELECT lpad(' ', 2 *(level - 1)) || privilege "Privilege",
type AS "Privilege_Type",
object_type AS "Object_Type",
owner AS "Owner",
table_name AS "Name",
grantable AS "Grantable",
grantor AS "Grantor",
hierarchy AS "Hierarchy"
FROM
(SELECT grantee,
privilege,
'Object' AS type,
do.object_type,
dtp.owner,
dtp.table_name,
dtp.grantable,
dtp.grantor,
dtp.hierarchy
FROM dba_tab_privs dtp
JOIN dba_objects DO
ON dtp.owner = do.owner
AND dtp.table_name = do.object_name
JOIN user_roles
ON dtp.grantee = user_roles.granted_role
WHERE NOT object_type LIKE '%_BODY'
UNION ALL
SELECT grantee,
privilege,
'Column' AS type,
'TABLE COLUMN' AS object_type,
owner,
table_name || ' (' || column_name || ')' AS table_name,
grantable,
grantor,
'NO' AS hierarchy
FROM dba_col_privs
JOIN user_roles
ON dba_col_privs.grantee = user_roles.granted_role
UNION ALL
SELECT grantee,
granted_role AS privilege,
'Role' AS type,
NULL AS object_type,
NULL AS owner,
NULL AS table_name,
NULL AS grantable,
NULL AS grantor,
NULL AS hierarchy
FROM dba_role_privs
)
START WITH grantee =:user_name
CONNECT BY grantee = prior privilege
Oracle Foreign Key info on table / user
primary key Foreign Key on a table
SQL> set serveroutput on
declare
cons varchar2(60);
r_cons varchar2(60);
type vc230 is table of varchar2(30) index by binary_integer;
type num is table of number index by binary_integer;
fk_coln vc230;
pk_coln vc230;
fk_pos num;
pk_pos num;
cnt number;
tab_name_with_fk varchar2(30);
tab_name_with_pk varchar2(30);
cursor main is
select constraint_name cons, r_constraint_name r_cons, table_name tab_name_with_fk
from user_constraints
where table_name = upper('&table_name_with_foreign_key')
and constraint_type = 'R';
begin
for cur in main loop
dbms_output.put_line(lpad('*', 80, '*'));
dbms_output.put_line('Reference Constraint_Name : ' || cur.r_cons);
dbms_output.put_line('Constraint_Name : ' || cur.cons);
select table_name into tab_name_with_pk
from user_constraints
where constraint_name = cur.r_cons;
declare
cursor c1 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.cons
order by position;
cursor c2 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.r_cons
order by position;
begin
cnt := 1;
for cur1 in c1 loop
fk_coln(cnt) := cur1.columnname;
fk_pos(cnt) := cur1.position;
cnt := cnt + 1;
end loop;
cnt := 1;
for cur2 in c2 loop
pk_coln(cnt) := cur2.columnname;
pk_pos(cnt) := cur2.position;
cnt := cnt + 1;
end loop;
dbms_output.put_line('Table Contains FK Is ' || rpad(cur.tab_name_with_fk, 15, ' ') ||
' Table Contains PK Is ' || rpad(tab_name_with_pk, 15, ' '));
for i in 1..cnt-1 loop
if (i != cnt-1) then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') ||
' Pos ' || rpad(to_char(fk_pos(i)), 2, ' ') || ' ' || ' Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
elsif i = cnt-1 then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') || ' Pos ' ||
rpad(to_char(fk_pos(i)), 2, ' ') || ' References ' || 'Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
end if;
end loop;
dbms_output.put_line(lpad('*', 80, '*'));
end;
end loop;
end;
/
SQL> /
Enter value for table_name_with_foreign_key: EMP
old 16: where table_name = upper('&table_name_with_foreign_key')
new 16: where table_name = upper('EMP')
********************************************************************************
Reference Constraint_Name : PK_DEPT
Constraint_Name : FK_DEPTNO
Table Contains FK Is EMP Table Contains PK Is DEPT
Foreign Key DEPTNO Pos 1 References Primary Key DEPTNO Pos 1
********************************************************************************
PL/SQL procedure successfully completed.
====
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT b.table_name, b.owner
FROM dba_constraints a,
dba_constraints b
WHERE b.r_constraint_name = a.constraint_name
AND b.r_owner = a.owner
AND a.constraint_type in ('P','U')
AND b.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')
union
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT a.table_name, a.owner
FROM dba_constraints a,
dba_constraints b
WHERE a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner
AND b.constraint_type in ('P','U')
AND a.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')
SQL> /
DDL
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") DISABLE
SQL> set serveroutput on
declare
cons varchar2(60);
r_cons varchar2(60);
type vc230 is table of varchar2(30) index by binary_integer;
type num is table of number index by binary_integer;
fk_coln vc230;
pk_coln vc230;
fk_pos num;
pk_pos num;
cnt number;
tab_name_with_fk varchar2(30);
tab_name_with_pk varchar2(30);
cursor main is
select constraint_name cons, r_constraint_name r_cons, table_name tab_name_with_fk
from user_constraints
where table_name = upper('&table_name_with_foreign_key')
and constraint_type = 'R';
begin
for cur in main loop
dbms_output.put_line(lpad('*', 80, '*'));
dbms_output.put_line('Reference Constraint_Name : ' || cur.r_cons);
dbms_output.put_line('Constraint_Name : ' || cur.cons);
select table_name into tab_name_with_pk
from user_constraints
where constraint_name = cur.r_cons;
declare
cursor c1 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.cons
order by position;
cursor c2 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.r_cons
order by position;
begin
cnt := 1;
for cur1 in c1 loop
fk_coln(cnt) := cur1.columnname;
fk_pos(cnt) := cur1.position;
cnt := cnt + 1;
end loop;
cnt := 1;
for cur2 in c2 loop
pk_coln(cnt) := cur2.columnname;
pk_pos(cnt) := cur2.position;
cnt := cnt + 1;
end loop;
dbms_output.put_line('Table Contains FK Is ' || rpad(cur.tab_name_with_fk, 15, ' ') ||
' Table Contains PK Is ' || rpad(tab_name_with_pk, 15, ' '));
for i in 1..cnt-1 loop
if (i != cnt-1) then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') ||
' Pos ' || rpad(to_char(fk_pos(i)), 2, ' ') || ' ' || ' Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
elsif i = cnt-1 then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') || ' Pos ' ||
rpad(to_char(fk_pos(i)), 2, ' ') || ' References ' || 'Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
end if;
end loop;
dbms_output.put_line(lpad('*', 80, '*'));
end;
end loop;
end;
/
SQL> /
Enter value for table_name_with_foreign_key: EMP
old 16: where table_name = upper('&table_name_with_foreign_key')
new 16: where table_name = upper('EMP')
********************************************************************************
Reference Constraint_Name : PK_DEPT
Constraint_Name : FK_DEPTNO
Table Contains FK Is EMP Table Contains PK Is DEPT
Foreign Key DEPTNO Pos 1 References Primary Key DEPTNO Pos 1
********************************************************************************
PL/SQL procedure successfully completed.
====
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT b.table_name, b.owner
FROM dba_constraints a,
dba_constraints b
WHERE b.r_constraint_name = a.constraint_name
AND b.r_owner = a.owner
AND a.constraint_type in ('P','U')
AND b.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')
union
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT a.table_name, a.owner
FROM dba_constraints a,
dba_constraints b
WHERE a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner
AND b.constraint_type in ('P','U')
AND a.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')
SQL> /
DDL
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") DISABLE
Subscribe to:
Posts (Atom)
