Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 11g user rights management study notes     - Close common port to protect server security (Linux)

- CentOS 6.6 permanent method to modify the DNS address (Linux)

- Using Ruby to build a simple HTTP service and sass environment (Server)

- Expand an existing RAID arrays and remove the failed disk in a RAID (Linux)

- [Android] Eclipse does not update the Android SDK Manager solution [using GoAgent] (Programming)

- Oracle View index and use indexes Precautions (Database)

- Using FTPClient to upload and download files in Java (Programming)

- MySQL view (Database)

- Java object initialization (Programming)

- Customize own small private Linux system (Linux)

- Linux VMware virtual machine after the cloning of the card can not start to solve (Linux)

- Get the Linux device PCI ID method (Linux)

- Ubuntu 15.10 15.04 14.10 14.04 Install Ubuntu Tweak (Linux)

- What is a logical partition management LVM, how to use in Ubuntu (Linux)

- Zookeeper Rights Management and Quota Management (Server)

- How to add a new hard disk without restarting the CentOS 7 / RHEL 7 virtual machine (Linux)

- Linux terminal program running in the background (Linux)

- Linux system - The understanding cpu load (Linux)

- Node.js simple interface server (Server)

- Python type way of comparison (Programming)

 
         
  Oracle 11g user rights management study notes
     
  Add Date : 2018-11-21      
         
         
         
  Summing up the work often use some Oracle 11g user rights management study notes:

1 View all database users each have a role:
select * from (select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a
where exists (select 1 from dba_users b where b.username = a.username)
order by 1,2
;

2, view the database system privileges for all users:
select d.username, d.privilege from
(Select a.username, b.privilege from
(Select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a,
(Select grantee, privilege from dba_sys_privs) b
where a.granted_role = b.grantee
union
select grantee, privilege from dba_sys_privs) d
where exists ((select 1 from dba_users c where d.username = c.username))
order by 1,2;

3, view the database table permissions for all users
select d.username, d.privilege, d.owner, d.table_name from
(Select a.username, b.privilege, b.owner, b.table_name from
(Select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a,
(Select grantee, owner, table_name, privilege from dba_tab_privs) b
where a.granted_role = b.grantee
union
select grantee, privilege, owner, table_name from dba_tab_privs) d
where exists ((select 1 from dba_users c where d.username = c.username))
order by 1,2;

4, how to view the user has the DBA role?
Conventional practice is generally directly query DBA_ROLE_PRIVS view. Query as follows:
select grantee, granted_role from dba_role_privs where granted_role = 'DBA';
This user will miss the test as follows:

Correct query statement queries shall be as follows:
select * from (select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a
where a.granted_role = 'DBA';

5, view the user has the SELECT ANY TABLE privilege
For grant role, look at who has the authority to query all the tables
select distinct rp.grantee from dba_role_privs rp, dba_sys_privs sp
where rp.granted_role = sp.grantee
and sp.privilege like 'SELECT ANY TABLE%'
     
         
         
         
  More:      
 
- Shell command line operation (Linux)
- Swift 2.0 brief (Linux)
- Android Delete project useless resource file (Programming)
- Installation and use of Linux Sniffer tool Tcpdump (Linux)
- Use C program in JavaScript (Programming)
- DB2 table space is redirected to restore the database combat (Database)
- CentOS7 build GlusterFS (Linux)
- Automatic Clear date directory shell script (Linux)
- Debian 8.1 (amd64) deployed Memcached (Server)
- To install Oracle Database Details and FAQ Summary under CentOS (Database)
- Apache POI Excel Document Processing (Linux)
- Bash added to the Vi mode indicator (Linux)
- Use Docker / LXC quickly launch a desktop system (Linux)
- Introduction and use of the Raspberry Pi (Linux)
- Python implementation Bursa transition model (Programming)
- Use OpenSSL to generate a certificate (Linux)
- C data types is how it is supported by most computer systems (Programming)
- Preview function to confirm the stop resource Oracle 12c new feature crsctl (Database)
- MySQL database master never solve the synchronization method (Database)
- How to turn Java String into Date (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.