Home » RDBMS Server » Server Administration » Session_privs (10.2.0 , oel 4)
Session_privs [message #563305] Sun, 12 August 2012 03:43 Go to next message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
Hello to everyone

I have little doubt with session_privs;

SQL> conn sam
Enter password:
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
BACKUP ANY TABLE
SELECT ANY TABLE
CREATE CLUSTER
CREATE SEQUENCE
SELECT ANY SEQUENCE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE TYPE
EXECUTE ANY TYPE
CREATE OPERATOR
CREATE INDEXTYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
READ ANY FILE GROUP

18 rows selected.

SQL> show user;
USER is "SAM"



=========================================================================================================
SQL> conn /as sysdba
connected.

SQL> SELECT * FROM dba_sys_privs WHERE privilege = 'UNLIMITED TABLESPACE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
Udaya                          UNLIMITED TABLESPACE                                  NO
OUTLN                          UNLIMITED TABLESPACE                     NO
DBSNMP                         UNLIMITED TABLESPACE                     NO
T1                             UNLIMITED TABLESPACE                     NO
SYSTEM                         UNLIMITED TABLESPACE                     YES
SAM                            UNLIMITED TABLESPACE                     NO     --> SAM
U2                             UNLIMITED TABLESPACE                     NO
TSMSYS                         UNLIMITED TABLESPACE                     NO
X                              UNLIMITED TABLESPACE                     NO
SAN                            UNLIMITED TABLESPACE                     NO
SAMS                           UNLIMITED TABLESPACE                     NO

11 rows selected.



I checked from sam user I have unlimited tablespace privilege.
As from sys user here sam user doesn't have unlimited tablespace.
please clarify.

Thanks
thiyagusham. G

[EDITED by LF: fixed [code] tags]

[Updated on: Sun, 12 August 2012 04:16] by Moderator

Report message to a moderator

Re: Session_privs [message #563306 is a reply to message #563305] Sun, 12 August 2012 04:14 Go to previous messageGo to next message
Littlefoot
Messages: 21580
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What you see is not what you think.
SQL> desc dba_sys_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

SQL>

ADMIN_OPTION = NO doesn't mean that SAM doesn't have UNLIMITED TABLESPACE granted, but that the grant was NOT given with the ADMIN option.
Re: Session_privs [message #563309 is a reply to message #563305] Sun, 12 August 2012 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67294
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, and once again:

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

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


Pay the greatest attention you can to the sentence in red.
When you do something with SYS you don't know if the result is valid in anyway.

Regards
Michel
Re: Session_privs [message #563311 is a reply to message #563309] Sun, 12 August 2012 09:12 Go to previous message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
@ littlefoot , @ michel

Thanks for your clarification. I am very clear.

Regards
Thiyagusham.G
Previous Topic: Ora-03113
Next Topic: Reorganization of table
Goto Forum:
  


Current Time: Mon Aug 10 03:11:30 CDT 2020