 | |
Re: Role/Security Question
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Role/Security Question -
06-20-2008
, 03:49 AM
|
Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). Also, every schema has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? |
With a view on some other schema, you indirectly expose data from that
schema to others in your schema. That's why you need the grant option
Shakespeare
#2
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
#3
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
#4
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
#5
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
#6
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
#7
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
#8
| |||
| |||
|
|
"Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> schreef in berichtnews:F3D6k.10505$uE5.689 (AT) flpi144 (DOT) ffdc.sbc.com... Oracle 10.2.0.3.0 Windows Server 2003 In our database the security has been wide open (every table has been granted SELECT TO PUBLIC). *Also, every schema *has had privileges such as SELECT ANY TABLE granted to them. We're going to be having outside users start using the database and there are only certain tables they're supposed to have access to. I've been trying to revoke PUBLIC from all the tables and grant privileges through roles. For the most part it's going okay but we've received an insufficient privilege violation for a schema trying to select from tables owned by other schemas to create a view. *The schema has the proper role to select from the tables but it wouldn't work until I granted the SELECT ANY TABLE privilege. What am I doing wrong? Grant select on the underlying tables with grant option. With a view on some other schema, you indirectly expose data from that schema to others in your schema. That's why you need the grant option Shakespeare- Hide quoted text - - Show quoted text - |
will require a direct grant with the "with grant option" clause.
Also stored functions, packages, and procedure will require that the
stored object owner have a direct grant from the table/object owner to
reference objects owned by another user.
HTH -- Mark D Powell --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.