Visit Our UNIX and Linux User Community


Grant privileges in Oracle


 
Thread Tools Search this Thread
Top Forums Programming Grant privileges in Oracle
# 1  
Old 11-07-2009
Question Grant privileges in Oracle

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  
Old 11-07-2009
Hi.

What do you mean by:
Quote:
i give the privileges as "select on" to user1
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.
Code:
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  
Old 11-07-2009
OR if there is no synonym,
Code:
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  
Old 11-07-2009
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

Previous Thread | Next Thread
Test Your Knowledge in Computers #446
Difficulty: Medium
The Large Hadron Collider experiments at CERN run on Scientific Linux.
True or False?

8 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Solaris, grant user to kill another process

the task is grant user1 to kill another (for example user2) process. My steps: by root: usermod -P "Process Management" user1 login user1 user1@server (~) pfexec kill <PID> the result is: ksh: <PID>: not found or user1@server (~) pfexec pkill <PID> the result: nothing happens, still... (0 Replies)
Discussion started by: dsyberia
0 Replies

2. Shell Programming and Scripting

How can i prepare grant staement with 2 files ?

---file1 ( tables A B C D E F ... ... Z ---file2 Joe Bob Mary Sally Fred Elmer David (1 Reply)
Discussion started by: rocking77
1 Replies

3. UNIX for Advanced & Expert Users

Unix privileges for Oracle user

Hi I have an issue I am currently trying to execute Unix Commands through a Java Source within Oracle 10g I am currently running Oracle on a Unix Sun Solaris Platform. I am executing the Java Source through a PL/SQL and on SQL Navigator it is saying that it has been executed. I'm... (2 Replies)
Discussion started by: CRL88
2 Replies

4. UNIX for Dummies Questions & Answers

grant sudo permission

Hi all, I have to grant sudo permission to a user. I have searched online and find that /etc/sudoers file needs to be changed with visudo command. As i am new to linux, this is not clear to me. Can anybody take an example and show me how exactly this done. Thanks in advance! (2 Replies)
Discussion started by: lramsb4u
2 Replies

5. Solaris

Grant print related privileges

Afternoon everyone, I would want to ask that how/what privileges i should grant to a new user so that the user can clear /disable printing job queue? Solaris OS: 5.9 Thanks. :b: (4 Replies)
Discussion started by: beginningDBA
4 Replies

6. UNIX and Linux Applications

grant usage to users listed in a file

hi, i'm trying to grant usage to multiple users whose ids are in a file. i thought that i could put the mysql code within a while loop, but that's not working for me: while read user; do userid=$user passwd="changeme" query="grant usage on mysql.USERS.* to $userid... (2 Replies)
Discussion started by: ankimo
2 Replies

7. Linux

grant root privileges to ordinary user

Hi, Is it possible to grant root privileges to an ordinary user? Other than 'sudo', is there some way under Users/Groups configuration? I want ordinary user to be able to mount, umount and use command mt. /Brendan (4 Replies)
Discussion started by: brendan76
4 Replies

8. UNIX for Dummies Questions & Answers

MySQL GRANT permission.

Hi, I'm one of a server administrators. I've the linux root account but I don't know the root password of MySQL (Server version: 5.0.32). I want to GRANT ALL PRIVILEGES to my MySQL account without changing the MySQL's root password. How can I do so? (0 Replies)
Discussion started by: mjdousti
0 Replies

Featured Tech Videos