Oracle queries users with sysdba Permissions

View users with sysdba Permissions

Log on to Oracle as a sys user and execute select * from V _ $ pwfile_users; the following results can be queried:

SQL> select * from V _ $ pwfile_users;

Username sysdba sysoper

-------------------------------------------

Sys true

(What is sysoper permission? The sysoper permission is also a special permission for Oracle databases. When a user has this privilege, the user can start and close the database, but cannot establish a database or perform Incomplete recovery. This is also the biggest difference with sysdba. In addition, you do not have any permissions for the DBA role. After an Oracle database is created, only the Sys user has the sysoper privilege by default. Note that to Log On As sysoper, the OS user must have the osoper role (Windows: ora_role). When logging on as sysoper, The
Sysoper option .)

1. View All Users:

Select * From dba_users;

Select * From all_users;

Select * From user_users;

2. view system permissions of users or roles (system permissions assigned to users or roles ):

Select * From dba_sys_privs;

Select * From user_sys_privs;

3. view the permissions contained in a role (only roles owned by login users can be viewed)

SQL> select * From role_sys_privs;

4. View user object permissions:

Select * From dba_tab_privs;

Select * From all_tab_privs;

Select * From user_tab_privs;

5. view all roles:

Select * From dba_roles;

6. view the roles owned by a user or role:

Select * From dba_role_privs;

Select * From user_role_privs;

7. Check which users have sysdba or sysoper system permissions (the corresponding permissions are required for query)

Select * from V $ pwfile_users

Find users with DBA Permissions

SQL> select * From dba_role_privs where granted_role = 'dba ';

Grantee granted_role ADM def
------------------------------------------------------------------
Sys DBA Yes
Sysman DBA No Yes
Rmantest DBA No Yes
System DBA Yes
Junfeng DBA No Yes