Grant privileges in Oracle

Applications Programming
1

i have installed oracle 10g and two databases.
i enter database1 as sysdba and create a user called user1.i give the privileges as "select on" to user1.
i enter sqlplus from the shell prompt. i enter as user1. but when i do "select * from emp" i have a "the table doesn't exist". how can i give the privileges to this user? how can i see his privileges and the tables this user1 can access?

2

Hi.

What do you mean by:

Select on what?

Who does the table belong to? If it belongs to a different user (schema) then the owner should grant you select.

i.e.

SQL> show user
USER is "USER12"
SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn scott/tiger
Connected.
SQL> grant select on emp to user12;

Grant succeeded.

SQL> conn user12/user12
Connected.
SQL> select count(1) from scott.emp;

  COUNT(1)
----------
        14

SQL> show user
USER is "USER12"

SQL> select grantor, grantee, table_name from user_tab_privs;

GRANTOR                        GRANTEE                        TABLE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT                          USER12                         EMP


SQL> select view_name from all_views where view_name like '%PRIV%' order by 1;
...
...

There's a bunch of data dictionary views which can give you the info you're after.

Catalog Views / Data Dictionary Views

3

OR if there is no synonym,

select fld1 from username.table;

I'm with Scottn ie., confused - you can do
'grant select on schema.tablename;' is that what you mean?

4

thank you to both you, i got the answer i need.
i am doing exercises on pl sql / oracle as database.
i have tables under system, and i want to make the users created by me to
select/update on the tables.
thank you, i will turn back as i am new to oracle.
thanks a lot and have a nice time