grant connect

I need to add a user with readonly
What would the script be?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you need the user to just be able to select from some tables, then there are a lot of ways to do it.

Consider an example.

username : scott
table    : emp

now as scott is the onwer of this table he can grant priveleges to others.

Lets say there is a user named "test"

the dba would just grant him the privelege to connect to the oracle database by :

sql> grant create session to test;

Now scott would grant him the select privelege to his table emp.

sql> grant select on emp to test;

When the user "test" logs in , he'll query  the table by :

sql> select * from scott.emp;

to overcome the prefix of using the username, the dba can create a synonym for it.

There are other ways like using views ....etc.

What excatly are you looking at?

If you want to give the user readonly privilage on any table then you can do the following:

1.. log into sqlplus with a user who has dba privileges.

2.. create the user

      CREATE USER user_name IDENTIFIED BY password;

      GRANT CREATE SESSION TO user_name;

3..  Grant select on any table:

      GRANT SELECT ANY TABLE TO user_name;

Now, the new user will have readonly on any table owned by any one.

The new user can select by:

    SELECT * FROM  owner.table_name;

where owner is another user who owns a table table_name.
thom_lsbAuthor Commented:
Is there a way to grant select on all
of a users tables, or tablespace?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

i don't think that there is statement to grant select on all the user objects to him. You have to grant him individually grant him.
if you grant him "select any table" then it is not ristricted to only one user.

The user can generate a sql*plus script to grant 'select' on all his tables to a particular user.

sql>spool grant.sql
sql>select 'Grant select on '||table_name||' to test;' from user_tables;

Then you can run the grant.sql script which will grant it.

There is NO STATEMENT  to grant select on all of one users tables.

1.. You can do 'GRANT SELECT ANY TABLE TO user_name' . This will work for you, but then the user user_name can select tables that belong to any user.

2.. Or are sudhi has indicated you can spool the grants to a file and then run the file script. If you want to grant select on user_A's tables to user_B

    Log in to sqlplus as the user_A who owns the tables.

    sqlplus>spool table_grants.sql
    sqlplus> select 'GRANT SELECT ON '||table_name||' TO
    user_B;' from user_tables;
    sqlplus> spool off

You will have to do this for every user whos tables you want to grant for each user you want to grant to.

3.. Or you can do something similar to the above except that you create a role and grant to the role. THen you can grant the role to any user you want.

      a.. Log into sqlplus using DBA account.
      b.. create role ROLE1;
      c..  Login as User_A
           sqlplus>spool table_grants.sql
           sqlplus> select 'GRANT SELECT ON '||table_name||' TO
           ROLE1;' from user_tables;
           sqlplus> spool off
      e..  grant ROLE1 to user_B

Now, you can grant the role ROLE1 to as many users as you want and they will get all the select privileges that the role ROLE1 has.

hi lsb!!

check out the following script

CREATE USER <user name>


p.s once again just check the syntax for writing PUBLIC.
this grant will have to be given by connecting to sys or system.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thom_lsbAuthor Commented:
Could you give mshaikh half the points.
Thanks All.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.