Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 11g user rights management study notes     - The Concept and Semantics of Java Memory Model (Programming)

- Nginx-1.9.7 TCP reverse proxy (Server)

- Fedora 20 users install the Mate 1.8 desktop (Linux)

- Quagga use authentication on strengthening BGP session security (Linux)

- Analysis of common mistakes when compiling MySQL installation (Database)

- MySQL 5.7 can not log in problem (Database)

- RedHat Linux source code package installation process (Linux)

- Oracle 10g after the failure to start the upgrade (Oracle instance terminated. Disconnection forced) (Database)

- To learn from scratch OpenWrt perfect tutorial (Linux)

- MySQL5.7 implement virtual column expression index (Database)

- Puppet 3.5 Source package Installation and Configuration (Server)

- QBit development of micro-services (Server)

- Those things packaged using Gradle to Android (Programming)

- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)

- Five strokes to find out the IP address you want to know (Linux)

- MySQL simple operation notes under Linux (Database)

- Ubuntu and derivative system users how to install Pinta 1.5 (Linux)

- On the design of Oracle database backup (Database)

- About Linux backdoor (Linux)

- JEdit 5.2 Pro Edition installation on Ubuntu 14.04 (Linux)

 
         
  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:      
 
- Linux desktop system using the remote server in clear text ssh password (Server)
- Docker: installation under Ubuntu (Server)
- About Git (Linux)
- C ++ you can become a new scripting language (Programming)
- configuration ssh without password under Linux (Linux)
- Java learning problems encountered (Programming)
- Ubuntu Telnet service settings (Linux)
- Linux ban single-user mode to enhance system security (Linux)
- File upload via AngularJS and ASP.NET MVC5 (Programming)
- PostgreSQL query result area is removed and precision (Database)
- CentOS6 install Redis (Database)
- Cool Android realization SVG animation (Programming)
- Hadoop 2.7.1 Installation and Configuration under RedHat Linux 6.5 (Server)
- Archlinux installation tutorial (Linux)
- How to back up Debian system backupninja (Linux)
- Each catalog Detailed Linux (Linux)
- MongoDB 3.2 to upgrade from 3.0.7 (Database)
- To compile install and test Swift under Linux (Linux)
- systemd Power Management (Linux)
- Java data structures - the linear form of the single-chain applications (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.