A reader, October 03, 2002 - 4:27 am UTC
where in the data dictionary can we see that the resource role has the unlimited tablespace privilege
October 03, 2002 - 9:24 am UTC
Well, semantically speaking, I mispoke.
the resource role doesn't "have it".
granting someone the resource role implicitly grants UNLIMITED TABLESPACE to the user.
revoking resource implicitly revokes it from them.
UNLIMITED TABLESPACE cannot actually be granted to a role at all! so, it is not in the data dictionary.
A reader, October 03, 2002 - 9:37 am UTC
which other privileges are implicitly granted through the roles
is it possible to list them somehow
October 03, 2002 - 10:26 am UTC
that is the only anomaly I am aware of.
It follows with this advice:
do not use connect, resource or DBA unless you have to. Somethings require DBA (the products look for that role unfortunately) but you should not need connect (which has WAY too much power) and resource (even more)
create your OWN roles and use those instead.
A reader, October 03, 2002 - 10:37 am UTC
thanks tom!
Need Advice for New Role
Michael, April 13, 2004 - 10:17 am UTC
We are in 9iR2 on Sun.
I just started work as a DBA for a new employer, and ran into a slight snag right away. They have a HUGE partitioned table which has daily partitions, and six new ones are added each week (no load on Saturday). A special account runs SQL*Loader scripts each morning to populate these partitions, which are created at the beginning of the week. The Owner of the partitioned table HAD the DBA role (previous DBA had granted) but the auditors made me revoke DBA from that account yesterday (I agreed that it should not have the DBA role anyway). However, the load blew up this morning on a tablespace quota exceeded message. The schema owner does have the Resource role. I would like to create a special role that has the system privileges required to enable the schema owner to have these partitions populated in the new tablespaces without having to explicitly alter that user to grant a tablespace quota. The problem is that the DBA role has 139 system privileges. I could guess which ones might enable this, but would like to solve this without trial and error. Once again, the goal is to create a role with whatever system privileges are necessary for the schema owner to use up the entire (new) tablespaces if desired, without requiring a tablespace quota. Our only other option would be to give the account that creates the new tablespaces each week (this is done from another location) the "alter user" privilege so it can grant six "quota unlimited" statements for the new tablespaces.
April 13, 2004 - 10:53 am UTC
resource has "UNLIMITED TABLESPACE" -- unless you manually revoked that.
grant unlimited tablespace to that_schema;
User has resource role
Michael, April 13, 2004 - 12:02 pm UTC
No, I did not revoke "unlimited tablespace" from the resource role. So, this user has the normal resource role, but that is not enough to use unlimited space inside the new partitions/tablespaces created each week. It appears that the user needs another role or some system privilege(s) to allow that. In the interim, I am simply granting quota unlimited for this user on this week's tablespaces.
BTW, I cannot find "unlimited tablespace" as a system privilege in 9iR2. I seem to recall it was there in previous versions.
April 13, 2004 - 6:21 pm UTC
you cannot revoke unlimited tablespace from a role (in fact, you cannot even really grant it to a role)
you can however from a user.
ops$tkyte@ORA9IR2> grant unlimited tablespace to scott;
Grant succeeded.
ops$tkyte@ORA9IR2> grant unlimited tablespace to connect;
grant unlimited tablespace to connect
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
the granting of RESOURCE or DBA to a user will grant them unlimited tablespace. revoking will revoke it.
I'll hypothesize:
a) user was granted DBA and RESOURCE (that gave them unlimited tablespace)
b) user was revoked DBA (that took it away)
c) simply granting it to them will put it back...
Consider:
ops$tkyte@ORA9IR2> drop user a cascade;
User dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by a;
User created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant dba, resource to a;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select 'tab', privilege from dba_tab_privs where grantee = 'A'
2 union all
3 select 'sys', privilege from dba_sys_privs where grantee = 'A'
4 union all
5 select 'rol', granted_role from dba_role_privs where grantee = 'A';
'TA PRIVILEGE
--- ----------------------------------------
sys UNLIMITED TABLESPACE
rol DBA
rol RESOURCE
<b>now you see it...</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> revoke dba from a;
Revoke succeeded.
ops$tkyte@ORA9IR2> select 'tab', privilege from dba_tab_privs where grantee = 'A'
2 union all
3 select 'sys', privilege from dba_sys_privs where grantee = 'A'
4 union all
5 select 'rol', granted_role from dba_role_privs where grantee = 'A';
'TA PRIVILEGE
--- ----------------------------------------
rol RESOURCE
<b>Now you don't</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant resource to a;
Grant succeeded.
ops$tkyte@ORA9IR2> select 'tab', privilege from dba_tab_privs where grantee = 'A'
2 union all
3 select 'sys', privilege from dba_sys_privs where grantee = 'A'
4 union all
5 select 'rol', granted_role from dba_role_privs where grantee = 'A';
'TA PRIVILEGE
--- ----------------------------------------
sys UNLIMITED TABLESPACE
rol RESOURCE
<b>now you see it again...</b>
unlimited tablespace
Jim, April 13, 2004 - 1:00 pm UTC
page 929 in 9i DBA Handbook lists unlimited tablespace grant. That is all you need for this problem
Unlimited Tablespace
Michael, April 13, 2004 - 1:40 pm UTC
Yes, I saw that in the DBA Handbook, and I agree that giving the user that privilege should solve the problem. That has been my experience in the past. The issue I cannot resolve right now is whether the RESOURCE role, which the user had, should have given that privilege via the role. To put it another way, if I issue the query:
select grantee from dba_sys_privs where privilege = 'UNLIMITED TABLESPACE'
the RESOURCE role is not one of the grantees. Is that correct? I have no other 9iR2 database to check this against yet. It is possible that the previous DBA messed with roles/privileges. Otherwise, it will be clear that the previous DBA simply gave the user the DBA role as a quick fix instead of creating a customized role including UNLIMITED TABLESPACE or granting it to the user directly.
April 13, 2004 - 6:28 pm UTC
see above...
By George He's Got It
Michael, April 14, 2004 - 11:20 am UTC
Thanks for the explanation. I could not understand why the user lost UNLIMITED TABLESPACE when I only revoked the DBA role but it still had the RESOURCE role.
Who Granted the Privs.
Raj, June 01, 2004 - 2:39 am UTC
Hi Tom
Can we know who granted the priv. to a user or Role
ex:
in SYS
Grant DBA to my_usr
is there any table which holds which privs given to whom by whom...
Thanks for Reading the Request.
Raj.
June 01, 2004 - 8:30 am UTC
we don't really track that
revoke select privilege from user
Teymur Hajiyev, September 10, 2004 - 6:28 am UTC
Dear Tom.
Is is possible to revoke select privilege from user? F.e: I dont want user to view my data.
Thanks in advance.
September 10, 2004 - 9:19 am UTC
of course.
How?
Teymur Hajiyev, September 13, 2004 - 1:58 am UTC
Thanks. Now how can I do it?
Thanks in advance.
September 13, 2004 - 8:00 am UTC
revoke select from bob;
???
Now of course, if select has been granted to public, or if select was granted to a role and that role is granted to bob, bob can still access that -- but only because you said "everyone can" with public or "anyone with this role can"
If so, that would be a failing of your implementation -- there is no "anti-privilege" that cancels out a priv
If bob has select any table
If public has select
If a role bob has has select
you would need to revoke those as well.
list of predefined privileges
j., October 28, 2004 - 3:11 am UTC
hi tom,
can one get the list of all privileges available (in general, "types") by querying the dictionary?
October 28, 2004 - 7:53 am UTC
ops$tkyte@ORA9IR2> select * from system_privilege_map;
PRIVILEGE NAME PROPERTY
---------- ------------------------------ ----------
-3 ALTER SYSTEM 0
.....
Property Flag ?
raju, October 28, 2004 - 10:04 am UTC
What does the Property Flag mean. Look at the reference manual and it just says "Property flag of the privilege". Please can you elaborate.
Thanks
October 28, 2004 - 1:36 pm UTC
0 = valid, good
1 = was valid, no longer is a privilege.
Privilege
Laxman Kondal, December 06, 2004 - 2:13 pm UTC
Hi Tom
If public synonyms has been ceated for an object - procedure, user can see that object_name in all_objects, and where can user see if he has execute privilege on that procedure rather executing and getting error:
PLS-00201: identifier 'OBJECT_NAME' must be declared.
Thanks.
December 07, 2004 - 9:38 am UTC
scott@ORA10GR1> select *
2 from all_tab_privs
3 where (table_schema,table_name) in ( select table_owner, table_name
4 from all_synonyms
5 where synonym_name = 'TKYTE_T' )
6 /
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
OPS$TKYTE SCOTT
OPS$TKYTE T
SELECT NO NO
Privilege
Laxman Kondal, December 06, 2004 - 5:00 pm UTC
Hi Tom
I got it from this query
SELECT table_name, privilege, grantor, grantee,
(SELECT object_type FROM ALL_OBJECTS
WHERE object_name = a.table_name
AND ROWNUM = 1) obj_ty
FROM ALL_TAB_PRIVS a
WHERE table_name LIKE 'P\_%' ESCAPE '\';
Thanks.
December 07, 2004 - 9:46 am UTC
it is wrong.
and rownum=1? that will return random stuff for you.
where is the "and owner = a.table_schema" in there (i have a table T, you have a procedure T, he has an index T, she has a function T -- now what gets returned...)
Privileges
Laxman Kondal, December 07, 2004 - 2:42 pm UTC
Hi Tom
Thanks for reply.
Without rownum=1 query will return package and package body.
For me I just needed package name/body to figure out if user has the execute privilege on that package.
You are correct, if I run this query as DBA then I defnitely need "and owner = a.table_schema".
Thanks for making it clear profession approach.
Thanks.
Revoke GRANT EXECUTE from the database schema owner
A reader, January 04, 2006 - 1:05 am UTC
Tom,
I have many database users in the database.
I have a situation where i need to revoke the "grant execute on <procedure/function>" to other users/roles
from the owner of the procedure itself.
In other words, he should not be allowed to grant on any PL/SQL procedural objects to others.
Is it doable in 9.2.0.5 and above.
Thanks
January 04, 2006 - 8:51 am UTC
No, it doesn't even begin to make sense. You have - in my opinion - set up your schema's wrong, letting the wrong people log in as certain schemas perhaps and are trying to "backend fix that"
Why shouldn't the OWNER of the procedure be able to GRANT on that procedure?!?!
A reader, January 04, 2006 - 11:59 am UTC
Tom,
Here is the scenario.
This is on development database.
I have a schema owner (appown) and app user (appusr) with necessary grant via roles. This is the single user schema used for every release. Now we have April release coming up, which needs to modify some existing stored procedures (Februray release).
I have setup the database user account which will share all the database objects with Feb schema via private synonyms and privileges.
As part of the sp code, we have grant statements to "grant execute privilege on SP" to app user, vieownly user and cron user schemas via ROLES as below.
grant execute on store_port to app_user;
grant execute on store_port to app_view;
grant execute on store_port to cron_user;
app_user, app_view and cron_user are database roles.
Now when the same stored procedure code is modified for April release by April schema owner, i do not give execute to app_user as it will messup the Februrary stored procedures.
Just wondering, is there any way to prevent executing GRANT so that it does not get granted to app_user which is the role assigned to "Feb schema user".
I just do not want to create another brand new database for this.
January 04, 2006 - 12:24 pm UTC
since roles are not enabled during the execution of definers rights procedures and never during the compilation of any type of procedure - how could a role muck up a procedure in your environment?
You would just "NOT GRANT" - seems straight forward?
Sam, May 12, 2010 - 12:17 am UTC
Hi Tom,
Some of the products recommend to create a user with powerful system privileges (alter any table, lock any table) for a user setup and we could not grant these permissions to the user. How do we enforce security policy in this situation?
Please share your thoughts on this.