Problem
Reader, September 19, 2001 - 9:25 am UTC
Hi, Tom,
When i run:
select u2.name granted_role
from ( select *
from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = uid or grantee# = 1) sa,
sys.user$ u2
where u2.user#=sa.privilege#
union all select user from dual
union all select 'PUBLIC' from dual;
every thing is Ok, but it gave me error as long as i run:
create view user_role_hierarchy
as
select u2.name granted_role
from ( select *
from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = uid or grantee# = 1) sa,
sys.user$ u2
where u2.user#=sa.privilege#
union all select user from dual
union all select 'PUBLIC' from dual
the error is:
from sys.sysauth$
*
ERROR at line 4:
ORA-00942: table or view does not exist
Does something wrong?
Thanks
September 19, 2001 - 10:45 am UTC
see
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
you have the ability to query sys.sysauth via a role and roles are not enabled during the compilation of stored objects.
You need direct SELECT on the sys tables (or if you create that as SYS as suggested, you can just grant select on that view)
I found this tip very useful
Javier Pflaum, March 22, 2002 - 8:39 am UTC
As an Infosec Officer I very often control right in ower DBs and this help me a lot to do my work.
Thanks.
Re: Listing roles and privs recursively
CJ, October 28, 2002 - 12:45 pm UTC
Is there any easy way to do this for the entire instance? That is, list out all users, their roles, and their privileges, not just for one user?
October 28, 2002 - 7:14 pm UTC
You can definitely use the same exact concept -- you would need to change the queries to include usernames -- not start with uid (no start with)
Account Privileges
mo, January 06, 2003 - 11:48 am UTC
Tom:
Does the DBA role mean that this account has DBA privilege? If not how do you find out if an account has a DBA privilege? Is this error because I am not logged in as SYS?
SQL> grant select on user_role_hierarchy to public;
grant select on user_role_hierarchy to public
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.USER$'
SQL> select * from user_role_hierarchy;
GRANTED_ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
IMP_FULL_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
PCSADMIN
PUBLIC
20 rows selected.
January 06, 2003 - 12:16 pm UTC
you are not allowed to grant on SYS.USER$ and you would be indirectly granting on it by granting on this view.
So, log in as sys, grant select on user$ to YOUR_SELF with grant option if you like.
account privilege
mo, January 06, 2003 - 12:33 pm UTC
Tom:
well i can not log in as sys as a developer. But is this the correct way of determining if this account has dba privilege? is that the DBA role that defines it?
January 06, 2003 - 1:13 pm UTC
select * from session_roles
Reader
A reader, January 07, 2003 - 6:03 pm UTC
"
following lists all of your roles for you (might list some N times)...
"
Is it possible to modify this, to get similar
results for any given user other than logged on user
Thanks
January 08, 2003 - 2:57 pm UTC
sure it is. it is just a query that selects out based on USER -- should be easy to upgrade to an "ALL_" or "DBA_" type query.
grantee# = 1 ????
r, January 31, 2003 - 5:25 am UTC
Why "grantee# = 1" in the query. I queried the dba_users table to find out which user had the user_id of 1 but I couldn't see any.
Thanks
January 31, 2003 - 8:25 am UTC
PUBLIC
cascading effect
A Reader, April 28, 2003 - 10:15 pm UTC
Why does oracle not cascade the effects of system privs when it is revoked and the same is not true with revoking object privs as cascading effect is there with object privs? Is there any logic or rational behind this implementation.
Thanks.
April 29, 2003 - 7:51 am UTC
it is the implementation -- it is the design. it is the way it works.
they are two totally different things. Imagine for a moment the effect of dropping "the" user that begat all other users. You would end up with a database full of people that aren't able to do anything.
object privs are just totally different then system privs.
cascade
Reader, May 23, 2003 - 4:17 pm UTC
If I granted a system priv to userX with admin option and userY grants that priv to UserZ. Now if i drop userY, what happens to the priv that was granted to UserZ by UserY? I was asked this question but did not know the answer. Could you help? Also, if the same is done with object priv with grant option, what Oracle would do if I dropped userY? Thanks. You are amazing. I enjoy reading your site almost everyday.
May 24, 2003 - 10:02 am UTC
<b>system privs persist -- things granted via the WITH ADMIN OPTION persist. object privs are retracted -- things granted via the WITH GRANT OPTION go away.</b>
Consider -- A and B:
ops$tkyte@ORA920> create table t ( x int );
Table created.
ops$tkyte@ORA920> create user a identified by a;
User created.
ops$tkyte@ORA920> create user b identified by b;
User created.
ops$tkyte@ORA920> grant select on t to a <b>with grant option;</b>
Grant succeeded.
ops$tkyte@ORA920> grant create view to a <b>with admin option;</b>
Grant succeeded.
ops$tkyte@ORA920> grant create session to a;
Grant succeeded.
ops$tkyte@ORA920> grant create session to b;
Grant succeeded.
ops$tkyte@ORA920> @connect a/a
a@ORA920> grant select on ops$tkyte.t to b;
Grant succeeded.
a@ORA920> grant create view to b;
Grant succeeded.
a@ORA920> @connect b/b
b@ORA920> desc ops$tkyte.t
Name Null? Type
----------------------------------- -------- ------------------------
X NUMBER(38)
b@ORA920> create view v1 as select * from dual;
View created.
<b>so, B can see ops$tkyte.t and B can create views... now we'll drop A</b>
b@ORA920> @connect /
ops$tkyte@ORA920> drop user a;
User dropped.
ops$tkyte@ORA920> @connect b/b
b@ORA920> desc ops$tkyte.t
ERROR:
ORA-04043: object ops$tkyte.t does not exist
b@ORA920> create view v2 as select * from dual;
View created.
<b>B can no longer see ops$tkyte.t but B can still create views</b>
b@ORA920>
THANKS a bunch!
Reader, May 24, 2003 - 1:28 pm UTC
Strong suggestion.
Saibabu Devabhaktuni, May 24, 2003 - 4:02 pm UTC
Since this thread is on Roles/privileges and accessibility, please do read Metalink doc# 9368.996 before using some of the system privileges like "create any view/procedure"...etc.
Thanks
A reader, September 03, 2003 - 11:04 am UTC
Hi Tom,
Can we grant select privileges on certain column of a table ?
for eg. I have table x with column a,b
I want to grant select on only column a to public.
How can I do that?
Thanks
September 03, 2003 - 12:05 pm UTC
no, you cannot.
you use vertical partitioning to achieve that goal.
or views.
SYSOPER
Anurag, September 03, 2003 - 12:30 pm UTC
Tom,
I am using Oracle 8i (8.1.5). When I connect as
conn sys/oracle as sysoper......
it shows connected to an ideal instance.
but the database is already open. Also where are these roles(Sysdba and Sysoper) listed.
and why are they called as roles. Becoz, normally we don't login as conn user/pwd as role.
pl. clarify. thanks in adv.
September 04, 2003 - 8:20 am UTC
i don't think so. you are doing something wrong -- you've connected to an idle instance, not one that is up -- just type startup and see what happens. if the database was already going, well, that'll not work.
sysdba and sysoper are "special roles"
actually -- only your dba should be using sysoper and sysdba and they would know what they are for. sysdba is "all powerful" and sysoper is allowed to startup and shutdown.
sysdba....
anurag, September 05, 2003 - 5:40 am UTC
When I wrote ..........startup
it says ... can not start already running oracle.
Can U try this on your machine.
thanks ...
September 05, 2003 - 3:57 pm UTC
then, why you you think it is "idle"
Roles and privs assigned to them.
jj, October 01, 2003 - 10:40 am UTC
How to get information about a role wrt roles and other privileges assigned to it? If a role is assigned to a role then just its name is okay, no recursion needed.
Which views/tables to query to get this info?
October 01, 2003 - 1:48 pm UTC
dba_tab_privs
dba_sys_privs
dba_role_privs
where grantee = 'ROLE NAME';
Execute privilege on triggers
A reader, October 13, 2003 - 9:32 pm UTC
Tom
We have a table T, it has a post insert trigger post_insert_T_trigger. The trigger calls a package P, to insert into another table TI.
Logged in as user1, when I inserted rows into the table, the trigger did not fire( this was evident from the fact that ti was not getting populated).
But when I logged in as user2, and inserted rows into T, the trigger fired and TI got populated.
1.Now I want to know, what can be the different reasons due to which user1 was not able to fire the trigger.
2.How can we find out whether user1 has execute privilege on the trigger T. Which V$view gives that information.
If privileges are being given via a role, how can we find out which role has the respective privilege, and whether user1 has the role assigned. If the role is assigned to user1, then to check and see if the execute privilege on trigger T is not assiged to user1.
October 14, 2003 - 6:30 am UTC
what you describe is just not possible. You've made a mistake somewhere (eg: most probably cause -- you have many table T's and you used the wrong one)
you need not have execute on a trigger - there is no such concept at all. triggers are part of the table. all you need is the ability to insert into the table and the code will run.
You've made a mistake in your test somewhere.
How can I verify
A reader, October 14, 2003 - 8:27 am UTC
How can I verify if I have privilege to insert into the table.
October 14, 2003 - 10:54 am UTC
well, you could try it - that would tell you right away.
you can query the data dictionary - dba_tab_privs - to see if you or any role you have has insert or all on that table as well
RE: How can I verify
Mark A. Williams, October 14, 2003 - 10:04 am UTC
RE: How can I verify
Have a look at user_tab_privs.
HTH,
Mark
Grants created/modified on
Tommy, October 22, 2003 - 11:02 am UTC
Is there a way to find out "when" a grant is created or modified? I have looked into USER_TAB_PRIVS and USER_OBJECTS...
October 22, 2003 - 5:58 pm UTC
we do not track the time, you would have to have enabled auditing to capture that.
Grants on USER_TABLES
Vinnie, February 27, 2004 - 9:50 am UTC
Is there anything special about granting select user_tables to a role?
>GRANT SELECT ON USER_TABLES TO my_role;
But when I log as my username that has been granted that role I can't see anything in user_tables?
February 27, 2004 - 10:12 am UTC
do you have any tables to see?
if the query executes but returns 0 records, you just don't own anything (but you had access to the view)
if you want to see tables you have access to, use all_tables
How to
A reader, October 13, 2004 - 9:41 pm UTC
1.I want to know the privileges assigned to a user on a particular object.
2.I want to know all the roles assigned to a particular user.
3.I want to see all the roles assigned to a particular role.
Please show the best way to achieve the above 3.
October 14, 2004 - 9:16 am UTC
1) query dba_tab_privs
2) query dba_role_privs
3) see #2
(all of these views are documented in the Oracle Reference Manual if doing desc <viewname> isn't clear -- very skinny views, easy to use)
users and privileges
Alf, March 04, 2005 - 12:44 pm UTC
Hello Tom,
I'm trying to get a list of usernames base on the dba_users table with their privileges
I'm using:
select u.username, p.privilege, p.admin_option
from dba_sys_privs p, dba_users u
where p.username = p.grantee;
But I'm not able to get all of them, there are about 80 usernames on the dba_users table that are not showing up. In the other hand if I use "p.username = p.grantee(+)" I won't shows blank space under the privileges column. Would please advice or point out what I'm doing wrong or what needs to be modified in order to get this right. Thanks.
Oracle 9i R2
AIX 5.2
Cheers
Alf
March 04, 2005 - 1:53 pm UTC
full outer join
from dba_sys_privs full outer join dba_users on (p.username = p.grantee)
Correction; users and privileges
Alf, March 11, 2005 - 2:09 pm UTC
Hi Tom,
Thanks for your help.
Please allow me to correct errors for the where clause of my original listing, it should has been:
u.username = p.grantee(+); instead of p.username = p.grantee
However I modified it to include the full outer join:
select u.username, p.privilege, p.admin_option
from dba_sys_privs p full outer join dba_users u on (u.username = p.grantee)
where u.username = p.grantee;
But it wouldn't work, I get disconnect from the DB (I'm trying this statement from TOAD) I cant catch what Im doing wrong here!
In the other hand, I decided to change this statement to:
select u.username, pr.privilege
from dba_users u,
dba_role_privs r,
dba_sys_privs pr
where r.grantee = pr.grantee and
u.username = u.username
Although this one worked and returned results as expect. Im wondering if there this is a good way to accomplish this.
Any suggestions would be greatly appreciated, thanks.
March 12, 2005 - 8:19 am UTC
if you are getting a 3113/600/7445 that implies -> contact support.
Can you check this out?
Bernice, April 13, 2005 - 9:03 pm UTC
Hi Tom,
This is my goal - spool into a csv file all the object and system privileges granted (even ones through roles) to every user in the database (except for system and sys).
I found this page (the query at the very top) and various other pages on your site to figure out how to write data out to a file (utl_file).
I am a dba 'learning' to code with pl/sql. Can you please look at this code to make sure I am not doing anything stupid? I ran this against my mini-test db of 4 users (9.2.0.5 oracle on solaris 5.9) and it does the job. I do plan to run this against my 8i and 9i production dbs(which have on average, 50 users).
Additional Questions -
1. As you can see, when marching through the roles for a specific user, I query for the object privs and system privs for every role rather than doing one obj priv query with all the roles in the predicate (ie. "where grantee in ('...','...')... ) Reason is because I do not know how to dynamically create the predicates. I searched this site and I came across "ref_cursors" and "dynamic sql" (dbms_sql). Which one should I use? Or should I bother?
2. I found in your expert 1:1 book in the appendix for utl_file, a section for 'exceptions'. Where would I stick this in my code (for good practice)? Is it between "utl_file.fclose" and "end"?
Here's my code --
create or replace procedure lkup_role3 is
type user_record_type is record
(id number,
name varchar2(30));
type priv_record_type is record
(privilege varchar2(40),
owner varchar2(30),
table_name varchar2(30));
v_user user_record_type;
v_role varchar2(30);
v_objpriv_rec priv_record_type;
v_syspriv varchar2(40);
l_output utl_file.file_type;
l_line long;
cursor c_allusers is
select user#, name from sys.user$
where type# = 1 and name not in ('SYSTEM', 'SYS');
cursor c_usr_role is
select u2.name granted_role
from ( select * from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = v_user.id or grantee# = 1) sa,
sys.user$ u2
where u2.user#=sa.privilege#
union all select v_user.name from dual;
cursor c_objprivs is
select distinct PRIVILEGE, owner, table_name
from dba_tab_privs
where grantee = v_role;
cursor c_sysprivs is
select distinct PRIVILEGE
from dba_sys_privs
where grantee = v_role;
begin
l_output := utl_file.fopen ( '/space3/temp/utlfile_out' , 'priv.csv', 'a', 32760);
-- Find all the users, except for sys and system, that exist in the database.
open c_allusers;
loop
-- Loop through all the users and get their assigned roles
fetch c_allusers into v_user;
exit when c_allusers%NOTFOUND;
open c_usr_role;
loop
fetch c_usr_role into v_role;
exit when c_usr_role%NOTFOUND;
-- For a specific role, look up all the object privs
open c_objprivs;
loop
fetch c_objprivs into v_objpriv_rec;
exit when c_objprivs%NOTFOUND;
l_line := v_user.name ||','|| v_role ||','|| v_objpriv_rec.privilege ||','|| v_objpriv_rec.owner ||','|| v_objpriv_rec.table_name;
utl_file.put_line(l_output, l_line);
end loop;
close c_objprivs;
-- For a specific role, look up all the system privs
open c_sysprivs;
loop
fetch c_sysprivs into v_syspriv;
exit when c_sysprivs%NOTFOUND;
l_line := v_user.name ||','|| v_role ||','|| v_syspriv;
utl_file.put_line(l_output, l_line);
end loop;
close c_sysprivs;
end loop;
close c_usr_role;
end loop;
close c_allusers;
utl_file.fclose(l_output);
end;
Thank you!
April 13, 2005 - 9:08 pm UTC
1) for a one off sort of thing like this, the brute force approach is OK. the query would be fairly complex to merge it all together. If this was run over and over all day long, I would say different.
I would code:
for x in c_allusers
loop
...
end loop;
instead of the open, fetch, exit when, close -- it is much easier and doesn't let you forget to close or exit a loop;
2) if you want that exception handler, so as to see what the utl_file error was, it would go right after the fclose and before the end.
Thank you
Tramp.Lai, April 13, 2005 - 10:50 pm UTC
thank you --tom!
Listing roles you granted to others
Gabriel Canton, July 19, 2005 - 11:37 am UTC
I want to get the same info (all roles granted to a user) but for a user other than the one querying the view. This is for developers to check they have granted the appropriate roles to users.
I created a similar view based on yours that shows (granted_role,grantee) for all roles granted with admin option to the querying user.
What bothers me is that this view is pretty much what I would have expected to find (even though not recursive) as a native all_role_privs (which unlike other dba/all/user views doesn't exist). Is my approach good, or is there any other way intended to do this?
Thanks a 10^6
July 19, 2005 - 12:09 pm UTC
you could do it recusively via the existing dictionary views, it just wouldn't perform to what you might like, that was the purpose of this particular view
Clarification
Gabriel Canton, July 21, 2005 - 12:50 pm UTC
Thanks for answering! I'm not sure I was clear enough. My reasoning was:
Premises
- Most such views come in user, all and dba flavours. These seem to be aimed at showing a users' own records, a broader set of records somehow restricted to what the user should be allowed to see based on his grants, and all the records.
- This one (role privs) lacks the all version. Odd.
- Stranger still because there seems to be no way for a user to query which roles he has granted to other users. It would be reasonable for the DB to let you access something you created (the grant). It does with your tables, you have admin option on roles you created, and so on.
If these three assumptions were right, the only conclusion I could come up with was that the all_role_privs had been skipped deliberately, and for a reason strong enough to outweigh the incovenience of not being able to query roles you granted. If the designers actually had a purpose for not defining the view (a security reason was my concern) it'd be a bad idea for me to thwart it by creating my own one ...
Or (likely) my assumptions could be wrong. Can you enlighten me?
Terathanks!
July 21, 2005 - 4:52 pm UTC
because USER would be equal to ALL in this case.
ROLES are SYStem objects. Not user objects
Logan Palanisamy, July 21, 2005 - 5:19 pm UTC
ROLES are not owned by any users even if they are created by them. Like DB links, they are owned by SYS objects.
That could be one of the reasons for merging all_role_privs with user_role_privs.
Semantics
Gabriel Canton, July 22, 2005 - 9:06 am UTC
Not sure I follow you both. I guess we have in mind different definitions for the missing all_role_privs. SQL may help me show you my problem:
<SQL_example>
SQL> conn /as sysdba
Connected.
SQL> create user developer identified by developer
2 default tablespace usuarios temporary tablespace temp
3 quota 1 M on usuarios;
User created.
SQL> grant create session, create table, create role to developer;
Grant succeeded.
SQL> conn developer/developer
Connected.
SQL> create table t (x number(1));
Table created.
SQL> create role my_role;
Role created.
SQL> grant select on t to my_role;
Grant succeeded.
SQL> grant my_role to user1;
Grant succeeded.
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
--------------- --------------- --- --- ---
DEVELOPER MY_ROLE YES YES NO
</SQL_example>
Now say you're developer and user1 calls reporting an error related to table t, so you want to make sure you granted him my_role. What do you do to check? User_role_privs won't help.
Well, what happened in real life was developer asked me to grant him select on dba_role_privs. Then I thought of creating this view:
create or replace view all_role_privs
as
select * from dba_role_privs
where granted_role in (
select granted_role from user_roles
where admin_option = 'YES'
)
/
where user_roles is a recursive view like user_role_hierarchy but augmented to show the admin_option column as well. I thought this definition of all_role_privs would match the user/all/dba philosophy shared by the rest of the views ...
Anyway, what would be the intended way to solve developer's problem? If none, what do you think of the view instead of a grant on dba_role_privs from a security perspective?
Thanks very much indeed for your time and help.
July 22, 2005 - 9:15 am UTC
The "who granted the role to someone" isn't tracked so the developer would need access to DBA_ROLE_PRIVS in order to see what roles other users had.
your all_role_privs would show all of the users that have a role the current user is allowed to grant, not the users they in fact granted to. I would think the developer needs an "admin routine" (if they are not to have access to dba_role_privs) that allows them to ask the rather focused question: "does Mary have this role 'foo'" rather than "show me mary's roles"
Actually, the application itself could do this, it is logged in as mary, it could run a query that says "Mary, you are missing the foo role, it is necessary, please contact the help desk" because the application can query session_roles to see what is "on"
A reader, September 03, 2005 - 6:05 am UTC
Tom,
1) When an Forms/any other application is running, is it possible to know what roles current application has got enabled?
2) Logged on as a different user, is it possible to know what the CURRENT_USER is for a different session that is currently executing a program?
Thanks
September 03, 2005 - 8:59 am UTC
1) not really. that is private to the session, part of the session state.
2) v$session shows that.
A reader, September 03, 2005 - 9:52 am UTC
Which column in V$session should be used to get CURRENT_USER of a session, is there something for SESSION_USER as well?
September 03, 2005 - 10:13 am UTC
look at schemaname and username in v$session
roles and privs
A reader, December 13, 2005 - 5:48 pm UTC
December 13, 2005 - 7:27 pm UTC
I haven't tested it - you'd have to do that, but Pete is fairly smart guy.
One shortcoming with my script above is the fact that it works only for the currently logged in user, you'd have to modify it somewhat to do it for "any user"
Thnaks a lot
Amit, December 28, 2005 - 3:49 am UTC
Thanks for such a wonderful activity of helping a newbie like me...
How to get all user ,roles etc?
Amit, December 28, 2005 - 3:52 am UTC
I want to get all user in oracle DB
all roles in the DB?
Also how can i get all roles assigned to a user?
can u help me?
How to add a user that wud be authenticated by windows?
Amit, December 28, 2005 - 3:55 am UTC
I have installed windows xp and oracle 9i
How many authentication mode are supported in oracle?
I want to add a user like machinename\username?
How to add this?
Also can i add a computer in a domianlike in MSSQL
like domainname\username?
Please help me out.....
Getting all users of a role
Amit, December 29, 2005 - 2:36 am UTC
How to get all users of a role?
December 29, 2005 - 10:20 am UTC
see the REFERENCE guide which documents the data dictionary
</code>
https://docs.oracle.com#index-REF <code>
dba_role_privs is what you are looking for "where grantee = 'SCOTT'"
How to check native authencation for a windows user added
Amit, December 29, 2005 - 4:48 am UTC
I have installed oracle 9i on window XP. I have added a user as OPS$DOMAIN\USERNAME to the server.
Now i want to check that this user has access to the database.
How do i do this?
I know that oracle 9i shud be on windows 2000 ,but does it wrk on window XP?
I have started the NTLM servicer from administrative tools.
Please any help is welcome.
December 29, 2005 - 10:22 am UTC
c:\> sqlplus /
when logged in as USERNAME in that domain
(you did read the referenced documentation above right?)
Domain in Windows user name
Michel Cadot, December 29, 2005 - 10:57 am UTC
Hi,
Take care that the domain name may or may not be included in Oracle user name depending on the data (TRUE/FALSE) of the OSAUTH_PREFIX_DOMAIN value in HKLM\Software\Oracle\HOMEx key.
Regards
Michel
Windows Authentication problem.
Amit, December 30, 2005 - 12:23 am UTC
These are the steps that i have follwed
1. Create a user that is a windows user in my domain.
Create user ops$domain\username identified externally.
2.My DB server is on win XP.I started the ntlm service there.
3. I tried connecting using c:> sqlplus \
4.It said TNS adapter error and prompted for username.
I tried locating the key in HKLM\Software\Oracle\HOMEx key but cud not find it.
Where am i goin wrong?
December 30, 2005 - 9:22 am UTC
sqplus /
not \
Linking a user from one database to other?
Amit, December 30, 2005 - 6:58 am UTC
I have created a user in the default db after installation.
If i create a new db, and i want to provide access to user in default db to this db, how to do this?
I have user
grant link database dbname to user identified by password;
but itz giving me error
December 30, 2005 - 9:46 am UTC
you would either
a) create that user in the other database and then either you or they
create database link database_link_name using 'tns-entry';
when they access that database link, it'll use their current credentials and log them on.
b) create a database link:
create database link database_link_name
CONNECT TO some_user_name
IDENTIFIED BY some_password
using 'tns-entry';
That creates a "proxy" database link - anyone that uses it will use that username and password (and the rights/privileges associated with it) to access the remote database - ANYONE that uses it!
How to list all logins associated with a particular role?
Amit, January 02, 2006 - 4:22 am UTC
Suppose i have a user amit.
I have assigned roles to it.
How to list all roles assigned to amit?
January 02, 2006 - 9:58 am UTC
dba_role_privs
Windows Authentication Mode
Amit, January 02, 2006 - 7:01 am UTC
I have oracle 9i on Windows XP.
I want to add a user to it that will be authenticated by OS.
I create a user through OEM that will be identified externally.
Then i start the NTLM service on my machine.The sqlnet.ora has SQLNET.AUTHENTICATION_SERVICES= (NTS)
Now i use the enterprise security manager tool to connect to the server using native authentication options....
But it gives me directory authentication failed?
Can u just list down the procedure to add and test windows authenticated user?
SSL support for thin driver
amit, January 12, 2006 - 6:19 am UTC
I have made a connection to oracle DB by using thin driver.
But if i want to have secure JDBC transactions then is there any parameter that i need to add in url
"jdbc:oracle:thin:@oracle.jdbc.driver.OracleDriver"
like ssl=require...
what are the configurations that are needed to be done for it lke installing ssl certificates and all
Help me out
Privileges
Brad, March 26, 2006 - 7:40 pm UTC
I have a user A who has select any table system privilege granted through a role.This user can basically see all the
tables in all the Schemas in the Oracle instance.Now we need to block this user from seeing just one table..Is there a way to do it??
Thanks
March 26, 2006 - 8:05 pm UTC
yes. absolutely, 100%.
revoke the really utterly bad ALL priv you have.
and grant them specific access to that they should.
(select ANY table - think about it... wow....)
could you use fine grained access control to make this magic really special table appear empty? yes (but hope that user doesn't have other "any" privileges... that can exempt them from other security details)
Brad, March 26, 2006 - 8:33 pm UTC
So..there is no specific way to just block access for this table??your explanation makes sense..i was wondering if there is
any quick/easy way out as this is a one time problem we are facing?
March 26, 2006 - 8:41 pm UTC
find grained access control (dbms_rls)
subconsiously deliberate mistake
Michel Cadot, March 27, 2006 - 1:26 am UTC
Is it? (find/fined) :))
March 27, 2006 - 9:48 am UTC
d and e are very close to eachother :)
fine grained access control.... indeed.
roles & privileges
velu, May 30, 2006 - 5:04 am UTC
-- Description : Use this script to find which privileges have been granted to a
-- particular user. This scripts lists ROLES, SYSTEM privileges
-- and object privileges granted to a user. If a ROLE is found
-- then it is checked recursively.
--
-- The output can be directed to either the screen via dbms_output
-- or to a file via utl_file. The method is decided at run time
-- by choosing either 'S' for screen or 'F' for File. If File is
-- chosen then a filename and output directory are needed. The
-- output directory needs to be enabled via utl_file_dir prior to
-- 9iR2 and a directory object after.
whenever sqlerror exit rollback
set arraysize 1
set space 1
set verify off
set pages 25
set lines 90
set termout on
clear screen
set feed off
set head off
set echo off
set serveroutput on size 1000000
col system_date noprint new_value val_system_date
select to_char(sysdate,'YYYYMMDD') system_date from sys.dual;
undefine user_to_find
prompt
accept user_to_find char prompt 'NAME OF USER TO CHECK : '
/* Creates a temporary view to get the list of ROLES recursivly for a user*/
create or replace view Privileges_Grant_user
(GRANTEE#,PRIVILEGE#,SEQUENCE#,LEV_EL) as
SELECT GRANTEE#,PRIVILEGE#,SEQUENCE#,LEVEL
from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = (select USER# from user$ where name = upper('&&user_to_find'));
/* list the roles Hierarchy */
spool find_all_roles_privs_&val_system_date..log;
prompt *********************************************************
prompt List of roles Hierarchically Granted to &user_to_find
prompt *********************************************************
select lpad( ' ', 6 * ( lev_el - 1 ) ) || u2.name "Role Name"
from Privileges_Grant_user v ,
sys.user$ u2
where u2.user#=v.privilege#;
prompt
drop view Privileges_Grant_user;
declare
--
lv_tabs number:=0;
procedure write_op (pv_str in varchar2) is
begin
dbms_output.put_line(pv_str);
exception
when others then
dbms_output.put_line('ERROR (write_op) => '||sqlcode);
dbms_output.put_line('MSG (write_op) => '||sqlerrm);
end write_op;
--
procedure get_privs (pv_grantee in varchar2,lv_tabstop in out number) is
--
lv_tab varchar2(50):='';
lv_loop number;
--
cursor c_main (cp_grantee in varchar2) is
select 'ROLE' typ,
grantee grantee,
granted_role priv,
admin_option ad,
'--' tabnm,
'--' colnm,
'--' owner
from dba_role_privs
where grantee=cp_grantee and
GRANTED_ROLE NOT IN ('CONNECT','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
'RESOURCE','DBA','IMP_FULL_DATABASE','EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE')
union
select 'SYSTEM' typ,
grantee grantee,
privilege priv,
admin_option ad,
'--' tabnm,
'--' colnm,
'--' owner
from dba_sys_privs
where grantee=cp_grantee
union
select 'TABLE' typ,
grantee grantee,
privilege priv,
grantable ad,
table_name tabnm,
'--' colnm,
owner owner
from dba_tab_privs
where grantee=cp_grantee
union
select 'COLUMN' typ,
grantee grantee,
privilege priv,
grantable ad,
table_name tabnm,
column_name colnm,
owner owner
from dba_col_privs
where grantee=cp_grantee
order by 1;
begin
lv_tabstop:=lv_tabstop+1;
for lv_loop in 1..lv_tabstop loop
lv_tab:=lv_tab||chr(9);
end loop;
for lv_main in c_main(pv_grantee) loop
if lv_main.typ='ROLE' then
write_op(lv_tab||'ROLE => '
||lv_main.priv||' which contains =>');
get_privs(lv_main.priv,lv_tabstop);
elsif lv_main.typ='SYSTEM' then
write_op(lv_tab||'SYS PRIV => '
||lv_main.priv
||' grantable => '||lv_main.ad);
elsif lv_main.typ='TABLE' then
write_op(lv_tab||'TABLE PRIV => '
||lv_main.priv
||' object => '
||lv_main.owner||'.'||lv_main.tabnm
||' grantable => '||lv_main.ad);
elsif lv_main.typ='COLUMN' then
write_op(lv_tab||'COL PRIV => '
||lv_main.priv
||' object => '||lv_main.tabnm
||' column_name => '
||lv_main.owner||'.'||lv_main.colnm
||' grantable => '||lv_main.ad);
end if;
end loop;
lv_tabstop:=lv_tabstop-1;
lv_tab:='';
exception
when others then
dbms_output.put_line('ERROR (get_privs) => '||sqlcode);
dbms_output.put_line('MSG (get_privs) => '||sqlerrm);
end get_privs;
begin
write_op('User => '||upper('&&user_to_find')||' has been granted the following privileges');
write_op('*********************************************************');
get_privs(upper('&&user_to_find'),lv_tabs);
exception
when others then
dbms_output.put_line('ERROR (main) => '||sqlcode);
dbms_output.put_line('MSG (main) => '||sqlerrm);
end;
/
prompt
prompt ********************************************************
prompt End of the report
prompt ********************************************************
spool off
whenever sqlerror continue
Role & privileges
velu, May 30, 2006 - 5:07 am UTC
The out put of the above report:
*********************************************************
List of roles Hierarchically Granted to mtest
*********************************************************
CONNECT
RESOURCE
ROLE_PARENT
ROLE_CHILD01
ROLE_CHILD02
ROLE_OTHER
ROLE_OTHER_CHILD01
User => MTEST has been granted the following privileges
*********************************************************
ROLE => ROLE_OTHER which contains =>
ROLE => ROLE_OTHER_CHILD01 which contains =>
SYS PRIV => SELECT ANY TABLE grantable => NO
SYS PRIV => CREATE ANY TABLE grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES_EMP grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES_LOC grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES_SAL grantable => NO
ROLE => ROLE_PARENT which contains =>
ROLE => ROLE_CHILD01 which contains =>
ROLE => ROLE_CHILD02 which contains =>
TABLE PRIV => ALTER object => MTEST.EDS grantable => NO
TABLE PRIV => DEBUG object => MTEST.EDS grantable => NO
TABLE PRIV => DELETE object => MTEST.EDS grantable => NO
TABLE PRIV => FLASHBACK object => MTEST.EDS grantable => NO
TABLE PRIV => INSERT object => MTEST.EDS grantable => NO
TABLE PRIV => ON COMMIT REFRESH object => MTEST.EDS grantable => NO
TABLE PRIV => QUERY REWRITE object => MTEST.EDS grantable => NO
TABLE PRIV => SELECT object => MTEST.EDS grantable => NO
TABLE PRIV => UPDATE object => MTEST.EDS grantable => NO
TABLE PRIV => INSERT object => MTEST.WORK grantable => NO
TABLE PRIV => SELECT object => MTEST.WORK grantable => NO
TABLE PRIV => UPDATE object => MTEST.SALES grantable => NO
TABLE PRIV => SELECT object => MTEST.ABCD grantable => NO
TABLE PRIV => UPDATE object => MTEST.EMP grantable => NO
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
TABLE PRIV => ALTER object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => ALTER object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => DEBUG object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => DEBUG object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => DELETE object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => DELETE object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => FLASHBACK object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => FLASHBACK object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => INDEX object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => INDEX object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => INSERT object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => INSERT object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => ON COMMIT REFRESH object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => ON COMMIT REFRESH object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => QUERY REWRITE object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => QUERY REWRITE object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => REFERENCES object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => REFERENCES object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => SELECT object => SLS.XEROX_EQUIP_PROFILE grantable => NO
TABLE PRIV => SELECT object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => SELECT object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => SELECT object => ENT.XNAC_MSC grantable => NO
TABLE PRIV => UPDATE object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => UPDATE object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => UPDATE object => SLS.XTI_TRADE_OUT_INFO grantable => NO
********************************************************
End of the report
********************************************************
Copy schema with all privileges
Olaf, June 21, 2006 - 9:11 am UTC
Tom,
Thanks again for all you help.
I wonder if there is any way to copy the user from one db to other by using exp/imp fromuser touser and copy all privileges(granted to the user directly and through the roles) without searching dba_role_privs/dba_sys_privs etc on the one system and executing grant on the other.
Thanks in advance
Olaf
June 22, 2006 - 10:42 am UTC
nope, things granted "outside the scope of the schema" (eg: grants to the schema itself!) are not in the schema export.
moving object privs from one DB to other
Maximus in Gladiator :), July 17, 2006 - 5:22 pm UTC
I want to move object privileges from one database to other database. We can generate a dynamic script to move the privs. but my question is what about the grantor. I want to keep the same grantor for the privileges in the destination database.
how can we achieve this?
thanks,
July 18, 2006 - 8:01 am UTC
you would have to have that "grantor" in the destination database and they would have to run the appropriate privileges.
default roles and user defined roles
A reader, July 18, 2006 - 11:38 am UTC
How can i distiguish default roles and userdefined roles?
Just curious, is i am missing any view?
July 19, 2006 - 8:13 am UTC
what is the difference to you between them?
All roles are "user defined", we defined a couple - you define more, they are all user defined.
Sasi Kumar
Sasikumar, October 12, 2006 - 3:51 pm UTC
It is very useful.
Tom, I need one more help from you. Is there any qeury to find the privileges of the functions?
October 13, 2006 - 6:57 am UTC
I don't know what you mean by that.
Fuctions and Privileges
Sasi Kumar, October 13, 2006 - 6:01 pm UTC
Hi Tom,
There is function in database.. I am dropping the function and again I am creating the same function in that database.
But while recreating the function .. I want to grant the same privileges that the function had previously(Other then create or replace). Is there is any query to capture the privilege on function??
Thanks for the Help in advance
October 13, 2006 - 7:20 pm UTC
don't drop it, create or replace it.
done.
Shannon St. Dennis, January 04, 2007 - 5:40 pm UTC
I am looking at taking this script or idea, and finding all users who have been granted the DBA role somewhere down the line (ie. either directly, or through a role, or through a role granted to a role, etc).
so basically, is there someway to return the top level record in the hierarchy, when the 'DBA' role is found anywhere in the tree?
January 05, 2007 - 9:40 am UTC
how about the query given in the original answer?
Thanks to velu for his useful code showing Hierarchically Granted roles and privs
A reader, March 30, 2007 - 7:39 pm UTC
Blatantly Plagiarised Script!
Tony Killen, March 30, 2010 - 4:52 am UTC
To "A reader" above:
You shouldn't thank Velu for his useful code. You should thank Pete Finnegan and his really helpful security website from where Velu copied the code and removed all the credits and copyright notices.
http://www.petefinnigan.com/find_all_privs.sql Pete says on his website that his script is free to use as long as you don't remove the copyright notices. Velu could have just posted the link.
So credit where credit is due - Thanks Pete, nice script. And thanks Tom, nice recursive script too. I'm using both of them.
April 05, 2010 - 10:42 am UTC
Thanks for pointing that out. appreciate that.
What about a recursive script to check all roles privs?
Roberta, April 11, 2011 - 1:36 pm UTC
Hi Tom,
I'm trying to find a specific script to check all databases roles privs. I tried to create some scripts but I don't have much success. :(
My problem is. I work in a company that have many databases, and some of these, have roles granted to roles, granted to roles,... I need a script that show me a roles list with the sum of all sys and tab privs recursively.
Do you have any tip for this?
I can send my last script if it's useful (but it's not working perfectly)
Thank very much for your time and help! :)
April 13, 2011 - 9:05 am UTC
that was sort of the original question way back at the top of the page - and it seems the answer I gave way back when answers your question - doesn't it???
Why does this not work?
A reader, January 26, 2012 - 4:38 pm UTC
I wanted a report that looked like this with roles, users
with those roles, and then privilges
ROLE USERA TABLEA INSERT
USERB TABLEA UPDATE
TABLEA DELETE
select c.granted_role, a.grantee, a.TABLE_NAME, a.PRIVILEGE
from dba_tab_privs a,
dba_role_privs c
where a.grantee=c.grantee
Seems close, but it gives different results for each user,
which tells me it is somehow not getting the actual role privileges.
January 31, 2012 - 3:18 pm UTC
SQL works like a "spreadsheet", a table (query, view...) is 'square'
you will have a different number of users than you will have a number of tables with privileges.
It will be hard to make that "square".
I don't now how you got that output - your query sure doesn't give that sort of output.
can you show your work as a cut and paste from SQL*Plus
Also, describe in detail what you want - your 'pictogram' above doesn't convey to me a set of requirements, I'm not sure what you want (since you give me a query that does not work, I'll need to know every thing there is to know in order to generate a query)
list of users
Latha, February 21, 2013 - 1:19 pm UTC
How do I find list of database users who have access to a particular schema? there are roles defined in the database and privileges to tables and pl/sql procedures/triggers/functions are granted to the user ids through roles. I was looking at dba_tab_privs but how can I get a comprehensive list as the roles are involved. Thank you for your time.
February 25, 2013 - 10:29 am UTC
I just whipped this together - literally just now, it is not extensively field tested - that is for you to do ;)
ops$tkyte%ORA11GR2> variable owner varchar2(30)
ops$tkyte%ORA11GR2> exec :owner := 'HR'
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> with init_users
2 as
3 (
4 select grantee
5 from dba_tab_privs
6 where owner = :owner
7 union
8 select grantee
9 from dba_sys_privs
10 where privilege = 'SELECT ANY TABLE'
11 ),
12 final_list
13 as
14 (
15 select grantee
16 from dba_role_privs
17 start with granted_role in (select * from init_users)
18 connect by prior grantee = granted_role
19 union
20 select *
21 from init_users
22 )
23 select grantee
24 from final_list
25 where grantee in (select username from dba_users)
26 /
GRANTEE
------------------------------
FB_DEMO
BIG_TABLE
OPS$TKYTE
OLAPSYS
WMSYS
MDSYS
SYSTEM
SYS
NORM
OPS$ORA11GR2
DIY
11 rows selected.
we start by getting anyone with any privs on the given schema (lines 4-6). These include users and roles.
We also add in anyone with 'SELECT ANY TABLE' (probably need to add some other privs to that like execute any procedure and the like as they would have access to procedures in the schema and so on)
Now, init_users has a distinct list of all users and roles with direct access to the schema in some way shape or form.
We take that and do a connect by query on dba_role_privs to get the list of roles granted to users/roles granted to users/roles and so on. Add to that the initial list of users and we end up with a distinct list of users and roles.
if we created some hierarchical roles - a, b, c - gave them a grant and granted that to a user:
ops$tkyte%ORA11GR2> create role a;
Role created.
ops$tkyte%ORA11GR2> create role b;
Role created.
ops$tkyte%ORA11GR2> create role c;
Role created.
ops$tkyte%ORA11GR2> grant select on hr.employees to c;
Grant succeeded.
ops$tkyte%ORA11GR2> grant c to b;
Grant succeeded.
ops$tkyte%ORA11GR2> grant b to a;
Grant succeeded.
ops$tkyte%ORA11GR2> grant a to scott;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> with init_users
2 as
3 (
4 select grantee
5 from dba_tab_privs
6 where owner = :owner
7 union
8 select grantee
9 from dba_sys_privs
10 where privilege = 'SELECT ANY TABLE'
11 ),
12 final_list
13 as
14 (
15 select grantee
16 from dba_role_privs
17 start with granted_role in (select * from init_users)
18 connect by prior grantee = granted_role
19 union
20 select *
21 from init_users
22 )
23 select grantee
24 from final_list
25 where grantee in (select username from dba_users)
26 /
GRANTEE
------------------------------
FB_DEMO
BIG_TABLE
SCOTT
OPS$TKYTE
OLAPSYS
WMSYS
MDSYS
SYSTEM
SYS
NORM
OPS$ORA11GR2
DIY
12 rows selected.
we all of a sudden pick up that user...