Help with Privileges in new database

[maka1]

Hi, I am trying to create a new oracle databsae, but cannot seem to create a user with the correct privileges. I am having trouble creating a user with less than admin privilegs but want them to be able to create any table, select any table, but only within this schema. I was trying to accomplish this with the 'object privileges', but it seems to be able to work with only existing objects? How can I say that the user can do anything within a given specific schema or tablespace?

Thanks in advance

 

[SantaMufasa]

Maka,

Notice that the RESOURCE role has the privileges that you want:

SELECT PRIVILEGE FROM DBA_SYS_PRIVS
WHERE grantee = 'RESOURCE';

PRIVILEGE
----------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

8 rows selected.

Any table you CREATE, you automatically have full access rights to that object. Here is the code to GRANT that privilege:

GRANT RESOURCE TO <Oracle user>;

A user (such as a DBA) that both is a member of this role (either directly or via DBA-role membership) and has "admin rights" to this role, can GRANT the role.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[maka1]

Hi, thanks for the help.

After granting resources to my user, I was able to create tables just fine. But I am still unable to see these tables for some reason. I see that the tables are there from going onto the enterprise manager.

so if this user was the one who created this table, having the "Resources" role, they should technically be able to select that same object it created?

 

[rcurva]

If you're the sys or dba user other than the user that you just created, you can "see" your table using sqlplus by specifying the schema (which is the user) of the tables created.

e.g. (assuming SCOTT is the user);

SQL> SELECT * FROM SCOTT.EMP;

Are you saying that you can not see the tables that you just created (connected as the newly-created user)?


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided&quot" -- Art of War

 

[SantaMufasa]

Yes, Maka,

Robbie's analysis is absolutely correct...If you create a table as "USER_X", but attempt to query the table as any other user, even if the other user has been granted permission to SELECT from the "USER_X" table, the other user must either:[ul][li]Qualify the table name during any reference to the table (e.g. "SELECT * FROM USER_X.<table_name>;"), or[/li][li]The other user must use a synonym to access the USER_X table. There are multiple methods to create such a synonym:[/li][ul][li]The other user can issue the command:

CREATE SYNONYM ABC FOR USER_X.<table_name>;

or,[/li][li] someone with the CREATE PUBLIC SYNONYM privilege (such as a DBA) can issue the SQL command:

CREATE PUBLIC SYNONYM ABC FOR USER_X.<table_name>;

[/li][/ul][/ul]If, as Robbie mentions, you are logged in as the owner of the newly created table, then there should be no reason to not be able to SELECT from (or INSERT, UPDATE, or DELETE) that table.



Let us know your findings/confirmation(s) on this topic.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[maka1]

Hi, yes, in fact I have been trying to see the tables that the user (the one who created it in the first place). I ran a trace of what query was being sent, apparently this is the query which I'm having a bit of trouble breaking down.

SELECT NULL, OWNER, OBJECT_NAME, CASE WHEN OWNER IN ('SYS','SYSTEM','CTXSYS','DMSYS','EXFSYS','OLAPSYS','ORDSYS','MDSYS','WKSYS','WK_TEST','WMSYS','XDB') THEN 'SYSTEM ' ELSE '' END || OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('VIEW','TABLE','SYNONYM') AND (1<2 OR (1>2)) AND (LOWER(OWNER) = LOWER1) OR (1<2)) AND (OBJECT_NAME = :2 OR (1<2)) AND ((CASE WHEN OWNER IN ('SYS','SYSTEM','CTXSYS','DMSYS','EXFSYS','OLAPSYS','ORDSYS','MDSYS','WKSYS','WK_TEST','WMSYS','XDB') THEN 'SYSTEM ' ELSE '' END) || OBJECT_TYPE IN 3,:4,:5,:6,:7)) ORDER BY 2, 3

A little more detail: This same query is used against one of our existing Oracle 10g servers (10.1.0.2.0). This new oracle database I am setting up is a newer release (10.2.0.1.0). Is there a reason why it would work against an older server but not a newer one?

Really appreciate the help. I'm new to this, but I'm trying to learn my best

 

[rcurva]

I am using 10.2.0.4 and your query works for me. Here's how I tested it with minor modifications to make it a bit faster;

in sqlplus>

var c1 varchar2(10)
var c2 varchar2(30)
var c3 varchar2(30)
var c4 varchar2(30)
var c5 varchar2(30)
var c6 varchar2(30)
var c7 varchar2(30)

-- to get all objects that you have access to
begin
select null,null,'VIEW','TABLE','SYNONYM','PROCEDURE','FUNCTION'
into :c1,:c2,:c3,:c4,:c5,:c6,:c7
from dual;
end;
/

-- to get SCOTT objects only
begin
select 'SCOTT',null,'VIEW','TABLE','SYNONYM','PROCEDURE','FUNCTION'
into :c1,:c2,:c3,:c4,:c5,:c6,:c7
from dual;
end;
/


SELECT null, OWNER, OBJECT_NAME,
CASE WHEN OWNER IN ('SYS','SYSTEM','CTXSYS','DMSYS','EXFSYS','OLAPSYS','ORDSYS','MDSYS','WKSYS','WK_TEST','WMSYS','XDB')
THEN 'SYSTEM '
ELSE '' END || OBJECT_TYPE
FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('VIEW','TABLE','SYNONYM')
AND (1<2 OR (1>2))
AND (LOWER(OWNER) = LOWER(NVLc1,OWNER)))
AND (OBJECT_NAME = NVLc2,OBJECT_NAME))
--AND (LOWER(OWNER) = LOWERc1) OR (1<2))
--AND (OBJECT_NAME = :c2 OR (1<2))
AND ((CASE WHEN OWNER IN ('SYS','SYSTEM','CTXSYS','DMSYS','EXFSYS','OLAPSYS','ORDSYS','MDSYS','WKSYS','WK_TEST','WMSYS','XDB')
THEN 'SYSTEM '
ELSE '' END) || OBJECT_TYPE IN c3,:c4,:c5,:c6,:c7))
ORDER BY 2, 3
/

Somehow, the following predicates are too slow to run and unnecessary.

--AND (LOWER(OWNER) = LOWERc1) OR (1<2))
--AND (OBJECT_NAME = :c2 OR (1<2))

Do you have a way to modify this SQL?


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[maka1]

Hi Robbie,

I can probably get this SQL modified, but not immediately and rather get it working with the existing query since it should be working.

So how did you test if the query I gave out worked for you? Because when I run this query through SQL Plus, it tells me "SP2-0552: Bind variable "C5" not declared"

Thanks.

 

[SantaMufasa]

Maka,

As you are probably aware, in the SQL*Plus environment, any named token that has a colon (":") preceding its name when referencing that token is in the category of "bind variable".

Within SQL*Plus, to see the currently existing bind variables, you can type (at the SQL*Plus prompt) the command, "var".

Following is a simple set of commands that define, populate, and reference a bind variable:

SQL> var maka varchar2(10)
SQL> exec :maka := 'Hello'
SQL> select :maka from dual;

:MAKA
----------------------------
Hello
SQL>

In the error you are encountering, above, the bind variable, ":c5", apparently does not exist at reference time. Could you please re-run your code, above, then following the error message, please execute the SQL*Plus "var" command, and post the results here?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[rcurva]

Hi Maka,

Mufasa is right, maybe you forgot to assign values for your bind variables.

You define the variables first, in sqlplus;

var c1 varchar2(10)
var c2 varchar2(30)
var c3 varchar2(30)
var c4 varchar2(30)
var c5 varchar2(30)
var c6 varchar2(30)
var c7 varchar2(30)

Then, assign values to these variables within the same session of sqlplus;

begin
select null,null,'VIEW','TABLE','SYNONYM','PROCEDURE','FUNCTION'
into :c1,:c2,:c3,:c4,:c5,:c6,:c7
from dual;
end;
/

Take note :c1 is your OWNER and :c2 is the OBJECT_NAME. From the assign SQL about, I put NULL values on both to retrieve everything but you can play around with these two variables if you want.

Then run your SQL, but replacing :1 with :c1 etc...

Robbie



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[maka1]

hmm.. okay running what you suggested Mufasa, I get no bind variables have been defined. But in my app, I know the bind variables have been set.

Running this:
SELECT NULL, OWNER, OBJECT_NAME,
CASE WHEN OWNER IN ('SYS','SYSTEM','CTXSYS','DMSYS','EXFSYS','OLAPSYS','ORDSYS','MDSYS','WKSYS','WK_TEST','WMSYS','XDB')
THEN 'SYSTEM '
ELSE '' END || OBJECT_TYPE
FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('VIEW','TABLE','SYNONYM')

returned 21625 rows....

I cannot seem to find the correct command to see what "ROLES" have been set on a specific USER. I want to verify what ROLES have been given to the user on my existing database for comparison.

I'm starting to believe it is a bug in the application itself used to connect to the database (maybe a compatibility problem)? Seeing the roles of this existing user will help..

Thanks.

 

[SantaMufasa]

To see the roles for the currently logged-in user:

select granted_role from user_role_privs;

To see the role membership for all users:

select grantee,granted_role from dba_role_privs order by 1,2;

Let us know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[maka1]

Hi Mufasa, thank you for that.

So indeed, the roles are the same. the queries are the same, I do not understand why the existing database works, but the new one will not. The only difference I guess as mentioned before is the slightly newer version in the new database..

Any ideas? I am tempted to switch to the older database version, but that seems silly.

 

[rcurva]

Hi Maka,

When you say that the query does not work, I am getting the impression here that the SQL DOES WORK but returning 0 (zero) records.

Is that right?

But if the SQL is totally not working and returning some "ORA-*" errors then you have problems with your user.

Regards,
Robbie

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[SantaMufasa]

Maka,

Database versions are not the source of your privileges/access problems. Despite the roles being identical between databases, the permissions to SELECT, INSERT, UPDATE, and DELETE from your applications tables are not the same...I can just about guarantee that.

There is a data dictionary view named USER_TAB_PRIVS. Connect to Oracle as the Oracle user for whom privileges is the issue. Next, execute the SQL*Plus command, "describe user_tab_privs". That command shows you the columns available to query. Query (SELECT) the columns in that view for the table(s) for which that user lacks proper privileges. Issue the same query on the other database. Again, I'll bet there are differences.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Robbie brings up a good point...When you say:

I do not understand why the existing database works, but the new one will not.

...You have given us no clue as to what "works" versus "doesn't work" means...Are you encountering:[ul][li]syntax errors[/li][li]run-time errors (including "ORA-00942: table or view does not exist", or "ORA-01031: insufficient privileges", et cetera)[/li][li]logic errors (i.e., your results aren't what you expected, such as "no rows selected")[/li][/ul]Please let us know specifically what you are encountering.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[maka1]

hmm...

to Robbie:
the app i am using to connect will hang for the new database (in the trace, hangs on execution of the query i posted earlier).

to Mufasa:
I'm not too sure what I have to do here. I ran 'describe user_tab_privs' and see this:

[small]
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
[/small]
I'm not sure what you mean by query (select) the columns here. Please bear with me.

 

[rcurva]

Hi Maka,

I encountered the same problem when I run your SQL against my 10.20.4, somehow it was too slow for these two predicates;

AND (LOWER(OWNER) = LOWERc1) OR (1<2))
AND (OBJECT_NAME = :c2 OR (1<2))

So, I changed it to;

AND (LOWER(OWNER) = LOWER(NVLc1,OWNER)))
AND (OBJECT_NAME = NVLc2,OBJECT_NAME))

Regards,
Robbie





Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[SantaMufasa]

I'm not too sure what I have to do here.

Sorry...with your being a new participant in this forum, we're just learning your level of literacy with Oracle/SQL...we don't want to insult you with suggestions that are condescending, yet we don't want to confuse you with suggestions that are beyond your knowledge/literacy level either.


I'll show an example of a query that discloses the permissions that a connected user ("YADA") possesses:

select owner, table_name, privilege
from user_tab_privs
order by 1,2,3;

OWNER TABLE_NAME PRIVILEGE
----- ---------- ---------
TEST  S_DEPT     INSERT
TEST  S_DEPT     SELECT
TEST  S_EMP      SELECT
TEST  S_REGION   SELECT
TEST  S_REGION   UPDATE

Let us know if this answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[maka1]

hi mufasa, thanks. you were right, running these two queries returned different results. On my existing database, it returned:

[small]
SQL> select owner, table_name, privilege from user_tab_privs order by 1,2,3

OWNER TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE
----------------------------------------
SCOTT NUMBERECHO_ORACLE
EXECUTE
[/small]

but on my new database, no rows were selected.

robbie:
I think what you are experiencing is exactly what I am running into. I left the query running when I assumed it was hanging yesterday before leaving. Now this morning, I can see that the "two" tables that have been created by my user has been populated and shows up. so indeed, it wasn't hanging.. it was just taking a really long time.