oracle security information.

If your application has its own user table, then you have to query that.  Nobody could tell you how to do that.

What is the definition of all those things?  If these queries are correct, I didn't test them.  You should be able to figure out any syntax issues.

I define administrator as anyone who has the DBA role, so:

select count(1) from dba_role_privs where granted_role = 'DBA'

INSERT/UPDATE/DELETE direct grants:

select count(distinct a.user) from dba_users a join dba_tab_privs b on b.grantee = a.username where c.privilege in ('UPDATE','INSERT','DELETE)

INSERT/UPDATE/DELETE through a role:

select count(distinct a.user) from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in ('UPDATE','INSERT','DELETE)

You can combine those 2 if you want.

If a user can INSERT/UPDATE/DELETE through a procedure, it is very likely that the privilege is hidden through the code and not able to query through the dictionary.

Read only, i.e. only direct grants are SELECT.

select count(1) from (select a.user from dba_users a join dba_tab_privs b on b.grantee = a.username where c.privilege = 'SELECT' minus select a.user from dba_users a join dba_tab_privs b on b.grantee = a.username where c.privilege in ('UPDATE','INSERT','DELETE))

Given the other queries, if you want read only through a role, you should be able to modify the above for that.

What do you mean by security group?  Quite possibly you are looking for DBA_PROFILES, but not sure.

"If your application has its own user table, then you have to query that.  Nobody could tell you how to do that"

any script for it? I am digging into SQL developer can see if it can help me.

"select count(1) from dba_role_privs where granted_role = 'DBA'"

tks. but seems only one account is return , which is SYSTEM.

"select count(distinct a.user) from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in ('UPDATE','INSERT','DELETE)"

this must change to count(distinct user) or it won't work.

the first query and the second one only return 1, which is ONLY SYSTEM account, not making sense it seems.

"Read only, i.e. only direct grants are SELECT."


why don't just this:

select distinct user from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee
where c.privilege in('SELECT');

Select allOpen in new window

and my result on read only show again only 1 account, not right.

"What do you mean by security group?"

oracle do not use group permission to control security ?

I have no idea how your application works.  You should know that.  If user access by the application is controlled by application tables, you should be able to query them, but I can't tell you how to do that.

One user with the DBA role doesn't seem that odd to me.  It's your database.  If you define administrator differently, then sure it is possible.  That is why the question in the original post asked how you defined administrator and clearly stated what was being shown as administrator.

There is no difference between these 2 queries:

select count(distinct a.user) from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in ('UPDATE','INSERT','DELETE)

select count(distinct user) from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in ('UPDATE','INSERT','DELETE)

I don't see why you need to remove the alias.  Again, as I clearly stated, I just typed them in and didn't test them.  If there are errors, you should have the knowledge to fix them.

This query will not work for read only users:

select distinct user from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in('SELECT');

A user that has UPDATE/INSERT/DELETE privileges typically also has SELECT, that would negate read only.

Again, if access is controlled at the application level, then you won't see these things.

If the application connects as the owner of the objects (a very bad practice, but it is done), then no privileges are required as you own the objects and don't require privileges.

Still not sure what you are looking for with group security.  It may be roles, which I believe I accounted for in the queries.

"I have no idea how your application works.  You should know that.  "

I am not talking about application, only on DB level.

"I don't see why you need to remove the alias.  Again, as I clearly stated, I just typed them in and didn't test them.  If there are errors, you should have the knowledge to fix them"

they are accurate enough in some sense. I remove the alias to make it works

"Still not sure what you are looking for with group security. "

can I define  a group in oracle so that I apply all necessary permission on it, then I add users need that permission in that group, they will get all mission applied to that group, this is what it means.

"If your application has its own user table, then you have to query that.  Nobody could tell you how to do that"

any script for it? I am digging into SQL developer can see if it can help me.

You asked for a script for an application user table.  We can't give that to you.  We don't know your application.

What you are calling group security is called a role in every database that I am aware of.  I just searched Oracle, DB2, MySql, SQL Server and MariaDB.  They all call it a role.

>>can I define  a group in oracle so that I apply all necessary permission on it

I also believe you are referring to ROLEs as well but wanted to muddy the waters with RESOURCE PLANS.  You can set up specific resource management to limit things like CPU, TIME, etc...  That way no one can use all available resources and bring the system to its knees.

That is what I was trying to get at too, profiles and resource plans and other things can limit a user as well, trying to find out what type of things that they are looking for, but as per usual, no straight answers.  The reason I went away from roles in the first place was that someone who claims to use all these different databases and be knowledgeable in them should know about roles.

johnsone,

"You asked for a script for an application user table."

no..... it is system wise information.  we don't build one to store user permission.

I want to check the existing permission of each of the schema.

"What you are calling group security is called a role in every database that I am aware of."

ok, then a role should I say. I am still referring to window server concept. so yes, a role in database system.

my word is a bit diff.

So anyway to show out role security information  ?

"INSERT/UPDATE/DELETE through a role:

select count(distinct a.user) from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in ('UPDATE','INSERT','DELETE)"

so this one is going to say which ROLE has Read only , update/insert/delete priviliege ?

No, look at it.  It will give a count of users that are granted the privilege through a role.

hi,

I run this again:

select count(distinct user) from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee where c.privilege in ('UPDATE','INSERT','DELETE')

Select allOpen in new window

but I do in this way:

select *  from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee
where c.privilege in ('UPDATE','INSERT','DELETE');

Select allOpen in new window

and it is funny that both result is not the same

the first one shows only one answer: SYSTEM.

The second one return a lot of thing and only 3x accounts is there, and I am not sure why the rest of schema I imported from other oracle is not shown.

SYS	0		OPEN		03-APR-19	SYSTEM	TEMP	TEMP	08-MAR-17	DEFAULT	SYS_GROUP		11G 12C 	N	PASSWORD	N	YES		Y	YES	USING_NLS_COMP	NO	NO	SYS	DATAPUMP_IMP_FULL_DATABASE	YES	NO	YES	YES	YES	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$sYAWrhX5THKRHLtiZZyevw==$0	SYS	INSERT	NO	NO	NO	TABLE	NO
GSMADMIN_INTERNAL	21		EXPIRED & LOCKED	08-MAR-17	08-MAR-17	SYSAUX	TEMP	TEMP	08-MAR-17	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	YES		Y	YES	USING_NLS_COMP	NO	NO	GSMADMIN_INTERNAL	DATAPUMP_IMP_FULL_DATABASE	NO	NO	YES	YES	YES	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$sYAWrhX5THKRHLtiZZyevw==$0	SYS	INSERT	NO	NO	NO	TABLE	NO
TONY	177		OPEN		08-APR-19	USERS	DEV_IAS_TEMP	DEV_IAS_TEMP	10-OCT-18	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	NO	29-OCT-18 11.59.39.000000000 AM ASIA/SHANGHAI	N	NO	USING_NLS_COMP	NO	NO	TONY	DATAPUMP_IMP_FULL_DATABASE	NO	NO	YES	NO	NO	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$sYAWrhX5THKRHLtiZZyevw==$0	SYS	INSERT	NO	NO	NO	TABLE	NO
SYS	0		OPEN		03-APR-19	SYSTEM	TEMP	TEMP	08-MAR-17	DEFAULT	SYS_GROUP		11G 12C 	N	PASSWORD	N	YES		Y	YES	USING_NLS_COMP	NO	NO	SYS	DATAPUMP_IMP_FULL_DATABASE	YES	NO	YES	YES	YES	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$sYAWrhX5THKRHLtiZZyevw==$0	SYS	UPDATE	NO	NO	NO	TABLE	NO
GSMADMIN_INTERNAL	21		EXPIRED & LOCKED	08-MAR-17	08-MAR-17	SYSAUX	TEMP	TEMP	08-MAR-17	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	YES		Y	YES	USING_NLS_COMP	NO	NO	GSMADMIN_INTERNAL	DATAPUMP_IMP_FULL_DATABASE	NO	NO	YES	YES	YES	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$sYAWrhX5THKRHLtiZZyevw==$0	SYS	UPDATE	NO	NO	NO	TABLE	NO
TONY	177		OPEN		08-APR-19	USERS	DEV_IAS_TEMP	DEV_IAS_TEMP	10-OCT-18	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	NO	29-OCT-18 11.59.39.000000000 AM ASIA/SHANGHAI	N	NO	USING_NLS_COMP	NO	NO	TONY	DATAPUMP_IMP_FULL_DATABASE	NO	NO	YES	NO	NO	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$sYAWrhX5THKRHLtiZZyevw==$0	SYS	UPDATE	NO	NO	NO	TABLE	NO
SYS	0		OPEN		03-APR-19	SYSTEM	TEMP	TEMP	08-MAR-17	DEFAULT	SYS_GROUP		11G 12C 	N	PASSWORD	N	YES		Y	YES	USING_NLS_COMP	NO	NO	SYS	DATAPUMP_IMP_FULL_DATABASE	YES	NO	YES	YES	YES	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$p2TBztzdTPavBz3LZWaMfg==$0	SYS	INSERT	NO	NO	NO	TABLE	NO
GSMADMIN_INTERNAL	21		EXPIRED & LOCKED	08-MAR-17	08-MAR-17	SYSAUX	TEMP	TEMP	08-MAR-17	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	YES		Y	YES	USING_NLS_COMP	NO	NO	GSMADMIN_INTERNAL	DATAPUMP_IMP_FULL_DATABASE	NO	NO	YES	YES	YES	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$p2TBztzdTPavBz3LZWaMfg==$0	SYS	INSERT	NO	NO	NO	TABLE	NO
TONY	177		OPEN		08-APR-19	USERS	DEV_IAS_TEMP	DEV_IAS_TEMP	10-OCT-18	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	NO	29-OCT-18 11.59.39.000000000 AM ASIA/SHANGHAI	N	NO	USING_NLS_COMP	NO	NO	TONY	DATAPUMP_IMP_FULL_DATABASE	NO	NO	YES	NO	NO	DATAPUMP_IMP_FULL_DATABASE	SYS	BIN$p2TBztzdTPavBz3LZWaMfg==$0	SYS	INSERT	NO	NO	NO	TABLE	NO

Select allOpen in new window

One account shown is create by me AFTER import, so this account is supposed to be grant through role ? I just assign permission over there.


but this one seems grants through role too but second script don't show it out.


"Again, if access is controlled at the application level, then you won't see these things."

Actually don't know how this kind permission controlled in application level, you mean use application to control what a use can do in the application in terms of application user behavior control ? so it don't even controlled by the DB at all? it is controlled by system permission ?

the read only user query should be that:

select count (*) from (select user from dba_users a join dba_tab_privs b on a.username = b.grantee
where b.privilege in ('SELECT')
minus 
select user from dba_users a join dba_tab_privs b on a.username  = b.grantee
where b.privilege in ('UPDATE','INSERT','DELETE'))

Select allOpen in new window

agree? but result is 0,  I am wondering...

do you know if read-only user can  be list out by SQL developer ?

Like I said, don't remove the column alias and I typed them in, I didn't test them.  The column name in DBA_USERS was referenced incorrectly.  But, you should have been able to figure that out.

This is the corrected query for read write privileges through a role.

SELECT Count(DISTINCT a.username) 
FROM   dba_users a 
       JOIN dba_role_privs b 
         ON a.username = b.grantee 
       JOIN dba_tab_privs c 
         ON b.granted_role = c.grantee 
WHERE  c.privilege IN ( 'UPDATE', 'INSERT', 'DELETE' ); 
; 

Select allOpen in new window

Only change is to correct the column that was being selected.  Honestly, I have no idea why you couldn't do that yourself.  Set up a simple test and it seems to be working for me.

"Honestly, I have no idea why you couldn't do that yourself.  Set up a simple test and it seems to be working for me."

if I don't change it and debug it part by part, I will keep seeing error.

I am now worry more on read only right as i said the result return 0 last night, so this mean no read only role but read and write ?

"This is the corrected query for read write privileges through a role."
tried that and it seems one more ; sign, I removed it.

the result is the same as what I do yesterday by this:

select count(distinct username)  from dba_users a join dba_role_privs b on a.username = b.grantee join dba_tab_privs c on b.granted_role = c.grantee
where c.privilege in ('UPDATE','INSERT','DELETE');

Select allOpen in new window

I am happy with it.

for read only , if I do this:

SELECT Count(DISTINCT a.username) 
FROM   dba_users a 
       JOIN dba_role_privs b 
         ON a.username = b.grantee 
       JOIN dba_tab_privs c 
         ON b.granted_role = c.grantee 
WHERE  c.privilege IN ( 'select' ); 

Select allOpen in new window

result also 0, match what I found out yesterday ,so really no read only ?

i am told that a user  with no tablespace quotas and no insert, update, delete, create, or alter privs. can a read only user, so any script can specify based on  tablespace quotas ?

This query:

SELECT Count(DISTINCT a.username) 
FROM   dba_users a 
       JOIN dba_role_privs b 
         ON a.username = b.grantee 
       JOIN dba_tab_privs c 
         ON b.granted_role = c.grantee 
WHERE  c.privilege IN ( 'select' ); 

Select allOpen in new window

Would return no rows in every Oracle database in existence.  It would also have nothing to do with read only privileges.  If corrected, it would give a count of users that have been granted select privileges through a role.  However, they could have other privileges, like insert, update and delete.

You can look at quota, but you also have to account for the system privilege that overrides quota.

You are only focusing on ONE aspect of looking at privileges.  To get what you really are after you need multiple queries against multiple views.

For example:  Look at the output below.  I created a table and used a fairly dangerous way of giving someone read only access to ALL tables.  Then connected as that user and queried my table to show I can.

Now run your query and see if FRED is in the list.  Nope.  The priv wasn't granted through a role...

SQL> create table tab1(col1 char(1));

Table created.

SQL> insert into tab1 values('a');

1 row created.

SQL> commit;

Commit complete.

SQL> create user fred identified by flintstone;

User created.

SQL> grant create session,select any table to fred;

Grant succeeded.

SQL> conn fred/flintstone@orcl

Connected.

SQL> select * from scott.tab1;
a

SQL>

Select allOpen in new window

I'm just pointing out to marrowyung that focusing on that single query isn't all inclusive of just looking at who has SELECT.

I realize that wasn't directed at me.  I had given 5 different queries to consider for privileges.  Untested (and I noted that).  Should be simple enough to fix any syntax issues or incorrect column names for someone that seems to have the experience of the author.

hi,

for read only , this one might help:

SELECT Count(DISTINCT a.username) 
FROM   dba_users a 
       JOIN dba_role_privs b 
         ON a.username = b.grantee 
       JOIN dba_tab_privs c 
         ON b.granted_role = c.grantee 
WHERE  c.privilege NOT IN ( 'UPDATE', 'INSERT', 'DELETE','create', 'drop', 'execute' ); 

Select allOpen in new window

answer is not 0, is it making any sense ?

but this also count any account owner by oracle system itself, right ?

Depends on how you define read only.

That counts users that have at least one privilege not on that list.  It doesn't exclude users that have a privilege on that list.

johnsone,

"Depends on how you define read only."

yeah, someone ask me this too.. :):)  this is SELECT only, any other right you can think of all is read only ?

slightwv,

"There are several other methods to have READ ONLY users."

direct grants you mean ?

"select privilege, count(*) from dba_tab_privs group by privilege;"

use,REFERENCES, INHERIT PRIVILEGES , DEQUEUE  and read also read only ?

oh,

this is the script for finding out read only users, tks.

tks all.