oracle security information.

marrowyung
marrowyung used Ask the Experts™
on
hi,

how can I find out in Oracle :

 The Number of Users:
      i) Administrator
      ii) User can insert/update/delete data
      iii) Read only user
Any group security applied?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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');

Open 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 ?
johnsoneSenior Oracle DBA

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
johnsoneSenior Oracle DBA

Commented:
"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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.
johnsoneSenior Oracle DBA

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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  ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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 ?
johnsoneSenior Oracle DBA

Commented:
No, look at it.  It will give a count of users that are granted the privilege through a role.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You can grant privs directly to a user or a role.

To get a complete list you will need several views:
dba_sys_privs
dba_tab_privs
dba_role_privs


There might be one or two more.  There are scripts posted all over the web that show how do do this.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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')

Open 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');

Open 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

Open 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.

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

CISUAT-user-role.jpg
"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'))

Open 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 ?
johnsoneSenior Oracle DBA

Commented:
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' ); 
; 

Open 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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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');

Open 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' ); 

Open in new window


result also 0, match what I found out yesterday ,so really no read only ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
johnsoneSenior Oracle DBA

Commented:
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' ); 

Open 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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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>

Open in new window

johnsoneSenior Oracle DBA
Commented:
I would agree that ANY TABLE privileges are out there, but if you are subject to any type of auditing, you really cannot use them.  That is one of the top things they look for.

I gave samples that should have the basic logic of what needs to be done with how I defined the different sets of privileges.  Someone with the experience of the author should be able to tweak them as needed.

I have asked for strict definitions of what those different sets of rules mean, but as of yet, I don't see anything posted, except a question as to whether quota defines a read only user.  I don't think it does, because I have seen scripted user created that automatically grant some sort of quota on a user or some other ancillary tablespace.

If the definition of what you need to look for can be defined, I'll see what I can do, but constantly changing the requirements isn't helpful to anyone.
Most Valuable Expert 2012
Distinguished Expert 2018

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

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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' ); 

Open in new window


answer is not 0, is it making any sense ?

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

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>c.privilege NOT IN ( 'UPDATE', 'INSERT', 'DELETE','create', 'drop', 'execute' );

Be careful of your case of your strings.  Oracle converts ALL object names to upper case unless you force it to case sensitive (which you should NEVER do).

Check out what is out there and adjust accordingly:
select privilege, count(*) from dba_tab_privs group by privilege;

>>for read only , this one might help:

I need to make sure you understand this but it is the last time I'm going say it:
That query only involves privs through ROLEs.  There are several other methods to have READ ONLY users.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
Senior Oracle DBA
Commented:
All the pieces to do what you asked are in this thread.  You really should try to put it together.
SELECT username 
FROM   (SELECT a.username 
        FROM   dba_users a 
               join dba_tab_privs b 
                 ON b.grantee = a.username 
        WHERE  b.privilege = 'SELECT' 
        UNION ALL 
        SELECT 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 = 'SELECT' 
        UNION ALL 
        SELECT grantee 
        FROM   dba_users a 
               join dba_sys_privs b 
                 ON b.grantee = a.username 
        WHERE  b.privilege = 'SELECT ANY TABLE') 
MINUS 
(SELECT a.username 
 FROM   dba_users a 
        join dba_tab_privs b 
          ON b.grantee = a.username 
 WHERE  b.privilege IN ( 'UPDATE', 'INSERT', 'DELETE' ) 
 UNION ALL 
 SELECT 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' ) 
 UNION ALL 
 SELECT grantee 
 FROM   dba_users a 
        join dba_sys_privs b 
          ON b.grantee = a.username 
 WHERE  b.privilege IN ( 'UPDATE ANY TABLE', 'INSERT ANY TABLE', 
                         'DELETE ANY TABLE' )); 

Open in new window

NOTE:  This does not handle nested roles.  If you need to check for that, you can modify it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
oh,

this is the script for finding out read only users, tks.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial