Home » RDBMS Server » Server Administration » Privilege information (9.2.0.7)
Privilege information [message #343767] Thu, 28 August 2008 01:53 Go to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

I want to restrict a user from viewing V$ views, CREATE TABLESPACE .

I want to know which table contains this information.

I checked in
dba_sys_privs
dba_role_privs
role_role_privs
role_sys_privs


Brian.
Re: Privilege information [message #343781 is a reply to message #343767] Thu, 28 August 2008 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one has these privileges unless you give him directly or through a role (like DBA).
So you have to recursively search in these views for all user roles and their subroles.

Regards
Michel
Re: Privilege information [message #343785 is a reply to message #343767] Thu, 28 August 2008 02:28 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

How do I check for which User/Roles has DBA privileges.

Brian.
Re: Privilege information [message #343786 is a reply to message #343785] Thu, 28 August 2008 02:41 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I checked in
dba_sys_privs
dba_role_privs
role_role_privs
role_sys_privs


What you checked in above data dictionary views?
Re: Privilege information [message #343788 is a reply to message #343767] Thu, 28 August 2008 02:52 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Taj,

select * from dba_sys_privs where privilege = 'DBA'
select * from  dba_roles where role = 'DBA'
select * from dba_role_privs where granted_role = 'DBA'


From dba_role_privs I can see that, only SYS and SYSTEM has DBA role granted.

Also, I want to restrict from viewing V$ views, which privilege I should check ?

Brian
Re: Privilege information [message #343800 is a reply to message #343788] Thu, 28 August 2008 03:40 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

...From dba_role_privs I can see that, only SYS and SYSTEM has DBA role granted. ...

Which user you want to still restrict when no user have DBA role granted except SYS,SYSTEM.?
Re: Privilege information [message #343805 is a reply to message #343767] Thu, 28 August 2008 03:57 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Taj,

When I login to TOAD with a user OWB_TARGET, in the ROLE section it displays "DBA", but when I query

SQL> select * from dba_role_privs where granted_role = 'DBA' ;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SYS                            DBA                            YES YES
SYSTEM                         DBA                            YES YES

SQL>


That is why my confusion!

Brian.
Re: Privilege information [message #343811 is a reply to message #343805] Thu, 28 August 2008 04:17 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQLPLUS is always correct. don't trust on TOAD.(GUI)...Smile

Re: Privilege information [message #343813 is a reply to message #343767] Thu, 28 August 2008 04:22 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Taj,

Some more doubts,

1. Even after revoke SELECT ANY DICTIONARY from owb_target ;, owb_target can select from dba_data_files

2. How can I restrict a user from selecting v$xxx views?

Brian
Re: Privilege information [message #343814 is a reply to message #343813] Thu, 28 August 2008 04:25 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

connect with OWA_TARGET and post below query output

select * from session_roles;
select * from session_privs;
Re: Privilege information [message #343816 is a reply to message #343767] Thu, 28 August 2008 04:32 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

SQL> connect owb_target
Enter password:
Connected.
SQL> select * from session_roles;

no rows selected

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK

PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE TRIGGER
CREATE SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE ANY TYPE
CREATE INDEXTYPE
GLOBAL QUERY REWRITE
CREATE DIMENSION
SELECT ANY DICTIONARY

21 rows selected.

SQL>


I have logged in as sysdba and revoked SELECT ANY DICTIONARY, But still showing as granted.

Regards,
Re: Privilege information [message #343882 is a reply to message #343816] Thu, 28 August 2008 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have logged in as sysdba and revoked SELECT ANY DICTIONARY, But still showing as granted.

You did it wrong.

Regards
Michel
Re: Privilege information [message #343914 is a reply to message #343767] Thu, 28 August 2008 07:48 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Michel,

To revoke rights on dba_data_files/SELECT ANY DICTIONARY , How I should have done ?

Thanking in advance.

Brian.
Re: Privilege information [message #344017 is a reply to message #343914] Thu, 28 August 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> connect scott/tiger
Connected.
SCOTT> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SELECT ANY DICTIONARY

11 rows selected.

SCOTT> connect michel/michel
Connected.
MICHEL> revoke select any dictionary  from scott;

Revoke succeeded.

MICHEL> connect scott/tiger
Connected.
SCOTT> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

Regards
Michel
Re: Privilege information [message #344350 is a reply to message #343767] Fri, 29 August 2008 08:46 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
SQL> connect owb_target
Enter password:
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE ANY TYPE
CREATE INDEXTYPE
GLOBAL QUERY REWRITE
CREATE DIMENSION
SELECT ANY DICTIONARY

21 rows selected.

SQL>
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> revoke SELECT ANY DICTIONARY from owb_target ;
revoke SELECT ANY DICTIONARY from owb_target
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'OWB_TARGET'


SQL>


1. Since, SELECT ANY DICTIONARY is not graned, how it is appearing in session_privs.

2. How do I revoke SELECT ANY DICTIONARY in this case.

Thanking in advance,
Brian.
Re: Privilege information [message #344410 is a reply to message #344350] Fri, 29 August 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It has this privilege through a role, revoke the role.

- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel
Re: Privilege information [message #344476 is a reply to message #343767] Fri, 29 August 2008 23:39 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

But in this case no role assigned.

SQL> connect owb_target
Enter password:
Connected.
SQL> select * from session_roles;

no rows selected

SQL>


Brian

Re: Privilege information [message #344503 is a reply to message #344476] Sat, 30 August 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the content of all views you listed for this user.

Regards
Michel
Re: Privilege information [message #344687 is a reply to message #343767] Mon, 01 September 2008 00:58 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

After bouncing DB session_privs doesn't show SELECT ANY DICTIONARY.

But, My doubt is why it requires DB bounce to revoke SELECT ANY DICTIONARY.

I've done following actions.

1. Revoke SELECT ANY DICTIONARY from OWB_TARGET;
2. Still it was showing in session_privs
3. No rols were granted.
4. After bouncing the DB, session_privs doesnt show SELECT ANY DICTIONARY.

Brian.
Re: Privilege information [message #344715 is a reply to message #344687] Mon, 01 September 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You likely did something wrong, but as you didn't post the information I requested, You're On Your Own (YOYO).

Regards
Michel
Re: Privilege information [message #344743 is a reply to message #343767] Mon, 01 September 2008 02:54 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Michel,

Please find the information.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select * from dba_sys_privs where grantee = 'OWB_TARGET' ;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
OWB_TARGET                     CREATE VIEW                              NO
OWB_TARGET                     CREATE TABLE                             NO
OWB_TARGET                     ALTER SESSION                            NO
OWB_TARGET                     CREATE SESSION                           NO
OWB_TARGET                     CREATE SYNONYM                           NO
OWB_TARGET                     CREATE TRIGGER                           NO
OWB_TARGET                     CREATE ANY TYPE                          NO
OWB_TARGET                     CREATE SEQUENCE                          NO
OWB_TARGET                     CREATE SNAPSHOT                          NO
OWB_TARGET                     CREATE DIMENSION                         NO
OWB_TARGET                     CREATE INDEXTYPE                         NO

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
OWB_TARGET                     CREATE PROCEDURE                         NO
OWB_TARGET                     DROP ANY DIRECTORY                       NO
OWB_TARGET                     DROP PUBLIC SYNONYM                      NO
OWB_TARGET                     CREATE ANY DIRECTORY                     NO
OWB_TARGET                     CREATE DATABASE LINK                     NO
OWB_TARGET                     GLOBAL QUERY REWRITE                     NO
OWB_TARGET                     UNLIMITED TABLESPACE                     NO
OWB_TARGET                     CREATE PUBLIC SYNONYM                    NO
OWB_TARGET                     CREATE PUBLIC DATABASE LINK              NO

20 rows selected.

SQL> select * from dba_role_privs where grantee = 'OWB_TARGET';

no rows selected

SQL> 


Brian
Re: Privilege information [message #344753 is a reply to message #344743] Mon, 01 September 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it has not SELECT ANY DICTIONARY

Regards
Michel
Re: Privilege information [message #344795 is a reply to message #343767] Mon, 01 September 2008 05:03 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Michel,

As I said before, after bouncing the DB the SELECT ANY DICTIONARY privilege is disapeared from SESSION_PRIVS.

That is why I've confused!

Brian
Re: Privilege information [message #344814 is a reply to message #344795] Mon, 01 September 2008 05:40 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to reproduce the case now we can't say anything.

Regards
Michel
Previous Topic: move DB from one server to another server [merged]
Next Topic: ORA-01652, ORA-02063
Goto Forum:
  


Current Time: Wed Dec 07 09:05:28 CST 2016

Total time taken to generate the page: 0.41062 seconds