Re: sqlplus small doubt

From: Alex Hudghton <alex.hudghton_at_capgemini.co.uk>
Date: Fri, 29 Jan 1999 16:13:02 +0000
Message-ID: <36B1DE0D.DF695DE9@capgemini.co.uk>

Not only in the archives but also in Oracle 8 SQL Reference manual

Oracle provides shortcuts for specifying groups of system privileges and statement options at once. However, Oracle encourages you to choose individual system privileges and statement options for auditing, because these shortcuts may not be supported in future versions of Oracle. The shortcuts are follows:

 CONNECT

                  is equivalent to specifying the CREATE SESSION system

privilege.
 RESOURCE

                  is equivalent to specifying the following system privileges:

                       ALTER SESSION
                       CREATE CLUSTER
                       CREATE DATABASE LINK
                       CREATE PROCEDURE
                       CREATE ROLLBACK SEGMENT
                       CREATE SEQUENCE
                       CREATE SYNONYM
                       CREATE TABLE
                       CREATE TABLESPACE
                       CREATE VIEW


 DBA
                  is equivalent to the SYSTEM GRANT statement option and the

following system privileges:

                       AUDIT SYSTEM
                       CREATE PUBLIC DATABASE LINK
                       CREATE PUBLIC SYNONYM
                       CREATE ROLE
                       CREATE USER

Alex

Jonathan Lewis wrote:

> The CONNECT, RESOURCE and DBA roles were left in Oracle 7
> to allow backwards compatibility with Oracle 6.
>
> Many 3rd party applications assume that a user is create by:
> grant connect to username identified by password;
>
> The 'correct' method is:
> create user username identified by password;
> grant create session to user;
>
> If you check the CONNECT privilege, it allows far more
> privileges in the database than a typical end-user should need
> (create table is the obvious one).
>
> This is documented somewhere in the archives, but don't ask
> me for chapter and verse.
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Chuck Hamilton wrote in message <36b4d98b.8955462_at_news2.axs2000.net>...
> >So why are CONNECT, RESOURCE, and DBA roles all obsolete on Oracle 7?
> >Without CONNECT, a user can't connect to the database.
> >
> >The only time I don't grant CONNECT to a user is if that's the user
> >the owns the applications tables. I only occasionally give that user
> >connect privileges if I need to do maintenance. Like grant access to
> >his tables to a new role or user. Then I revoke it as soon as I'm
> >done.
> >
> > Chuck
> >
> >On Fri, 29 Jan 1999 15:32:37 -0000, "Jonathan Lewis"
> ><jonathan_at_jlcomp.demon.co.uk> wrote:
> >
> >>
> >>
> >>Hint: It is a common error in almost all Oracle 7 and 8 installation to
> >>make use of the obsolete CONNECT, RESOURCE and DBA roles
> >>when creating Oracle accounts. You should review the requirements
> >>of your site and create roles which are relevant.
> >>
> >>
> >>Jonathan Lewis
> >>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
> >>
> >>
> >>
> >>
> >>
> >>
> >>kvsraju_at_usa.net wrote in message <78ro5s$sed$1_at_nnrp1.dejanews.com>...
> >>>I have my Oracle 8.0.3.0 database running on windows nt platform.
> >>>All my users,they will be connected to database using an account,say
> TEST1
> >>>Now i want to make sure that all the users cann't drop tables.
> >>>How is it possible?
> >>>there is no command called DROP TABLE to disable the user.
> >>>so i want to control through sql*plus
> >>>How is it possible.
> >>>I came to know that we have change user product profile file.But i am not
> >>sure
> >>>about it
> >>>
> >>>-----------== Posted via Deja News, The Discussion Network ==----------
> >>>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >>
> >
> >--
> >Chuck Hamilton
> >chuckh_at_safeaccess.net
> >
> >Never share a foxhole with anyone braver than yourself!
Received on Fri Jan 29 1999 - 10:13:02 CST