 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / April 2006public role question
Using SS2000 SP4. I'm trying to lock down the database as much as possible. I created a new sql server login and gave the user no permissions other than public role. Why am I able to create a stored procedure with the new user. I thought Public had select, insert, update, delete permissions. Does that allow a user to create a stored procedure? Thanks,  SignatureDan D. I ran this query EXEC sp_helprotect NULL, 'public' and saw that public has the permissions to create Default, create function, create procedure, create rule, create table, create view, backup database, and backup transaction. I created a new database and these public did not have these permissions so I guess public doesn't have them by default. Is it better to revoke or deny those permissions to public? Thanks, SignatureDan D. > Using SS2000 SP4. I'm trying to lock down the database as much as possible. I > created a new sql server login and gave the user no permissions other than [quoted text clipped - 5 lines] > > Thanks, In the new database that I created, I was able to create a stored procedure. I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create procedure" permisson listed under the action column. How am I able to create a stored procedure with a user that is only a member of public and has no other permissions? SignatureDan D. > Using SS2000 SP4. I'm trying to lock down the database as much as possible. I > created a new sql server login and gave the user no permissions other than [quoted text clipped - 5 lines] > > Thanks, What does the query 'SELECT USER' return? SignatureHope this helps. Dan Guzman SQL Server MVP > In the new database that I created, I was able to create a stored > procedure. [quoted text clipped - 16 lines] >> >> Thanks, It returns 'dbo'. SignatureDan D. > What does the query 'SELECT USER' return? > [quoted text clipped - 18 lines] > >> > >> Thanks, Hi Dan The user dbo is a privileged user and can create tables and procs, execute procs, and access data in all the tables. You should try to revoke permissions from the user dbo. You have said you created a new user. How are you trying to connect as that new user? SignatureHTH Kalen Delaney, SQL Server MVP www.solidqualitylearning.com > It returns 'dbo'. > [quoted text clipped - 23 lines] >> >> >> >> Thanks, The new user that I created in the new database I connected to through QA and was able to create a stored procedure. If I run EXEC sp_helprotect NULL, 'public', the only permissions that public has is for "select" to 18 system tables. If I run "select user" (and what does this tell me - who owns the database?), I get 'dbo'. So I'm wondering how I'm able to create a stored procedure if public doesn't have permissions and the new user is only in the public role with no other permissions? Are you saying that the permissions to create the stored procedure are coming from dbo so I need to revoke them from 'dbo'? The ultimate objective is to be able to create a login/user that a web app will use and the user should only have permissions for "select" to the views and "execute" on the stored procedures with no permissions on the underlying tables. Would I need to revoke insert, update, and delete permissions from public in this case? Thanks for you help, SignatureDan D. > Hi Dan > [quoted text clipped - 32 lines] > >> >> > >> >> Thanks, If 'select user' is returning DBO, it means you are connecting AS DBO, and not as your new user. You ARE the special DBO user, and not the new user, which is why you have all these permissions. You are not connecting as your new user and not testing the permissions the new user has. So again, please tell us exactly HOW you are trying to connect as the new user. If you are connecting through QA, please tell us what you are putting in every box in the connection dialog. (Telling you that dbo 'owns' the database would be meaningless. DBO is a user name. It is the name always given to whatever login is the owner of the database, when that login connects to the database. Please read about logins and user names in the Books Online.) SignatureHTH Kalen Delaney, SQL Server MVP www.solidqualitylearning.com > The new user that I created in the new database I connected to through QA > and [quoted text clipped - 62 lines] >> >> >> >> >> >> Thanks, I'm sorry. I didn't understand what Dan was asking me to do. If I log in through QA using the new user and run "select user" I get "dantest" which is the new user. Then I am able to create a stored procedure. If I run 'EXEC sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which to report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18 system tables with select permissions. SignatureDan D. > If 'select user' is returning DBO, it means you are connecting AS DBO, and > not as your new user. [quoted text clipped - 78 lines] > >> >> >> > >> >> >> Thanks, Sorry I didn't make my instructions clearer. A common cause of elevated permissions is that users are members of the 'BUILTIN\Administrators' Windows group and thereby get sysadmin permissions. User 'dbo' would be returned in that case. Since 'SELECT USER' returns 'dantest' instead of'dbo', we know 'dantest' is not a sysadmin role member and is not the database owner. That means one of the following must be true for the user to create procs: 1) The user is a member of the db_owner role. You can check this with sp_helprolemember 'db_owner' 2) The user is a member of the db_ddladmin role. You can check this with sp_helprolemember 'db_ddladmin' 3) The user (or a role the member belongs to) was granted CREATE PROCEDURE permissions. You can check this with sp_helprotect 'CREATE PROCEDURE' SignatureHope this helps. Dan Guzman SQL Server MVP > I'm sorry. I didn't understand what Dan was asking me to do. If I log in > through QA using the new user and run "select user" I get "dantest" which [quoted text clipped - 102 lines] >> >> >> >> >> >> >> >> Thanks, I logged on to the database through QA as dantest. I ran sp_helprolemember 'db_owner' and one row was returned with 'db_owner' in the dbrole column. I ran sp_helprolemember 'db_ddladmin' and no rows were returned. I ran sp_helprotect 'CREATE PROCEDURE' and "There are no matching rows on which to report." was returned. SignatureDan D. > Sorry I didn't make my instructions clearer. A common cause of elevated > permissions is that users are members of the 'BUILTIN\Administrators' [quoted text clipped - 120 lines] > >> >> >> >> > >> >> >> >> Thanks, What database are you in? Who is the owner of the database? SignatureHTH Kalen Delaney, SQL Server MVP www.solidqualitylearning.com >I logged on to the database through QA as dantest. I ran sp_helprolemember > 'db_owner' and one row was returned with 'db_owner' in the dbrole column. [quoted text clipped - 153 lines] >> >> >> >> >> >> >> >> >> >> Thanks, I'm logged in to the 'dantest' database as user 'dantest'. When I run sp_helpdb 'dantest' I get 'dantest'. So I guess since dantest owns the database, dantest has permission to create a procedure. One mystery solved. I'm not sure how dantest becamse the owner but that's a mystery for another time. I created another user called 'caroltest' and that user was not able to create a procedure. As a rule if I want to restrict permissions as long as I don't explicitly give public permissions to any objects I should be ok, is that correct? Or if I want to be sure, I could revoke insert, update and delete permissions from public and create a user or role and give insert, update or delete permissions to the user/role. Is that correct? Thanks Kalen and Dan for yuor help, SignatureDan D. > What database are you in? Who is the owner of the database? > [quoted text clipped - 155 lines] > >> >> >> >> >> > >> >> >> >> >> Thanks, Who is the owner of the procedure when user 'dantest' runs the following script? Perhaps that will provide a clue. CREATE PROC TestProc AS SET GO I can't think of another way your 'dantest' user can have CREATE PROCEDURE permissions. Try the following controlled test to see if a newly created login/user can create procedures: EXEC sp_addlogin 'dantest2', 'somepassword', 'MyDatabase' GO USE MyDatabase GO EXEC sp_adduser 'dantest2' GO SETUSER 'dantest2' GO CREATE PROC TestProc AS SET GO SETUSER GO SignatureHope this helps. Dan Guzman SQL Server MVP >I logged on to the database through QA as dantest. I ran sp_helprolemember > 'db_owner' and one row was returned with 'db_owner' in the dbrole column. [quoted text clipped - 153 lines] >> >> >> >> >> >> >> >> >> >> Thanks, Dan, I'm sorry but after I responded to Kalen's email I deleted the test database. Somehow the user 'dantest' was the owner of the database so I guess that is how I was able to create the procedure. I'm not sure how dantest became the database owner because it didn't have any permissions other than public but I'm not concerned about that now. I did create a second user with only the public role for permissions and was not able to create a stored procedure. Thanks for you help, SignatureDan D. > Who is the owner of the procedure when user 'dantest' runs the following > script? Perhaps that will provide a clue. [quoted text clipped - 178 lines] > >> >> >> >> >> > >> >> >> >> >> Thanks, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.