0

I'm using the Oracle Database EX 11.2.0.2.0 and I hava a quite simple database created there. Now the issue is i would like to have multiple users with different privileges set up. I have found that topic: How to create a user in Oracle 11g and grant permissions but I cannot find anywhere the basic thing about users accounts: what are the difference between creating system-leveled and particular database-leveled user? I've logged in sqlplus as SYSTEM and executed the following commands:

CREATE USER TEST IDENTIFIED BY password;
GRANT CONNECT TO TEST;

and now the problem is that my databse is actually called let's say BASE with one table called PAYMENTS and to give any privileges to a newly created user I cannot execute:

GRANT SELECT ON PAYMENTS TO TEST;

but I have to type in:

GRANT SELECT ON BASE.PAYMENTS TO TEST;

so I suppose I missed something. Is it any way of connecting the created user to a particular database? So that the newly created user will be visible as a database user in Oracle APEX?

Community
  • 1
  • 1
Dawid Sibiński
  • 1,657
  • 3
  • 21
  • 40

2 Answers2

1

Users and schemas are synonymous in Oracle - basically. A schema is the collection of objects owned by a user.

To get what you want, you would need to create users lacking the privs to create anything and only have the ability to select from the objects of others.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
1

When referencing objects in other schemas, you must provide the schema name. An other user might have a table with the same name. Currently you are logged in with the system user, which is not advisable. When creating objects in the BASE schema (another name for user in de Oracle DB), why not give the user some extra rights (like granting privileges)? The core of your problem is that you want to grant privileges to user A on object owned by B, logged in as user C. You have to be very specific in that case to Oracle what privileges are granted to whom ;)

Non Plus Ultra
  • 867
  • 7
  • 17
  • Thank you for answering. So it means I have to be logged as the BASE's owner and create a new user there? I've tried, but how to logon on that user from the console then? Using sqlplus that user is not visible. – Dawid Sibiński Jan 25 '15 at 14:55
  • I'm not sure if I understand what you want to achieve. It's ok to create users and do other administrative tasks as system. Once you set those up, it's best practice to login to SQL*Plus as that user. – Non Plus Ultra Jan 26 '15 at 08:32
  • logon to sqlplus with the following command: sqlplus base@xe where base is your user and xe the name of the database. Then you are logged in as user base. If you want to know what users are in the database, simply execute the query
    select username from all_users;     – Non Plus Ultra Jan 26 '15 at 08:40
  • The core of my problem is that I'm connecting to database using JDBC with the following "jdbc:oracle:thin:@localhost:1521/XE". So XE is default database in Oracle, and my BASE is only schema? I have all my tables in that "BASE" thing (whatever it is) and would like to set up a few users with different privileges. When I create user of "BASE" being logged to Oracle APEX and trying to logon with command 'sqlplus username@base' it says "ORA-12154: could not resolve the connect identifier specified". – Dawid Sibiński Jan 28 '15 at 11:48
  • and also when I use "jdbc:oracle:thin:@localhost:1521/XE" to connect to a database I can logon as SYSTEM or any user created at "SYSTEM level"; I would like to connect to the database and then be able to logon to the database BASE only as the BASE users, not the global users like SYSTEM or any user I created being logged as SYSTEM. – Dawid Sibiński Jan 28 '15 at 11:54
  • The database mentioned in the jdbc connection string is XE. I do not know what BASE is, but I'm still guessing it's the user you connect with.Check http://www.orafaq.com/wiki/JDBC for explanation on jdbc syntax.You can use jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID for instance jdbc:oracle:thin:base/pwd@localhost:1521:xe or any other user instead of base.When using sql connect string the syntax is sqlplus username/password@SID , so base/pwd@XE in your case – Non Plus Ultra Jan 28 '15 at 13:02