Re: sysdba privileges and shutdown

On Mon, 10 Mar 2003 04:07:39 +0000, Rachel Wilson wrote:

> Apologies to Mr Rogers for thinking no-one had answered my query.
> Thanks very much

No worries.

>
> This table system_privilege_map... found it, but now i'm wondering,
> why is there no mention of it when doing 'select * from dict'

Because select * from dictionary shows you all the VIEWS in the data dictionary. SYSTEM_PRIVILEGE_MAP is a table in its own right.  

> also, would you know which tables this links to, i'm interested to see
> the "drill" down privileges for the sysdba priv

Sure. Have a look at sql.bsq in ORACLE_HOME/rdbms/admin. This is the script which is run implicitly when you issue the 'create database' command.

Mine says:

create table SYSTEM_PRIVILEGE_MAP (

        PRIVILEGE     number not null,
        NAME          varchar2(40) not null,
        PROPERTY      number not null /* 0x01 = do not export this privilege */
                                      /* using sql statements */
        )

Followed shortly therafter by:

insert into SYSTEM_PRIVILEGE_MAP values (-3, 'ALTER SYSTEM', 0); insert into SYSTEM_PRIVILEGE_MAP values (-4, 'AUDIT SYSTEM', 0);

and so on and on.

So the table isn't derived from anywhere, but is created and populated as per the script invoked by database creation.

Meaning that there's nothing to drill down to. You can't decompose SYSDBA privilege into something else, because it is a privilege not a role.

I'll give you another example. Look at the following privileges:

       -40 CREATE TABLE                                      0
       -41 CREATE ANY TABLE                                  0
       -42 ALTER ANY TABLE                                   0
       -43 BACKUP ANY TABLE                                  0
       -44 DROP ANY TABLE                                    0
       -45 LOCK ANY TABLE                                    0

Notice anything? There's a 'create table' privilege (meaning you can create tables in your own schema) and a 'create any table' privilege (meaning you can create tables in other people's schemas).

Now: notice there's a 'drop any table' privilege. So where's the 'drop table' privilege?

Answer: there isn't one. If I give you the right to create tables in your own schema, it's assumed that you have the right to drop those tables, not granted explicity as a privilege. So 'create table' privilege actually implies 'drop table'. Likewise, have a look here:

       -71 CREATE ANY INDEX                                  0
       -72 ALTER ANY INDEX                                   0

So where's the 'create index' privilege? Answer: there isn't one. Again, it is assumed that what you do in your own schema is up to you. If you've the right to create a table, it's implicit that you've the right to slap indexes on them in your own schema, too. So 'create ANY index' is an explicit privilege, but 'create index' is implied by 'create table'.

Well, the mother of all implications is the SYSDBA privilege. Granting it implies that you can startup, shutdown, backup cold, backup hot, perform complete recoveries, perform incomplete recoveries, and create databases. You can also put the database into archivelog mode.Also implied is 'restricted session'. And also implied is that you are the administrator of all these privileges, so you can grant them to others (though granting SYSDBA to someone else also requires that you have an exclusive password file).

SYSOPER is also a system privilege that implies you can startup, shutdown, backup cold, perform complete recoveries, put the database into archivelog mode and have restricted session. It does not imply the ability to perform hot backups, the ability to create databases, or the ability to perform incomplete recoveries (which involve data loss). And you merely possess these privileges, but can't administer (ie, grant them to, or revoke them from, others).

Point being, there is no explicit privilege for 'create database'. It's implied by the SYSDBA privilege. And the same is true for many of the other 'privileges' I've mentioned, too. So there's nothing to drill down into: you're just supposed to know this stuff (and I have no idea where you're supposed to know it *from*!!).

>
> and did you have any clarification about the shutdown command

Not really. I didn't understand the question.

>
> (Fair enough rgarding the unix privileges by the way - our security
> sucks :) )
>
> Thanks again,
> Rachel

No problems.

Regards
HJR Received on Mon Mar 10 2003 - 11:51:09 CST