I want to know which users have SYS and SYSDBA privilege before export/import operation.
I searched a lot but I could not find.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI want to know which users have SYS and SYSDBA privilege before export/import operation.
I searched a lot but I could not find.
To your question:
I want to know which users have SYS and SYSDBA privilege before export/import operation.
SYSDBA is a role, not a user or schema. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
As per the Burleson consulting blog, the v$pwfile_users
view contains a list of all users who have been granted the SYSDBA or SYSOPER roles.
SQL> desc v$pwfile_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
SYSDBA VARCHAR2(5)
SYSOPER VARCHAR2(5)
SYSASM VARCHAR2(5)
SQL>
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
TOM TRUE FALSE FALSE
In 11g there is a new column in v$pwfile_users
for the SYSASM privilege:
SYS AS SYSDBA @ orcl11 SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
For further details see this Tom Kyte article.%3A%3AP11_QUESTION_ID:2659418700346202574">Here
There is no SYS privilege instead there is SYS user. To list the users who have SYSDBA privilege use the following query.
SQL>conn sys/password as sysdba SQL>SELECT * FROM v$pwfile_users;
select table_name from dict where table_name like '%PRIV%';
– JSapkota
Mar 6 '16 at 10:12
There are many situations where ADMIN wanted to revoke DBA privileges granted to the user. Using below query you can find out users having dba roles in Oracle :
SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS DBA YES YES
SYSTEM DBA YES YES
As well as the v$pwfile_users
view referenced elsewhere, there are also the O/S authenticated users.
Type groups oinstall
at the command line (on Unix) and ensure only authorised users (oracle
) have been assigned that group. Any user in that group (or depending on how the software was installed, the 'dba' group also), has automatic access to sysdba without a password via sqlplus / as sysdba
.
Maybe you want privileges on objects owned by SYS from DBA_TAB_PRIVS.
select table_name from dba_tab_privs where owner='SYS' and grantee='PUBLIC';
This would give you all the objects owned by SYS and accessible by PUBLIC.