2

I want to know which users have SYS and SYSDBA privilege before export/import operation.

I searched a lot but I could not find.

0
1

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

2
  • 3
    I'd be very wary of using Donald Burleson as a reference for anything. Google "Charles Hooper Burleson" and you'll see what I mean. Charles Hooper is IMHO one of the world's top experts on Oracle and I would respect his opinion and I don't think he made the effort to put up what he has done if he didn't feel it was worth the effort to point out Burleson's (ahem...) lacunae. – Vérace Mar 6 '16 at 8:50
  • @Vérace, Thanks Vérace for future suggestion. – Md Haidar Ali Khan Mar 6 '16 at 8:53
0

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;
3
  • after export/import operation we are getting sys error. For example; grant select on SYS.dba_role_privs to public; What i want to know is how can i find them all ? – john true Mar 6 '16 at 7:53
  • @john true: if you are looking for all the views related to privileges then use select table_name from dict where table_name like '%PRIV%'; – atokpas Mar 6 '16 at 10:12
  • 1
    _after export/import operation we are getting sys error. _ What error? what options did you use on the export? On the import? – EdStevens Mar 6 '16 at 14:52
0

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
0

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.

-1

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.

2
  • that does not answer the question – miracle173 Dec 7 '20 at 22:45
  • Trying to answer the real question. Could be wrong but I think the OP wants to know about grants to SYS objects. – Bobby Durrett Dec 7 '20 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.